How to update a database from a DataSet object by using Visual Basic .NET

The following code sample shows how to update a database from a DataSet object by using Visual Basic .NET


Imports System
Imports System.Data
Imports System.Data.SqlClient

Module Module1

Sub Main()
Dim sConnectionString As String
' Modify the following code to correctly connect to your SQL Server.
sConnectionString = "Password=StrongPassword;User ID=UserName;" & _
"Initial Catalog=pubs;" & _
"Data Source=(local)"

Dim objConn As New SqlConnection(sConnectionString)
objConn.Open()

' Create an instance of a DataAdapter.
Dim daAuthors As _
New SqlDataAdapter("Select * From Authors", objConn)

' Create an instance of a DataSet, and retrieve data from the Authors table.
Dim dsPubs As New DataSet("Pubs")
daAuthors.FillSchema(dsPubs, SchemaType.Source, "Authors")
daAuthors.Fill(dsPubs, "Authors")

'*****************
'BEGIN ADD CODE
' Create a new instance of a DataTable
Dim tblAuthors As DataTable
tblAuthors = dsPubs.Tables("Authors")

Dim drCurrent As DataRow
' Obtain a new DataRow object from the DataTable.
drCurrent = tblAuthors.NewRow()

' Set the DataRow field values as necessary.
drCurrent("au_id") = "993-21-3427"
drCurrent("au_fname") = "George"
drCurrent("au_lname") = "Johnson"
drCurrent("phone") = "800 226-0752"
drCurrent("address") = "1956 Arlington Pl."
drCurrent("city") = "Winnipeg"
drCurrent("state") = "MB"
drCurrent("contract") = 1

'Pass that new object into the Add method of the DataTable.Rows collection.
tblAuthors.Rows.Add(drCurrent)
MsgBox("Add was successful.")

'END ADD CODE
'*****************
'BEGIN EDIT CODE

drCurrent = tblAuthors.Rows.Find("213-46-8915")
drCurrent.BeginEdit()
drCurrent("phone") = "342" & drCurrent("phone").ToString.Substring(3)
drCurrent.EndEdit()
MsgBox("Record edited successfully")

'END EDIT CODE
'*****************
'BEGIN SEND CHANGES TO SQL SERVER

Dim objCommandBuilder As New SqlCommandBuilder(daAuthors)
daAuthors.Update(dsPubs, "Authors")
MsgBox("SQL Server updated successfully" & chr(13) & "Check Server explorer to see changes")

' END SEND CHANGES TO SQL SERVER
'*****************
'BEGIN DELETE CODE

drCurrent = tblAuthors.Rows.Find("993-21-3427")
drCurrent.Delete()
MsgBox("Record deleted successfully")

'END DELETE CODE
'*****************
' CLEAN UP SQL SERVER
daAuthors.Update(dsPubs, "Authors")
MsgBox("SQL Server updated successfully" & Chr(13) & Chr(13) & "Check Server Explorer to see changes")
End Sub

End Module



Comments

Author: vivek kushwaha08 May 2008 Member Level: Silver   Points : 2

this is great. mostly used in shopping cart.

Author: Mastan08 May 2008 Member Level: Bronze   Points : 2

Its really nice to understand , but can we update the Authors(using command builder) if any column has primary/foriegn key references?

Regards
Mastan

Author: Raj09 Nov 2010 Member Level: Silver   Points : 0

if i am getting records from more than 1 table means, it shows error....



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: