Editing DB with Datagrid; Update Syntax error

Added points for urgency.

Hey,

what's the error?

ayha

sorry, I guess this would help
If you want the Stack Trace I can post that also.

Syntax error in UPDATE statement.Server Error in '/' Application.


Syntax error in UPDATE statement.
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.OleDb.OleDbException: Syntax error in UPDATE
statement.

Source Error:


Line 68:         DBConnection.Open()
Line 69:         DBCommand = New OleDbCommand(SQLString, DBConnection)
Line 70:         DBCommand.ExecuteNonQuery()
Line 71:         DBConnection.Close()
Line 72:    

Source File: C:\Matrix\Datagrid.aspx    Line: 70

Here's the stack trace

Stack Trace:

[OleDbException (0x80040e14): Syntax error in UPDATE statement.]
   System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr) +41
   System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) +122
   System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) +92
   System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) +65
   System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) +112
   System.Data.OleDb.OleDbCommand.ExecuteNonQuery() +66
   ASP.Datagrid_aspx.dgSolutions_Update(Object sender, DataGridCommandEventArgs e) in C:\Matrix\Datagrid.aspx:70
   System.Web.UI.WebControls.DataGrid.OnUpdateCommand(DataGridCommandEventArgs e) +83
   System.Web.UI.WebControls.DataGrid.OnBubbleEvent(Object source, EventArgs e) +499
   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +26
   System.Web.UI.WebControls.DataGridItem.OnBubbleEvent(Object source, EventArgs e) +106
   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +26
   System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +95
   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +115
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
   System.Web.UI.Page.ProcessRequestMain() +1277




Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET
Version:1.1.4322.573

I think the Jet engine wants string literals to be inclosed in double quotes, not single quotes

Update table
Set somefield = "Blah"

not

Update table
Set somefield = 'Blah'

you'd need to fix your replace statements to accompany this as well.

Hi,

Just put this function in your page

 Function fixQuotes(ByVal theString)
        fixQuotes = Replace(theString, "'", "''")
    End Function

then

change all your items where you have replace like this

'" & fixQuotes(strCategoryID) & "'

see what you get.

ayha

SQLString = "UPDATE tblSolutions SET " & _
    "CategoryID = '" & Replace(strCategoryID, "'", "''") & "', " & _
    "SolutionTitle = '" & Replace(strSolutionTitle, "'", "''") & "', " & _
    "Solution = '" & Replace(strSolution, "'", "''") & "', " & _
    "WHERE SolutionID = '" & strSolutionID & "'"

Microsoft.VisualBasic.Replace(...)

hi,

make sure u put ( '" "') for text datatypes and (" " ) for numbers.

I just noted a syntax error. Remove last "," from the follwoing and try.... where no need ",".

  "Solution = '" & Replace(strSolution, "'", "''") & "', " & _

ayha

Hi,

If you still get syntax error after testing all of my soution then you have to make sure that you spell correctly your database field names in the sql statement.

ayha

OK ayha

This was part of my problem:

 make sure u put ( '" "') for text datatypes and (" " ) for numbers.

I've got it working without using Replace.

I'm borrowing the code and I don't understand how Replace works, and why I should use it.

What does Replace do, and when is it necessary?

You must use replace, or you will confuse the engine when it encounters quotes within the actual string.  Say a person's name is O'Brien, if you try to update with this name, your statement will look like this

Update sometable
Set name='O'Brien'

as you can see, the ' in the name will cause errors, this is also where you run into script injection attacks.  Now to the DB engine, if you put two single quotes '' together, this tells the engine to treat this as one quote.  Thus the replace statements.

however, this is bad design altogether, as dynamic sql needs to die...http://www.knowdotnet.com/articles/dynamisql.html

Ok raterus,

This may sound stupid, but if I use a parameterized SQL Statement do I still need to use replace?

I'm still trying to get the concept.

Well I know you don't need to with SQL Server, but since you are using Access, I can't say for sure.  I'd try it with a value with a quote in it and see what happens.

I'm using these parameters and it seems to be working.

I haven't figured what OleDBTypes I should be using yet.

Sub dgSolutions_Update(sender As Object, e As DataGridCommandEventArgs)
   
   
       'Read in the values of the updated row
        Dim strSolutionID = e.Item.Cells(0).Text
        Dim strCategoryID = CType(e.Item.Cells(1).Controls(0), TextBox).Text
        Dim strSolutionTitle = CType(e.Item.Cells(2).Controls(0), TextBox).Text
        Dim strSolution = CType(e.Item.Cells(3).Controls(0), TextBox).Text
   
       'Build SQL Statement
        Dim strSQL as String = _
          "UPDATE [tblSolutions] SET [CategoryID] = @CategoryID, " & _
          "[SolutionTitle] = @SolutionTitle, [Solution] = @Solution " & _
          "WHERE [SolutionID] = @SolutionID"
     
        'Provide Connection String
        Const strConnString as String = _
           "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\db1.mdb"
        Dim objConn as New OleDbConnection(strConnString)
        objConn.Open()
   
        Dim myCommand as OleDbCommand = new OleDbCommand(strSQL, objConn)
        myCommand.CommandType = CommandType.Text
   
        'Add Parameters
        Dim parameterCategoryID as OleDbParameter = _
                   new OleDbParameter("@CategoryID", OleDbType.Char)
        parameterCategoryID.Value = strCategoryID
        myCommand.Parameters.Add(parameterCategoryID)
   
        Dim parameterSolutionTitle as OleDbParameter = _
                   new OleDbParameter("@SolutionTitle", OleDbType.Char)
        parameterSolutionTitle.Value = strSolutionTitle
        myCommand.Parameters.Add(parameterSolutionTitle)
   
        Dim parameterSolution as OleDbParameter = _
                   new OleDbParameter("@Solution", OleDbType.Char)
        parameterSolution.Value = strSolution
        myCommand.Parameters.Add(parameterSolution)
   
        Dim parameterSolutionID as OleDbParameter = _
                   new OleDbParameter("@SolutionID", OleDbType.Char)
        parameterSolutionID.Value = strSolutionID
        myCommand.Parameters.Add(parameterSolutionID)
   
       'Execute the UPDATE query
        myCommand.ExecuteNonQuery()
   
       'Close the connection
        objConn.Close()
   
        'Set the EditItemIndex to -1 and rebind the DataGrid
        dgSolutions.EditItemIndex = -1
        BindData()
   
    End Sub

FYI,  It takes " and the ' without an error

Thanks,  I'm looking for all the criticism I can get.  Just trying to learn.

And yours is much easier to read.

another thing, you are not declaring these as strings.
        Dim strSolutionID = e.Item.Cells(0).Text
        Dim strCategoryID = CType(e.Item.Cells(1).Controls(0), TextBox).Text
        Dim strSolutionTitle = CType(e.Item.Cells(2).Controls(0), TextBox).Text
        Dim strSolution = CType(e.Item.Cells(3).Controls(0), TextBox).Text

Hi TCantrell,

Please learn how to split points when you get solutions from more than one people.

ayha

He didn't use your solution ayha, I suggested a completely different / better way to fix his problem, which he used.

Hi raterus,

The solution was never possible if didn't  remove "," fromthe following

"Solution = '" & Replace(strSolution, "'", "''") & "', " & _
( from his original post)

ayha

No offense intended ayha

TCantrell, the moderators will reopen the question so you can reassign points if you'd like.  You just have to post a question in Community Support asking them to do such.