SitePoint Sponsor |
|
User Tag List
Results 1 to 10 of 10
Thread: Joining Tables
-
Oct 28, 2001, 18:19 #1
- Join Date
- Jul 2001
- Location
- Wilmington, NC
- Posts
- 116
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Joining Tables
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?
-
Oct 28, 2001, 18:33 #2
- Join Date
- Jul 2001
- Location
- Wilmington, NC
- Posts
- 116
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Oct 28, 2001, 19:59 #3
try this:
Code: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, "'", "''") + "')"
http://www.echo-consulting.net - Sound Solutions for Online Inspriations.
-
Oct 28, 2001, 20:34 #4
- Join Date
- Jul 2001
- Location
- Wilmington, NC
- Posts
- 116
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Unfortunately this did not work. Thanks for the idea, though ....
Still confused ....
-
Oct 28, 2001, 20:52 #5
did you get the same error?
http://www.echo-consulting.net - Sound Solutions for Online Inspriations.
-
Oct 28, 2001, 21:11 #6
- Join Date
- Jul 2001
- Location
- Wilmington, NC
- Posts
- 116
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Yes, same error.
-
Oct 29, 2001, 00:52 #7
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.
http://www.echo-consulting.net - Sound Solutions for Online Inspriations.
-
Oct 29, 2001, 04:15 #8
- Join Date
- Jul 2001
- Location
- Scotland
- Posts
- 4,836
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Try this:
Code:<% 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 %>
-
Oct 29, 2001, 04:16 #9
- Join Date
- Jan 2001
- Location
- Milton Keynes, UK
- Posts
- 1,011
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Try the following Seabelle.
Code: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>
PS: I haven't forgot about the upload scripts.Last edited by shane; Oct 29, 2001 at 04:25.
-
Oct 29, 2001, 07:12 #10
- Join Date
- Jul 2001
- Location
- Wilmington, NC
- Posts
- 116
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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)
%>
Bookmarks