locked
characters found after end of sql statement RRS feed

  • 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

    Wednesday, May 11, 2011 8:38 PM

Answers

  • 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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 11, 2011 10:17 PM
  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, May 14, 2011 3:56 AM

All replies

  • 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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 11, 2011 10:17 PM
  • 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.

     

     

    Thursday, May 12, 2011 8:19 AM
  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, May 14, 2011 3:56 AM