Problem updating Excel using ado.net
 

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()

Problem updating Excel using ado.net
 

"Lisa" wrote ...

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()

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.

--

Problem updating Excel using ado.net
 

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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Problem updating Excel using ado.net
 

lisa lisa wrote ...

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

The update statement worked with out errors, but the data is not updated
on the Excel sheet.

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.

--

Problem updating Excel using ado.net
 

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

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()

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.

--
.