from a textbox to Excel
-
Hello, I am tring to create a program to help my work schedual Pull Stock. I have a problem getting the value of the variables into a spreadsheet. This is some test code I am trying to get to work: Public Sub Insertdata() 'Insert data typed in txt boxes into ExcelData1.xls Dim conn1 As New System.Data.OleDb.OleDbConnection(m_sConn1) conn1.Open() Dim cmd As New System.Data.OleDb.OleDbCommand() Dim strID As String Dim strName As String Dim Birthdate As Date strID = txtID.Text strName = txtName.Text Birthdate = txtBirthdate.Text cmd.Connection = conn1 '******Here is where the trouble is*********************** cmd.CommandText = "INSERT INTO [EmployeeData$] (ID, Name, BirthDate) values (strID, strName, Birthdate)" '****** the values of:(strID, strName, Birthdate)will not pass********** cmd.ExecuteNonQuery() conn1.Close() End Sub Any advice would be greatly appreciated Thanks!
-
Hello, I am tring to create a program to help my work schedual Pull Stock. I have a problem getting the value of the variables into a spreadsheet. This is some test code I am trying to get to work: Public Sub Insertdata() 'Insert data typed in txt boxes into ExcelData1.xls Dim conn1 As New System.Data.OleDb.OleDbConnection(m_sConn1) conn1.Open() Dim cmd As New System.Data.OleDb.OleDbCommand() Dim strID As String Dim strName As String Dim Birthdate As Date strID = txtID.Text strName = txtName.Text Birthdate = txtBirthdate.Text cmd.Connection = conn1 '******Here is where the trouble is*********************** cmd.CommandText = "INSERT INTO [EmployeeData$] (ID, Name, BirthDate) values (strID, strName, Birthdate)" '****** the values of:(strID, strName, Birthdate)will not pass********** cmd.ExecuteNonQuery() conn1.Close() End Sub Any advice would be greatly appreciated Thanks!
DawgTagz wrote: '******Here is where the trouble is*********************** cmd.CommandText = "INSERT INTO [EmployeeData$] (ID, Name, BirthDate) values (strID, strName, Birthdate)" You might want to try this instead:
cmd.CommandText = "INSERT INTO [EmployeeData$] (ID, Name, BirthDate) values (" & strID & "," & strName & "," & Birthdate & ")"
That way, you're passing the actual values of strID, strName, and Birthdate instead of the text string "strID, strName, Birthdate". RageInTheMachine9532
-
DawgTagz wrote: '******Here is where the trouble is*********************** cmd.CommandText = "INSERT INTO [EmployeeData$] (ID, Name, BirthDate) values (strID, strName, Birthdate)" You might want to try this instead:
cmd.CommandText = "INSERT INTO [EmployeeData$] (ID, Name, BirthDate) values (" & strID & "," & strName & "," & Birthdate & ")"
That way, you're passing the actual values of strID, strName, and Birthdate instead of the text string "strID, strName, Birthdate". RageInTheMachine9532
-
DawgTagz wrote: '******Here is where the trouble is*********************** cmd.CommandText = "INSERT INTO [EmployeeData$] (ID, Name, BirthDate) values (strID, strName, Birthdate)" You might want to try this instead:
cmd.CommandText = "INSERT INTO [EmployeeData$] (ID, Name, BirthDate) values (" & strID & "," & strName & "," & Birthdate & ")"
That way, you're passing the actual values of strID, strName, and Birthdate instead of the text string "strID, strName, Birthdate". RageInTheMachine9532
-
In that case, it's the SQL INSERT statement that is bad or not formated correctly. After looking up the INSERT statement on MSDN, I've found that it should look something like this:
cmd.CommandText = "INSERT INTO [EmployeeData$] (ID, Name, BirthDate) values ('" & strID & "', '" & strName & "', #" & Birthdate & "#)"
Where string values must be enclosed in single quotes. Date values must be passed something like #1/1/1999# (enclosed in pound signs). I haven't tested this, but it should work according to the docs. ;) You might have to make sure that the Birthdate gets passed in the correct format. RageInTheMachine9532