Solved

VB.net SQL Append DataTable SQL table

Posted on 2015-01-04
2
340 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
vb.net - How to check if current user is an administrator? 6 48
Convert an ASPX page into PDF 7 32
ASP/VB email question 4 35
How to open form using item in Listbox. 8 21
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…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

821 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