Introduction
This articles helps user to Insert, Update, Delete, and Select data in Excel files using the OLEDBDataProvider
in VB.NET.
Here is the connection string to connect with Excel using OleDBDataProvider
:
Private Const connstring As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Test.xls;Extended Properties=""Excel 8.0;HDR=YES;"""
Here is the code on the button click event to select and insert data in an Excel file:
Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
Dim pram As OleDbParameter
Dim dr As DataRow
Dim olecon As OleDbConnection
Dim olecomm As OleDbCommand
Dim olecomm1 As OleDbCommand
Dim oleadpt As OleDbDataAdapter
Dim ds As DataSet
Try
olecon = New OleDbConnection
olecon.ConnectionString = connstring
olecomm = New OleDbCommand
olecomm.CommandText = _
"Select FirstName, LastName, Age, Phone from [Sheet1$]"
olecomm.Connection = olecon
olecomm1 = New OleDbCommand
olecomm1.CommandText = "Insert into [Sheet1$] " & _
"(FirstName, LastName, Age, Phone) values " & _
"(@FName, @LName, @Age, @Phone)"
olecomm1.Connection = olecon
pram = olecomm1.Parameters.Add("@FName", OleDbType.VarChar)
pram.SourceColumn = "FirstName"
pram = olecomm1.Parameters.Add("@LName", OleDbType.VarChar)
pram.SourceColumn = "LastName"
pram = olecomm1.Parameters.Add("@Age", OleDbType.VarChar)
pram.SourceColumn = "Age"
pram = olecomm1.Parameters.Add("@Phone", OleDbType.VarChar)
pram.SourceColumn = "Phone"
oleadpt = New OleDbDataAdapter(olecomm)
ds = New DataSet
olecon.Open()
oleadpt.Fill(ds, "Sheet1")
If IsNothing(ds) = False Then
dr = ds.Tables(0).NewRow
dr("FirstName") = "Raman"
dr("LastName") = "Tayal"
dr("Age") = 24
dr("Phone") = 98989898
ds.Tables(0).Rows.Add(dr)
oleadpt = New OleDbDataAdapter
oleadpt.InsertCommand = olecomm1
Dim i As Integer = oleadpt.Update(ds, "Sheet1")
MessageBox.Show(i & " row affected")
End If
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
olecon.Close()
olecon = Nothing
olecomm = Nothing
oleadpt = Nothing
ds = Nothing
dr = Nothing
pram = Nothing
End Try
End Sub