How do I insert into an Excel spreadsheet?

  • Thread starter Thread starter rsine
  • Start date Start date
R

rsine

I am using Excel as a small database to store settings of a comm program
I am writing. While I can read from excel fine, I am stuck on how to
write any changes back to the database. I have been trying to use an
INSERT INTO but I get the following error:

Operation must use an updateable query

I am really new with using ADO.Net and this is my first application
trying to use Excel as a datasource. Any guidance or suggestions on how
to go about fixing my error would be appreciated.

The code I am using is as follows:

'clear the dataset
dsSettings.Tables("Settings").Clear()

'insert a new row into the datatable
Dim dtSettings As DataTable = dsSettings.Tables("Settings")

Dim drNewRow As DataRow = dtSettings.NewRow

drNewRow.Item("Baudrate") = sBaudRate
drNewRow.Item("Parity") = sParity
drNewRow.Item("Handshake") = sHandShake
drNewRow.Item("Databits") = sDataBits
drNewRow.Item("Stopbits") = sStopBits

'add new row to Settings table
dtSettings.Rows.Add(drNewRow)


Dim sSQL As String

sSQL = "INSERT INTO [Settings$] "
sSQL = sSQL & "(Baudrate,"
sSQL = sSQL & "Parity,"
sSQL = sSQL & "Handshake,"
sSQL = sSQL & "Databits,"
sSQL = sSQL & "Stopbits) "
sSQL = sSQL & "VALUES ("
sSQL = sSQL & "@Baudrate,"
sSQL = sSQL & "@Parity,"
sSQL = sSQL & "@Handshake,"
sSQL = sSQL & "@Databits,"
sSQL = sSQL & "@Stopbits)"

Dim mExcelConnection As OleDb.OleDbConnection = New
OleDb.OleDbConnection

mExcelConnection.ConnectionString =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sAppPath &
"Settings.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""

Dim mExcelAdapter As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter

mExcelAdapter.InsertCommand = New OleDb.OleDbCommand(sSQL,
mExcelConnection)

mExcelAdapter.InsertCommand.Parameters.Add("@Baudrate",
System.Data.OleDb.OleDbType.VarChar, 10, "Baudrate")

mExcelAdapter.InsertCommand.Parameters.Add("@Parity",
System.Data.OleDb.OleDbType.VarChar, 10, "Parity")

mExcelAdapter.InsertCommand.Parameters.Add("@Handshake",
System.Data.OleDb.OleDbType.VarChar, 10, "Handshake")

mExcelAdapter.InsertCommand.Parameters.Add("@Databits",
System.Data.OleDb.OleDbType.VarChar, 10, "Databits")

mExcelAdapter.InsertCommand.Parameters.Add("@Stopbits",
System.Data.OleDb.OleDbType.VarChar, 10, "Stopbits")

mExcelConnection.Open()

mExcelAdapter.Update(dsSettings, "Settings")

mExcelConnection.Close()
 
¤ I am using Excel as a small database to store settings of a comm program
¤ I am writing. While I can read from excel fine, I am stuck on how to
¤ write any changes back to the database. I have been trying to use an
¤ INSERT INTO but I get the following error:
¤
¤ Operation must use an updateable query
¤
¤ I am really new with using ADO.Net and this is my first application
¤ trying to use Excel as a datasource. Any guidance or suggestions on how
¤ to go about fixing my error would be appreciated.
¤
¤ The code I am using is as follows:
¤
¤ 'clear the dataset
¤ dsSettings.Tables("Settings").Clear()
¤
¤ 'insert a new row into the datatable
¤ Dim dtSettings As DataTable = dsSettings.Tables("Settings")
¤
¤ Dim drNewRow As DataRow = dtSettings.NewRow
¤
¤ drNewRow.Item("Baudrate") = sBaudRate
¤ drNewRow.Item("Parity") = sParity
¤ drNewRow.Item("Handshake") = sHandShake
¤ drNewRow.Item("Databits") = sDataBits
¤ drNewRow.Item("Stopbits") = sStopBits
¤
¤ 'add new row to Settings table
¤ dtSettings.Rows.Add(drNewRow)
¤
¤
¤ Dim sSQL As String
¤
¤ sSQL = "INSERT INTO [Settings$] "
¤ sSQL = sSQL & "(Baudrate,"
¤ sSQL = sSQL & "Parity,"
¤ sSQL = sSQL & "Handshake,"
¤ sSQL = sSQL & "Databits,"
¤ sSQL = sSQL & "Stopbits) "
¤ sSQL = sSQL & "VALUES ("
¤ sSQL = sSQL & "@Baudrate,"
¤ sSQL = sSQL & "@Parity,"
¤ sSQL = sSQL & "@Handshake,"
¤ sSQL = sSQL & "@Databits,"
¤ sSQL = sSQL & "@Stopbits)"
¤
¤ Dim mExcelConnection As OleDb.OleDbConnection = New
¤ OleDb.OleDbConnection
¤
¤ mExcelConnection.ConnectionString =
¤ "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sAppPath &
¤ "Settings.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
¤
¤ Dim mExcelAdapter As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter
¤
¤ mExcelAdapter.InsertCommand = New OleDb.OleDbCommand(sSQL,
¤ mExcelConnection)
¤
¤ mExcelAdapter.InsertCommand.Parameters.Add("@Baudrate",
¤ System.Data.OleDb.OleDbType.VarChar, 10, "Baudrate")
¤
¤ mExcelAdapter.InsertCommand.Parameters.Add("@Parity",
¤ System.Data.OleDb.OleDbType.VarChar, 10, "Parity")
¤
¤ mExcelAdapter.InsertCommand.Parameters.Add("@Handshake",
¤ System.Data.OleDb.OleDbType.VarChar, 10, "Handshake")
¤
¤ mExcelAdapter.InsertCommand.Parameters.Add("@Databits",
¤ System.Data.OleDb.OleDbType.VarChar, 10, "Databits")
¤
¤ mExcelAdapter.InsertCommand.Parameters.Add("@Stopbits",
¤ System.Data.OleDb.OleDbType.VarChar, 10, "Stopbits")
¤
¤ mExcelConnection.Open()
¤
¤ mExcelAdapter.Update(dsSettings, "Settings")
¤
¤ mExcelConnection.Close()
¤

I think your OleDbDataAdapter requires a Select on the Worksheet before you can do an Update or
Insert. I would take a look at the following MS KB articles:

HOW TO: Use ADO.NET to Retrieve and Modify Records in an Excel Workbook With Visual Basic .NET
http://support.microsoft.com/default.aspx?scid=kb;EN-US;316934

PRB: Error Occurs When You Use ADO.NET OLEDbDataAdapter to Modify Excel Workbook
http://support.microsoft.com/default.aspx?scid=kb;en-us;316756


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
I am using Excel as a small database to store settings of a comm program
I am writing. While I can read from excel fine, I am stuck on how to
write any changes back to the database. I have been trying to use an
INSERT INTO but I get the following error:

Operation must use an updateable query

I am really new with using ADO.Net and this is my first application
trying to use Excel as a datasource. Any guidance or suggestions on how
to go about fixing my error would be appreciated.
Click to expand...

Why did you choose Excel to just store settings? If you are not using any
Excel "features" that make Excel unique, it is a wasteful overhead to use
it. Store application settings in an XML file, or in the default
application settings file.

If you want to read/set your settings using DataTable/DataSet logic you can
do that using XML to persist the data. A DataSet has a WriteXML() and
ReadXML() method that accomplishes that very well.
 
Back
Top