characters found after end of sql statement

Question

User-327874637 posted

i am trying to view all the details in my orders table where the customer id matches the customer id from a session varriable from the page before

 

but i am getting this error :- System.Data.OleDb.OleDbException: Characters found after end of SQL statement.

 

--this is my code

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
        If (Not Page.IsPostBack) Then

           
            Dim custID As String
           
            custID = Session("custID")
           
           
            Dim strDatabaseNameAndLocation As String
            strDatabaseNameAndLocation = Server.MapPath("Products.mdb")
            Dim strSQLCommand As String
            strSQLCommand = "SELECT Orders.* FROM Orders ORDER BY Orders.OrderID DESC;" & "WHERE Orders.CustomerID=" & custID
            Dim objOleDbConnection As System.Data.OleDb.OleDbConnection
            objOleDbConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0; Data Source=" & strDatabaseNameAndLocation)
            objOleDbConnection.Open()
            Dim objOleDbCommand As System.Data.OleDb.OleDbCommand
            objOleDbCommand = New System.Data.OleDb.OleDbCommand(strSQLCommand, objOleDbConnection)
            Dim objOleDbDataReader As System.Data.OleDb.OleDbDataReader
            objOleDbDataReader = objOleDbCommand.ExecuteReader()
            Dim datDataTable As System.Data.DataTable
            datDataTable = New System.Data.DataTable()
            datDataTable.Load(objOleDbDataReader)
            gdvProducts.DataSource = datDataTable
            gdvProducts.DataBind()
            objOleDbConnection.Close()
           
        End If
       
       
    End Sub

Answer 1

User-158764254 posted

you'vegot your sql statement a little out of order

the semicolon should be at the end and the WHERE clause should come before the ORDER BY clause.  So something a little more like this:

    "SELECT Orders.* FROM Orders WHERE Orders.CustomerID=" & custID  & " ORDER BY Orders.OrderID DESC;" 

But to be honest, i'd strongly recommend you use parameterized sql statements rather than concatenating values into the statement like that. parameterized statements help protect against sql injection attacks.

Answer 2

User-327874637 posted

thanx very much, i fixed that problem, but now it wont display the data into my datagrid view, do u have any suggestions that might fix this.

 

 

Answer 3

User3866881 posted

Hi:)

Try your best to execute the sql select statement in the Access db directly to see whether you've really got the result or not.

I mean that you should set some break points to fetch the running-time sql statement, and copy it to paste into Access db directly to test with that.

Thx