Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1872
  • Last Modified:

Insert records using SQLDataAdapter using VB.net

I'm looking for an example of how to insert records using SqlDataAdapter.  Nothing elaborate is needed, just the basics.
0
TheChos
Asked:
TheChos
  • 2
1 Solution
 
ktaczalaCommented:
Private Sub MakeChanges()
        Dim myConn As New SqlConnection
        Dim dbSource As String
        Dim sql As String
        Dim myTableName As String
        Dim myDataSet As New DataSet()
        Dim myDataAdapter As SqlDataAdapter
        Dim myDataRow As DataRow

        myTableName = "Customer" 'Make sure it has a Primary Key (required by CommandBuilder)

        dbSource = "Data Source=.\SQLEXPRESS;AttachDbFilename=E:\VB Databases\Develop\Develop.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
        myConn.ConnectionString = dbSource
        myConn.Open()
        sql = "SELECT * FROM " & myTableName

        myDataAdapter = New SqlDataAdapter(sql, myConn)
        myDataAdapter.Fill(myDataSet, myTableName)

        'Example Code below modifies Data in "myDataSet"

        'UPDATE record
        myDataSet.Tables(myTableName).Rows(1).Item("ContactName") = "Peter Williams"

        'ADD record
        myDataRow = myDataSet.Tables(myTableName).NewRow()
        myDataRow.Item("ContactName") = "New Name"
        myDataRow.Item("CompanyName") = "New Company Name"
        myDataSet.Tables("Customer").Rows.Add(myDataRow)

        'DELETE record
        myDataSet.Tables(myTableName).Rows(0).Delete() 'Delete Row(0)

        'Make changes made in myDataSet, persist in actual SQL Table
        Dim cb As SqlCommandBuilder = New SqlCommandBuilder(myDataAdapter)
        myDataAdapter.Update(myDataSet, myTableName)
        myDataAdapter.AcceptChangesDuringUpdate = True

        myConn.Close()

        MsgBox("Now check SQL table after Refreshing")

    End Sub
0
 
TheChosAuthor Commented:
Awesome!  Worked great first time out.  Thank you very much.
0
 
TheChosAuthor Commented:
I'd give this an A+.  Everything worked the first try.  Well documented and very easy to follow.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now