Use Multi Table data for Querystring

[danieljoo]

I am trying to pull data all from the Attendee table and the CustomerName from the Customer Table, below is the code, I can't seem to get it to work when I added the select from Customer stuff:

Attendee
CustomerNumber
AttendeeFirstName
AttendeeLastName

Customer
CustomerNumber
CustomerName


IF Request.Querystring("CustomerNumber&quot= "" then
strsql = "select * from [Attendee],[Customer]"
If dbwhere <> &quot;&quot; Then
strsql = strsql & &quot; WHERE &quot; & dbwhere
strsql = strsql & &quot; WHERE ATTENDEE.CUSTOMERNUMBER=CUSTOMER.CUSTOMERNUMBER&quot;
End If
If OrderBy <> &quot;&quot; then
strsql = strsql & &quot; ORDER BY [&quot; & OrderBy & &quot;] &quot; & Session(&quot;Attendee_OT&quot
End if
ELSE...



The error message &quot;Item cannot be found in the collection corresponding to the requested name or ordinal.&quot; refers to the following lines:

<%
x_ID = rs(&quot;ID&quot
x_CustomerNumber = rs(&quot;CustomerNumber&quot
x_AttendeeFirstName = rs(&quot;AttendeeFirstName&quot
x_AttendeeLastName = rs(&quot;AttendeeLastName&quot
x_AttendeeCity = rs(&quot;AttendeeCity&quot
x_AttendeeState = rs(&quot;AttendeeState&quot
x_CustomerName = rs(&quot;CustomerName&quot
%>

 

[jen89]

I think the problem is that you can't have two &quot;where&quot;s in the same select statement. Right now, if dbwhere is not &quot;&quot;, your statement looks like this:

&quot; WHERE &quot; & dbwhere & &quot; WHERE ATTENDEE.CUSTOMERNUMBER=CUSTOMER.CUSTOMERNUMBER&quot;.

I think you need to delete one of these where clauses, or use different code to distinguish which one you want to use. what would normally be in the variable dbwhere?