update dataset and database

  • Thread starter Thread starter Jean Christophe Avard
  • Start date Start date
J

Jean Christophe Avard

Hi! I have a dataset that retreive all the item information from the
database. I need to be able to edit them, in the dataset and in the
database.

I have this code, could anyone tell me if I'm in the good way, cause I build
that code from a book example, but nothing works.

I HAVE THIS CODE TO SAVE THE CHANGE OF THE DATASET

dstPlant.Tables("plant").Rows(intEditRow)("latin_name") = "KLHJJKJKJKHHHHHH"
dstPlant.GetChanges()

dadPlant.Update(dstPlant, "plant")





AND THIS IS THE CODE I USE TO DECLARE THE DATA SET AND DATA ADAPTER

dadPlant = New SqlDataAdapter(STR_SQL_PLANT_SELECT_ALL, cnnSignspro)

' instantiate the sql command

cmmUpdate = New SqlCommand("UPDATE tblItems SET latin_name=@latin_name WHERE
id_item=@id", cnnSignspro)

' instantiater the da update command

dadPlant.UpdateCommand = cmmUpdate

' add update parameter

cmmUpdate.Parameters.Add("@latin_name", SqlDbType.VarChar, 70, "latin_name")

prmUpdate = dadPlant.UpdateCommand.Parameters.Add("@id_item", SqlDbType.Int,
4, "id_item")

prmUpdate.Direction = ParameterDirection.Input

prmUpdate.SourceVersion = DataRowVersion.Original

' instantiate the data set

dstPlant = New DataSet("plant")

' fill the data set

dadPlant.Fill(dstPlant, "plant")





ANYONE!?!?!?
 
Jean - before you call update, throw in this line

System.Diagnostics.Debug.Assert(dstPlant.HasChanges(), "There are no changes
present so any updates won't do anything")

If you don't have changes, then nothing is going to happen. Your getchanges
line does nothing here. Typically you'd do something like

DataSet ds2 = dstPlant.GetChanges();
dadPlant.Update(ds2, "plant")

THis will create a dataset of only the changed rows which you would pass to
the adapter (in bandwidth restricted scenarios - this can be a great boost
to performance instead of passing a whole dataset which has many rows that
haven't changed)
 
how do I update the dataset.....

lets say I want to set the latin_name to the value of latinName.text when
the user click the save button. then I want to update the database...
 
I get this error

"Additional information: Update requires a valid UpdateCommand when passed
DataRow collection with modified rows."

when I try this code.
System.Diagnostics.Debug.Assert(dstPlant.HasChanges(), "There are no changes
present so any updates won't do anything")

dstPlant.Tables("plant").Rows(intEditRow)("latin_name") = "KJHKJHJKHKJ"

If dstPlant.HasChanges() Then

Dim ds2 = dstPlant.GetChanges()

dadPlant.Update(ds2, "plant")

End If
 
Have you specified an Insert/Delete and Update command for the dataadapter?
If not, that's the problem. The easiest way to do this is run through the
dataadapter configuration wizard (for right now - I'd not use this once I
was familiar with the way adapters work) and it will generate them for you.
You can just use a new adapter and use it, or copy it's syntax - you'll need
to create the command with paramaters and map those fields to columns - (or
you can roll your own logic but if you're new to this, it's a lot more
complex ) which the adapter does for you.
 
BTW, on the Debug.Assert line - it's not failing? YOu're not getting a big
obnoxious message box saying "There are no changes present"? If not,this is
a good sign b/c that means there are changes that can be submitted to the
db.. THe way the adapter works is that it loops through each row, checking
the rowstates. If they are modified, deleted, added etc, then it will look
to the corresponding command in the adapter (which in this case appears not
to exist, that's why you're getting the error you ware) and fire that
command for the row.

For right now, you're best bet is to use a CommandBuilder (which is lame but
works in a pinch and is easy to use ) or use the Configuratino wizard.

Check out http://www.betav.com -> Articles -> MSDN - Weaning developers from
the commandBuilder which discusses update scenarios in depth.
 
Jean,

In addition to Bill.
If you use a binded column to a textbox than the column will pushed in the
datatable by a rowchange.

If there is no rowschange than you can force that with.

BindingContext(mydatasource).endcurrentedit

I hope this helps,

Cor
 
thank a lot dude!
W.G. Ryan MVP said:
BTW, on the Debug.Assert line - it's not failing? YOu're not getting a
big obnoxious message box saying "There are no changes present"? If
not,this is a good sign b/c that means there are changes that can be
submitted to the db.. THe way the adapter works is that it loops through
each row, checking the rowstates. If they are modified, deleted, added
etc, then it will look to the corresponding command in the adapter (which
in this case appears not to exist, that's why you're getting the error you
ware) and fire that command for the row.

For right now, you're best bet is to use a CommandBuilder (which is lame
but works in a pinch and is easy to use ) or use the Configuratino wizard.

Check out http://www.betav.com -> Articles -> MSDN - Weaning developers
from the commandBuilder which discusses update scenarios in depth.
Click to expand...
 
Back
Top