locked
Help Sintaxis Error on Update Statement RRS feed

  • Question

  • User-659728574 posted

    Hi I need Help Im trying to update from a gridview but when I insert the data on click Update its giving me a 

    System.Data.OleDb.OleDbException was unhandled by user code
    HResult=-2147217900
    Message=Syntax error in UPDATE statement.
    Source=Microsoft Office Access Database Engine
    ErrorCode=-2147217900
    StackTrace:
    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 System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation)
    at System.Web.UI.WebControls.SqlDataSourceView.ExecuteUpdate(IDictionary keys, IDictionary values, IDictionary oldValues)
    at System.Web.UI.WebControls.SqlDataSource.Update()
    at _Default.GridView1_RowUpdating(Object sender, GridViewUpdateEventArgs e) in c:\Users\ITMANAGER\Documents\Visual Studio 2012\Projects\Nova\Nova\Company_menu\receiving_update.aspx.cs:line 209
    at System.Web.UI.WebControls.GridView.OnRowUpdating(GridViewUpdateEventArgs e)
    at System.Web.UI.WebControls.GridView.HandleUpdate(GridViewRow row, Int32 rowIndex, Boolean causesValidation)
    at System.Web.UI.WebControls.GridView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup)
    at System.Web.UI.WebControls.GridView.OnBubbleEvent(Object source, EventArgs e)
    at System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args)
    at System.Web.UI.WebControls.GridViewRow.OnBubbleEvent(Object source, EventArgs e)
    at System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args)
    at System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e)
    at System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument)
    at System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
    at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
    at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
    at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
    InnerException:

    heres is the code 

    protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
    //Get the values stored in the text boxes
    string strCompanyName = ((TextBox)GridView1.Rows[e.RowIndex].FindControl("txtCompany_Name")).Text;
    string strReceivedfrom = ((TextBox)GridView1.Rows[e.RowIndex].FindControl("txtRECEIVED_FROM")).Text;
    string strShipTo = ((TextBox)GridView1.Rows[e.RowIndex].FindControl("txtSHIP_TO")).Text;
    string strDate = ((TextBox)GridView1.Rows[e.RowIndex].FindControl("txtDATE")).Text;
    string strPO = ((TextBox)GridView1.Rows[e.RowIndex].FindControl("txtPO")).Text;
    string strBLN = ((TextBox)GridView1.Rows[e.RowIndex].FindControl("txtBLN")).Text;
    string strShipVia = ((TextBox)GridView1.Rows[e.RowIndex].FindControl("txtSHIP_VIA")).Text;
    string strWO = ((Label)GridView1.Rows[e.RowIndex].FindControl("lblWO")).Text;
    string strInVoice_date = ((TextBox)GridView1.Rows[e.RowIndex].FindControl("txtINVOICE_DATE")).Text;
    string strMx = ((TextBox)GridView1.Rows[e.RowIndex].FindControl("txtDATE_MX")).Text;
    string strTrailer = ((TextBox)GridView1.Rows[e.RowIndex].FindControl("txtTRAILER")).Text;
    string strTo = ((TextBox)GridView1.Rows[e.RowIndex].FindControl("txtTO")).Text;

    //Prepare the Update Command of the DataSource control
    string strSQL = "";
    strSQL = "UPDATE NEWRECEIVE SET COMPANY_NAME = '" + strCompanyName + "'" +
    ",RECEIVED_FROM = '" + strReceivedfrom + "'" +
    ",SHIPTO = '" + strShipTo + "'" +
    ",DATE_RECEIVED = '" + DateTime.Parse (strMx) + "'" +
    ",PO = '" + strPO + "'" +
    ",BLN = '" + strBLN + "'" +
    ",SHIP_VIA = '" + strShipVia + "'" +
    ",INVOICE_DATE = '" + DateTime.Parse(strInVoice_date) + "'" +
    ",DATE_MX = '" + DateTime.Parse(strMx)+ "'" +
    ",TRAILER = '" + strTrailer + "'" +
    ",TO = '" + strTo + "'" +
    " WHERE WO = '" + strWO + "'";

    AccessDataSource1.UpdateCommand = strSQL;
    AccessDataSource1.Update();

    thanks 

    Monday, November 25, 2013 10:50 AM

Answers

  • User-1199946673 posted

    When you cancatenate sql strings, you should use delimiters. You're using string delimiters (single quotes) on all fields, however, you also have data fields and the date delimiter in OleDd is #, not '. Also, when you're using certain characters in the string value, the resulting SQL string might not be correct and this error can occur also

    But when you concatenate sql you're vulnarable to SQL injections. To avoid this, you should use parameterized queries. Another advantage is that you don't need to worry about delimiters....

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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 26, 2013 9:33 AM

All replies

  • User753101303 posted

    Hi,

    Just print out the strSQL variable to see what is the statement syou arre trying to run...

    Creating queries this way is bad as using a particular format for dates, decimals and even strings (you have to use '' when you have ' in a string) so it's better to use parameterized query : see http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbparameter(v=vs.110).aspx

    Basically you write a querty with ? as placeholders for literal values and then your provide the actual values using their native type in the same order and data are transferred for you using a suitable form...

    Monday, November 25, 2013 10:56 AM
  • User-1199946673 posted

    When you cancatenate sql strings, you should use delimiters. You're using string delimiters (single quotes) on all fields, however, you also have data fields and the date delimiter in OleDd is #, not '. Also, when you're using certain characters in the string value, the resulting SQL string might not be correct and this error can occur also

    But when you concatenate sql you're vulnarable to SQL injections. To avoid this, you should use parameterized queries. Another advantage is that you don't need to worry about delimiters....

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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 26, 2013 9:33 AM