Hi, I am doing a window servise application using VB.net. I have faced problems with sql suery, as below:

Dim dt As New DataTable()

            SQLStr = "SELECT FautyScanTbl.Model, FautyScanTbl.Version, FautyScanTbl.WIP,   FautyScanTbl.ScanNo, FautyScanTbl.LotSize, FautyScanTbl.SerialNo, FautyScanTbl.PIC," & _
                             "SerialNoTbl.SetWeight, SerialNoTbl.AccesWeight,
                             SerialNoTbl.pdmid, ScannedPartNoTbl.ScannedTime " & _
                     "FROM   FautyScanTbl LEFT OUTER JOIN " & _
                             "ScannedPartNoTbl ON FautyScanTbl.ID = ScannedPartNoTbl.ID
                              LEFT OUTER JOIN" & _
                             "SerialNoTbl ON FautyScanTbl.ID = SerialNoTbl.ID"

            dt = AccessDb_Cmd.RetrieveData(SQLStr, Access_Db).Tables(0)

the retrieve command method:

Public Function RetrieveData(ByVal Cmd As String, ByVal mTrans As SQLClass) As DataSet
        Try
            Dim ds As New DataSet()
            OleDbCmd = New SqlCommand(Cmd, mTrans.TransConn, mTrans.Trans)
            DataAdp = New SqlDataAdapter(OleDbCmd)

            ds.Clear()
            DataAdp.Fill(ds)
            Return ds

        Catch ex As Exception
            Throw New Exception(ex.Message)           

        Finally
            ds.Dispose()
            OleDbCmd.Dispose()
            DataAdp.Dispose()

        End Try

    End Function

When I debug, I found that an exception occured at line 8 (DataAdp.Fill(ds)), and when I point the pointer to the ds, the ds has nothing. Thus I thinks problem should be appreared in my query, the debug indicator on the "ex" stated that "Incorrect syntax near the keywords ON".

Anyone know what is the problems with my query?

Edited 5 Years Ago by leo88: n/a

what is the error???

any error code or error text ??????? or screen shot of error??

hi,
The program had run to catch statement(line 12) after line 8, then it flow to second catch statement which shows the error "Object reference not set to instance of an object", by the way the trigger of the error is from the first catch statement, as I had mentioned just now.

Edited 5 Years Ago by leo88: n/a

i think you forgot a space.

change

SQLStr = "SELECT FautyScanTbl.Model, FautyScanTbl.Version, FautyScanTbl.WIP,   FautyScanTbl.ScanNo, FautyScanTbl.LotSize, FautyScanTbl.SerialNo, FautyScanTbl.PIC," & _
                             "SerialNoTbl.SetWeight, SerialNoTbl.AccesWeight,
                             SerialNoTbl.pdmid, ScannedPartNoTbl.ScannedTime " & _
                     "FROM   FautyScanTbl LEFT OUTER JOIN " & _
                             "ScannedPartNoTbl ON FautyScanTbl.ID = ScannedPartNoTbl.ID
                              LEFT OUTER JOIN" & _ 'I think you forgot a space before " here
                             "SerialNoTbl ON FautyScanTbl.ID = SerialNoTbl.ID"

to

SQLStr = "SELECT FautyScanTbl.Model, FautyScanTbl.Version, FautyScanTbl.WIP,   FautyScanTbl.ScanNo, FautyScanTbl.LotSize, FautyScanTbl.SerialNo, FautyScanTbl.PIC, " & _
                             " SerialNoTbl.SetWeight, SerialNoTbl.AccesWeight,
                             SerialNoTbl.pdmid, ScannedPartNoTbl.ScannedTime " & _
                     " FROM   FautyScanTbl LEFT OUTER JOIN " & _
                             " ScannedPartNoTbl ON FautyScanTbl.ID = ScannedPartNoTbl.ID
                              LEFT OUTER JOIN " & _
                             " SerialNoTbl ON FautyScanTbl.ID = SerialNoTbl.ID"

see if it helps

Edited 5 Years Ago by sandeepparekh9: n/a

It is works, thank you very much. But still have problem there, this time it can go to return ds (line 9), when it go to ds.dispose(line 15) in the finally statement, it go to the second catch statement which shows the "Object reference not set to instance of an object", what had happened? I thought just now is because of my query, but I am wrong.

do you see any data in ds ??

Yes, the indicator stated "System.Data.Dataset"

It is success!!! Thank you very much sandeepparekh, your help is appreciated:)

Edited 5 Years Ago by leo88: n/a

This question has already been answered. Start a new discussion instead.