Write data to SQL Server. 1

[bddsolid]

Hi,

I try to import data from an excel file in my VB.NET application in a datagrid, this part is succesfully.
Then I want to write the data in the datagrid in my SQL Server, but this fails! I think the problem is the SqlCommandBuilder, but I can't find the problem.

(I use VS.NET 2003 and SQL Server 2000)

This is my code:

Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.OleDb
Imports System.Data.SqlClient.SqlTransaction


Public Class Form1
Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

Public Sub New()
MyBase.New()

'This call is required by the Windows Form Designer.
InitializeComponent()

'Add any initialization after the InitializeComponent() call

End Sub

'Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub

'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer

'NOTE: The following procedure is required by the Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents grdArticles As System.Windows.Forms.DataGrid
Friend WithEvents Button1 As System.Windows.Forms.Button
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Me.grdArticles = New System.Windows.Forms.DataGrid
Me.Button1 = New System.Windows.Forms.Button
CType(Me.grdArticles, System.ComponentModel.ISupportInitialize).BeginInit()
Me.SuspendLayout()
'
'grdArticles
'
Me.grdArticles.DataMember = ""
Me.grdArticles.HeaderForeColor = System.Drawing.SystemColors.ControlText
Me.grdArticles.Location = New System.Drawing.Point(48, 40)
Me.grdArticles.Name = "grdArticles"
Me.grdArticles.Size = New System.Drawing.Size(504, 248)
Me.grdArticles.TabIndex = 0
'
'Button1
'
Me.Button1.Location = New System.Drawing.Point(384, 336)
Me.Button1.Name = "Button1"
Me.Button1.TabIndex = 1
Me.Button1.Text = "Button1"
'
'Form1
'
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
Me.ClientSize = New System.Drawing.Size(608, 414)
Me.Controls.Add(Me.Button1)
Me.Controls.Add(Me.grdArticles)
Me.Name = "Form1"
Me.Text = "Form1"
CType(Me.grdArticles, System.ComponentModel.ISupportInitialize).EndInit()
Me.ResumeLayout(False)

End Sub

#End Region

Protected Const GetAllArticlesSqlString As String = "Select Merk, ArtikelNummer, Omschrijving, Datum, Prijs, VerkpEH, Groep, GroepOmschrijving, EAN from Artikels"

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim connectionString As String = "Server=VIPER;database=Cheyns;uid=sa;pwd="
Dim myConnection As New SqlConnection(connectionString)

Dim myDT As New DataTable("Artikels")
Dim myDS As New DataSet
Dim myAdapter As New SqlDataAdapter(GetAllArticlesSqlString, myConnection)
myAdapter.Fill(myDS)

grdArticles.DataSource = myDS

Dim excelConnection As New OleDbConnection
Dim excelAdapter As New OleDbDataAdapter

excelConnection = New System.Data.OleDb.OleDbConnection( _
"provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source=C:\TEST.XLS; " & _
"Extended Properties=Excel 8.0;")

excelAdapter = New System.Data.OleDb.OleDbDataAdapter( _
"select * from [Sheet1$]", excelConnection)
Try
excelAdapter.Fill(myDT)
Catch ex As Exception
MessageBox.Show(ex.ToString())
End Try
excelConnection.Close()

Dim changedRows As New ArrayList
Dim row As DataRow
For Each row In myDT.Rows
changedRows.Add(row)
Next

myConnection.Open()

Dim builder As New SqlCommandBuilder(myAdapter)

Dim rows() As DataRow = CType(changedRows.ToArray(GetType(DataRow)), DataRow())

Try
myAdapter.Update(rows)
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try

myAdapter.Dispose()
myConnection.Close()
End Sub

End Class

Does somebody knows why it fails?

Thx in advance.

Bart.

 

[jubble]

what error are you getting?

to start with, you'd be very lucky if it's letting you connect considering you haven't set the provider ( eg Provider=sqloledb ) in the connection string

 

