Help with SqlDataAdapter Update command (C#)

  • Thread starter Thread starter Norton
  • Start date Start date
N

Norton

I am pulling my hair out over this one. I have done this same kind of thing in VB.Net but in C# it doesn't work. I dont' receive any errors either, it just won't update the pubs.Authors table. Here is the code:

private void CodedDataGrid_Load(object sender, System.EventArgs e)
{
//create a new connection object
String ConnectString = "Server=" + "MyMachine" + "; " + "User ID=" + "sa" + "; Password=" + "password" + "; initial catalog = " + "pubs" + ";";
conn = new SqlConnection(ConnectString); //create a new adapter
adp = new SqlDataAdapter("Select au_id,au_lname,au_fname,contract FROM authors",conn);

//creat ea new dataset
ds = new DataSet();
adp.Fill(ds);
this.dbGrid.DataSource = ds;

}

private void btnNew_Click(object sender, System.EventArgs e)
{
AdapterInsert();


//add a new record to the dataset, but first

try
{
adp.Update(ds,"Table");
}
catch (System.Data.SqlClient.SqlException sqlEx)
{
MessageBox.Show(sqlEx.Message);
}
catch(System.Exception ex)
{
MessageBox.Show(ex.Message);
}

}
private void AdapterInsert()
{
insComm = new SqlCommand();
adp.InsertCommand = insComm;
adp.InsertCommand.Connection = conn;

adp.InsertCommand.CommandType =

CommandType.StoredProcedure;
adp.InsertCommand.CommandText = "InsertAuthor";

SqlParameter MySqlParam1 = new SqlParameter("@au_id",SqlDbType.VarChar);
MySqlParam1.Value = this.txtID.Text;
MySqlParam1.SourceColumn = "au_id";
adp.InsertCommand.Parameters.Add(MySqlParam1);

SqlParameter MySqlParam2 = new SqlParameter("@au_fname",SqlDbType.VarChar);
MySqlParam2.Value = this.txtFName.Text;
MySqlParam2.SourceColumn = "au_fname";
adp.InsertCommand.Parameters.Add(MySqlParam2);

SqlParameter MySqlParam3 = new SqlParameter("@au_lname",SqlDbType.VarChar);
MySqlParam3.Value = this.txtLName.Text;
MySqlParam3.SourceColumn = "au_lname";
adp.InsertCommand.Parameters.Add(MySqlParam3);

SqlParameter MySqlParam4 = new SqlParameter("@contract",SqlDbType.Bit);
MySqlParam4.Value = "1";
MySqlParam4.SourceColumn = "contract";
adp.InsertCommand.Parameters.Add(MySqlParam4);

}

What the heck am I missing here?

Thanks.
 
Well, apparently I had forgotten all the automagical stuff that a data adapter does for me. Not only were my parameter source columns incorrectly named, but I also did not add the row to the dataset table.

Geez!

Norton
I am pulling my hair out over this one. I have done this same kind of thing in VB.Net but in C# it doesn't work. I dont' receive any errors either, it just won't update the pubs.Authors table. Here is the code:

private void CodedDataGrid_Load(object sender, System.EventArgs e)
{
//create a new connection object
String ConnectString = "Server=" + "MyMachine" + "; " + "User ID=" + "sa" + "; Password=" + "password" + "; initial catalog = " + "pubs" + ";";
conn = new SqlConnection(ConnectString); //create a new adapter
adp = new SqlDataAdapter("Select au_id,au_lname,au_fname,contract FROM authors",conn);

//creat ea new dataset
ds = new DataSet();
adp.Fill(ds);
this.dbGrid.DataSource = ds;

}

private void btnNew_Click(object sender, System.EventArgs e)
{
AdapterInsert();


//add a new record to the dataset, but first

try
{
adp.Update(ds,"Table");
}
catch (System.Data.SqlClient.SqlException sqlEx)
{
MessageBox.Show(sqlEx.Message);
}
catch(System.Exception ex)
{
MessageBox.Show(ex.Message);
}

}
private void AdapterInsert()
{
insComm = new SqlCommand();
adp.InsertCommand = insComm;
adp.InsertCommand.Connection = conn;

adp.InsertCommand.CommandType =

CommandType.StoredProcedure;
adp.InsertCommand.CommandText = "InsertAuthor";

SqlParameter MySqlParam1 = new SqlParameter("@au_id",SqlDbType.VarChar);
MySqlParam1.Value = this.txtID.Text;
MySqlParam1.SourceColumn = "au_id";
adp.InsertCommand.Parameters.Add(MySqlParam1);

SqlParameter MySqlParam2 = new SqlParameter("@au_fname",SqlDbType.VarChar);
MySqlParam2.Value = this.txtFName.Text;
MySqlParam2.SourceColumn = "au_fname";
adp.InsertCommand.Parameters.Add(MySqlParam2);

SqlParameter MySqlParam3 = new SqlParameter("@au_lname",SqlDbType.VarChar);
MySqlParam3.Value = this.txtLName.Text;
MySqlParam3.SourceColumn = "au_lname";
adp.InsertCommand.Parameters.Add(MySqlParam3);

SqlParameter MySqlParam4 = new SqlParameter("@contract",SqlDbType.Bit);
MySqlParam4.Value = "1";
MySqlParam4.SourceColumn = "contract";
adp.InsertCommand.Parameters.Add(MySqlParam4);

}

What the heck am I missing here?

Thanks.
 
Norton:

For future reference, there's a few things you can try that may help you
out. If your dataset doesn't have changes, calling update all year won't do
anything. http://www.knowdotnet.com/articles/efficient_pt4.html (this
should help a bit). Anyway, one thing helpful in a development/test/debug
scenario is inserting an assertion right before you call update :

