Solved

Insert records using SQLDataAdapter using VB.net

Posted on 2014-01-02
3
1,483 Views
Last Modified: 2014-01-03
I'm looking for an example of how to insert records using SqlDataAdapter.  Nothing elaborate is needed, just the basics.
0
Comment
Question by:TheChos
  • 2
3 Comments
 
LVL 12

Accepted Solution

by:
ktaczala earned 500 total points
ID: 39752963
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
 

Author Comment

by:TheChos
ID: 39754027
Awesome!  Worked great first time out.  Thank you very much.
0
 

Author Closing Comment

by:TheChos
ID: 39754029
I'd give this an A+.  Everything worked the first try.  Well documented and very easy to follow.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

After several hours of googling I could not gather any information on this topic. There are several ways of controlling the USB port connected to any storage device. The best example of that is by changing the registry value of "HKEY_LOCAL_MACHINE\S…
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question