Grid Update - System.Data.OleDb.OleDbException (0x80040E14): Syntax error in UPDATE statement. RSS

• • Reply
  

  jslist

  Member

  42 Points

  33 Posts

  Grid Update - System.Data.OleDb.OleDbException (0x80040E14): Syntax error in UPDATE statement.

  Nov 15, 2012 01:48 PM|LINK

  Hello,

  I'm manually developing code to make a grid work.  The update record is throwing an error:

  System.Data.OleDb.OleDbException (0x80040E14): Syntax error in UPDATE statement.   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)   at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)   at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)   at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)   at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()   at ExecNonQuery.InsertRecord(String strSQL, Int32 thisDB)

  I'm using VB & an Access db.  The database is on a different part of the server, in a different pool.

  The table has 3 fields
  ID=Autonumber, JobTitleDesc=Text, ActiveSupervisor=Boolean

  I'm testing with this SQL string:
  Update JobTypes SET (JobTitleDesc = '3 Tight Rope Walker', ActiveSupervisor = False) WHERE [ID] = 235

  Here's the aspx page

  <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
             DataKeyNames="ID" Width="650px" CellPadding="4" GridLines="Horizontal"
        BackColor="#DEE7DE" EmptyDataText=" There are currently no items in this table."
        ForeColor="#DEE7DE" AllowSorting="True">
         <AlternatingRowStyle BackColor="#DEE7DE" CssClass="text" ForeColor="Black" />
    <Columns>
        <asp:TemplateField HeaderText="ID" InsertVisible="False" SortExpression="ID"
            Visible="False">
            <EditItemTemplate>
                <asp:Label ID="IDLabel" runat="server" Text='<%# Eval("ID") %>'></asp:Label>
            </EditItemTemplate>
            <ItemTemplate>
                <asp:Label ID="IDLabel" runat="server" Text='<%# Eval("ID") %>'></asp:Label>
            </ItemTemplate>
            <ItemStyle HorizontalAlign="Center" Width="40px" />
        </asp:TemplateField>
  
        <asp:TemplateField HeaderText="Job Type Description" SortExpression="JobTitleDesc">
            <EditItemTemplate>
                <asp:TextBox ID="DescTextbox" runat="server" Text='<%# Eval("JobTitleDesc") %>'></asp:TextBox>
            </EditItemTemplate>
            <ItemTemplate>
                <asp:Label ID="DescLabel" runat="server" Text='<%# Eval("JobTitleDesc") %>'></asp:Label>
            </ItemTemplate>
            <ControlStyle Width="350px" />
            <HeaderStyle HorizontalAlign="Left" />
            <ItemStyle Width="350px" HorizontalAlign="Left" />
        </asp:TemplateField>
  
        <asp:TemplateField HeaderText="Supervisor" SortExpression="ActiveSupervisor">
            <EditItemTemplate>
                <asp:CheckBox ID="SuperChkE" runat="server"
                    Checked='<%# Eval("ActiveSupervisor") %>' />
            </EditItemTemplate>
            <ItemTemplate>
                <asp:CheckBox ID="SuperChk" runat="server"
                    Checked='<%# Eval("ActiveSupervisor") %>' Enabled="false" />
            </ItemTemplate>
            <HeaderStyle HorizontalAlign="Center" />
            <ItemStyle HorizontalAlign="Center" />
        </asp:TemplateField>
  
        <asp:CommandField ButtonType="Button" ShowEditButton="True"
            CausesValidation="False" />
        <asp:CommandField ButtonType="Button" ShowDeleteButton="True" />
    </Columns>
     <HeaderStyle CssClass="textBold" ForeColor="Black" />
        <RowStyle BackColor="White" CssClass="text" ForeColor="#333333" />
        <SelectedRowStyle BackColor="#FFFFFF" CssClass="text" Font-Bold="True" ForeColor="Navy" />
  </asp:GridView>

  Here's the aspx.vb page:

  Imports ExecNonQuery
  

  Protected Sub GridView1_RowUpdating(sender As Object, e As GridViewUpdateEventArgs) Handles GridView1.RowUpdating
  
      Dim Id As Integer
      Dim IDString, strSQL, i As String
      Dim SuperCheck As Boolean
      Dim JDesc As String
      lbl3.Text = "Row Updating"
  
      IDString = DirectCast(GridView1.Rows(e.RowIndex).FindControl("IDLabel"), Label).Text
      Id = CInt(IDString)
  
      JDesc = DirectCast(GridView1.Rows(e.RowIndex).FindControl("DescTextbox"), TextBox).Text.ToString
      SuperCheck = DirectCast(GridView1.Rows(e.RowIndex).FindControl("SuperChkE"), CheckBox).Checked
  
      strSQL = "Update JobTypes SET (JobTitleDesc = '" & JDesc & "', ActiveSupervisor = " & SuperCheck & ") WHERE [ID] = " & Id & " "
  
      lbl3.Text = strSQL
  
      GridView1.EditIndex = -1
      Try
  
          i = InsertRecord(strSQL, 22)
  
          If (i = "1") Then
              lbl1.Text = lbl1.Text + "This record has been updated."
          Else
              lbl1.Text = lbl1.Text + "No record was updated." 
          End If
  
      Catch ex As Exception
          lbl1.Text = ex.ToString
      End Try
      GridBind()
  
  End Sub

  Here's the class page: ExecNonQuery.vb

  Imports Microsoft.VisualBasic
  Imports System.Data
  Imports System.Data.OleDb
  
  
  Public Class ExecNonQuery
  Public Shared Function InsertRecord(ByVal strSQL As String, ByVal thisDB As Integer) As String
      Try
          Dim con As New OleDbConnection(GetConnectionString(thisDB))
          Dim cmd As New OleDbCommand(strSQL, con)
          con.Open()
          cmd.ExecuteNonQuery()
          con.Close()
          con = Nothing
          Return 1
      Catch ex As Exception
          Return ex.ToString
      End Try
  End Function
  
  Private Shared Function GetConnectionString(ByVal thisDB As Integer) As String
      Dim conString As String
  
      Select Case thisDB
        
          Case 22
              conString = "csJobDescriptions"
  
      End Select
  
      Dim returnValue As String = Nothing
  
      Dim settings As ConnectionStringSettings = ConfigurationManager.ConnectionStrings(conString)
      returnValue = settings.ConnectionString
      Return returnValue
  
  End Function
  End Class

  There is a part of the aspx page that allows the user to add a record.  This uses the ExecNonQuery and it works fine.  Records get Added.

  Any ideas?

  
  
   

   

