Updating with data adpater

[kyledunn]

I have successfully filled a dataset using the data adapter, added new records to the dataset with the NewRecord function and updated the source database with one call to the data adapter Update function. When I change the data in the fields of the dataset and call the data adapter Update function I thought the source database would get updated with the one call. It's not working for me. The only way I have been able to update the source database was to write a command and use ExecuteNonQuery. What is the C# or VB.NET syntax for updating the source database after the fields in the dataset have been changed using the data adapter?

 

[onedizzydevil]

I know a little about what you are talking about; however, I can not answer your question. But I was reading this today and it might help you figure out what you need. It is a article on "Get Acquainted With ADO.NET" and starting on page 8, Assign a Command Object, it begins talking about Delete, Update, Insert, RejectChanges, AcceptChanges, and how to make your changes to the source.

http://www.fawcette.com/dotnetmag/2001_12/online/online_eprods/agordon/page8.asp

I hope this helps.

Wayne Sellars

"Programming, today is a race between software developers, striving to build bigger and better idiot-proof programs, and the Universe, trying to produce bigger and better idiots. So far, Universe 1 - Programmers 0."

 

[kyledunn]

Hi Wayne,

Boy did that help!!!!!! That was the clearest article on ADO.NET I have seen yet. When I wrote the email above I was updating the dataset table directly, I had not defined the datarow I was working with, I was updating the data table values directly and I think that is what caused the problem. Once I saw the code in the article, it was all very clear. Before I read the article I had moved on to adding a dataview so I can sort the dataset. In the code for updating a row in the dataview I had defined a DataRowView which correct the problem in my code, I just didn't know it yet. I deleted my open, write, close NonExecuteQuery code and replace it with the data adapter update and it worked! This also gave me a much clearer picture of AcceptChanges and RejectChanges allowing me to add and cancel without updating the source. I can't thank you enough for pointing this article out to me!

Kyle

 

[jfrost10]

hey Kyle,

what are you using as the interface for the dataset? Are you using a datagrid/datalist, or just accessing the dataset through objects?

jack

 

[kyledunn]

I'm binding a dataset to a listbox that is part of a custom user control to store entries that are typed into a textbox. As you type in the textbox the listbox lists all items that have the same matching letters. If no letters match the listbox goes away. When the textbox is exited the textbox entry is added to the listbox bound database unless there is already a match in the database.

I am then binding several instances of the custom user control and other regular textboxes to the item database binding each database field to a specific control. All these fields comprise one row in the database. There are several databases that then get combined into a report. I haven't got to the report stage yet but I'm getting closer.

Kyle

 

[ija]

Hi.
Is there a way how to avoid repeating:
[tt]
private void txtColor_TextChanged(object sender, System.EventArgs e)
{
mydataset.tablename.Rows[CurrentIndex]["color"]=txtColor.Text;
}
[/tt]
for each textbox, which is bound to the dataset?

It's not funny when you have to filter out all the cases, when the TextChanged is launched without user interaction. And besides, the value in underlying dataset changed, only the RowStatus property remained "Unchanged" :-(

Help please!

 

[kyledunn]

Hi Jack and Wayne!

Ija,

Yes, there is a way to avoid repeating. Sorry for the delayed response but I finally had time this morning to figure it out.

The parameter "object sender" in the event handler contains the object that triggered the event. To detect which object sent the event, within the event handler you assign an object of the same type to the sender object parameter and that object gives you access to the properties of the sender object.

1. First I'll change your event handler to have the generic name "TextBox_TextChanged".

2. Then you use this generic name when you attach the event handler method to each of your events.

3. In the event handler method you assign the sender object to an object of the same type.

4. You can then access the properties of the sender object to determine to which event the event handler is responding.

Sample 1:

private void TextBox_TextChanged(object sender, System.EventArgs e)
{
Control control = (Control)sender;
if (control == txtColor)
{
mydataset.tablename.Rows[CurrentIndex]["color"]=txtColor.Text;
}
else if (control == txtName)
{
mydataset.tablename.Rows[CurrentIndex]["name"]=txtName.Text;
}
}

I much prefer the switch syntax over the if then else syntax, especially if there are more that 2 options, but you cannot parse an object with switch. This gives me the opportunity to better show that you have access to the original objects properties. I'll use the controls Name property in the switch.

Sample 2:

private void TextBox_TextChanged(object sender, System.EventArgs e)
{
Control control = (Control)sender;
switch (control.Name)
{
case "txtColor":
mydataset.tablename.Rows[CurrentIndex]["color"]=txtColor.Text;
break;
case "txtName":
mydataset.tablename.Rows[CurrentIndex]["name"]=txtName.Text;
break;
}
}

Please note that the object type must match the sending object. You could use this same technique in a Paint event handler with multiple forms sharing the same event method. The Control control = (Control)sender; would be replaced by Form form = (Form)sender; and you would test for the form object or name.

Thanks for asking the question.

Kyle