Member Avatar for leo88

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 by leo88 because: n/a
  • 2 Contributors
  • 9 Replies
  • 123 Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by sandeepparekh9
Member Avatar for sandeepparekh9

what is the error???

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

Member Avatar for leo88

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 by leo88 because: n/a
Member Avatar for sandeepparekh9

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 by sandeepparekh9 because: n/a
Member Avatar for leo88

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.

Member Avatar for sandeepparekh9

do you see any data in ds ??

Member Avatar for leo88

do you see any data in ds ??

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

Member Avatar for sandeepparekh9

just comment out the Finally Part and see if it works...

Member Avatar for leo88

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

Edited by leo88 because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.