Joining Tables

[Seabelle]

This is a question about my SQL statement on an asp page. The page is a detail page which a user arrives at when they have searched the database and chosen a specific match from the results list. The detail page needs to pull data from two separate tables in the database. There is one field common to both tables and so I am using that to do the join. However, I get an error when clicking on the link from the results page, trying to reach the detail page:

Request object error 'ASP 0102 : 80004005'
Expecting string input
The function expects a string as input.

Any ideas what this means? The field I am passing as a URL parameter is a numeric field (AdNum). I tried changing it to text but that did not make a difference.

Here's the code for the SQL statement:
<%
Dim rsDetail__varAdNum
set rsDetail = Server.CreateObject("ADODB.Recordset")
rsDetail.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=E:\Sullivan\BrettKnowles_com\web\database\Properties.mdb"
rsDetail.Source = "SELECT PropertiesFull.*, Photos.Photo1, Photos.Photo2, Photos.Photo3, Photos.Photo4, Photos.Photo5 FROM PropertiesFull, Photos WHERE Photos.AdNum = PropertiesFull.AdNum AND PropertiesFull.AdNum = " + Replace(rsDetail__varAdNum, "'", "''") + ""
rsDetail.CursorType = 0
rsDetail.CursorLocation = 2
rsDetail.LockType = 3
rsDetail.Open()
rsDetail_numRows = 0
%>

Any suggestions? I'm lost! Why does the detail page not recognize the variable I am passing to it from the results page?

[Seabelle]

I forgot to include this line as well:
MM_paramName = "AdNum"

'D don't shoot me, this detail page is from my use of UltraDev so there is some Macromedia gobbledygook in some of the code. However, I'm hand coding the SQL and everything else I'm doing to the page now.

[Snowbird122]

try this:

rsDetail.Source = "SELECT PropertiesFull.*, Photos.Photo1, Photos.Photo2, Photos.Photo3, Photos.Photo4, Photos.Photo5 FROM PropertiesFull, 
Photos WHERE (Photos.AdNum = PropertiesFull.AdNum) AND (PropertiesFull.AdNum = '" + Replace(rsDetail__varAdNum, "'", "''") + "')"

I added () around the where items and single quotes around the variable at the end.

[Seabelle]

Unfortunately this did not work. Thanks for the idea, though ....

Still confused ....

[Snowbird122]

did you get the same error?

[Seabelle]

Yes, same error.

[Snowbird122]

What database are you using? Is there a way you can query the database directly without asp? This would tell you if the problem is in the query or in the asp.

[dhtmlgod]

Try this:

<%
set rsDetail = Server.CreateObject("ADODB.Recordset") 
rsDetail.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=E:\Sullivan\BrettKnowles_com\web\database\Properties.mdb" 
matchType = Replace(rsDetail__varAdNum, "'", "''")
rsDetail.Source = "SELECT PropertiesFull.*, Photos.Photo1, Photos.Photo2, Photos.Photo3, Photos.Photo4, Photos.Photo5 FROM PropertiesFull, Photos WHERE Photos.AdNum = PropertiesFull.AdNum AND PropertiesFull.AdNum = '" & matchType & "'"
rsDetail.CursorType = 0 
rsDetail.CursorLocation = 2 
rsDetail.LockType = 3 
rsDetail.Open() 
rsDetail_numRows = 0 
%>

[shane]

Try the following Seabelle.

SELECT 	PROP.*, 
	P.Photo1, 
	P.Photo2, 
	P.Photo3, 
	P.Photo4, 
	P.Photo5 
FROM 	PropertiesFull PROP
LEFT OUTER JOIN Photos P ON PROP.AdNum = P.AdNum
WHERE 	PROP.AdNum = <adnum>

Download Winsql and you can use this to run queries against Access. It makes life easier than using the Access query wizard.

PS: I haven't forgot about the upload scripts.

[Seabelle]

Hi Guys,
Thanks for the ideas!

'D, the revised recordset produced this error, "Syntax error in string in query expression."

Shane, I'm pretty sure you were abbreviating (since my code is so terribly long ) but I had trouble following the table name abbreviations you used and it caused the entire recordset to not work. I was interpreting "Prop" as PropertiesFull and "P" as Photos. The part after "Left Outer Join" is where I got stuck ....

My original SQL gives this message, "Type mismatch in expression." The AdNum field is numeric and the photo pathnames are text. I think the mismatch is caused by the parameter AdNum I'm passing from the results page to the detail page. But I can't figure out how to name that variable properly. I also may not have my "Move To Record" piece correct so I'll paste it below as well.

<%
Dim rsDetail__varAdNum
rsDetail__varAdNum = "2008"
if (Request.QueryString("AdNum") <> "") then rsDetail__varAdNum = Request.QueryString("AdNum")
set rsDetail = Server.CreateObject("ADODB.Recordset")
rsDetail.ActiveConnection = MM_connProperties_STRING
rsDetail.Source = "SELECT PropertiesFull.*, Photos.Photo1, Photos.Photo2, Photos.Photo3, Photos.Photo4, Photos.Photo5 FROM PropertiesFull, Photos WHERE (Photos.AdNum = PropertiesFull.AdNum) AND (PropertiesFull.AdNum = '" + Replace(rsDetail__varAdNum, "'", "''") + "')"
rsDetail.CursorType = 0
rsDetail.CursorLocation = 2
rsDetail.LockType = 3
rsDetail.Open()
rsDetail_numRows = 0
%>
<%
' *** Move To Record and Go To Record: declare variables

Set MM_rs = rsDetail
MM_rsCount = rsDetail_total
MM_size = rsDetail_numRows
MM_uniqueCol = "AdNum"
MM_paramName = "AdNum"
MM_offset = 0
MM_atTotal = false
MM_paramIsDefined = false
If (MM_paramName <> "") Then
MM_paramIsDefined = (Request.QueryString(MM_paramName) <> "")
End If

' *** Move To Specific Record: handle detail parameter
If (MM_paramIsDefined And MM_rsCount <> 0) Then

' get the value of the parameter
param = Request.QueryString(MM_paramName)
%>