using Excel as a DB


M

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
%>
 
Ad

Advertisements

T

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

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"


Tim said:
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
 
B

Bob Barrows [MVP]

Mark said:
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?
 
Ad

Advertisements

T

Tim Williams

File is definitely not readonly ?

Tim.


Mark said:
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"


Tim said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top