none
SQL Data Adaper Update issue for repeating table RRS feed

  • Question

  •  Hi, I am having a issue with dataadapter for update query;  all items are updated except repeating table;  Repeating table update with last executed value;  there are 20 items in the table and its updated with last value.  (CID and CA columns).  Here is the code, can any one assist this issue please.  Thanks.

    public void UpdateTable(DataTable dtTable)
            {
                string connectionString = GetDBConnectionString();
                // Connect to the AdventureWorks database.
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    // Create a SqlDataAdapter.
                    SqlDataAdapter adapter = new SqlDataAdapter("Select Column1MID,Column2MID,IDate, IType,IResn,ModifiedBy, CId, CA From tblTable Where Column1MID =-1", connectionString);
                    // Create a SqlCommand to execute the stored procedure.
                    adapter.InsertCommand = new SqlCommand("[spUpdateTable]", connection);
                    adapter.InsertCommand.CommandType = CommandType.StoredProcedure;
                    //SqlParameter parameter= new SqlParameter();
                    adapter.InsertCommand.Parameters.Add("@Column1MID", SqlDbType.BigInt, 100, "Column1MID");
                    adapter.InsertCommand.Parameters.Add("@Column2MID", SqlDbType.BigInt, 100, "Column2MID");
                    adapter.InsertCommand.Parameters.Add("@IDate", SqlDbType.DateTime, 100, "IDate");
                    adapter.InsertCommand.Parameters.Add("@IType", SqlDbType.VarChar, 100, "IType");
                    adapter.InsertCommand.Parameters.Add("@IResn", SqlDbType.VarChar, 100, "IResn");
                    adapter.InsertCommand.Parameters.Add("@CId", SqlDbType.Int, 100, "CId");
                    adapter.InsertCommand.Parameters.Add("@CA", SqlDbType.VarChar, 100, "CA");
                    adapter.InsertCommand.Parameters.Add("@UserName", SqlDbType.VarChar, 100, "ModifiedBy");
                    adapter.UpdateBatchSize = 1;
                    connection.Open();
                    DataTable dtCuestion = new DataTable();
                    adapter.Fill(dtCuestion);
                    foreach (DataRow row in dtTable.Rows)
                    {
                        DataRow dRow = dtCuestion.NewRow();
                        dRow["Column1MID"] = row["Column1MID"].ToString();
                        dRow["Column2MID"] = row["Column2MID"].ToString();
                        dRow["IDate"] = row["IDate"].ToString();
                        dRow["IType"] = row["IType"].ToString();
                        dRow["IResn"] = row["IResn"].ToString();
                        dRow["CId"] = row["ClinicalId"].ToString();
                        dRow["CA"] = row["CA"].ToString();
                        dRow["ModifiedBy"] = row["ModifiedBy"].ToString();
                        dtCuestion.Rows.Add(dRow);
                    }
                    adapter.Update(dtCuestion);
                    connection.Close();
                }
            }

    Thursday, December 18, 2014 5:41 PM

Answers

