[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 262
  • Last Modified:

Editing DB with Datagrid; Update Syntax error

Hey Experts,

I'm trying to update an ACCESS DB from a Datagrid, but I keep getting a syntax error on my Update Statement.  I'm fairly new to ASP.net and I'm having trouble finding my error.  I'm a little fuzzy on using "replace" in the statement.  Is it necessary?

 Sub dgSolutions_Update(sender As Object, e As DataGridCommandEventArgs)

   Dim DBConnection As OleDbConnection
   Dim DBCommand As OleDbCommand
   Dim DBReader As OleDbDataReader

   '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

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

    DBConnection = New OleDbConnection( _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=c:\db1.mdb")
    DBConnection.Open()
    DBCommand = New OleDbCommand(SQLString, DBConnection)
    DBCommand.ExecuteNonQuery()
    DBConnection.Close()

    dgSolutions.EditItemIndex = -1
    BindData()
End Sub


Thanks for your help.
0
TCantrell
Asked:
TCantrell
  • 9
  • 7
  • 6
  • +1
1 Solution
 
TCantrellAuthor Commented:
Added points for urgency.
0
 
ayha1999Commented:
Hey,

what's the error?

ayha
0
 
TCantrellAuthor Commented:
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

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
TCantrellAuthor Commented:
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
0
 
raterusCommented:
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.

0
 
ayha1999Commented:
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

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

Microsoft.VisualBasic.Replace(...)

0
 
ayha1999Commented:
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
0
 
ayha1999Commented:
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
0
 
TCantrellAuthor Commented:
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?
0
 
raterusCommented:
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
0
 
TCantrellAuthor Commented:
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.
0
 
raterusCommented:
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.
0
 
TCantrellAuthor Commented:
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
0
 
TCantrellAuthor Commented:
FYI,  It takes " and the ' without an error
0
 
raterusCommented:
I'd write it like this...I couple of notes on your code, if you care for some constructive criticism.

-use try/catch with DB connections, if your SQL fails, you don't hang a connection, the objConn.Close() method will always be called, that is a good thing...
-Nothing wrong with how you did your parameters, but my example below should work, and it has always worked good for me.  It's also much more readable.

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)
   
        Dim myCommand as OleDbCommand = new OleDbCommand(strSQL, objConn)
        myCommand.CommandType = CommandType.Text
   
        'Add Parameters
        myCommand.Parameters.Add("@CategoryID", strCategoryID)
        myCommand.Parameters.Add("@SolutionTitle", strSolutionTitle)
        'and so on and so forth...  

       try
          objConn.Open()

          'Execute the UPDATE query
           myCommand.ExecuteNonQuery()
       catch ex as Exception
           throw
       finally
          'Close the connection
          objConn.Close()
        End Try

        'Set the EditItemIndex to -1 and rebind the DataGrid
        dgSolutions.EditItemIndex = -1
        BindData()
   
    End Sub
0
 
TCantrellAuthor Commented:

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

And yours is much easier to read.
0
 
raterusCommented:
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

0
 
ayha1999Commented:
Hi TCantrell,

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

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

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

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

ayha
0
 
TCantrellAuthor Commented:


No offense intended ayha
0
 
raterusCommented:
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.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 9
  • 7
  • 6
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now