DataSets and Excel

ronin2307

Member
Joined
Mar 10, 2005
Messages
16
Programming Experience
1-3
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.

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()
 
Back
Top