Database Updates

[winesls]

Hi all

 

After much searching through tutorials and books, I must be missing something (or too much or not enough caffeine). As a test, I created a test form with two text boxes as bound controls. I added an OleDbConnection to an Access database; OleDbDataAdapter; DataSet1. The data adapter contains a parameterized query that has an assigned variable. Here's the code:

 

Public Sub New()

MyBase.New()

 

'This call is required by the Windows Form Designer.

InitializeComponent()

OleDbDataAdapter1.Fill(DataSet1)

 

 

'Add any initialization after the InitializeComponent() call

 

End Sub

 

The data flows in yippee-skippy. I then added a button on the form to update any changes made in the text box:

 

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

OleDbDataAdapter1.Update(DataSet1)

End Sub

 

Everything works fine, except no data has changed in the database. Am I missing something here? Everything I have read shows that the data adapter takes care of checking rowstates and taking the appropriate action.

 

Thanks in advance.

[JABE]

You have to properly set up the OleDbDataAdapter1.UpdateCommand property; this property defines how updates will be sent to the backend database.

[winesls]

  Quote

You have to properly set up the OleDbDataAdapter1.UpdateCommand property; this property defines how updates will be sent to the backend database.

 

 

Thanks Jabe.

 

I thought that was taken care of when using the command builder in the data adapter. I see the SQL statement in the designer code under OleUpdateCommand1.

[JABE]

Probably OleUpdateCommand1 wasn't assigned to OleDbDataAdapter1.UpdateCommand?

[winesls]

  Quote

Probably OleUpdateCommand1 wasn't assigned to OleDbDataAdapter1.UpdateCommand?

 

 

As best as I can tell, it is. I've created about as plain vanilla test as I can (.zip enclosed). I'm sure it's something simple, but I cannot figure out what it is.

 

Thanks

test.zipFetching info...

Edited June 8, 2004 by PlausiblyDamp

[pendragon]

I think this thread will help

 

http://www.xtremedotnettalk.com/showthread.php?t=83912

 

At the bottom there is also a link to a tutorial that he has done.

[winesls]

  Quote

I think this thread will help

 

http://www.xtremedotnettalk.com/showthread.php?t=83912

 

At the bottom there is also a link to a tutorial that he has done.

 

Thanks

 

That's certainly a different approach from what I have seen. Please allow me to regurgitate to make sure I get it. We fill the dataset with a single record. We then create a datatable and assign the values from the dataset. Once the edit has been done on the datatable, we update the database through the data adapter.

 

My question is this: At the end of Denae's edit procedure, he adds a new row to the data table, setting its position to the current record. Are there two rows or one at this point? If there is only one, then I assume that the rowstate has changed to modified, and the data adapter will modify the database through an update command.

 

From his notes, it seems he prefers to rely on the command generator wizard only for loading datasets.

 

Thanks

[pendragon]

I think the new row command is just for adding a new row and should not be needed on an edit, I did a test using the program in your zip, this is all I added to your program

 

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

       Dim CurrentRow As DataRow
       CurrentRow = Me.DsTest1.Tables("Customers").Rows(Me.BindingContext( _
           Me.DsTest1.Tables("Customers")).Position)

       With CurrentRow
           .BeginEdit()
           .Item("CompanyName") = Me.TextBox2.Text
           .EndEdit()
       End With

       OleDbDataAdapter1.Update(DsTest1.Customers)

End Sub

 

I don't think that you can change a primary key this way.

 

When I first tried your program after downloading, I looked to see if there where any records to update before doing the .Update command and it came back with nothing so i don't think that databinding is changing the row state. The above should.

 

Sorry I'm not much help but I don't use dataBinding myself (or ADO.NET as I prefer the old ADO :D)

Edited June 8, 2004 by pendragon

[winesls]

  Quote

I think the new row command is just for adding a new row and should not be needed on an edit, I did a test using the program in your zip, this is all I added to your program

 

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

       Dim CurrentRow As DataRow
       CurrentRow = Me.DsTest1.Tables("Customers").Rows(Me.BindingContext( _
           Me.DsTest1.Tables("Customers")).Position)

       With CurrentRow
           .BeginEdit()
           .Item("CompanyName") = Me.TextBox2.Text
           .EndEdit()
       End With

       OleDbDataAdapter1.Update(DsTest1.Customers)

End Sub

 

I don't think that you can change a primary key this way.

 

When I first tried your program after downloading, I looked to see if there where any records to update before doing the .Update command and it came back with nothing so i don't think that databinding is changing the row state. The above should.

 

Sorry I'm not much help but I don't use dataBinding myself (or ADO.NET as I prefer the old ADO :D)

 

That looks really good! I'll give it a try. I was about fifteen percent along on a VB6 project and decided to go with .Net I was hoping to forego having to wallpaper the code editor with constant connections and recordset declarations, close connections, etc. This still looks a lot cleaner than VB6 and ADO I was coming up with.

 

Thanks for your help.