Solved

VB.net SQL Append DataTable SQL table

Posted on 2015-01-04
2
330 Views
Last Modified: 2015-01-05
Hi

I create a DataTable that has the same structure as a SQL database table.
What is the best way to add the contents of this DataTable to my SQL table?

Thanks
0
Comment
Question by:murbro
2 Comments
 
LVL 16

Accepted Solution

by:
Easwaran Paramasivam earned 500 total points
ID: 40531018
Use DataAdapter's update method to save the datatable to database. Below example code could help you.

Imports System
Imports System.Data
Imports System.Data.SqlClient

Module Module1

    Sub Main()
        Dim sConnectionString As String
        ' Modify the following code to correctly connect to your SQL Server.
        sConnectionString = "Password=StrongPassword;User ID=UserName;" & _
                            "Initial Catalog=pubs;" & _
                            "Data Source=(local)"

        Dim objConn As New SqlConnection(sConnectionString)
        objConn.Open()

        ' Create an instance of a DataAdapter.
        Dim daAuthors As _
            New SqlDataAdapter("Select * From Authors", objConn)

        ' Create an instance of a DataSet, and retrieve data from the Authors table.
        Dim dsPubs As New DataSet("Pubs")
        daAuthors.FillSchema(dsPubs, SchemaType.Source, "Authors")
        daAuthors.Fill(dsPubs, "Authors")

        '*****************
        'BEGIN ADD CODE 
        ' Create a new instance of a DataTable
        Dim tblAuthors As DataTable
        tblAuthors = dsPubs.Tables("Authors")

        Dim drCurrent As DataRow
        ' Obtain a new DataRow object from the DataTable.
        drCurrent = tblAuthors.NewRow()

        ' Set the DataRow field values as necessary.
        drCurrent("au_id") = "993-21-3427"
        drCurrent("au_fname") = "George"
        drCurrent("au_lname") = "Johnson"
        drCurrent("phone") = "800 226-0752"
        drCurrent("address") = "1956 Arlington Pl."
        drCurrent("city") = "Winnipeg"
        drCurrent("state") = "MB"
        drCurrent("contract") = 1

        'Pass that new object into the Add method of the DataTable.Rows collection.
        tblAuthors.Rows.Add(drCurrent)
        MsgBox("Add was successful.")

        'END ADD CODE    
        '*****************
        'BEGIN EDIT CODE

        drCurrent = tblAuthors.Rows.Find("213-46-8915")
        drCurrent.BeginEdit()
        drCurrent("phone") = "342" & drCurrent("phone").ToString.Substring(3)
        drCurrent.EndEdit()
        MsgBox("Record edited successfully")

        'END EDIT CODE   
        '*****************
        'BEGIN SEND CHANGES TO SQL SERVER

        Dim objCommandBuilder As New SqlCommandBuilder(daAuthors)
        daAuthors.Update(dsPubs, "Authors")
        MsgBox("SQL Server updated successfully" & chr(13) & "Check Server explorer to see changes")

        ' END SEND CHANGES TO SQL SERVER 
        '*****************
        'BEGIN DELETE CODE 

        drCurrent = tblAuthors.Rows.Find("993-21-3427")
        drCurrent.Delete()
        MsgBox("Record deleted successfully")

        'END DELETE CODE 
        '*****************
        ' CLEAN UP SQL SERVER
        daAuthors.Update(dsPubs, "Authors")
        MsgBox("SQL Server updated successfully" & Chr(13) & Chr(13) & "Check Server Explorer to see changes")
    End Sub

End Module

Open in new window

0
 

Author Closing Comment

by:murbro
ID: 40531392
Thanks very much
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

911 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now