?
Solved

Insert records using SQLDataAdapter using VB.net

Posted on 2014-01-02
3
Medium Priority
?
1,754 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 13

Accepted Solution

by:
ktaczala earned 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

719 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