[bddsolid]

I've replaced the sqlconnection by an OleDbConnection. I'm using now also the Provider keyword in my connection string. I get no error. (I replaced also the SqlDataAdapter by an OleDbDataAdapter)
But it doesn't work yet with the OleDbConnection.

 

[jubble]

try the RefreshSchema method and if that fails perhaps setting an UpdateCommand will work

 

[bddsolid]

The problem is something else. I tried also the UpdateCommand but it doesn't work.

 

[jubble]

Just to verify, are you updating or inserting? Due to the fact that you're pulling data I'm assuming that you're updating.

I'm not 100% sure of what datasets do behind the scenes but it looks like your possibly killing any schema links that were created from the select when you build changedrows from myDT.

In all the examples I've seen of using the dataset update method it is run against the original table (with original schema) whereas you a compiling a brand new row array from the excel data.

If you can find a way to iterate through the original dataset table and change the row data rather than replace the actual rows that might solve the problem.

Personally, I find in the long run it's easier to debug inserting and updating by using the old method of row by row rather then doing it all at once using dataset update but it is a bit slower (or so they say - the difference seems to be negligible)

 

[bddsolid]

No I don't update the data. What I want to do:
1) Get the data from the Excel file
2) Put the data in a DataTable
3) Write the data in the DataTable to the SQL-Server
So, I want to write a tool in VB.NET to import data from an Excel File and put this data in the SQL-Server.

I don't know if it is necessary to use a DataTable. May be there is an other (beter) way to do this??

 

[jubble]

in that case we can use row by row which is a charm to debug

i whipped this up quite quickly so please excuse any impurities

it works on the northwind database and shows how you can build the insert query from each row that comes from the excel data

the excel file I used was as follows

customerid,companyname,contactname
cust1,co1,cn1
cust2,co2,cn2
cust3,co3,cn3

two things to note:

1) it assumes the column headers are in the excel file
2) it assumes all values are string and places a single quote around each in the query - for other data types you'll need a gettype check on the column

I've used your original code at the beginning with changes to the connection string and excel file location up until the 'excelConnection.Close()' line


Dim connectionString As String = "Provider=sqloledb;Data Source=saturn;Initial Catalog=northwind;User Id=sa;Password=secure1;"
Dim myConnection As New OleDbConnection(connectionString)

Dim myDT As New DataTable("Artikels")
'Dim myDS As New DataSet
Dim myAdapter As New OleDbDataAdapter(GetAllArticlesSqlString, myConnection)
Dim excelConnection As New OleDbConnection
Dim excelAdapter As New OleDbDataAdapter

excelConnection = New System.Data.OleDb.OleDbConnection( _
"provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source=C:\temp\TEST.XLS; " & _
"Extended Properties=Excel 8.0;")

excelAdapter = New System.Data.OleDb.OleDbDataAdapter( _
"select * from [Sheet1$]", excelConnection)
Try
excelAdapter.Fill(myDT)
Catch ex As Exception
MessageBox.Show(ex.ToString())
End Try
excelConnection.Close()

Dim insertquery As String
Dim row As DataRow
Dim col As DataColumn
Dim colseg As String
Dim comm As New OleDbCommand

'build column segment
colseg = "("
For Each col In myDT.Columns
colseg += col.Caption & ","
Next

'replace last , with )
colseg = colseg.Remove(colseg.Length - 1, 1) & ")"
myConnection.Open()
For Each row In myDT.Rows
insertquery = "INSERT Customers" & colseg & " VALUES ('"
For Each col In myDT.Columns
insertquery += CStr(row(col)) & "','"
Next
'replace last ,' with )
insertquery = insertquery.Remove(insertquery.Length - 2, 2) & ")"
comm = New OleDbCommand(insertquery, myConnection)

comm.ExecuteNonQuery()

Next
myConnection.Close()

Hope this helps