Here's my latest code:
Private Sub cmdSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSave.Click
Try
Dim MyExcelConnection As System.Data.OleDb.OleDbConnection
Dim myCommand As New System.Data.OleDb.OleDbCommand
Dim daExcel As New System.Data.OleDb.OleDbDataAdapter
MyExcelConnection = New System.Data.OleDb.OleDbConnection _
("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=S:\SanctionsDb\2007-08\points.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=0""")
For Each row As DataGridViewRow In DataGridView1.Rows
With myCommand
.CommandText = "Insert into [Sheet1$] (StudentId,StuName,ClassGrp,TheDate,Merits,Demerits,Lesson," + _
"TeachingGrp,Description,Action,MeritType) values(?,?,?,?,?,?,?,?,?,?,?)"
.CommandType = CommandType.Text
.Connection = MyConnection
' Configure the insert parameters
.Parameters.Add(New OleDb.OleDbParameter("StudId", OleDb.OleDbType.Double, 0, "StudId"))
.Parameters.Add(New OleDb.OleDbParameter("Name", OleDb.OleDbType.Double, 0, "Name"))
.Parameters.Add(New OleDb.OleDbParameter("Class Grp", OleDb.OleDbType.Double, 0, "ClassGrp"))
.Parameters.Add(New OleDb.OleDbParameter("Date", OleDb.OleDbType.Double, 0, "Date"))
.Parameters.Add(New OleDb.OleDbParameter("Merits to Add", OleDb.OleDbType.Double, 0, "Merits"))
.Parameters.Add(New OleDb.OleDbParameter("Demerits to Add", OleDb.OleDbType.Double, 0, "Demerits"))
.Parameters.Add(New OleDb.OleDbParameter("Lesson", OleDb.OleDbType.Double, 0, "Lesson"))
.Parameters.Add(New OleDb.OleDbParameter("TeachingGrp", OleDb.OleDbType.Double, 0, "TeachingGrp"))
.Parameters.Add(New OleDb.OleDbParameter("Description", OleDb.OleDbType.Double, 0, "Description"))
.Parameters.Add(New OleDb.OleDbParameter("Action", OleDb.OleDbType.Double, 0, "Action"))
.Parameters.Add(New OleDb.OleDbParameter("Type", OleDb.OleDbType.Double, 0, "Type"))
End With
Next
daExcel.InsertCommand = myCommand
MyExcelConnection.Open()
daExcel.Update(ds, "Groups")
' Cleanup
MyExcelConnection.Close()
daExcel.Dispose()
daExcel = Nothing
MyExcelConnection.Dispose()
MyExcelConnection = Nothing
Catch ex As Exception
MsgBox(ex.ToString)
End Try
MsgBox("Row Added ")
End Sub
Surely it should work shouldn't it?
|