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.
TheChosAsked:
Who is Participating?
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.