Results 1 to 6 of 6

Thread: inner join

  1. #1
    Join Date
    Mar 2004
    Posts
    53

    Unhappy Unanswered: inner join

    i have 2 table which i have to do inner join in way to display the info. however, every time i do it i will get a syntax error. i don't know how to solve it. below is the code :

    <%@LANGUAGE="VBSCRIPT"%>
    <!--#include file="Connections/sambungan.asp" -->
    <%
    Dim Recordset1__MMColParam
    Recordset1__MMColParam = "1"
    If (Request.QueryString("caricetak") <> "") Then
    Recordset1__MMColParam = Request.QueryString("caricetak")
    End If
    %>
    <%
    Dim Recordset1
    Dim Recordset1_numRows

    Set Recordset1 = Server.CreateObject("ADODB.Recordset")
    Recordset1.ActiveConnection = MM_sambungan_STRING
    Recordset1.Source = "SELECT tb_aduan.id_aduan,tb_aduan.id_user,tb_aduan.kod_ke rosakan,tb_aduan.sebab_kerosakan,tb_aduan.lokasi,t b_aduan.blok,tb_aduan.tingkat,tb_aduan.no_bilik,tb _aduan.tarikhmasa,tb_aduan.arahan,tb_aduan.bhn_gan ti,tb_aduan.laporan,tb_aduan.status_id,tb_aduan.ju mlah_kos,tb_aduan.upah,tb_aduan.kosbhn_ganti,tb_ad uan.perundingan,staff IT.nostaffuitm,staff IT.namastaffuitm,staff IT.kodjawatan,staff IT.samb FROM tb_aduan INNER JOIN staff IT ON tb_aduan.id_user= staff IT.nostaffuitm WHERE id_aduan = " + Replace(Recordset1__MMColParam, "'", "''") + ""
    Recordset1.CursorType = 0
    Recordset1.CursorLocation = 2
    Recordset1.LockType = 1
    Recordset1.Open()

    Recordset1_numRows = 0
    %>


    and the error is:

    Error Type:
    Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
    [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'staff IT.nostaffuitm'.
    /systemku/TMP29ngbw1a3o.asp, line 20

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i don't do ASP but i'm thinking your problem might be in this part of the query string --

    WHERE id_aduan = " + Replace(Recordset1__MMColParam, "'", "''") + ""

    i think there should be single quotes around the value

    WHERE id_aduan = '" + Replace(Recordset1__MMColParam, "'", "''") + "'"
    Last edited by r937; 04-11-04 at 23:02.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2004
    Posts
    53

    error is still here

    the error is still there. i think the error coccurs because of the MM_ColParam(it represent input from previous page- i use get). is there's other way to solve it.

    please help me

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    108

    Post hmm

    The error is your SQL statement.

    staff IT.nostaffuitm,staff IT.namastaffuitm,staff IT.kodjawatan,staff IT.samb
    Those are illegal. You cannot have spaces.

    When you say "INNER JOIN staff IT", That tells the server that STAFF is the tablename, and IT is the nickname you are giving it. Once you declare that, you must use Only the Tablename or the Nickname... not both.

    This should fix it:
    ---------------
    Recordset1.Source = "SELECT tb_aduan.id_aduan," _
    & " tb_aduan.id_user,tb_aduan.kod_kerosakan," _
    & " tb_aduan.sebab_kerosakan, tb_aduan.lokasi," _
    & " tb_aduan.blok,tb_aduan.tingkat,tb_aduan.no_bilik," _
    & " tb_aduan.tarikhmasa, tb_aduan.arahan," _
    & " tb_aduan.bhn_ganti,tb_aduan.laporan,tb_aduan.statu s_id," _
    & " tb_aduan.jumlah_kos,tb_aduan.upah," _
    & " tb_aduan.kosbhn_ganti,tb_aduan.perundingan, " _
    & " IT.nostaffuitm,IT.namastaffuitm,IT.kodjawatan,IT.s amb" _
    & " FROM tb_aduan " _
    & " INNER JOIN staff IT ON " _
    & " tb_aduan.id_user = IT.nostaffuitm " _
    & " WHERE id_aduan = " & Replace(Recordset1__MMColParam, "'", "''")"
    ---------------

    I removed "Staff" from the initial select and in the ON clause.

    Let me know if it works.

    ~Le

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002

  6. #6
    Join Date
    Mar 2004
    Posts
    53

    Re: hmm

    i'have try the suggestion. what i do is i change the table name to staffIT, where u can see there is no more space or underscore.
    after i do the changing, everything is ok. so, right now i know that any table's name should avoid of using space or underscore. thanks a lot for the advice. thank u everybody.........

Posting Permissions

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