My DataGrid is working fine in that it populates the correct data from the database. However, when I try to update/edit through the DataGrid, it just goes back to normal as if I cancled.

Here the code:

string strConn;

protected void Page_Load(Object S, EventArgs E)
     {
     strConn="PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=";
     strConn += Server.MapPath (@"\WNAFormTest.mdb") + ";";

     if (! IsPostBack)
         {
         FillUpGrid();
         }
     } // end page_load

protected void FillUpGrid()
     {

     OleDbConnection Conn= new OleDbConnection();
     OleDbDataReader Rdr=null;

     try
         {
         string strSQL;
         strSQL="select * from buyers";
         Conn=new OleDbConnection(strConn);
         OleDbCommand Cmd=new OleDbCommand(strSQL,Conn);
         Conn.Open();
         Rdr=Cmd.ExecuteReader();
         D1.DataSource = Rdr;
         D1.DataBind();
         // litAmy.Text="hi<br> <b>glad to see you</b><br> hello";
         } // end try
     catch (Exception excl)
         {
         litExc.Text ="";
         litExc.Text +=excl.ToString();
         // litExc.Text += "<blockquote>" + excl.StackTrace() + "</blockquote>";
         litExc.Text += "";
         } // end catch
     finally
         {
         if (Rdr !=null)
             {
             if (Rdr.IsClosed==false) {Rdr.Close();}
             }
         if (Conn != null)
             {
             if (Conn.State==System.Data.ConnectionState.Open) {Conn.Close();}
             }
         } // end finally
     } // end FillUpGrid


protected void DataEdit(Object S, DataGridCommandEventArgs E)
     {
     D1.EditItemIndex=(int) E.Item.ItemIndex;
     FillUpGrid();
     } // end DataEdit


protected void DataUpdate(Object S, DataGridCommandEventArgs E)
     {
     D1.EditItemIndex=(int) E.Item.ItemIndex;

     int BuyerID =(int)D1.DataKeys[(int)E.Item.ItemIndex];
     TextBox FirstName=(TextBox) E.Item.Cells[2].Controls[0];
     TextBox LastName=(TextBox) E.Item.Cells[3].Controls[0];
     TextBox Address1=(TextBox) E.Item.Cells[4].Controls[0];
     TextBox Address2=(TextBox) E.Item.Cells[5].Controls[0];
     TextBox City=(TextBox) E.Item.Cells[6].Controls[0];
     TextBox State=(TextBox) E.Item.Cells[7].Controls[0];
     TextBox ZipCode=(TextBox) E.Item.Cells.Controls[0];
     TextBox Availability=(TextBox) E.Item.Cells[9].Controls[0];
     TextBox HomePhone=(TextBox) E.Item.Cells[10].Controls[0];
     TextBox WorkCellPhone=(TextBox) E.Item.Cells[11].Controls[0];
     TextBox Email=(TextBox) E.Item.Cells[12].Controls[0];

     // Update publishers set Name='yyy',Company Name='Prentice Hall', Fax='555-1212' where PUBID=56

     string strSQL="";
     strSQL +="Update Buyers set ";
     strSQL +="FirstName='" + FirstName.Text + "', ";
     strSQL +="LastName='" + LastName.Text + "', ";
     strSQL +="Address1='" + Address1.Text + "', ";
     strSQL +="Address2='" + Address2.Text + "', ";
     strSQL +="City='" + City.Text + "', ";
     strSQL +="State='" + State.Text + "', ";
     strSQL +="ZipCode='" + ZipCode.Text + "', ";
     strSQL +="Availability='" + Availability.Text + "', ";
     strSQL +="HomePhone='" + HomePhone.Text + "', ";
     strSQL +="WorkCellPhone='" + WorkCellPhone.Text + "', ";
     strSQL +="Email='" + Email.Text + "', ";


     strSQL +=" Where BuyerID=" + BuyerID;


     Trace.Write ("strSQL", strSQL);
     OleDbConnection Conn=null;

     try
         {
         Conn=new OleDbConnection(strConn);
         OleDbCommand Cmd=new OleDbCommand(strSQL,Conn);
         Conn.Open();
         Cmd.ExecuteNonQuery();
         } // end try
     catch (Exception exc1)
         {
         Trace.Write("bad things happened","wow",exc1);
         }
     finally
         {
         if (Conn != null)
             {
             Conn.Close();
             }
         }

     D1.EditItemIndex=-1;
     FillUpGrid();

     } // end DataUpdate

protected void DataCancel(Object S, DataGridCommandEventArgs E)
     {
     D1.EditItemIndex=-1;
     FillUpGrid();
     } // end DataCancel

This is the error I am recieving:

Syntax error in UPDATE statement.
  at System.Data.OleDb.OleDbCommand.ExecuteCommandTextE rrorHandling(Int32 hr)
  at System.Data.OleDb.OleDbCommand.ExecuteCommandTextF orSingleResult(tagDBPARAMS dbParams, Object& executeResult)
  at System.Data.OleDb.OleDbCommand.ExecuteCommandText( Object& executeResult)
  at System.Data.OleDb.OleDbCommand.ExecuteCommand(Comm andBehavior behavior, Object& executeResult)
  at System.Data.OleDb.OleDbCommand.ExecuteReaderIntern al(CommandBehavior behavior, String method)
  at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
  at ASP.BuyerPersonalDataAdmin_aspx.DataUpdate(Object S, DataGridCommandEventArgs E)

I am not quite sure where to go from here.

I found the error after running the query in MS Access. I recommend testing out queries to help find errors.


     strSQL +="City='" + City.Text + "', ";
     strSQL +="State='" + State.Text + "', ";
     strSQL +="ZipCode='" + ZipCode.Text + "', ";
     strSQL +="Availability='" + Availability.Text + "', ";
     strSQL +="HomePhone='" + HomePhone.Text + "', ";
     strSQL +="WorkCellPhone='" + WorkCellPhone.Text + "', ";
     strSQL +="Email='" + Email.Text + "' ";


     strSQL +=" Where BuyerID=" + BuyerID;