using Excel as a DB

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

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" <Mark@MARK.MArk.Mark.Com> wrote in message
news:%23KsJC1d6EHA.1564@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
> %>

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" <Mark@MARK.MArk.Mark.Com> wrote in message
> news:%23KsJC1d6EHA.1564@TK2MSFTNGP09.phx.gbl...
> 
>
>
>
> From - Sat

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"

File is definitely not readonly ?

Tim.


"Mark" <Mark@MARK.MArk.Mark.Com> wrote in message
news:el5Gkkk6EHA.2156@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:
>