Solved

Insert records using SQLDataAdapter using VB.net

Posted on 2014-01-02
3
1,447 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

840 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