Update SQL Database with VB.NET Datatable Using SQLCommandBuilder

2013-03-21

Question

_{Thursday, March 21, 2013 7:36 PM}

I have been looking for a decent example of how to update a SQL table with the results from a dataset.datatable. I can insert the rows from my dataset.datatable no problem, as long as the row doesn't already exist. From what i read SQLCommandBuilder was suppose to handle key constraints as long as you filled the adpater with the schema. Still seems to be unable to handle the duplicate rows though. I keep getting the Primary Key violation. I suspect my syntax is wrong.

Dim sConnStr As String
sConnStr = "Server=MyServer;Database=MyDB;Trusted_Connection=True;"
Dim SQLConn As New SqlConnection(sConnStr)
Dim ds As DataSet = New DataSet("NewDataSet")
Dim MyDataTable As DataTable = New DataTable("NewMyDataTable")

ds.Tables.Add(MyDataTable)

'Loaded rows into MyDataTable

'Then I attempt to update my SQL Database, and start getting Primary Key violation
Dim LoadAdapt As SqlDataAdapter = New SqlDataAdapter("SELECT * From MySQLTable", SQLConn)
Dim cbSQLCOMB As SqlCommandBuilder = New SqlCommandBuilder(LoadAdapt)
SQLConn.Open()
LoadAdapt.FillSchema(ds, SchemaType.Source, "MySQLTable")
LoadAdapt.Fill(ds, "MySQLTable")

LoadAdapt.UpdateCommand = cbSQLCOMB.GetUpdateCommand

LoadAdapt.Update(ds, "NewMyDataTable")

SQLConn.Close()

All replies (3)

_{Thursday, March 21, 2013 8:15 PM ✅Answered}

try this:

Dim SQLConn As New SqlConnection("Server=MyServer;Database=MyDB;Trusted_Connection=True;")
Dim ds As DataSet = New DataSet("NewDataSet")
Dim MyDataTable As New DataTable("NewMyDataTable")

ds.Tables.Add(MyDataTable)

'Loaded rows into MyDataTable

'Then I attempt to update my SQL Database, and start getting Primary Key violation
Dim LoadAdapt As New SqlDataAdapter("SELECT * From MySQLTable", SQLConn)
SQLConn.Open()
LoadAdapt.FillSchema(ds, SchemaType.Source, "MySQLTable")
LoadAdapt.Fill(ds, "MySQLTable")

Dim cbSQLCOMB As New SqlCommandBuilder(LoadAdapt)

'save changes
'if you intend to do this in a seperate procedure, ds + 
'LoadAdapt must be declared at class level instead of  
'locally
LoadAdapt.Update(ds, "NewMyDataTable")

SQLConn.Close()

thanks for any help

_{Sunday, March 24, 2013 2:21 AM ✅Answered}

This is how you can navigate to a source, and load the result into your DataGridView:

Imports System.Data.SqlClient
Imports System.IO
Imports Microsoft.VisualBasic.FileIO
Imports System.Data
Imports System.Data.Odbc
Imports System.Data.OleDb
Imports System.Configuration

.....

Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
'Dim m_strConnection As String = "server=Excel-PC\SQLEXPRESS;Initial Catalog=Northwind;Trusted_Connection=True;"

        'Catch ex As Exception
        '    MessageBox.Show(ex.ToString())
        'End Try

        'Dim objDataset1 As DataSet()
        'Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Dim da As OdbcDataAdapter
        Dim OpenFile As New System.Windows.Forms.OpenFileDialog ' Does something w/ the OpenFileDialog
        Dim strFullPath As String, strFileName As String
        Dim tbFile As New TextBox
        ' Sets some OpenFileDialog box options
        OpenFile.Filter = "CSV Files (*.csv)|*.csv|All files (*.*)|*.*" ' Shows only .csv files
        OpenFile.Title = "Browse to file:" ' Title at the top of the dialog box

        If OpenFile.ShowDialog() = DialogResult.OK Then ' Makes the open file dialog box show up
            strFullPath = OpenFile.FileName ' Assigns variable
            strFileName = Path.GetFileName(strFullPath)

If OpenFile.FileNames.Length > 0 Then ' Checks to see if they've picked a file

tbFile.Text = strFullPath ' Puts the filename in the textbox

                ' The connection string for reading into data connection form
                Dim connStr As String
                connStr = "Driver={Microsoft Text Driver (*.txt; *.csv)}; Dbq=" + Path.GetDirectoryName(strFullPath) + "; Extensions=csv,txt "

                ' Sets up the data set and gets stuff from .csv file
                Dim Conn As New OdbcConnection(connStr)
                Dim ds As DataSet
                Dim DataAdapter As New OdbcDataAdapter("SELECT * FROM [" + strFileName + "]", Conn)
                ds = New DataSet

                Try
                    DataAdapter.Fill(ds, strFileName) ' Fills data grid..
                    DataGridView1.DataSource = ds.Tables(strFileName) ' ..according to data source

                    ' Catch and display database errors
                Catch ex As OdbcException
                    Dim odbcError As OdbcError
                    For Each odbcError In ex.Errors
                        MessageBox.Show(ex.Message)
                    Next
                End Try

                ' Cleanup
                OpenFile.Dispose()
                Conn.Dispose()
                DataAdapter.Dispose()
                ds.Dispose()

End If
End If

End Sub

Also, see this:

http://vb.net-informations.com/dataadapter/dataadapter-datagridview-sqlserver.htm

Ryan Shuell

_{Thursday, March 21, 2013 8:25 PM}

I believe I had tried it that way before, but I switched it and still giving me the error.

Violation of PRIMARY KEY constraint 'PK__MySQLTable__956ECD645FB337D6'. Cannot insert duplicate key in object 'dbo.MySQLTable'. The duplicate key value is (11, 10, 100, 10, 100, 1004).
The statement has been terminated.

It just seems to me that SQLCommandBuilder doesn't in fact handle duplicates. Out of all the examples I looked at I am following the syntax closely.

שתף באמצעות

Update SQL Database with VB.NET Datatable Using SQLCommandBuilder

Question

All replies (3)

משאבים נוספים