c#+dataset update +problem +URGENT!!!!!!!!

• • Reply

  13 years ago

  by FRED596

  Brian Richardson

  , Australia

  Joined 13 years ago

   hey frens,
  
  i have a class customer where there r two methods getdataset()  and update when get dataset is called a datagrid is displayed as a result of search.
  when when button update is pressed to save the changes in grid view update() method is called:
  my problem is i get the error:
  update query syntax incorrect. my class goeds lik ethis:
     public class customer
  {
    // private static string strConn;
  
          private static DataTable ds;
   
  
     public  DataTable getdataset(string fname, DataGridView dg)
     {
          OleDbConnection con = new OleDbConnection(@"provider= Microsoft.Jet.OLEDb.4.0; Data Source = D:\My Documents\Visual Studio 2005\Projects\db2.mdb");
          con.Open();
  
          OleDbDataAdapter thisadapter1 = new OleDbDataAdapter("Select * from tbl_client where fname= '" + fname + "'", con);
          //OleDbCommandBuilder cd = new OleDbCommandBuilder(thisadapter1);
          ds = new DataTable();
          thisadapter1.Fill(ds);
  
          dg.BringToFront();
          dg.Visible = true;
          BindingSource bsource = new BindingSource();
          bsource.DataSource = ds;
          dg.DataSource = bsource;
          dg.Columns[0].Visible = false;
          dg.Columns[1].Visible = false;
          dg.Columns[2].HeaderText = "Fname";
          dg.Columns[2].Width = 45;
          dg.Columns[3].HeaderText = "Lname";
          dg.Columns[3].Width = 45;
          dg.Columns[5].HeaderText = "Gender";
          dg.Columns[5].Width = 46;
          dg.Columns.HeaderText = "Address";
          dg.Columns.Width = 48;
          dg.Columns[4].Visible = false;
          dg.Columns[7].HeaderText = "Phone";
          dg.Columns[7].Width = 45;
          dg.Columns.HeaderText = "Mobile";
          dg.Columns.Width = 45;
          dg.Columns[9].HeaderText = "Email";
          dg.Columns[9].Width = 45;
          dg.Columns[10].HeaderText = "Project_ID";
          dg.Columns[10].Width = 55;
          dg.Columns[11].HeaderText = "Visit Date";
          dg.Columns[11].Width = 55;
          dg.Columns[12].HeaderText = "Agent";
          dg.Columns[12].Width = 45;
          dg.Columns[13].HeaderText = "FeedBack";
          dg.Columns[14].HeaderText = "Remarks";
          string feedback = "FeedBack";
          dg.Columns[13].HeaderText = feedback;
  
          return ds;
                                     
     }
          public DataTable getdataset_date(string fname, DataGridView dg)
          {
              OleDbConnection con = new OleDbConnection(@"provider= Microsoft.Jet.OLEDb.4.0; Data Source = D:\My Documents\Visual Studio 2005\Projects\db2.mdb");
              con.Open();
  
              OleDbDataAdapter thisadapter1 = new OleDbDataAdapter("Select * from tbl_client where fname= '" + fname + "'", con);
              //OleDbCommandBuilder cd = new OleDbCommandBuilder(thisadapter1);
              ds = new DataTable();
              thisadapter1.Fill(ds);
  
              dg.BringToFront();
              dg.Visible = true;
              BindingSource bsource = new BindingSource();
              bsource.DataSource = ds;
              dg.DataSource = bsource;
              dg.Columns[0].Visible = false;
              dg.Columns[1].Visible = false;
              dg.Columns[2].HeaderText = "Fname";
              dg.Columns[2].Width = 45;
              dg.Columns[3].HeaderText = "Lname";
              dg.Columns[3].Width = 45;
              dg.Columns[5].HeaderText = "Gender";
              dg.Columns[5].Width = 46;
              dg.Columns.HeaderText = "Address";
              dg.Columns.Width = 48;
              dg.Columns[4].Visible = false;
              dg.Columns[7].HeaderText = "Phone";
              dg.Columns[7].Width = 45;
              dg.Columns.HeaderText = "Mobile";
              dg.Columns.Width = 45;
              dg.Columns[9].HeaderText = "Email";
              dg.Columns[9].Width = 45;
              dg.Columns[10].HeaderText = "Project_ID";
              dg.Columns[10].Width = 55;
              dg.Columns[11].HeaderText = "Visit Date";
              dg.Columns[11].Width = 55;
              dg.Columns[12].HeaderText = "Agent";
              dg.Columns[12].Width = 45;
              dg.Columns[13].HeaderText = "FeedBack";
              dg.Columns[14].HeaderText = "Remarks";
              string feedback = "FeedBack";
              dg.Columns[13].HeaderText = feedback;
  
              return ds;
  
          }
  
  
     public int update()
     {
        OleDbConnection con = new OleDbConnection(@"provider= Microsoft.Jet.OLEDb.4.0; Data Source = D:\My Documents\Visual Studio 2005\Projects\db2.mdb");
        con.Open();
  
        string sqlUpdate;
        sqlUpdate = "Update tbl_client Set date= @p2, fname=@p3, lname= @p4, mname= @p5, gender= @p6, address= @p7, phone= @p8, mobile=@p9, email=@p10, project_id= @p11, visit_date= @p12, agent= @p13, feetdback1= @p14, remarks= @p15 Where client_id= @p1";
        OleDbParameter[] pUpdate = new OleDbParameter[16];
            
  
        pUpdate[0] = new OleDbParameter("@p1", OleDbType.VarChar, 40,"client_id");
        pUpdate[1] = new OleDbParameter("@p2", OleDbType.VarChar, 40,"date");
        pUpdate[2] = new OleDbParameter("@p3", OleDbType.VarChar, 40,"fname");
        pUpdate[3] = new OleDbParameter("@p4", OleDbType.VarChar, 5,"lname");
        pUpdate[4] = new OleDbParameter("@p5", OleDbType.VarChar, 20,"mname");
        pUpdate[5] = new OleDbParameter("@p6", OleDbType.VarChar, 40, "gender");
        pUpdate = new OleDbParameter("@p7", OleDbType.VarChar, 40, "address");
        pUpdate[7] = new OleDbParameter("@p8", OleDbType.VarChar, 9, "phone");
        pUpdate = new OleDbParameter("@p9", OleDbType.VarChar, 40, "mobile");
        pUpdate[9] = new OleDbParameter("@p10", OleDbType.VarChar, 40, "email");
        pUpdate[10] = new OleDbParameter("@p11", OleDbType.VarChar, 40, "project_id");
        pUpdate[11] = new OleDbParameter("@p12", OleDbType.VarChar, 5, "visit_date");
        pUpdate[12] = new OleDbParameter("@p13", OleDbType.VarChar, 40, "agent");
        pUpdate[13] = new OleDbParameter("@p14", OleDbType.VarChar, 40, "feedback1");
        pUpdate[14] = new OleDbParameter("@p15", OleDbType.VarChar, 5, "remarks");
      
        OleDbCommand cmdUpdate = new OleDbCommand(sqlUpdate,con);
       // cmdUpdate.Parameters.AddRange(pUpdate);
       
  
        OleDbDataAdapter da = new OleDbDataAdapter();
        da.UpdateCommand  = cmdUpdate;
        int i=da.Update(ds);
        ds.AcceptChanges();
         if(i>0)
         {
         MessageBox.Show("database updated");
       }
       return i;
      }
  
     
   }
  
  
  
  the methods are called in the event handlers of search and update_datagrid buttons:
   private void button_search_name_Click(object sender, EventArgs e)
          {
    customer cust= new customer();
             DataTable ds;
             ds=  cust.getdataset(fname, datagrid_search);
  }
  
  and
   private void button_savegrid_Click(object sender, EventArgs e)
          {
              customer cs = new customer();
               int i= cs.update();
          }
  
  best regards,
  Radhika
  
  

  Report abuse