none
Update command cut my InsertCommand.CommandText

    Question

  • hi guys,

    i have a problem and i hope you could help me, sorry for my bad english.
    i've got 2 different situations that seems to be equal, but behaves in two differents ways and i don't understand why.
    i've got one table with an identity for primary key.i set dataadapter and insert command first automatically and then manually.in the second case it works, instead in the first the Update command CUT my sql command.i write some code so you can understand something

    Case A automatic with command builder

     

    da = new SqlDataAdapter("SELECT * FROM Subjects", Settings.Default.CrmConnectionString);
    SqlCommandBuilder bld = new SqlCommandBuilder(da);
    da.InsertCommand = bld.GetInsertCommand(true);
    da.InsertCommand.CommandText += ";SELECT [Code] FROM [Subjects] WHERE [Code] = SCOPE_IDENTITY();";
    
    (...doing some code..., fill dataset dts and add a row to it...)
    
    DataTable dataChanges = dts.Tables[0].GetChanges();
    da.Update(dataChanges);
    
    

     

    at this point, after Update, on da.InsertCommand.CommandText i find my query CUTTED: it's rewrited without the second select with scope identity.
    precisely it's
    INSERT INTO [Subjects] ([Social], [Address], [Cap], [Country], [State], [Phone], [Fax]) VALUES (@Social, @Address, @Cap, @Country, @State, @Phone, @Fax).obviously the insert works fine but the Code field is wrong cause it could't receive the correct identity value.
     
    CASO B manual insert

     

    da = new SqlDataAdapter("SELECT * FROM Subjects", Settings.Default.CrmConnectionString);
    da.InsertCommand = new SqlCommand("INSERT INTO dbo.Subjects (Social, Address, Cap, Country, State, 
    Phone, Fax) " + "VALUES (@Social, @Address, @Cap, @Country, @State,
    @Phone, @Fax); " + "SELECT Code FROM dbo.Subjects " + "WHERE Code = SCOPE_IDENTITY();"); da.InsertCommand.Parameters.Add(new SqlParameter("@Social", SqlDbType.NVarChar, 200,"Social")); da.InsertCommand.Parameters.Add(new SqlParameter("@Address", SqlDbType.NVarChar, 200,"Address")); (etc etc... insert other parameters manually) (...doing some code..., fill dataset dts and add a row to it...) DataTable dataChanges = dts.Tables[0].GetChanges(); da.Update(dataChanges);

     

    at this point, if i go to observe insertcommand.commandtext after Update, i've got compelte query with the second select, so after row insert and Update i find the right Code value. i can't undesrtand the difference between the two codes and why in the first case Update command CUT my query...i hope you help me ;)
    thaks
    Dario

     

    Friday, October 22, 2010 4:08 PM

Answers

  • Hi ratal,

     

    I think this code will give you some opinion, this is written by your request.

     

        private void button2_Click(object sender, EventArgs e)
        {
          SqlConnection conn = new SqlConnection(@"Data Source=.;Initial Catalog=Northwind;Integrated Security=True");
          conn.Open();
          SqlDataAdapter adapter = new SqlDataAdapter("select * from region", conn);
          SqlCommandBuilder commandBuilder = new SqlCommandBuilder(adapter);
          SqlCommand command = null;
     
          // first way
          command = commandBuilder.GetInsertCommand(true);
          command.CommandText += ";select RegionID from Region;";
          command.Parameters["@RegionID"].Value = 6;
          command.Parameters["@RegionDescription"].Value = "abc8";
     
          // second way: please execute the following three lines if you want test the second way and annotate the above four lines
          //command = new SqlCommand("INSERT INTO [region] ([RegionID], [RegionDescription]) VALUES (@RegionID, @RegionDescription);select RegionID from Region");
          //command.Parameters.Add("@RegionID", 9);
          //command.Parameters.Add("@RegionDescription", "abc");
          
          command.Connection = conn;
          adapter.InsertCommand = command;
          SqlDataReader dr = adapter.InsertCommand.ExecuteReader();
          while (dr.Read())
          {
            // you can see the result from the last sql command here
            for (int i = 0; i < dr.FieldCount; i++)
            {
              Console.WriteLine(dr.GetValue(i));
            } 
          }
          dr.Close();
          DataSet ds = new DataSet();
          adapter.Fill(ds);//it will also use the first sql command to fill this dataset in this demo
          this.dataGridView1.DataSource = ds.Tables[0];
        }
    

     

    If there’s anything unclear, please feel free to let me know.

     

    Have a nice day!

    Mike

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

    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to the others community members reading the thread.

     

    *****************************************************

    [All-In-One Code Framework]

    Sample world! You will get more from this world!

    Welcome to the new world!

    Tuesday, October 26, 2010 5:47 PM

