|
 |
asp_databases thread: Must use Updateable Query error - why?
Message #1 by "Pat Waddington" <paw@s...> on Sun, 29 Oct 2000 17:31:15 -0000
|
|
This is a multi-part message in MIME format.
------=_NextPart_000_000F_01C041CE.0A014E20
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi,
I am using an Access 97 database via a DSN-less connection to maintain a
set of hit counters. This is the code:
Set objConn =3D Server.CreateObject("ADODB.Connection")
strConnect =3D "Provider=3DMicrosoft.Jet.OLEDB.4.0;Data Source=3D" &
strDBName & ";Persist Security Info=3DFalse"
objConn.Open strConnect
'Open recordset
Set objRS =3D Server.CreateObject("ADODB.Recordset")
objRS.Open fp_sQry, objConn, adOpenKeyset, adLockPessimistic, adCmdText
objConn.Execute "SELECT * FROM Hitter WHERE page_name=3D'" & strFilename
& "';"
' If we've got a record then we read the current value
' If we don't then we create one, set the filename, and
start at 1
If objRS.EOF Then
objRS.Close
Set objRS =3D Nothing
iCount =3D 1
sql_insert =3D "INSERT into Hitter (page_name,
hit_count)"
sql_values =3D "'" & strFilename & "', " &
iCount
fp_sQry =3D sql_insert & " values (" &
sql_values & ");"
Set objRS =3D
Server.CreateObject("ADODB.Recordset")
objRS.Open fp_sQry, objConn, adOpenKeyset,
adLockPessimistic, adCmdText
objConn.Execute "SELECT * FROM Hitter WHERE
page_name=3D'" & strFilename & "';"
Else
objRS.MoveFirst
iCount =3D objRS("hit_count") + 1
objRS.Close
Set objRS =3D Nothing
sql_update =3D "UPDATE Hitter "
setHit_Count =3D "hit_count =3D " & icount
sql_set =3D "SET " & setHit_Count
sql_criteria =3D " WHERE page_name=3D'" &
strFilename & "';"
fp_sQry =3D sql_update & sql_set & sql_criteria
Set objRS =3D
Server.CreateObject("ADODB.Recordset")
objRS.Open fp_sQry, objConn, adOpenKeyset,
adLockPessimistic, adCmdText
objConn.Execute "SELECT * FROM Hitter WHERE
page_name=3D'" & strFilename & "';"
End If
However, when I run the page on my hosting (NT) server and try to insert
a record I get the error message:
Microsoft JET Database Engine error '80004005'
Operation must use an updateable query.
Where is the error in this code that causes the error? I have checked
permissions on both the folder and database residing on the host and
both have full read, write and execute permissions. I've always used
ODBC for this kind of thing before, and have no problems with it. I'm
really stumped, because this code works fine on my PC (Win 98, PWS), and
I know the connection is OK.
Help please!
Pat
Message #2 by "Ken Schaefer" <ken@a...> on Mon, 30 Oct 2000 12:29:45 +1100
|
|
Pat,
You've marked your message as "urgent", but a crisis on your end does not
constitute urgency on ours...
To be honest, your code looks a little bit messy.
You have lines like:
> objConn.Execute "SELECT * FROM Hitter WHERE page_name='" & strFilename &
"';"
What is the point of these lines? You are not allocating the results to a
recordset.
You have lines like this:
> objRS.Open fp_sQry, objConn, adOpenKeyset, adLockPessimistic, adCmdText
but you don't want to update this recordset...why not open it
adOpenForwardOnly, adLockReadOnly? You are also specifying adCmdText, but I
don't see any creation of an SQL string...
You also have this bit of code:
> sql_insert = "INSERT into Hitter (page_name, hit_count)"
> sql_values = "'" & strFilename & "', " & iCount
> fp_sQry = sql_insert & " values (" & sql_values & ");"
> Set objRS = Server.CreateObject("ADODB.Recordset")
> objRS.Open fp_sQry, objConn, adOpenKeyset, adLockPessimistic, adCmdText
However you SQL query is of type INSERT, not SELECT, so no records would be
returned, so why are you using a recordset object to "open" the results of
this query?
objConn.execute(fp_sQry)
will do what you want.
As well, why are using three different variables to create your SQL string,
just use one:
strSQL = "INSERT INTO Hitter "
strSQL = strSQL & "(page_name, hit_count) "
strSQL = strSQL & "VALUES"
strSQL = strSQL & "('" & strFilename & "', "
strSQL = strSQL & " & iCount & ")"
objConn.execute(strSQL)
is all that you need.
HTH
Cheers
Ken
----- Original Message -----
From: "Pat Waddington" <paw@s...>
To: "ASP Databases" <asp_databases@p...>
Sent: Monday, October 30, 2000 4:31 AM
Subject: [asp_databases] Must use Updateable Query error - why?
Hi,
I am using an Access 97 database via a DSN-less connection to maintain a set
of hit counters. This is the code:
Set objConn = Server.CreateObject("ADODB.Connection")
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBName &
";Persist Security Info=False"
objConn.Open strConnect
'Open recordset
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open fp_sQry, objConn, adOpenKeyset, adLockPessimistic, adCmdText
objConn.Execute "SELECT * FROM Hitter WHERE page_name='" & strFilename &
"';"
' If we've got a record then we read the current value
' If we don't then we create one, set the filename, and start at
1
If objRS.EOF Then
objRS.Close
Set objRS = Nothing
iCount = 1
sql_insert = "INSERT into Hitter (page_name,
hit_count)"
sql_values = "'" & strFilename & "', " & iCount
fp_sQry = sql_insert & " values (" & sql_values &
");"
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open fp_sQry, objConn, adOpenKeyset,
adLockPessimistic, adCmdText
objConn.Execute "SELECT * FROM Hitter WHERE
page_name='" & strFilename & "';"
Else
objRS.MoveFirst
iCount = objRS("hit_count") + 1
objRS.Close
Set objRS = Nothing
sql_update = "UPDATE Hitter "
setHit_Count = "hit_count = " & icount
sql_set = "SET " & setHit_Count
sql_criteria = " WHERE page_name='" & strFilename &
"';"
fp_sQry = sql_update & sql_set & sql_criteria
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open fp_sQry, objConn, adOpenKeyset,
adLockPessimistic, adCmdText
objConn.Execute "SELECT * FROM Hitter WHERE
page_name='" & strFilename & "';"
End If
However, when I run the page on my hosting (NT) server and try to insert a
record I get the error message:
Microsoft JET Database Engine error '80004005'
Operation must use an updateable query.
Where is the error in this code that causes the error? I have checked
permissions on both the folder and database residing on the host and both
have full read, write and execute permissions. I've always used ODBC for
this kind of thing before, and have no problems with it. I'm really stumped,
because this code works fine on my PC (Win 98, PWS), and I know the
connection is OK.
Help please!
Pat
Message #3 by "JOHN P. PARLATO" <jparlato@m...> on Mon, 30 Oct 2000 08:07:25 -0800
|
|
This is a multi-part message in MIME format.
------=_NextPart_000_001C_01C04248.7056F740
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Try making sure your data base is not read only - check attributes.
----- Original Message -----
From: Pat Waddington
To: ASP Databases
Sent: Sunday, October 29, 2000 9:31 AM
Subject: [asp_databases] Must use Updateable Query error - why?
Hi,
I am using an Access 97 database via a DSN-less connection to maintain
a set of hit counters. This is the code:
Set objConn =3D Server.CreateObject("ADODB.Connection")
strConnect =3D "Provider=3DMicrosoft.Jet.OLEDB.4.0;Data Source=3D" &
strDBName & ";Persist Security Info=3DFalse"
objConn.Open strConnect
'Open recordset
Set objRS =3D Server.CreateObject("ADODB.Recordset")
objRS.Open fp_sQry, objConn, adOpenKeyset, adLockPessimistic,
adCmdText
objConn.Execute "SELECT * FROM Hitter WHERE page_name=3D'" &
strFilename & "';"
' If we've got a record then we read the current value
' If we don't then we create one, set the filename, and
start at 1
If objRS.EOF Then
objRS.Close
Set objRS =3D Nothing
iCount =3D 1
sql_insert =3D "INSERT into Hitter (page_name,
hit_count)"
sql_values =3D "'" & strFilename & "', " &
iCount
fp_sQry =3D sql_insert & " values (" &
sql_values & ");"
Set objRS =3D
Server.CreateObject("ADODB.Recordset")
objRS.Open fp_sQry, objConn, adOpenKeyset,
adLockPessimistic, adCmdText
objConn.Execute "SELECT * FROM Hitter WHERE
page_name=3D'" & strFilename & "';"
Else
objRS.MoveFirst
iCount =3D objRS("hit_count") + 1
objRS.Close
Set objRS =3D Nothing
sql_update =3D "UPDATE Hitter "
setHit_Count =3D "hit_count =3D " & icount
sql_set =3D "SET " & setHit_Count
sql_criteria =3D " WHERE page_name=3D'" &
strFilename & "';"
fp_sQry =3D sql_update & sql_set &
sql_criteria
Set objRS =3D
Server.CreateObject("ADODB.Recordset")
objRS.Open fp_sQry, objConn, adOpenKeyset,
adLockPessimistic, adCmdText
objConn.Execute "SELECT * FROM Hitter WHERE
page_name=3D'" & strFilename & "';"
End If
However, when I run the page on my hosting (NT) server and try to
insert a record I get the error message:
Microsoft JET Database Engine error '80004005'
Operation must use an updateable query.
Where is the error in this code that causes the error? I have checked
permissions on both the folder and database residing on the host and
both have full read, write and execute permissions. I've always used
ODBC for this kind of thing before, and have no problems with it. I'm
really stumped, because this code works fine on my PC (Win 98, PWS), and
I know the connection is OK.
Help please!
Pat
---
FREE SOFTWARE DEVELOPMENT CODE, CONTENT, AND
INSIGHTS IN YOUR INBOX!
Get the latest and best C++, Visual C++, Java, Visual Basic, and XML
tips, tools, and
developments from the experts. Sign up for one or more of EarthWeb?s
FREE IT newsletters at http://www.earthweb.com today!
jparlato@m...
$subst('Email.Unsub')
Message #4 by "Pat Waddington" <paw@s...> on Tue, 31 Oct 2000 09:31:10 -0000
|
|
Ken,
Thank you for taking the time to reply to my question. Your comments were
useful in that they helped me identify redundant code, but didn't answer the
question I originally posed, and did not help me solve the problem.
For the record, everyone has their own coding style and I have my own
reasons for constructing SQL strings the way I do. It is not my place to
comment on other people's style, and it is not your place to criticise mine.
Thank you for your help however, and I am pleased to say that I have now
resolved the problem
Pat Waddington
----- Original Message -----
From: "Ken Schaefer" <ken@a...>
To: "ASP Databases" <asp_databases@p...>
Sent: Monday, October 30, 2000 1:29 AM
Subject: [asp_databases] Re: Must use Updateable Query error - why?
> Pat,
>
> You've marked your message as "urgent", but a crisis on your end does not
> constitute urgency on ours...
>
> To be honest, your code looks a little bit messy.
> You have lines like:
>
> > objConn.Execute "SELECT * FROM Hitter WHERE page_name='" & strFilename &
> "';"
>
> What is the point of these lines? You are not allocating the results to a
> recordset.
>
> You have lines like this:
> > objRS.Open fp_sQry, objConn, adOpenKeyset, adLockPessimistic, adCmdText
>
> but you don't want to update this recordset...why not open it
> adOpenForwardOnly, adLockReadOnly? You are also specifying adCmdText, but
I
> don't see any creation of an SQL string...
>
> You also have this bit of code:
> > sql_insert = "INSERT into Hitter (page_name, hit_count)"
> > sql_values = "'" & strFilename & "', " & iCount
> > fp_sQry = sql_insert & " values (" & sql_values & ");"
> > Set objRS = Server.CreateObject("ADODB.Recordset")
> > objRS.Open fp_sQry, objConn, adOpenKeyset, adLockPessimistic, adCmdText
>
> However you SQL query is of type INSERT, not SELECT, so no records would
be
> returned, so why are you using a recordset object to "open" the results of
> this query?
>
> objConn.execute(fp_sQry)
>
> will do what you want.
> As well, why are using three different variables to create your SQL
string,
> just use one:
>
> strSQL = "INSERT INTO Hitter "
> strSQL = strSQL & "(page_name, hit_count) "
> strSQL = strSQL & "VALUES"
> strSQL = strSQL & "('" & strFilename & "', "
> strSQL = strSQL & " & iCount & ")"
>
> objConn.execute(strSQL)
>
> is all that you need.
>
> HTH
>
> Cheers
> Ken
>
> ----- Original Message -----
> From: "Pat Waddington" <paw@s...>
> To: "ASP Databases" <asp_databases@p...>
> Sent: Monday, October 30, 2000 4:31 AM
> Subject: [asp_databases] Must use Updateable Query error - why?
>
>
> Hi,
>
> I am using an Access 97 database via a DSN-less connection to maintain a
set
> of hit counters. This is the code:
>
> Set objConn = Server.CreateObject("ADODB.Connection")
> strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBName &
> ";Persist Security Info=False"
> objConn.Open strConnect
> 'Open recordset
>
> Set objRS = Server.CreateObject("ADODB.Recordset")
> objRS.Open fp_sQry, objConn, adOpenKeyset, adLockPessimistic, adCmdText
> objConn.Execute "SELECT * FROM Hitter WHERE page_name='" & strFilename &
> "';"
> ' If we've got a record then we read the current value
> ' If we don't then we create one, set the filename, and start
at
> 1
> If objRS.EOF Then
> objRS.Close
> Set objRS = Nothing
> iCount = 1
> sql_insert = "INSERT into Hitter (page_name,
> hit_count)"
> sql_values = "'" & strFilename & "', " & iCount
> fp_sQry = sql_insert & " values (" & sql_values &
> ");"
> Set objRS = Server.CreateObject("ADODB.Recordset")
> objRS.Open fp_sQry, objConn, adOpenKeyset,
> adLockPessimistic, adCmdText
> objConn.Execute "SELECT * FROM Hitter WHERE
> page_name='" & strFilename & "';"
> Else
> objRS.MoveFirst
> iCount = objRS("hit_count") + 1
> objRS.Close
> Set objRS = Nothing
> sql_update = "UPDATE Hitter "
> setHit_Count = "hit_count = " & icount
> sql_set = "SET " & setHit_Count
> sql_criteria = " WHERE page_name='" & strFilename
&
> "';"
> fp_sQry = sql_update & sql_set & sql_criteria
> Set objRS = Server.CreateObject("ADODB.Recordset")
> objRS.Open fp_sQry, objConn, adOpenKeyset,
> adLockPessimistic, adCmdText
> objConn.Execute "SELECT * FROM Hitter WHERE
> page_name='" & strFilename & "';"
> End If
>
> However, when I run the page on my hosting (NT) server and try to insert a
> record I get the error message:
>
> Microsoft JET Database Engine error '80004005'
> Operation must use an updateable query.
> Where is the error in this code that causes the error? I have checked
> permissions on both the folder and database residing on the host and both
> have full read, write and execute permissions. I've always used ODBC for
> this kind of thing before, and have no problems with it. I'm really
stumped,
> because this code works fine on my PC (Win 98, PWS), and I know the
> connection is OK.
>
> Help please!
>
> Pat
>
>
>
>
>
>
Message #5 by "Pat Waddington" <paw@s...> on Tue, 31 Oct 2000 09:32:54 -0000
|
|
This is a multi-part message in MIME format.
------=_NextPart_000_001B_01C0431D.8C0ABB40
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
John,
Thank you for your suggestion. It transpired that the problem lay with
the Folder permissions, not those of the database itself. The code
worked fine once I moved the database to a folder with write
permissions.
Pat
----- Original Message -----
From: JOHN P. PARLATO
To: ASP Databases
Sent: Monday, October 30, 2000 4:07 PM
Subject: [asp_databases] Re: Must use Updateable Query error - why?
Try making sure your data base is not read only - check attributes.
----- Original Message -----
From: Pat Waddington
To: ASP Databases
Sent: Sunday, October 29, 2000 9:31 AM
Subject: [asp_databases] Must use Updateable Query error - why?
Hi,
I am using an Access 97 database via a DSN-less connection to
maintain a set of hit counters. This is the code:
Set objConn =3D Server.CreateObject("ADODB.Connection")
strConnect =3D "Provider=3DMicrosoft.Jet.OLEDB.4.0;Data Source=3D" &
strDBName & ";Persist Security Info=3DFalse"
objConn.Open strConnect
'Open recordset
Set objRS =3D Server.CreateObject("ADODB.Recordset")
objRS.Open fp_sQry, objConn, adOpenKeyset, adLockPessimistic,
adCmdText
objConn.Execute "SELECT * FROM Hitter WHERE page_name=3D'" &
strFilename & "';"
' If we've got a record then we read the current value
' If we don't then we create one, set the filename, and
start at 1
If objRS.EOF Then
objRS.Close
Set objRS =3D Nothing
iCount =3D 1
sql_insert =3D "INSERT into Hitter
(page_name, hit_count)"
sql_values =3D "'" & strFilename & "', " &
iCount
fp_sQry =3D sql_insert & " values (" &
sql_values & ");"
Set objRS =3D
Server.CreateObject("ADODB.Recordset")
objRS.Open fp_sQry, objConn, adOpenKeyset,
adLockPessimistic, adCmdText
objConn.Execute "SELECT * FROM Hitter WHERE
page_name=3D'" & strFilename & "';"
Else
objRS.MoveFirst
iCount =3D objRS("hit_count") + 1
objRS.Close
Set objRS =3D Nothing
sql_update =3D "UPDATE Hitter "
setHit_Count =3D "hit_count =3D " & icount
sql_set =3D "SET " & setHit_Count
sql_criteria =3D " WHERE page_name=3D'" &
strFilename & "';"
fp_sQry =3D sql_update & sql_set &
sql_criteria
Set objRS =3D
Server.CreateObject("ADODB.Recordset")
objRS.Open fp_sQry, objConn, adOpenKeyset,
adLockPessimistic, adCmdText
objConn.Execute "SELECT * FROM Hitter WHERE
page_name=3D'" & strFilename & "';"
End If
However, when I run the page on my hosting (NT) server and try to
insert a record I get the error message:
Microsoft JET Database Engine error '80004005'
Operation must use an updateable query.
Where is the error in this code that causes the error? I have
checked permissions on both the folder and database residing on the host
and both have full read, write and execute permissions. I've always used
ODBC for this kind of thing before, and have no problems with it. I'm
really stumped, because this code works fine on my PC (Win 98, PWS), and
I know the connection is OK.
Help please!
Pat
---
FREE SOFTWARE DEVELOPMENT CODE, CONTENT, AND
INSIGHTS IN YOUR INBOX!
Get the latest and best C++, Visual C++, Java, Visual Basic, and XML
tips, tools, and
developments from the experts. Sign up for one or more of EarthWeb?s
FREE IT newsletters at http://www.earthweb.com today!
jparlato@m...
$subst('Email.Unsub')
---
NEED TECHNICAL TIPS, TOOLS, AND INSIGHTS? Is FREE okay?
Visit EarthWeb for the latest in IT Management, Software Development,
Web Development, Networking & Communications, and Hardware & Systems.
Click on http://www.earthweb.com for FREE articles, tutorials,
and discussions from the experts.
paw@s...
$subst('Email.Unsub')
|
|
|
View Cart
