Hi All, I'm joining the Orders and Order Details tables in Northwind
DB. I'm not sure of the syntax for the objRS.Open statement. I'm getting compilation errors from everything I use in the statement eg)
new_sql, "Orders", anybody have any ideas how to format it? Here's my code. Thanks all.
<% @LANGUAGE = VBScript %>
<% Option Explicit
Response.Expires = 0
Dim objConn, objRS, strConnection, new_sql
Set objConn = Server.CreateObject("ADODB.Connection")
strConnection = "DSN=Nwind;Database=Nwind;"
strConnection = strConnection & "User ID=sa;Password=;"
objConn.Open strConnection
Set objRS = Server.CreateObject("ADODB.Recordset")
Set objRS.ActiveConnection = ObjConn
new_sql = "SELECT Orders.CustomerID AS CustomerName, [Quantity] * [UnitPrice] AS OrderAmount, [Order Details].ProductID AS ProductOrdered, Orders.EmployeeID AS SalesPerson, Orders.Freight AS FreightCost, [Order Amount] * [1-(Discount/100)] + [Freight] AS TotalCost "
response.write new_sql
new_sql = new_sql & "FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID"
objRS.Open new_sql
%>
<HTML>
<HEAD>
<TITLE>Order Information Table</TITLE>
</HEAD>
<BODY bgcolor="#FFCCFF">
<Table Width = 80%><TD Width = 80% >
<TABLE border=1 bgcolor=#FFFFFF bordercolor= #000033>
<H1 ALIGN=CENTER></H1>
<CellPadding=0 CellSpacing=0>
<TR>
<TH NOWRAP>Customer Name</TH>
<TH NOWRAP>Product Ordered</TH>
<TH NOWRAP>Sales Person</TH>
<TH NOWRAP>Order Amount</TH>
<TH NOWRAP>Freight Cost</TH>
<TH NOWRAP>Total Cost</TH>
</TR>
<% Do While Not objRS.EOF %>
<TR>
<TD><%=objRS("CustomerID")%></TD>
<TD><%=objRS("ProductID")%></TD>
<TD><%=objRS("EmployeeID")%></TD>
<TD><%=objRS("Quantity")%></TD>
<TD><%=objRS("UnitPrice")%></TD>
<TD><%=objRS("Freight")%></TD>
</TR>
<%
objRS.MoveNext
Loop
%>
</TABLE>
</BODY>
</HTML>
<%
objRS.close
objConn.close:
Set objRS = Nothing
Set objConn = Nothing
%>
problem with objRS.Open syntax
