SQLCommandBuilder is Dumb?

[blacklocist]

Hi all,

I am having a wee bit trouble with with the SQL Command Builder. It does not build anything but frustration. From all the books I have read and everything this is how you go about updating changes from dataset or datatable without writing your own updating logic.

Build a sqlconnection

build a sqldata adapter

create a datatable or dataset

build a sqlcommandbuilder object

then have the sqldata adapter fill the dataset or table

make some changes to the dataset or table

use the sqldataadpater.Update method and boom changes should happen.

But if I debug and look at the sqldata adpater under Insert, Update, Delete they are null. Isn't the sqlcommand builder designed to build updating logic on the fly. It builds nothing it seems. What am I doing wrong or has anyone else have the same problem. I know the code works cause I have used it in my ASP.Net applications but won't work with windows app.

private void btnSql_Click(object sender, EventArgs e)
        {

            //creat a dataset
            DataSet dsExcel = new DataSet("Excel");

            //create a sql data adapter
            SqlDataAdapter sqlDA = new SqlDataAdapter("Select * from ExcelToSql", sqlConn);
            
            //build commands for the damn data adpater so we can update
            SqlCommandBuilder sqlComm1 = new SqlCommandBuilder(sqlDA);

            //fill the dataset, table called Excel
            sqlDA.Fill(dsExcel, "Excel");

            //make changes to the dataset
            dsExcel.Clear();

            //update please work!!!
            sqlDA.Update(dsExcel, "Excel");




        }//end btnSql_Click

I have even attached a print screen of my laptop in debug mode, look at the highlighted update part. It's null....

Would like to be put in the ritght direction.

[Paladine]

The reason it is Null is this :

dsExcel.Clear();

You are clearing the Dataset, i.e. making it empty, and then updating it. That is why it would read NULL

Example of SQLCommandBuilder

public static DataSet SelectSqlSrvRows(string myConnection, string mySelectQuery, string myTableName)
{
   SqlConnection myConn = new SqlConnection(myConnection);
   SqlDataAdapter myDataAdapter = new SqlDataAdapter();
   myDataAdapter.SelectCommand = new SqlCommand(mySelectQuery, myConn);
   SqlCommandBuilder cb = new SqlCommandBuilder(myDataAdapter);

   myConn.Open();

   DataSet ds = new DataSet();
   myDataAdapter.Fill(ds, myTableName);

   //code to modify data in DataSet here

   //Without the SqlCommandBuilder this line would fail
   myDataAdapter.Update(ds, myTableName);

   myConn.Close();

   return ds;
}

Hope this helps:cool:

[blacklocist]

What is was actually trying to do is just make a fast change to the dataset so I can see the result in the sql server. Okay I was just to damn lazy to write code to add a row.

Well now I am back at work and looking at my ASP project that I mentioned works no problem. It will add the row and everyhing.

I was debugging and found the same things too, the sql adapter update was null. So my next question is where can you, if you can, find the update logic. Also why does this ASP application work and the windows app doesn"t??

[blacklocist]

Okay well I found out that you was 100% right and sorry or not taking your reply more seriously. So I wiped out all my code and started clean and did not use the Dataset.Clear method. And I was like wow this code it taking some time then looked at my sql server and behold, there was my row.

Thanks Man!!

[Paladine]

Okay well I found out that you was 100% right and sorry or not taking your reply more seriously. So I wiped out all my code and started clean and did not use the Dataset.Clear method. And I was like wow this code it taking some time then looked at my sql server and behold, there was my row.

Thanks Man!!

No problem at all.

Glad I could help and you got it working!