how to update database using dataset and stored procedures

how to update database using dataset and stored procedures

• srividyaramesha

  Mate you'll have to give a little more info
  
  
  

• Sam Jost

  Follow these steps:-

  1: populate the dataset

  2. Make the changes that you want

  3.Create update,insert ,delete commands for the dataadaper

  3.pass the dataset to dataAdapters update method.

  The code will look as follows:-

  SqlDataAdapter da = new SqlDataAdapter();

  SqlCommand com = new SqlCommand();

  DataSet ds = new DataSet();

  SqlCommand com1 = new SqlCommand();

  SqlCommand com2 = new SqlCommand();

  SqlCommand com3 = new SqlCommand();

  SqlParameter name = new SqlParameter();

  SqlParameter brand = new SqlParameter();

  SqlParameter id = new SqlParameter();

  SqlParameter name2 = new SqlParameter();

  SqlParameter brand2 = new SqlParameter();

  SqlParameter id2 = new SqlParameter();

  try

  {

  com1.CommandText = "update products set prodname=@name,brand=@brand where prodid=@id";

  com2.CommandText = "insert into products(prodname,brand) values(@name,@brand)";

  com3.CommandText = "delete from products where prodid=@id";

  com1.Connection = Con;//connection object

  com2.Connection = Con;

  com3.Connection = Con;

  da.InsertCommand = com2;

  da.UpdateCommand = com1;

  da.DeleteCommand = com3;

  name = com1.Parameters.Add("@name", SqlDbType.NVarChar);

  name.SourceColumn = dt.Columns[1].ToString(); //dt is the table in the dataset in question, that has been updated

  name.SourceVersion = DataRowVersion.Current;

  brand = com1.Parameters.Add("@brand", SqlDbType.NVarChar);

  brand.SourceColumn = dt.Columns[2].ToString();

  brand.SourceVersion = DataRowVersion.Current;

  id = com1.Parameters.Add("@id", SqlDbType.NVarChar);

  id.SourceColumn = dt.Columns[0].ToString();

  id.SourceVersion = DataRowVersion.Original;

  name2 = com2.Parameters.Add("@name", SqlDbType.NVarChar);

  name2.SourceColumn = dt.Columns[1].ToString();

  name2.SourceVersion = DataRowVersion.Current;

  brand2 = com2.Parameters.Add("@brand", SqlDbType.NVarChar);

  brand2.SourceColumn = dt.Columns[2].ToString();

  brand2.SourceVersion = DataRowVersion.Current;

  id2 = com3.Parameters.Add("@id", SqlDbType.Int);

  id2.SourceColumn = dt.Columns[0].ToString();

  id2.SourceVersion = DataRowVersion.Original;

  da.Update(dt); //Here you can also supply the dataset

  dt.AcceptChanges();

  return "";

  
  

• Martijn Mulder

  anyone...
  
  

• polymorphicx

  found no errors when running the code and my sp works fine when im not using dataset when saving data.
  
  
  

• Michael Gates

  using (SqlConnection SqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["PPADB"].ConnectionString))
  {
  SqlDataAdapter adapter = new SqlDataAdapter();
  SqlCommand command = new SqlCommand("espPPAInsert",SqlConn);
  
  command.CommandType = CommandType.StoredProcedure;
  
  SqlParameter CYear = new SqlParameter();
  SqlParameter Office_ID = new SqlParameter();
  SqlParameter Modified_By = new SqlParameter();
  SqlParameter Date_Modified = new SqlParameter();
  SqlParameter Status = new SqlParameter();
  
  CYear = command.Parameters.Add("@CYear",SqlDbType.Int);
  CYear.SourceColumn = prmDS.Tables[0].Columns[0].ToString();
  CYear.SourceVersion = DataRowVersion.Current;
  
  Office_ID = command.Parameters.Add("@Office_ID",SqlDbType.Int);
  Office_ID.SourceColumn = prmDS.Tables[0].Columns[1].ToString();
  Office_ID.SourceVersion = DataRowVersion.Current;
  
  Modified_By = command.Parameters.Add("@Modified_By",SqlDbType.Int);
  Modified_By.SourceColumn = prmDS.Tables[0].Columns[2].ToString();
  Modified_By.SourceVersion = DataRowVersion.Current;
  
  Date_Modified = command.Parameters.Add("@Date_Modified",SqlDbType.DateTime);
  Date_Modified.SourceColumn = prmDS.Tables[0].Columns[4].ToString();
  Date_Modified.SourceVersion = DataRowVersion.Current;
  
  Status = command.Parameters.Add("@Status",SqlDbType.Int);
  Status.Direction = ParameterDirection.Output;
  
  adapter.InsertCommand = command;
  adapter.Update(prmDS.Tables[0]);
  
  here's my code. but it doesnt work
  
  this is my stored procedure
  
  CREATE PROCEDURE espPPAInsert
  (
  @CYear INT,
  @Office_ID INT,
  @Modified_By INT,
  @Date_Modified DATETIME,
  @prmOutStatus int output
  
  )
  
  AS
  INSERT INTO PPA
  (CYear,
  Office_ID,
  Modified_By,
  Date_Modified)
  VALUES
  (@CYear,
  @Office_ID,
  @Modified_By,
  @Date_Modified)
  
  
  IF @@ERROR <> 0
  SELECT @prmOutStatus =1
  ELSE
  SELECT @prmOutStatus =0
  GO
  
  
  
  

• Richard Hough

  Are you getting any exceptions What do you see when tracing the sproc in SQL Server
  
  
  
• how to update database using dataset and stored procedures