Problem updating Excel using ado.net

  • Thread starter Thread starter Lisa
  • Start date Start date
L

Lisa

Here is my code. The "Update" statement gives an
error "Too few parameters. Expected 1"
Thanks in advance for any info. regarding this


Dim m_sConn1 As String
= "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data
Source=dsn=ratesexcelfile;" & _
"Extended Properties=""Excel
10.0;HDR=YES"""
Dim connString As String
connString
= "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=ExcelData2.xls;" & _
"Extended Properties=""Excel
8.0;HDR=YES"""
objconnection = New OleDbConnection(connString)
objconnection.Open() 'open the connection
Dim cmd1 As New OleDbCommand
cmd1.Connection = objconnection
cmd1.CommandText = "UPDATE
[InventoryData$A4:A4] SET A4 = '1' where (A4<> 'Cell
I4') "
cmd1.ExecuteNonQuery()
objconnection.Close()
 
Lisa said:
Here is my code. The "Update" statement gives an
error "Too few parameters. Expected 1"
Thanks in advance for any info. regarding this


Dim m_sConn1 As String
= "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data
Source=dsn=ratesexcelfile;" & _
"Extended Properties=""Excel
10.0;HDR=YES"""
Dim connString As String
connString
= "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=ExcelData2.xls;" & _
"Extended Properties=""Excel
8.0;HDR=YES"""
objconnection = New OleDbConnection(connString)
objconnection.Open() 'open the connection
Dim cmd1 As New OleDbCommand
cmd1.Connection = objconnection
cmd1.CommandText = "UPDATE
[InventoryData$A4:A4] SET A4 = '1' where (A4<> 'Cell
I4') "
cmd1.ExecuteNonQuery()
objconnection.Close()
Click to expand...

Change your connection string from HDR=YES to HDR=NO (no headers) and your SQL to:

UPDATE [InventoryData$A4:A4]
SET F1 = '1'
WHERE F1 <> 'Cell I4'

When no headers are used, Jet assigns column name in the sequence F1, F2, F3 etc.

Jamie.

--
 
Hi Jamie-

THANKS A LOT FOR YOUR REPLY!!

The update statement worked with out errors, but the data is not updated
on the Excel sheet. Am I doing any thing wrong?

-Lisa
 
lisa lisa said:
UPDATE [InventoryData$A4:A4]
SET F1 = '1'
WHERE F1 <> 'Cell I4'
Click to expand...
Click to expand...
The update statement worked with out errors, but the data is not updated
on the Excel sheet.
Click to expand...

What were you expecting to happen i.e. what are the values before the
update and what do you want them to be afterwards? The above updates
cell A4 if cell A4 doesn't contain the value 'Cell I4' (no quotes). If
you want to update A4 if cell A4 contains a value other than the value
in cell I4, then use:

UPDATE [InventoryData$A4:I4]
SET F1 = '1'
WHERE F1 <> F9
;

Jamie.

--
 
Jamie,

Thanks for your reply. Your SQL statement worked. But, I
cannot see the updates on the Excel file. Am I doing some
thing wrong?

-Lisa
-----Original Message-----
Lisa said:
Here is my code. The "Update" statement gives an
error "Too few parameters. Expected 1"
Thanks in advance for any info. regarding this


Dim m_sConn1 As String
= "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data
Source=dsn=ratesexcelfile;" & _
"Extended Properties=""Excel
10.0;HDR=YES"""
Dim connString As String
connString
= "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=ExcelData2.xls;" & _
"Extended Properties=""Excel
8.0;HDR=YES"""
objconnection = New OleDbConnection (connString)
objconnection.Open() 'open the connection
Dim cmd1 As New OleDbCommand
cmd1.Connection = objconnection
cmd1.CommandText = "UPDATE
[InventoryData$A4:A4] SET A4 = '1' where (A4<> 'Cell
I4') "
cmd1.ExecuteNonQuery()
objconnection.Close()
Click to expand...

Change your connection string from HDR=YES to HDR=NO (no headers) and your SQL to:

UPDATE [InventoryData$A4:A4]
SET F1 = '1'
WHERE F1 <> 'Cell I4'

When no headers are used, Jet assigns column name in the sequence F1, F2, F3 etc.

Jamie.
Click to expand...
 
Back
Top