VS 2019 VB How to save a DataTable into sql Database

[RonbSA]

Hi team

I have a datatable and or dataset that have been filled from a csv file which is working fine.

This I display in a unbound data Grid. So I can see the data.

I need to insert the data in the datatable or dataset into an Sql Database.

I can use either the datatable OR dataset which ever is the best.

The SQL Column headers match the Column headers in the datatable that was in the csv file.

I delete the contents of the SQL Database just before inserting and reset the Key back to 1

Note the Datatable do's not have the Key ID which is in the SQL Database.

So all I need is a simple way to insert this data into the SQL Database.

My Code I have tryed:

Protected Sub UpdateDriversLicence(dt As DataTable)
Dim connectionString As String = MySQLString
Dim queryString As String = "SELECT ID, ValidCompetitionLicence,ValidOfficialLicence, LicenceNumber, LastName, FirstName, DateValidated, CompetitionLicenceExpiryDate, OfficialLicenceExpiryDate, CompetitionLicenceSuspended, OfficialLicenceSuspended, LicenceDescription EventID FROM CAMScsvFileData"

Using connection As SqlConnection = New SqlConnection(connectionString)
connection.Open()
Dim adapter As SqlDataAdapter = New SqlDataAdapter()
adapter.SelectCommand = New SqlCommand(queryString, connection)
Dim builder As SqlCommandBuilder = New SqlCommandBuilder(adapter)
builder.RefreshSchema()
Try
adapter.Fill(dt)
adapter.InsertCommand = builder.GetUpdateCommand()
'' adapter.UpdateCommand = builder.GetUpdateCommand()
adapter.Update(dt)

Catch ex As Exception
MsgBox("The Error : " & ex.ToString)
End Try

End Using

End Sub



Regards

Ron B

Continue reading...