sql join won't work with asp

Results 1 to 6 of 6

Thread: sql join won't work with asp

  1. #1
    aku Guest

    Default sql join won't work with asp

    My join works in access but not within asp<BR><BR>I have 2 tables called cmodules and modules.<BR>table cmodules includes fields ccode and mcode.<BR>table modules includes fields code and name.<BR>fields code nad mcode are used for the join.<BR><BR>I would like "name" field to be returned against "ccode" field<BR>ccode is passed from previous page as "id" (then called number)<BR><BR>number = request.querystring("id")<BR>set rs=Server.CreateObject("adodb.Recordset")<BR>conne ctme="DSN=cccph"<BR>sqlstmt = "SELECT cmodules.ccode, cmodules.mcode, modules.name<BR>FROM modules INNER JOIN cmodules ON modules.code = cmodules.mcode<BR>WHERE (((cmodules.ccode)=" & number))<BR><BR><BR>Pulling my hair out, can anyone help

  2. #2
    John Weeflaar Guest

    Default RE: sql join won't work with asp

    You can try this:<BR>number = request.querystring("id") <BR>num = CInt(number) &#039 add this<BR>Then, everything is the same except replacing number with num in<BR>the last line:<BR>....<BR>WHERE (((cmodules.ccode)=" & num))<BR><BR>Hope it works now (and no more hair pulled out!).<BR><BR>John<BR><BR>

  3. #3
    aku Guest

    Default RE: sql join won't work with asp

    :(<BR>I still get the following error <BR><BR>Microsoft VBScript compilation error &#039 800a0409&#039 <BR><BR>Unterminated string constant <BR><BR>course.asp, line 20 <BR><BR>sqlstmt = "SELECT cmodules.ccode, cmodules.mcode, modules.name<BR>--------------------------------------------------------------^<BR><BR>I guess the problem is with the sql<BR><BR><BR>Thanks anyway John<BR>

  4. #4
    John Weeflaar Guest

    Default RE: sql join won't work with asp

    You probably haven&#039t put the continuation character<BR>(as the SQL string is in 3 lines). One way is like this:<BR>sqlstmt = "SELECT cmodules.ccode, cmodules.mcode, " & _<BR>"modules.name " & _<BR>"FROM modules INNER JOIN cmodules ON " & _<BR>"modules.code = cmodules.mcode " & _<BR>"WHERE cmodules.ccode=" & num<BR><BR>Make sure there is a space (followed by ") after (,) in the first line. Also another space after modules.name,after ON, after mcode.<BR>& _ is separated by a space.<BR>(" & is separated by a space also).<BR>You might like to try it. Let me know if it works or not.<BR><BR>John<BR>


  5. #5
    Aku Guest

    Default RE: sql join won't work with asp

    He Hey - I will not need a wig !!<BR><BR>Continuation of the statement was the problem!! Yet another thing learnt. Using CInt did not fix it as I had already defined the field as number in access database.<BR><BR>Many thanks John, and below the final code which works.<BR><BR>Aku<BR><BR>number = request.querystring("id")<BR>set rs=Server.CreateObject("adodb.Recordset")<BR>conne ctme="DSN=cccph"<BR>sqlstmt = "SELECT cmodules.ccode, cmodules.mcode, modules.name FROM modules INNER JOIN cmodules ON modules.code = cmodules.mcode WHERE cmodules.ccode=" & number

  6. #6
    John Weeflaar Guest

    Default RE: sql join won't work with asp

    Hi Aku,<BR><BR>You&#039re right!<BR> I realized I made an error myself after I posted the<BR>first answer as I didn&#039t notice the brackets.<BR><BR>Thanks Aku

Posting Permissions

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