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" <Ma**@MARK.MArk.Mark.Com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...

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

[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 Williams wrote:

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" <Ma**@MARK.MArk.Mark.Com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...

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]]

Mark wrote:

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?

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Tim Williams]

File is definitely not readonly ?

Tim.
"Mark" <Ma**@MARK.MArk.Mark.Com> wrote in message
news:el**************@TK2MSFTNGP10.phx.gbl...

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 Williams wrote:

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" <Ma**@MARK.MArk.Mark.Com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...

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