Insert into excel

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

Quote:

---

Originally Posted by youngnoviceinneedofh

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

Code:

---

<Worksheet ss:Name="Sheet1">
  <Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="8" x:FullColumns="1"
  x:FullRows="1" ss:DefaultRowHeight="15">
  <Row>
    <Cell><Data ss:Type="Number">1</Data></Cell>
    <Cell><Data ss:Type="String">One</Data></Cell>
  </Row>
  <Row>
    <Cell><Data ss:Type="Number">12</Data></Cell>
    <Cell><Data ss:Type="String">Two</Data></Cell>
  </Row>
  <Row>
    <Cell><Data ss:Type="Number">34</Data></Cell>
    <Cell><Data ss:Type="String">Three</Data></Cell>
  </Row>
  <Row>
    <Cell><Data ss:Type="Number">22</Data></Cell>
    <Cell><Data ss:Type="String">Four</Data></Cell>
  </Row>
  <Row>
    <Cell><Data ss:Type="Number">33</Data></Cell>
    <Cell><Data ss:Type="String">Five</Data></Cell>
  </Row>
  <Row>
    <Cell><Data ss:Type="Number">66</Data></Cell>
    <Cell><Data ss:Type="String">Six</Data></Cell>
  </Row>
  <Row>
    <Cell><Data ss:Type="Number">17</Data></Cell>
    <Cell><Data ss:Type="String">Seven</Data></Cell>
  </Row>
  <Row>
    <Cell><Data ss:Type="String">a</Data></Cell>
  </Row>
  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
  <PageSetup>
    <Header x:Margin="0.3"/>
    <Footer x:Margin="0.3"/>
    <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
  </PageSetup>
  <Selected/>
  <Panes>
    <Pane>
    <Number>3</Number>
    <ActiveRow>7</ActiveRow>
    <ActiveCol>1</ActiveCol>
    </Pane>
  </Panes>
  <ProtectObjects>False</ProtectObjects>
  <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>

---

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

OK, but I'm not the first person to use OleDb, surely it is possible to write text to an excel cell using it?

Quote:

---

Originally Posted by youngnoviceinneedofh

OK, but I'm not the first person to use OleDb, surely it is possible to write text to an excel cell using it?

---

Never said that, only indicated that you can not write a string to a column that is numeric only, same as a database table field.

Did you try using IMEX in your connection string.

References
http://www.connectionstrings.com/excel

Standard alternative from the above link

Quote:

---

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."

---

Hi,

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?

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.