SQL Query

Question

User1109811461 posted

currently i working with sql query to implement crstal report when i run i got error...

i'm belieave that my query got a problem...need help from expertise here

below is my sql query that i run from sql server this query below can run with no error

SELECT     MaklumatPelajar.Nama, MaklumatPelajar.NoIc, KesalahanDisiplin.KategoriKes, KesalahanDisiplin.SubKes,KesalahanDisiplin.TarikhKes, KesalahanDisiplin.AduanOleh
FROM         MaklumatPelajar INNER JOIN KesalahanDisiplin ON MaklumatPelajar.IdPelajar = KesalahanDisiplin.IdPelajar
WHERE     (KesalahanDisiplin.KategoriKes = N'Kenalan') AND (KesalahanDisiplin.SubKes = N'Keluar Kawasan Sekolah')

this is query that i run from my web form

 Dim da As New SqlDataAdapter("SELECT MaklumatPelajar.Nama, MaklumatPelajar.NoIc, KesalahanDisiplin.KategoriKes, KesalahanDisiplin.SubKes,KesalahanDisiplin.TarikhKes, KesalahanDisiplin.AduanOleh" & _
                                    "FROM MaklumatPelajar INNER JOIN KesalahanDisiplin ON MaklumatPelajar.IdPelajar = KesalahanDisiplin.IdPelajar" & _
                                    "WHERE KesalahanDisiplin.KategoriKes = '" & getSessionKategori & "' AND KesalahanDisiplin.SubKes = " & getSessionSubKategori, mySQLConnection)

this is the error i got

Incorrect syntax near the keyword 'INNER'.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'INNER'.

Source Error:

Line 28: Line 29: Line 30: da.Fill(dt) Line 31: ds.Tables(0).Merge(dt) Line 32: rptDoc.Load(Server.MapPath("SenaraiKesalahan.rpt"))

Source File: C:\Application Check IC\13-11-2012\Application\ReportSenaraiKesalahan.aspx.vb Line: 30

Answer 1

User477186420 posted

Hi,

Check following code i have added space into second and third line of your query

 Dim da As New SqlDataAdapter("SELECT MaklumatPelajar.Nama, MaklumatPelajar.NoIc,KesalahanDisiplin.KategoriKes, KesalahanDisiplin.SubKes,KesalahanDisiplin.TarikhKes,KesalahanDisiplin.AduanOleh" & _
                                    " FROM MaklumatPelajar INNER JOIN KesalahanDisiplin ON MaklumatPelajar.IdPelajar = KesalahanDisiplin.IdPelajar" & _
                                    " WHERE KesalahanDisiplin.KategoriKes = '" & getSessionKategori & "' AND KesalahanDisiplin.SubKes = " & getSessionSubKategori, mySQLConnection)

let me know if any query

Answer 2

User1109811461 posted

hi dhol.gaurav

thanks for reply

 still got problem, i provide to you my full code:

  Dim getSessionKategori As Object
        Dim getSessionSubKategori As Object

        getSessionKategori = Session("kategori")
        getSessionSubKategori = Session("subkes")

        Dim rptDoc As New ReportDocument
        Dim ds As New DataSet1
        Dim mySQLConnection = New SqlConnection()
        Dim dt As New DataTable
        dt.TableName = "Student Report"

        mySQLConnection.ConnectionString = ConfigurationManager.ConnectionStrings("default").ConnectionString
     


        Dim da As New SqlDataAdapter("SELECT MaklumatPelajar.Nama, MaklumatPelajar.NoIc,KesalahanDisiplin.KategoriKes, KesalahanDisiplin.SubKes,KesalahanDisiplin.TarikhKes,KesalahanDisiplin.AduanOleh" & _
                                   " FROM MaklumatPelajar INNER JOIN KesalahanDisiplin ON MaklumatPelajar.IdPelajar = KesalahanDisiplin.IdPelajar" & _
                                   " WHERE KesalahanDisiplin.KategoriKes = '" & getSessionKategori & "' AND KesalahanDisiplin.SubKes = " & getSessionSubKategori, mySQLConnection)


        da.Fill(dt)
        ds.Tables(0).Merge(dt)
        rptDoc.Load(Server.MapPath("SenaraiKesalahan.rpt"))
        rptDoc.SetDataSource(ds)
        CrystalReportViewer1.ReportSource = rptDoc

below is the error:

Incorrect syntax near '='.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near '='.

Source Error:

Line 28: Line 29: Line 30: da.Fill(dt) Line 31: ds.Tables(0).Merge(dt) Line 32: rptDoc.Load(Server.MapPath("SenaraiKesalahan.rpt"))

Answer 3

User477186420 posted

Again I have updated query in where condition i have added single Quotes in last conditon

Dim da As New SqlDataAdapter("SELECT MaklumatPelajar.Nama,MaklumatPelajar.NoIc,KesalahanDisiplin.KategoriKes,KesalahanDisiplin.SubKes,KesalahanDisiplin.TarikhKes,KesalahanDisiplin.AduanOleh" & _
                                   " FROM MaklumatPelajar INNER JOIN KesalahanDisiplin ON MaklumatPelajar.IdPelajar = KesalahanDisiplin.IdPelajar" & _
                                   " WHERE KesalahanDisiplin.KategoriKes = '" & getSessionKategori & "' AND KesalahanDisiplin.SubKes = '" & getSessionSubKategori & "'", mySQLConnection)

let me know if any query

Answer 4

User1109811461 posted

i got no error...

but there are no data display in crstas report...do you have any ideas...?

Answer 5

User1109811461 posted

hi dhol.gaurav

you resolve my problem...thanks a lot my expertise,,so happy with that....