| Author |
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
%>
| |
| Tim Williams 2004-12-24, 3:55 pm |
| 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
| |
| Bob Barrows [MVP] 2004-12-25, 3:55 pm |
| 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 2004-12-25, 8:55 pm |
| File is definitely not readonly ?
Tim.
"Mark" <Mark@MARK.MArk.Mark.Com> wrote in message
news:el5Gkkk6EHA.2156@TK2MSFTNGP10.phx.gbl...[color=darkred]
>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:
>
|
|
|
|