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.
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.