All replies

  • Hi ratal,

     

    Welcome to MSDN Forums!

     

    Can you show me the complete method body? I want to see the complete method to make sure if the insert command is from this, “bld.GetInsertCommand(true);”, or you just use the add row method to add one row to the datatable to insert data to the database. I hope you can provide this information then I can clear about what the method had done and it can help us narrow down the problem scope.

     

    Have a nice day!

    Mike

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

    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to the others community members reading the thread.

     

    *****************************************************

    [All-In-One Code Framework]

    Sample world! You will get more from this world!

    Welcome to the new world!

    Tuesday, October 26, 2010 3:27 PM
  • thanks these are the 2 complete cases:

    Case A

     
    private SqlDataAdapter da = null;
    private DataSet dts = null;
    private BindingSource bnd = null;
    private SqlParameter IDParameter;
    
    public void InizializeData() {
          da = new SqlDataAdapter("SELECT * FROM Subjects",  Settings.Default.CrmConnectionString);
          SqlCommandBuilder bld = new SqlCommandBuilder(da);
          da.InsertCommand = bld.GetInsertCommand(true);
          da.InsertCommand.CommandText += ";SELECT [Code] FROM [Subjects] WHERE [Code] = SCOPE_IDENTITY();";
          da.InsertCommand.UpdatedRowSource = UpdateRowSource.Both;
          
          da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
    
          da.UpdateCommand = bld.GetUpdateCommand(true);
          da.DeleteCommand = bld.GetDeleteCommand(true);
    
          dts = new DataSet("Soggetti");
    
          da.Fill(dts);
    
          bnd = new BindingSource(dts, dts.Tables[0].TableName);
    
          gridControl.DataSource = bnd;
    
          gridView.BestFitColumns();
        }
    
        private void btnSave_Click(object sender, EventArgs e) {
          
          DataTable dataChanges = dts.Tables[0].GetChanges();
    
          da.RowUpdated += new SqlRowUpdatedEventHandler(OnRowUpdated);
    
          da.Update(dataChanges);
    
          dts.Tables[0].Merge(dataChanges);
    
          dts.Tables[0].AcceptChanges();
    
        }

    Case B is equal except for the difference explicated in the first post (command and parameters added manually)

     

    Tuesday, October 26, 2010 3:56 PM
  • Hi ratal,

     

    I think this code will give you some opinion, this is written by your request.

     

        private void button2_Click(object sender, EventArgs e)
        {
          SqlConnection conn = new SqlConnection(@"Data Source=.;Initial Catalog=Northwind;Integrated Security=True");
          conn.Open();
          SqlDataAdapter adapter = new SqlDataAdapter("select * from region", conn);
          SqlCommandBuilder commandBuilder = new SqlCommandBuilder(adapter);
          SqlCommand command = null;
     
          // first way
          command = commandBuilder.GetInsertCommand(true);
          command.CommandText += ";select RegionID from Region;";
          command.Parameters["@RegionID"].Value = 6;
          command.Parameters["@RegionDescription"].Value = "abc8";
     
          // second way: please execute the following three lines if you want test the second way and annotate the above four lines
          //command = new SqlCommand("INSERT INTO [region] ([RegionID], [RegionDescription]) VALUES (@RegionID, @RegionDescription);select RegionID from Region");
          //command.Parameters.Add("@RegionID", 9);
          //command.Parameters.Add("@RegionDescription", "abc");
          
          command.Connection = conn;
          adapter.InsertCommand = command;
          SqlDataReader dr = adapter.InsertCommand.ExecuteReader();
          while (dr.Read())
          {
            // you can see the result from the last sql command here
            for (int i = 0; i < dr.FieldCount; i++)
            {
              Console.WriteLine(dr.GetValue(i));
            } 
          }
          dr.Close();
          DataSet ds = new DataSet();
          adapter.Fill(ds);//it will also use the first sql command to fill this dataset in this demo
          this.dataGridView1.DataSource = ds.Tables[0];
        }
    

     

    If there’s anything unclear, please feel free to let me know.

     

    Have a nice day!

    Mike

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

    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to the others community members reading the thread.

     

    *****************************************************

    [All-In-One Code Framework]

    Sample world! You will get more from this world!

    Welcome to the new world!

    Tuesday, October 26, 2010 5:47 PM