SitePoint Sponsor

User Tag List

Results 1 to 10 of 10

Thread: Joining Tables

  1. #1
    SitePoint Zealot
    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?

  2. #2
    SitePoint Zealot
    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.

  3. #3
    grasshoppa Snowbird122's Avatar
    Join Date
    Apr 2001
    Location
    Austin
    Posts
    353
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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, "'", "''") + "')"
    I added () around the where items and single quotes around the variable at the end.
    http://www.echo-consulting.net - Sound Solutions for Online Inspriations.

  4. #4
    SitePoint Zealot
    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 ....

  5. #5
    grasshoppa Snowbird122's Avatar
    Join Date
    Apr 2001
    Location
    Austin
    Posts
    353
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    did you get the same error?
    http://www.echo-consulting.net - Sound Solutions for Online Inspriations.

  6. #6
    SitePoint Zealot
    Join Date
    Jul 2001
    Location
    Wilmington, NC
    Posts
    116
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, same error.

  7. #7
    grasshoppa Snowbird122's Avatar
    Join Date
    Apr 2001
    Location
    Austin
    Posts
    353
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  8. #8
    ALT.NET - because we need it silver trophybronze trophy dhtmlgod's Avatar
    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 
    %>

  9. #9
    SitePoint Wizard
    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>
    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.
    Last edited by shane; Oct 29, 2001 at 03:25.

  10. #10
    SitePoint Zealot
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •