feedback on DataAdapter.Update

[adshocker]

hi all,

i think i'm beginning to understand how the dataadapter.update method is being used here in ado.net... but i need some verifications first.

here's is my scenario..

at form load, i retrieve data from my database using of course, a connection, an adapter, and a dataset.
sample source:

Dim connection As New SqlConnection("connection string here")
Dim adapter As New SqlDataAdapter("SELECT ID, username, password FROM accounts", connection)

Dim update As New SqlCommand("UPDATE accounts SET username = @username, password = @password WHERE ID = @ID", connection)

update.Parameters.Add("@id", SqlDbType.Int, 4, "id")
update.Parameters.Add("@username", SqlDbType.VarChar, 15, "username")
update.Parameters.Add("@password", SqlDbType.VarChar, 15, "password")

adapter.UpdateCommand = update

dim mydataset as new dataset

adapter.fill(mydataset, "accounts")
datagrid.datasource = mydataset

here is a sample screen after form loaded and data was binded to my datagrid.

then, i try to edit some data in my datagrid like this...

then i put this code in a save button for example.

adapter.Update(mydataset, "accounts")

now, based on the steps i have here, if i click the save button, will it update the changes made to my database so the next time the form loads, it will display records equal to that of the second screen?

i just want to clarify, based on the images, i changed data on 2 rows... will my update command automatically update both rows for me or do i need to do something else?

thanks...

 

[cjard]

hi all,

i think i'm beginning to understand how the dataadapter.update method is being used here in ado.net... but i need some verifications first.

here's is my scenario..

at form load, i retrieve data from my database using of course, a connection, an adapter, and a dataset.
sample source:

Dim connection As New SqlConnection("connection string here")
Dim adapter As New SqlDataAdapter("SELECT ID, username, password FROM accounts", connection)

Dim update As New SqlCommand("UPDATE accounts SET username = @username, password = @password WHERE ID = @ID", connection)

update.Parameters.Add("@id", SqlDbType.Int, 4, "id")
update.Parameters.Add("@username", SqlDbType.VarChar, 15, "username")
update.Parameters.Add("@password", SqlDbType.VarChar, 15, "password")

adapter.UpdateCommand = update

dim mydataset as new dataset

adapter.fill(mydataset, "accounts")
datagrid.datasource = mydataset

Yes, but you personally wouldnt write this code. You would create a dataset and the relevant tableadapters and the IDE would write this code. It does an excellent job of it too.

here is a sample screen after form loaded and data was binded to my datagrid.

Yes, but again.. youre using .NET 2.0 - you wouldnt use a DataGrid. It was replaced by the DataGridView in 2.0

(screenshots)

Tip: I recommend use of PNG or GIF for screenshots. JPEG really doesnt handle harsh changes in contrast well. JPEG is for photos. PNG is for screenshots

now, based on the steps i have here, if i click the save button, will it update the changes made to my database so the next time the form loads, it will display records equal to that of the second screen?

Yes, though one thing you need to bear in mind is that Update doesnt have anything to do with SQL UPDATE. If you call Update, then some combination of the INSERT UPDATE and DELETE queries will run, to ensure the DB is synced with the local data source. If you add a new record, the INSERT query will be used to iupload it. You dont specifically set the insert query in your code, but the adapter (or the dataset generator which you ought to eb using) ill be able to guess it. Without a primary key, it wont generate the uPDATE or DELETE for you though.

i just want to clarify, based on the images, i changed data on 2 rows... will my update command automatically update both rows for me or do i need to do something else?

thanks...

If you currently have a concern that it isnt working out like that, you might want to read the DNU link in my signature

 

[adshocker]

thanks... but i got a question about table adapters though..

i recently installed .NET 2.0 Framework.. not sure if its the same with .NET 2.0 but i don't see / i'm unable to use table adapter... i am using visual basic.net 2003 by the way... not sure if its already available in my version... can you verify please... thanks.

 

[cjard]

thanks... but i got a question about table adapters though..

i recently installed .NET 2.0 Framework..

Doesnt mean you can produce .NET 2.0 exes though. Thats what VS2005 is for.

not sure if its the same with .NET 2.0 but i don't see / i'm unable to use table adapter... i am using visual basic.net 2003 by the way...

Howcome you told us you used 2005 when you signed up? Edit your profile back to 1.1/2003

not sure if its already available in my version... can you verify please... thanks.

It isnt, sorry. Advice was given based on what you stated you were using at the time the post was written, which under your name said ".NET 2.0"

 

[adshocker]

i'm sorry.. i put .NET 2.0 coz i thought .NET 2.0 is the same with .NET 2.0 Framework... i'll download and install the .NET 2.0 in a bit... by the way, table adapter comes with visual basic.net 2005 and not with .NET 2.0?

 

[cjard]

Er, yeah.. if you use one of my compiled programs that I wrote with VS2005 that uses a tableadapter, then your .NET FW 2.0 will support my app..

VS2003 makes exes targeting .NET 1.1
VS2005 makes exes targeting .NET 2.0

Ford make cars targeting the low end of the consumer market
Mercedes Benz make cars targeting the prestige end of the consumer market


It's thus cast in stone

2003 is 4 years old now; time to upgrade methinks!

 

[adshocker]

Er, yeah.. if you use one of my compiled programs that I wrote with VS2005 that uses a tableadapter, then your .NET FW 2.0 will support my app..

VS2003 makes exes targeting .NET 1.1
VS2005 makes exes targeting .NET 2.0

Ford make cars targeting the low end of the consumer market
Mercedes Benz make cars targeting the prestige end of the consumer market


It's thus cast in stone

2003 is 4 years old now; time to upgrade methinks!

err... sorry, didn't know i cant use .Net 2.0 if i was using VS 2003... anyways, i've changed my signature to avoid any more confusions.

thanks...

 

[cjard]

you should find the DW1 link in my signature an interesting read

 

[adshocker]

i got everything working except for one last glitch...

it seems that every after changes i made to my datagrid data, i need to press <Enter> so that the adapter.Update will actually save changes to my database...

i did some research and it turns out pressing enter validates the grid cell or something like that...

so my only question is, is there a way to save these changes even without pressing enter after every changes on the data?

 

[Arg81]

Don't think so, not unless there's a call you can do to say "End dataGrid edit"

I remember reading something long ago about never letting users edit data in a grid, to instead bind a "model" form to that row and edit the data on that form.

Ever since I've done it that way, although I have 1 grid that gets edited directly - and I have EXACTLY the same problem (even in VS2005) so I'm interested to see if there is a solution (my user has to press tab in order to start a new row, then save - sometimes they forget to tab and that currently entered row is not saved).

 

[cjard]

sometimes they forget to tab and that currently entered row is not saved).

Perhaps you should call dataGridView.EndEdit() or underlyingBindingsource.EndEdit() before you save..

 

[cjard]

so my only question is, is there a way to save these changes even without pressing enter after every changes on the data?

read http://msdn2.microsoft.com/en-us/library/system.windows.forms.datagrid.endedit(vs.71).aspx

ensure you end before you commit to the db

 

[adshocker]

ok.. thanks... let me try this first.

 

[Arg81]

Perhaps you should call dataGridView.EndEdit() or underlyingBindingsource.EndEdit() before you save..

Tried that (me.bindingsource.endedit) , but didn't make a difference, the only way I could see the row saved was to move into another row.
I do use ComponentOne grids, so I sent an email to their support, but I was having the same issue with a standard grid when I was testing the problem

 

[cjard]

Tried that (me.bindingsource.endedit) , but didn't make a difference, the only way I could see the row saved was to move into another row.

Asking the grid to validate (remember that grid sits on top of bind source, but in edit mode the grid MIGHT NOT put the row into the bind source) might be the solution

I do use ComponentOne grids, so I sent an email to their support, but I was having the same issue with a standard grid when I was testing the problem

I never encountered this, tbh. I found with a standard grid, that EndEdit on the bind source was sufficient