Debug.Assert(whateverDataSet.HasChanges, "No Changes Present - Update Won't
Do Anything");

Checking for HasChanges in production is useless b/c calling haschanges
loops through the dataset anyway checking for changed rowstates, and that's
what Update does as well - the only difference is update fires updates if
there are any - but if there aren;'t the net effect is the same. However in
Test it's good to know right off the bat that an update that you expect to
work isn't in fact working. If it has changes as you expect then nothing
will be any different, if it doesn't, then the asssertion will fail and a
big ugly box will pop up telling you so (but will allow you to continue
break etc). For that reason I use Debug.Assert(false,
exceptionName.ToString()); in my try/catch blocks too b/c I can stop the app
from there without clicking Ok in a message box and then closing the app or
I can continue. Also, I don't have to worry about removing the code for
release builds - it won't execute if Debug is defined so that's also a
convenience.

The nice thing about checking this way is that you'll know immediately and
won't have to physically examine the db only to find it's not updated
(besides, you might miss it when you check the db - and if you want you can
still do both).

Another thing that's often a helpful tool is writing code for the
RowUpdating event - as a general habit, I'll do this so I can show the user
some progress while the db is updating and it has the side effect of helping
me verify that something happened (I also run my data access stuff in a
separate thread - if you do so as well, be careful of the standard thread
related issues when giving the user some feedback, dont' want to create
other problems for yourself).

Another thing that's a good idea is reconfiguring your dataadapters
periodically unlless you are positive that none of the field names or
anything have changed. This can cause some problem too particularly ifyou
use SELECT * and add additional colulmns.

Bill Vaughn has an excellent article on www.betav.com -> Articles -> MSDN
titled Weaning Developers from the CommandBuilder that will probably
convince you to roll your own logic in most instances. This elminates the
problems associated with AutoMagic code generation of your logic - it's a
mixed blessing b/c while it saves you some time, it also puts some distance
between you and your code and unless you have a firm understanding of what
it does for you (the configuration wizard), it can inhibit learning (or help
you forget) and make it a little more difficult to debug (the constructors
it uses are so verbose that it's very difficult to read at times).

Anyway, I know the stuff I recommended helped me along the way (As did
Bill's ADO.NET Best practices book and David Sceppa's ADO.NET Core
Reference) and I figured I'd mention it.

Cheers,

Bill

--
W.G. Ryan MVP Windows - Embedded

Have an opinion on the effectiveness of Microsoft Embedded newsgroups?
Let Microsoft know!
https://www.windowsembeddedeval.com/community/newsgroups
Well, apparently I had forgotten all the automagical stuff that a data
adapter does for me. Not only were my parameter source columns incorrectly
named, but I also did not add the row to the dataset table.

Geez!

Norton
I am pulling my hair out over this one. I have done this same kind of
thing in VB.Net but in C# it doesn't work. I dont' receive any errors
either, it just won't update the pubs.Authors table. Here is the code:

private void CodedDataGrid_Load(object sender, System.EventArgs e)
{
//create a new connection object
String ConnectString = "Server=" + "MyMachine" + "; " + "User ID=" + "sa"
+ "; Password=" + "password" + "; initial catalog = " + "pubs" + ";";
conn = new SqlConnection(ConnectString); //create a new adapter
adp = new SqlDataAdapter("Select au_id,au_lname,au_fname,contract FROM
authors",conn);

//creat ea new dataset
ds = new DataSet();
adp.Fill(ds);
this.dbGrid.DataSource = ds;

}

private void btnNew_Click(object sender, System.EventArgs e)
{
AdapterInsert();


//add a new record to the dataset, but first

try
{
adp.Update(ds,"Table");
}
catch (System.Data.SqlClient.SqlException sqlEx)
{
MessageBox.Show(sqlEx.Message);
}
catch(System.Exception ex)
{
MessageBox.Show(ex.Message);
}

}
private void AdapterInsert()
{
insComm = new SqlCommand();
adp.InsertCommand = insComm;
adp.InsertCommand.Connection = conn;

adp.InsertCommand.CommandType =

CommandType.StoredProcedure;
adp.InsertCommand.CommandText = "InsertAuthor";

SqlParameter MySqlParam1 = new
SqlParameter("@au_id",SqlDbType.VarChar);
MySqlParam1.Value = this.txtID.Text;
MySqlParam1.SourceColumn = "au_id";
adp.InsertCommand.Parameters.Add(MySqlParam1);

SqlParameter MySqlParam2 = new
SqlParameter("@au_fname",SqlDbType.VarChar);
MySqlParam2.Value = this.txtFName.Text;
MySqlParam2.SourceColumn = "au_fname";
adp.InsertCommand.Parameters.Add(MySqlParam2);

SqlParameter MySqlParam3 = new
SqlParameter("@au_lname",SqlDbType.VarChar);
MySqlParam3.Value = this.txtLName.Text;
MySqlParam3.SourceColumn = "au_lname";
adp.InsertCommand.Parameters.Add(MySqlParam3);

SqlParameter MySqlParam4 = new SqlParameter("@contract",SqlDbType.Bit);
MySqlParam4.Value = "1";
MySqlParam4.SourceColumn = "contract";
adp.InsertCommand.Parameters.Add(MySqlParam4);

}

What the heck am I missing here?

Thanks.
 
Back
Top