• • Reply
  

  hans_v

  All-Star

  35986 Points

  6550 Posts

  Re: Grid Update - System.Data.OleDb.OleDbException (0x80040E14): Syntax error in UPDATE statement...

  Nov 15, 2012 02:39 PM|LINK

  The correct syntax for an UPDATE statement is

  UPDATE [tablename] SET field1 = ...., field2 = .... WHERE....

  NOT

  UPDATE [tablename] SET (field1 = ....., field2 = ....) WHERE....

  But I see another security problem. You're concatenating the SQL string with user input, which is not good practice

  Read this article:

  http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access

  • Marked as answer by jslist on Nov 19, 2012 05:03 PM
• • Reply
  

  hans_v

  All-Star

  35986 Points

  6550 Posts

  Re: Grid Update - System.Data.OleDb.OleDbException (0x80040E14): Syntax error in UPDATE statement...

  Nov 15, 2012 02:50 PM|LINK

  By the way, there's an easier/better way to retrive the values out of the gridview

  Instead of using Eval (1 way binding), use Bind (2 way binding) in the EditItemTemplate for the values you want to use

  <EditItemTemplate>
                <asp:TextBox ID="DescTextbox" runat="server" Text='<%# Bind("JobTitleDesc") %>'></asp:TextBox>
            </EditItemTemplate>

  
   

  Then instead of using

  JDesc = DirectCast(GridView1.Rows(e.RowIndex).FindControl("DescTextbox"), TextBox).Text.ToString 

  You can simply use the e.NewValues collection in the RowUpdating method of the GridView

  JDesc = e.NewValues("JobTitleDesc")
  

   

   

  • Marked as answer by jslist on Nov 19, 2012 05:03 PM
• • Reply
  

  Decker Dong ...

  All-Star

  118619 Points

  18779 Posts

  Re: Grid Update - System.Data.OleDb.OleDbException (0x80040E14): Syntax error in UPDATE statement...

  Nov 16, 2012 08:29 AM|LINK

  jslist

  jslist

  Hi,

  As far as I see, I think you can take hans_v's idea.

  My addition：We don't have syntax checks in VS directly. So you can：

  1）Copy your SQL of Access into AccessDataBase's Query window to run to check whether it goes well or not. (Recommanded).

  2）You can use LINQ-TO-ACCESS, this isn't supported by Microsoft but It can also directly support CRUD in Vs's syntax checking, just like LINQ-TO-SQL.

  For more you can see this：

  http://www.codeproject.com/Articles/23599/LinqToSQL-Comprehensive-Support-for-SQLite-Microso

• • Reply
  

  jslist

  Member

  42 Points

  33 Posts

  Re: Grid Update - System.Data.OleDb.OleDbException (0x80040E14): Syntax error in UPDATE statement...

  Nov 19, 2012 05:04 PM|LINK

  hans_v

  Your the best.  There were a few things wrong and I added in the () thinking it might help.  Stupid Syntax error.  Thanks again

  JS