How to Save table to database

[NANC]

Hi all Gurus,
I am new to .net & I have very simple problem.

to save changes from dataset to database, I have written following code:


Private Sub updateDataSource()

'stop any current edits.
Me.BindingContext(DsInventory1, "Vendor").EndCurrentEdit()
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


It seems that code runs fine but no changes are made in database,
can you please help me out earlier.

Thank you

[Mike Hildner]

Not really sure, but I don't think you want to call the AcceptChanges method.

In addition, you may want to consider creating a new data set with the GetChanges method, this will return a data set that has only your changes (updates, inserts, deletes).

Here's some code, it's C#, it seems to work fine.

Client calls a web service to update changes:

        private void btnUpdate_Click(object sender, System.EventArgs e)
        {
            statusBar1.Text = "Updating...";
            DataSet changes = dsThisTable.GetChanges();
            if (changes == null)
            {
                statusBar1.Text = "Nothing to update";
                MessageBox.Show("There are no changes to update.", "Nothing to Update", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            else
            {
                PatriotWebService.PatriotClientWebService ws = new PatriotWebService.PatriotClientWebService();
                string result = "0";
                try
                {
                    result = ws.UpdateTable(dgSelectedTable.CaptionText, changes);
                }
                catch (SoapException sex)
                {
                    MessageBox.Show(sex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
                statusBar1.Text = result + " row(s) affected";
                RefreshData(dgSelectedTable.CaptionText);
            }
        } 

Web service method:

        [WebMethod]
        public string UpdateTable(string tableName, DataSet ds)
        {
            SqlConnection myConnection = new SqlConnection();
            myConnection.ConnectionString = myConnectionString;
            SqlDataAdapter myAdapter = new SqlDataAdapter();
            string sql = "SELECT * FROM " + tableName;
            myAdapter.SelectCommand = new SqlCommand(sql, myConnection);
            // The Update method would fail unless specifying my own
            // insert, update and delete commands, or just using the
            // automatic ones as the next line does.
            SqlCommandBuilder custCB = new SqlCommandBuilder(myAdapter);
            int i = myAdapter.Fill(new DataSet(), tableName);
            int j = myAdapter.Update(ds, tableName);
            string returnString = j.ToString();
            return returnString;
        } 

HTH,
Mike

[Andy]

i remember seeing a 'hasChanges()' member. It actually will create the insert/update sql commands for you and everything. I tried to find that article but I have so many in my favorites, it was a lost cause. I'll still look for it and post it if I find it.

[NANC]

Can you please give me code in VB.NET to save data in database.

Thank you,

[Andy]

are you wanting to know how to save data to a table or how to update a dataset that has changed?

those are two different problems.

[mar_zim]

to save in the database

if ds.haschanges then
dim cb as Sqlcommandbuilder
'da is an sqldataadapter
cb=new Sqlcommandbuilder(da)
da.update()
 
'if you want to get only the changes in your table
 
Dim TempTbl As DataTable
TempTbl = ds.Tables(0).GetChanges
Dim cb As New SqlCommandBuilder()
cb = New SqlCommandBuilder(da)
da.Update(TempTbl)
end if