• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1612
  • Last Modified:

On SQL Update: Microsoft JET Database Engine error '80040e0c' - Command text was not set for the command object

This is just annoying the heck out of me.
I have an asp page that is trying to update an access database. I can open the table ina recordset and update each record by looping through them, but when I call a SQL to do it I get:

Microsoft JET Database Engine error '80040e0c'
Command text was not set for the command object.

Since I know people will want to see source I broke it down to this, and it fails in a SQL update for me but works fine through the record set...

dim cnn: Set cnn = Server.CreateObject("ADODB.Connection")
dim strCnn: strCnn = "provider=Microsoft.Jet.OLEDB.4.0; data Source=\\psc_dev\PublicDatabase\complant\complant.mdb"
cnn.Open(strCnn)
cnn.execute("UPDATE cmplnt SET NoDupeCheck=True WHERE RecId in (1,2,3,4)"


I have tried setting the connection mode to 3. I have tried adding exec before the update.. I am at a loss here....

Thanks.


0
rhawk
Asked:
rhawk
  • 10
  • 9
  • 2
  • +1
1 Solution
 
sforcierCommented:
The parenthasis in the execute method isn't closed, or is that a typo?
0
 
sybeCommented:
you database is on another machine, are you sure you don't have a permissions issue?
0
 
rhawkAuthor Commented:
Typeo. they are closed.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
rhawkAuthor Commented:
Sybe, No that is the machine. I always use UNC to itself.
Oddly I can ADD records and update records through a recordset opening of the table using the same connection string...
0
 
alorentzCommented:
no colons after variables:

Was:

dim cnn: Set cnn = Server.CreateObject("ADODB.Connection")
dim strCnn: strCnn = "provider=Microsoft.Jet.OLEDB.4.0; data Source=\\psc_dev\PublicDatabase\complant\complant.mdb"

Should be:

dim cnn Set cnn = Server.CreateObject("ADODB.Connection")
dim strCnn strCnn = "provider=Microsoft.Jet.OLEDB.4.0; data Source=\\psc_dev\PublicDatabase\complant\complant.mdb"
0
 
alorentzCommented:
Actually should be:

dim cnn, strCnn
Set cnn = Server.CreateObject("ADODB.Connection")
strCnn = "provider=Microsoft.Jet.OLEDB.4.0; data Source=\\psc_dev\PublicDatabase\complant\complant.mdb"
0
 
sforcierCommented:
You had me questioning my sanity for a minute there, alorentz (with that single line declaration/initialization). I think, though, that the colons are just for succinct code reproduction in the post, otherwise they'd be getting a different error.
0
 
alorentzCommented:
Yes, I like the formatting better in my example, but the colons will work.

Which line is the error? Open or Execute?
0
 
rhawkAuthor Commented:
Error is on the execute.
0
 
alorentzCommented:
Can you post more code from the page...doesn't look like there should be anything wrong with that.
0
 
rhawkAuthor Commented:
This is now the WHOLE page:
<%
dim cnn: Set cnn = Server.CreateObject("ADODB.Connection")
dim strCnn: strCnn = "provider=Microsoft.Jet.OLEDB.4.0; data Source=\\psc_dev\PublicDatabase\complant\complant.mdb"
cnn.Open(strCnn)
cnn.execute("UPDATE cmplnt SET NoDupeCheck=True WHERE RecId in (1,2,3,4)")
%>
0
 
alorentzCommented:
What datatype is NoDupeCheck?
0
 
rhawkAuthor Commented:
Yes/No.
However I also tried to update a text field with "test" to see if that entered into the problem. Same error.
0
 
alorentzCommented:
Does this work?  I know you mentioned it, but please test again!

<%
dim cnn: Set cnn = Server.CreateObject("ADODB.Connection")
dim strCnn: strCnn = "provider=Microsoft.Jet.OLEDB.4.0; data Source=\\psc_dev\PublicDatabase\complant\complant.mdb"
cnn.Open(strCnn)
rs = cnn.execute("Select * from cmplnt WHERE RecId in (1,2,3,4)")
if not rs.eof then
   do until rs.eof
         rs("NoDupeCheck")=True
         rs.update
         rs.movenext
   loop
end if
cnn.close
%>
0
 
rhawkAuthor Commented:
alorentz: You left out a set on rs=
No, your code does not work.

But this does:

<%
dim cnn: Set cnn = Server.CreateObject("ADODB.Connection")
dim strCnn: strCnn = "provider=Microsoft.Jet.OLEDB.4.0; data Source=\\psc_dev\PublicDatabase\complant\complant.mdb"
dim rs: set rs = Server.CreateObject("ADODB.RecordSet")
cnn.Open(strCnn)
rs.Open "cmplnt", cnn,adOpenDynamic,adLockOptimistic
'set rs = cnn.execute("Select * from cmplnt WHERE complaint in (26876, 26875, 26874)")
if not rs.eof then
   do until rs.eof
         rs("NoDupeCheck")=True
         rs.update
         rs.movenext
   loop
end if
cnn.close
%>
0
 
alorentzCommented:
The code I gave is correct...you do not need SET when used like that...

Try my code again, just as it is....
0
 
rhawkAuthor Commented:
GGGRRRRR!! THis also works!

<%
dim cnn: Set cnn = Server.CreateObject("ADODB.Connection")
dim strCnn: strCnn = "provider=Microsoft.Jet.OLEDB.4.0; data Source=\\psc_dev\PublicDatabase\complant\complant.mdb"
dim rs: set rs = Server.CreateObject("ADODB.RecordSet")
cnn.Open(strCnn)
rs.Open "Select * from cmplnt where Complaint in (26876,26875,26874)", cnn,adOpenDynamic,adLockOptimistic
if not rs.eof then
   do until rs.eof
         rs("NoDupeCheck")=False
         rs.update
         rs.movenext
   loop
end if
cnn.close
%>

I may just pack it in and do the select in the RS open... I have to get this up. Unless someone can give me a fix before end of day... I'll up the points also now...
0
 
rhawkAuthor Commented:
alorentz: I did try it (I changed id to complaint as I renamed the field.

Your code failed on the rs=. I put the set on the line and it passed that line but gave me the exact same error I first posted....
0
 
rhawkAuthor Commented:
Alorentz:

Your code unchanged gives this result:
  Microsoft VBScript runtime error '800a01b6'
  Object doesn't support this property or method: 'rs.eof'
  /Utility/Complaints/test.asp, line 6

Your code with the set:
  ADODB.Recordset error '800a0cb3'
  Object or provider is not capable of performing requested operation.
  /Utility/Complaints/test.asp, line 8


Line 8 is the assignment...

THe code I just posted works.... Explain that?!
0
 
alorentzCommented:
Doing it like that works because of the Locking , and CursorLocation attributes:

cnn,adOpenDynamic,adLockOptimistic

Not sure why the Execute doesn't work though....

What's the error again with this:

<%
dim cnn: Set cnn = Server.CreateObject("ADODB.Connection")
dim strCnn: strCnn = "provider=Microsoft.Jet.OLEDB.4.0; data Source=\\psc_dev\PublicDatabase\complant\complant.mdb"
cnn.Open(strCnn)
cnn.execute("UPDATE cmplnt SET NoDupeCheck=True WHERE RecId in (1,2,3,4)")
%>

0
 
rhawkAuthor Commented:
I am getting very mad here. That now works! Clearly it is the exact same code you 1st sent me and exactly what I posted (save the paren I left off).

However I did reboot the server about an hour ago... One wonders.

I need to look into this more, but I am about to go home for the night. I'll ponder it tonight and retry in the morning...
0
 
alorentzCommented:
Rebooting the server should've fixed it, I read that somewhere about a 1/2 hour ago...was going to mention but my wife got home!  You know how it goes...
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 10
  • 9
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now