import data from excel to access using vb.net

[Peerless MHB]

Hi

I tried to insert data from sheet of excel to database access


but nothing imported


here the code


Public Class importdata

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Try
Dim MyConnection As System.Data.OleDb.OleDbConnection
Dim dataSet As System.Data.DataSet
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
Dim path As String = "C:\ESchool\att.xls"
Dim cmdhaha As New OleDb.OleDbCommand
MyConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1;';")
MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet 1$]", MyConnection)

dataSet = New System.Data.DataSet
MyCommand.Fill(dataSet)

MyConnection.Close()

dataSet.Tables(0).Columns(0).ColumnName = "StudentNo"
dataSet.Tables(0).Columns(1).ColumnName = "Studentid"
dataSet.Tables(0).Columns(2).ColumnName = "Studentname"
dataSet.Tables(0).Columns(3).ColumnName = "AttDate"
dataSet.Tables(0).Columns(4).ColumnName = "Logintime"
dataSet.Tables(0).Columns(5).ColumnName = "logouttime"
dataSet.Tables(0).Columns(6).ColumnName = "latemin"
dataSet.Tables(0).Columns(7).ColumnName = "Absent"
dataSet.Tables(0).Columns(8).ColumnName = "expleave"

Dim pathdb As String = "C:\ESchool\db.accdb"
Using connection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source =" + pathdb)
Dim adapter As New OleDbDataAdapter()
adapter.InsertCommand = New OleDbCommand("INSERT INTO AttTemp (StudentNo,StudentID,StudentName,AttDate,Logintime,logouttime,latemin,Absent,expleave)values(@StudentNo,@StudentID,@StudentName,@AttDate,@Logintime,@logouttime,@latemin,@Absent,@expleave)", connection)

connection.Open()
adapter.InsertCommand.Parameters.Add("@StudentNo", OleDbType.VarWChar, 255, "StudentNo")

adapter.InsertCommand.Parameters.Add("@StudentID", OleDbType.VarWChar, 255, "Studentid")
adapter.InsertCommand.Parameters.Add("@StudentName", OleDbType.VarWChar, 255, "Studentname")
adapter.InsertCommand.Parameters.Add("@AttDate", OleDbType.Date, 4, "AttDate")
adapter.InsertCommand.Parameters.Add("@Logintime", OleDbType.VarWChar, 255, "Logintime")
adapter.InsertCommand.Parameters.Add("@logouttime", OleDbType.VarWChar, 255, "logouttime")
adapter.InsertCommand.Parameters.Add("@latemin", OleDbType.VarWChar, 255, "latemin")
adapter.InsertCommand.Parameters.Add("@absent", OleDbType.Boolean, 1, "Absent")
adapter.InsertCommand.Parameters.Add("@expleave", OleDbType.VarWChar, 255, "expleave")
adapter.Update(dataSet.Tables(0))
End Using
Catch ex As Exception
MsgBox(ex.Message.ToString)
End Try

End Sub

Continue reading...