How to Save tables(dataset) to database

[NANC]

Hi all GURUs,
I am new in .NET.

Can you please tell me how to:

Save updated tables(dataset) to database.
I my code dosent work, its as follows:

'Caeate new dataset to hold changes from main dataset.
Dim dschanges As DataSet = DsInventory1.GetChanges()
'stop any current edits.
Me.BindingContext(DsInventory1, "Vendor").EndCurrentEdit()
'Get the changes that have been made to main dataset.
dsChanges = CType(DsInventory1.GetChanges, dsInventory)
'Check to see if any changes have been made.
If (Not (dsChanges) Is Nothing) Then
Try
Me.ConnINVENTORY.Open()
'Attempt to update data Source.
DsInventory1.Tables("Vendor").AcceptChanges()
DataAdINVENTORY.Update(dsChanges)
DataAdINVENTORY.Update(DsInventory1, "Vendor")
MsgBox("Record saved Successfully")
Catch updateExcepatoin As System.Exception
MsgBox("Can not save to data Source")
Finally
Me.ConnINVENTORY.Close()
End Try
DsInventory1.Merge(dsChanges)
DsInventory1.AcceptChanges()
End If

[robymix]

In your code 'dschanges' holds changes made to 'DsInventory1'. You only need to update 'dschanges'.

You need to call the Update method of the dataadaptor before calling the AcceptChanges method. Bear in mind you need the Update command for modified rows, DeleteCommand for deleted rows, InsertCommand for newly added rows.

Try something like:

Try
    Me.ConnINVENTORY.Open()
    'Attempt to update data Source.
    DataAdINVENTORY.Update(dsChanges)
    DsInventory1.Tables("Vendor").AcceptChanges()
    MsgBox("Record saved Successfully")
 
Catch updateExcepatoin As System.Exception
    MsgBox("Can not save to data Source")
 
Finally
    Me.ConnINVENTORY.Close()
 
End Try

You do not need to merge after AcceptChanges method has been called -- remember, the dataset is a reference and not a value Type

Goodluck!

[mar_zim]

try this one:

'ds is a dataset
'da is a Sqldataadapter
if ds.HasChanges then
   dim cb as SqlCommandBuilder
   da.SelectCommand = New Sqlcommand(cmdtext,cn)
   cb = new Sqlcommandbuilder(da)
   da.Update(ds,"Customer")
end if

[NANC]

I tried but its not working, In dataADAPTER.UPDATE methoe it gives error.

so, what could be the problem.

I am awaiting for your prompt reply.
thank you.

[mar_zim]

what is the error?

will you pls. show us a little code in your project.

so that we can figure that out..

[bazurdia]

Dim CONNMYDATABASE As New OleDb.OleDbConnection("PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA_ SOURCE=C:\MYDATABASE.MDB")
Dim ADOWNERS As New OleDb.OleDbDataAdapter("SELECT * FROM OWNERS", CONNMYDATABASE)
Dim CBOWNERS As New OleDb.OleDbCommandBuilder(ADOWNERS)
Dim DSOWNERS As New DataSet
Dim OBJROW As DataRow

OBJROW = DSOWNERS.Tables("OWNERS").NewRow
OBJROW.Item("OWNERID") = TXTOWNERID.Text
OBJROW.Item("NAME") = TXTNAME.Text
OBJROW.Item("ADDRESS") = TXTADDRESS.Text
DSOWNERS.Tables("OWNERS").Rows.Add(OBJROW)
ADOWNERS.Update(DSOWNERS, "OWNERS")


PROBABLY YOUR ERROR IS IN THE .UPDATE YOU HAVE TO ADD THE NAME OF YOUR TABLE TOO.

[NANC]

I tried

DataAdINVENTORY.Update(DsInventory1, "Vendor")

still it gives error in UPDATE method..

let me clear myself. to add multiple tables [around 20 tables]
I need one oledbDataAdapter
one oledbDataSet
one oledbConnection
AM I RIGHT ?

If I am configuring all above from code, then I dont need to configure dataAdapter or dataConnection through wizard window [graphical configuration].

I CAN partly use code and partly use wizard window [graphical configuration]

AM I RIGHT ?

what could be the problem

[mar_zim]

yes your right..
try to use this one

DataAdINVENTORY.Update(DsInventory1.Tables( "Vendor"))

[NANC]

my hardLuck,

I tried,

DataAdINVENTORY.Update(DsInventory1.Tables( "Vendor"))

but still, error comes

[robymix]

but still, error comes

What's the exact error message.

[siomai]

Originally posted by mar_zim
try this one:

'ds is a dataset
'da is a Sqldataadapter
if ds.HasChanges then
   dim cb as SqlCommandBuilder
   da.SelectCommand = New Sqlcommand(cmdtext,cn)
   cb = new Sqlcommandbuilder(da)
   da.Update(ds,"Customer")
end if

I tried your code, it only updated my dataset but not my database...also, it doen't update comboboxes? but if I typed the value in my combobox, it then updates my dataset (mind you, only the dataset) I don't understand why this happens, it just happens...

Hope you could get back to me on this...thanks a lot!

[salvelinus]

If you won't post the error message, as several people have asked you, you can't get much useful help.

[CyberHawke]

NANC,

Are you managing more than one table in your dataset?
If so, you will not get this to work.
Additionally, if your datatable is created from a select statement that retrieves data from more than one table, then using the update method will also not work.

In either of these scenarios, you will have to update your data source using commands.

And Please, Please, post the error message