using Excel as a DB

[Mark]

I am reading from an excel spreadsheet as an ODBC Recordset.
But whenever i try to write to it I get an error


A)
Ideally i would like to write "Mark" to A5
like this :
RS("$A5") = "Mark"
RS.UpdateBatch
but it doenst like the RS("$A5") is there an easy way to do this?


B) if i cant access it like that i have done loops to find out if it is
at the right location then do a
rs.fields.item(counter).value = "Mark"
which works but when i do a
rs.updatebatch
i get :
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Excel Driver] Operation must use an updateable query.

on the line number of the updatebatch

Code is as follows... I just dont understand what to change to make it
work. I believe all file permissions are ok and have checked twice.





##############################################
CODE
##############################################
<%
exceldb="betting.xls"
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "DBQ=" & Server.MapPath(exceldb) & ";" & _
"DRIVER={Microsoft Excel Driver (*.xls)};"


Set rs = Server.CreateObject("ADODB.Recordset")

sql="select * from hammerin_hank;"

rs.Open sql, cn, 3,4
dim MKGCount
MKGCount = 0
do while not rs.eof
' Loop through all of the fileds
MKGCount = MKGCount + 1
for counter = 0 to rs.fields.count - 1
response.write rs.fields.item(counter).value
if MKGCount = 4 then
if counter = 0 then
rs.fields.item(counter).value = "Mark"
rs.updatebatch
end if
end if
next
rs.movenext
loop
rs.Close
Set rs = Nothing
' Kill the connection
cn.Close
Set cn = Nothing
%>

 

[Tim Williams]

If you want to do an update then the best approach is to use SQL

<%
exceldb="betting.xls"
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "DBQ=" & Server.MapPath(exceldb) & ";" & _
"DRIVER={Microsoft Excel Driver (*.xls)};"

cn.execute "update hammerin_hank set updateFieldName='update value' where
idFieldName='recIdToUpdate'"


%>

Tim.

 

[Mark]

I still get the same error...



##########################################

Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Excel Driver] Operation must use an updateable query.
/excel/excel_display1.asp, line 34


line 34:

cn.execute "update hammerin_hank set Name='" & "MARK" &"' where ID=4"

If you want to do an update then the best approach is to use SQL

<%
exceldb="betting.xls"
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "DBQ=" & Server.MapPath(exceldb) & ";" & _
"DRIVER={Microsoft Excel Driver (*.xls)};"

cn.execute "update hammerin_hank set updateFieldName='update value' where
idFieldName='recIdToUpdate'"


%>

Tim.

I am reading from an excel spreadsheet as an ODBC Recordset.
But whenever i try to write to it I get an error


A)
Ideally i would like to write "Mark" to A5
like this :
RS("$A5") = "Mark"
RS.UpdateBatch
but it doenst like the RS("$A5") is there an easy way to do this?


B) if i cant access it like that i have done loops to find out if it is
at the right location then do a
rs.fields.item(counter).value = "Mark"
which works but when i do a
rs.updatebatch
i get :
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Excel Driver] Operation must use an updateable query.

on the line number of the updatebatch

Code is as follows... I just dont understand what to change to make it
work. I believe all file permissions are ok and have checked twice.





##############################################
CODE
##############################################
<%
exceldb="betting.xls"
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "DBQ=" & Server.MapPath(exceldb) & ";" & _
"DRIVER={Microsoft Excel Driver (*.xls)};"


Set rs = Server.CreateObject("ADODB.Recordset")

sql="select * from hammerin_hank;"

rs.Open sql, cn, 3,4
dim MKGCount
MKGCount = 0
do while not rs.eof
' Loop through all of the fileds
MKGCount = MKGCount + 1
for counter = 0 to rs.fields.count - 1
response.write rs.fields.item(counter).value
if MKGCount = 4 then
if counter = 0 then
rs.fields.item(counter).value = "Mark"
rs.updatebatch
end if
end if
next
rs.movenext
loop
rs.Close
Set rs = Nothing
' Kill the connection
cn.Close
Set cn = Nothing
%>

From - Sat

 

[Bob Barrows [MVP]]

I am reading from an excel spreadsheet as an ODBC Recordset.
But whenever i try to write to it I get an error


A)
Ideally i would like to write "Mark" to A5
like this :
RS("$A5") = "Mark"
RS.UpdateBatch
but it doenst like the RS("$A5") is there an easy way to do this?


B) if i cant access it like that i have done loops to find out if it
is at the right location then do a
rs.fields.item(counter).value = "Mark"
which works but when i do a
rs.updatebatch
i get :
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Excel Driver] Operation must use an updateable query.

on the line number of the updatebatch

Code is as follows... I just dont understand what to change to make it
work. I believe all file permissions are ok and have checked twice.

Does the IUSR account have permissions to the file?

 

[Tim Williams]

File is definitely not readonly ?

Tim.

I still get the same error...



##########################################

Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Excel Driver] Operation must use an updateable
query.
/excel/excel_display1.asp, line 34


line 34:

cn.execute "update hammerin_hank set Name='" & "MARK" &"' where
ID=4"

If you want to do an update then the best approach is to use SQL

<%
exceldb="betting.xls"
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "DBQ=" & Server.MapPath(exceldb) & ";" & _
"DRIVER={Microsoft Excel Driver (*.xls)};"

cn.execute "update hammerin_hank set updateFieldName='update value'
where
idFieldName='recIdToUpdate'"


%>

Tim.

I am reading from an excel spreadsheet as an ODBC Recordset.
But whenever i try to write to it I get an error


A)
Ideally i would like to write "Mark" to A5
like this :
RS("$A5") = "Mark"
RS.UpdateBatch
but it doenst like the RS("$A5") is there an easy way to do this?


B) if i cant access it like that i have done loops to find out if
it is
at the right location then do a
rs.fields.item(counter).value = "Mark"
which works but when i do a
rs.updatebatch
i get :
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Excel Driver] Operation must use an updateable
query.

on the line number of the updatebatch

Code is as follows... I just dont understand what to change to make
it
work. I believe all file permissions are ok and have checked
twice.





##############################################
CODE
##############################################
<%
exceldb="betting.xls"
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open "DBQ=" & Server.MapPath(exceldb) & ";" & _
"DRIVER={Microsoft Excel Driver (*.xls)};"


Set rs = Server.CreateObject("ADODB.Recordset")

sql="select * from hammerin_hank;"

rs.Open sql, cn, 3,4
dim MKGCount
MKGCount = 0
do while not rs.eof
' Loop through all of the fileds
MKGCount = MKGCount + 1
for counter = 0 to rs.fields.count - 1
response.write rs.fields.item(counter).value
if MKGCount = 4 then
if counter = 0 then
rs.fields.item(counter).value = "Mark"
rs.updatebatch
end if
end if
next
rs.movenext
loop
rs.Close
Set rs = Nothing
' Kill the connection
cn.Close
Set cn = Nothing
%>

From - Sat