Hi, this code is my code to add a row to an excel file
Code:
Public Sub Add_Records()
'==========================================================================
' Run an INSERT INTO command to add new records to the workbook.
'==========================================================================
Dim Variable As String
Variable = "15000"
Dim conn1 As New System.Data.OleDb.OleDbConnection(m_sConn1)
conn1.Open()
Dim cmd As New System.Data.OleDb.OleDbCommand()
cmd.Connection = conn1
cmd.CommandText = "INSERT INTO [Sales$] (Reference, Customer, Quantity) values (" & Reference & "," & Variable & "," & Quantity & ")"
cmd.ExecuteNonQuery()
conn1.Close()
End Sub
It works fine as long as the variable Variable is a number. If i make it a string it doesnt work. Any ideas why?
Hi, this code is my code to add a row to an excel file
Code:
Public Sub Add_Records()
'==========================================================================
' Run an INSERT INTO command to add new records to the workbook.
'==========================================================================
Dim Variable As String
Variable = "15000"
Dim conn1 As New System.Data.OleDb.OleDbConnection(m_sConn1)
conn1.Open()
Dim cmd As New System.Data.OleDb.OleDbCommand()
cmd.Connection = conn1
cmd.CommandText = "INSERT INTO [Sales$] (Reference, Customer, Quantity) values (" & Reference & "," & Variable & "," & Quantity & ")"
cmd.ExecuteNonQuery()
conn1.Close()
End Sub
It works fine as long as the variable Variable is a number. If i make it a string it doesnt work. Any ideas why?
Thanks
Alex
Think of the column in question as a field/column in a database table where the type is numeric, you could not assign a string to the numeric field, this is the same as what is happening here, Excel has the field type as numeric so a string will not work.
Example, I populate the first column of a sheet with numbers except for one row and the second column as all strings and look at the underlying definitions of the columns
I believe OleDb methods used to retrieve data looks at the first few rows to determine the data type of a column and not all columns which is why perhaps you are running into this issue.
It might be possible to use a different IMEX setting to alter how OleDb sees the column type
Important note!
The quota " in the string needs to be escaped using your language specific escape syntax.
c#, c++ \"
VB6, VBScript ""
xml (web.config etc) "
or maybe use a single quota '.
"HDR=Yes;" indicates that the first row contains columnnames, not data. "HDR=No;" indicates the opposite.
"IMEX=1;" tells the driver to always read "intermixed" (numbers, dates, strings etc) data columns as text. Note that this option might affect excel sheet write access negative.
SQL syntax "SELECT [Column Name One], [Column Name Two] FROM [Sheet One$]". I.e. excel worksheet name followed by a "$" and wrapped in "[" "]" brackets.
Check out the [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel] located registry REG_DWORD "TypeGuessRows". That's the key to not letting Excel use only the first 8 rows to guess the columns data type. Set this value to 0 to scan all rows. This might hurt performance. Please also note that adding the IMEX=1 option might cause the IMEX feature to set in after just 8 rows. Use IMEX=0 instead to be sure to force the registry TypeGuessRows=0 (scan all rows) to work.
If the Excel workbook is protected by a password, you cannot open it for data access, even by supplying the correct password with your connection string. If you try, you receive the following error message: "Could not decrypt file."
Yeah changed the line connecting to include IMEX=0, so that excel doesnt guess decide column types (also tried with =1 incase i miss understood) but didnt solve problem. My add line code is as follows
Code:
Dim Variable As String
Dim Order_date As String
Dim Variable2 As String
Label10.Text = DateTime.Now
Order_date = Label10.Text
Variable = "15000"
Variable2 = "Hello World"
Dim conn1 As New System.Data.OleDb.OleDbConnection(m_sConn1)
conn1.Open()
Dim cmd As New System.Data.OleDb.OleDbCommand()
cmd.Connection = conn1
cmd.CommandText = "INSERT INTO [Sales$] (Reference, Customer, Quantity, Postcode, Night) values (" & Variable & "," & Variable2 & "," & Variable & "," & Variable2 & "," & Variable & ")"
cmd.ExecuteNonQuery()
conn1.Close()
This is the line that causes the issue
Code:
cmd.ExecuteNonQuery()
Apprently: Syntax error (missing operator) in query expression 'Hello World'.
I.e I dont think it still likes the string.
P.S I also want to be able to put the date and time in a column, but I suspect this will sort itself out once I can add strings???
The code you added above writing to cells individually, how is that connecting to an excel file, because I'll just use that if its simpler?
Last edited by youngnoviceinneedofh; Jun 28th, 2011 at 11:12 AM.
Reason: wanted to add something
The attached VS2008 example shows how to read and write to an Excel worksheet where the data types are double and string.
Compile/run, press the add then update button which demos inserting rows into a newly created Excel file. Once the update has completed a dialog is displayed showing the field types. Since I control the creation of the file and worksheet along with knowing the data types the update works but let's say I did not know the data type then that could very well be an issue.
Reply With Quote
Originally Posted by youngnoviceinneedofh
