I am trying to update an excel sheet with the code below but I am not seing any results or any error messages. WHat am I doing wrong?
I have tried to go the simple route and use the Update method, but the InsertCommand syntax was not quite right. As you can see the sheet name is "log" but in the connection string I have to use log$.
The Insert command then was referencing the log$ table and not log as it should have.
I have tried to go the simple route and use the Update method, but the InsertCommand syntax was not quite right. As you can see the sheet name is "log" but in the connection string I have to use log$.
The Insert command then was referencing the log$ table and not log as it should have.
VB.NET:
Dim MyCommand As OleDb.OleDbDataAdapter
Dim MyConnection As OleDb.OleDbConnection
Dim strOLECMD As String
MyConnection = New OleDb.OleDbConnection( _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=X:\Curt\cutlist_log.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""")
MyConnection.Open()
Try
MyCommand = New OleDb.OleDbDataAdapter("SELECT * FROM [log$]", MyConnection)
Catch ex As Exception
MyConnection.Close()
Exit Function
End Try
DSLog = New DataSet
Try
Dim cmdInsert As New OleDbCommand
MyCommand.FillSchema(DSLog, SchemaType.Source, "log")
MyCommand.Fill(DSLog, "log")
Dim cmdBuilder As New OleDbCommandBuilder(MyCommand)
MyCommand.InsertCommand = cmdBuilder.GetInsertCommand()
cmdInsert = New OleDbCommand _
("INSERT INTO log (Cutlist_Number,Date_Worked,Unique_Jobs,Total_Cuts,Total_Line_Items,Total_Hours,Number_of_Employees,Employee_IDs) " & _
"VALUES (@Cutlist_Number,@Date_Worked,@Unique_Jobs,@Total_Cuts,@Total_Line_Items,@Total_Hours,@Number_of_Employees,@Employee_IDs)", MyConnection)
cmdInsert.Parameters.Add("@Cutlist_Number", OleDbType.VarChar, 30, "Cutlist_Number").Value = "1" 'txtMainJobNo.Text
cmdInsert.Parameters.Add("@Date_Worked", OleDbType.VarChar, 30, "Date_Worked").Value = "1" 'strDates
cmdInsert.Parameters.Add("@Unique_Jobs", OleDbType.VarChar, 30, "Unique_Jobs").Value = "1" 'txtJobs.Text
cmdInsert.Parameters.Add("@Total_Cuts", OleDbType.VarChar, 30, "Total_Cuts").Value = "1" 'txtCuts.Text
cmdInsert.Parameters.Add("@Total_Line_Items", OleDbType.VarChar, 30, "Total_Line_Items").Value = "1" 'txtLines.Text
cmdInsert.Parameters.Add("@Total_Hours", OleDbType.VarChar, 30, "Total_Hours").Value = "1" 'intTotalTimeWorked / 60
cmdInsert.Parameters.Add("@Number_of_Employees", OleDbType.VarChar, 30, "Number_of_Employees").Value = "1" 'lblTotalEmp.Text
cmdInsert.Parameters.Add("@Employee_IDs", OleDbType.VarChar, 30, "Employee_IDs").Value = "1" 'strEmpIDs
MyCommand.InsertCommand = cmdInsert
Debug.WriteLine(MyCommand.InsertCommand.CommandText)
MyCommand.Update(DSLog, "log")
Catch ex As Exception
MsgBox(ex.Message)
End Try
MyConnection.Close()