All replies

  •  Hi, I am having a issue with dataadapter for update query;  all items are updated except repeating table;  Repeating table update with last executed value;  there are 20 items in the table and its updated with last value.  (CID and CA columns).  Here is the code, can any one assist this issue please.  Thanks.
     public void UpdateTable(DataTable dtTable)
            {
                string connectionString = GetDBConnectionString();
                // Connect to the AdventureWorks database.
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    // Create a SqlDataAdapter.
                    SqlDataAdapter adapter = new SqlDataAdapter("Select Column1MID,Column2MID,IDate, IType,IResn,ModifiedBy, CId, CA From tblTable Where Column1MID =-1", connectionString);
                    // Create a SqlCommand to execute the stored procedure.
                    adapter.InsertCommand = new SqlCommand("[spUpdateTable]", connection);
                    adapter.InsertCommand.CommandType = CommandType.StoredProcedure;
                    //SqlParameter parameter= new SqlParameter();
                    adapter.InsertCommand.Parameters.Add("@Column1MID", SqlDbType.BigInt, 100, "Column1MID");
                    adapter.InsertCommand.Parameters.Add("@Column2MID", SqlDbType.BigInt, 100, "Column2MID");
                    adapter.InsertCommand.Parameters.Add("@IDate", SqlDbType.DateTime, 100, "IDate");
                    adapter.InsertCommand.Parameters.Add("@IType", SqlDbType.VarChar, 100, "IType");
                    adapter.InsertCommand.Parameters.Add("@IResn", SqlDbType.VarChar, 100, "IResn");
                    adapter.InsertCommand.Parameters.Add("@CId", SqlDbType.Int, 100, "CId");
                    adapter.InsertCommand.Parameters.Add("@CA", SqlDbType.VarChar, 100, "CA");
                    adapter.InsertCommand.Parameters.Add("@UserName", SqlDbType.VarChar, 100, "ModifiedBy");
                    adapter.UpdateBatchSize = 1;
                    connection.Open();
                    DataTable dtCuestion = new DataTable();
                    adapter.Fill(dtCuestion);
                    foreach (DataRow row in dtTable.Rows)
                    {
                        DataRow dRow = dtCuestion.NewRow();
                        dRow["Column1MID"] = row["Column1MID"].ToString();
                        dRow["Column2MID"] = row["Column2MID"].ToString();
                        dRow["IDate"] = row["IDate"].ToString();
                        dRow["IType"] = row["IType"].ToString();
                        dRow["IResn"] = row["IResn"].ToString();
                        dRow["CId"] = row["ClinicalId"].ToString();
                        dRow["CA"] = row["CA"].ToString();
                        dRow["ModifiedBy"] = row["ModifiedBy"].ToString();
                        dtCuestion.Rows.Add(dRow);
                    }
                    adapter.Update(dtCuestion);
                    connection.Close();
                }
            }

    • Moved by Kristin Xie Thursday, December 18, 2014 8:22 AM move to better forum
    • Merged by Fred BaoModerator Friday, December 19, 2014 5:31 AM Duplicate
    Wednesday, December 17, 2014 6:10 PM
  • A DataAdapter update method needs an SQL UpdateCommand, an SQL InsertCommand and a SQL DeleteCommand transaction.

    You have only one. 

    By the way the AdoNet Dataset has his own forum

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=adodotnetdataset


    Success
    Cor


    Wednesday, December 17, 2014 6:34 PM
  • Hello V.S268,

    What operation are you trying to do, insertion or update? From your provided code, I am confused because the stored procedure shows it should be an update sql statement while you assign it to the InsertCommand object. Not sure how you write the stored produce, however, you could use the SqlCommandBuilder Class to automatically generates single-table commands that are used to reconcile changes made to a DataSet with the associated SQL Server database so that you do not need to write a stored produce. Please have a try and if it does not work for you, please share your stored produce,the tblTable schema and some data in the dtTable object.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, December 19, 2014 2:48 AM
    Moderator
  •    adapter.Fill(dtCuestion);
                   
    foreach (DataRowrow indtTable.Rows)
                   
    {
                       
    DataRowdRow =dtCuestion.NewRow();
                        dRow
    ["Column1MID"] =row["Column1MID"].ToString();
                        dRow
    ["Column2MID"] =row["Column2MID"].ToString();
                        dRow
    ["IDate"] =row["IDate"].ToString();
                        dRow
    ["IType"] =row["IType"].ToString();
                        dRow
    ["IResn"] =row["IResn"].ToString();
                        dRow
    ["CId"] =row["ClinicalId"].ToString();
                        dRow
    ["CA"] =row["CA"].ToString();
                        dRow
    ["ModifiedBy"] =row["ModifiedBy"].ToString();
                        dtCuestion
    .Rows.Add(dRow);
                   
    }
                    adapter
    .Update(dtCuestion);

    Before the foreach loop and after the foreach loop there is no change in adapter. So the statement adapter.Update(dtCuestion) will not do nothing.

    ------------------------------------------------

    Hope above help !

    Doanh


    • Edited by Doanh, Qh Friday, December 19, 2014 7:48 AM bold
    Friday, December 19, 2014 7:47 AM