asked on

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.

sforcier

The parenthasis in the execute method isn't closed, or is that a typo?

sybe

you database is on another machine, are you sure you don't have a permissions issue?

rhawk

ASKER

Typeo. they are closed.

rhawk

ASKER

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

alorentz

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"

alorentz

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"

sforcier

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.

alorentz

Yes, I like the formatting better in my example, but the colons will work.

Which line is the error? Open or Execute?

rhawk

ASKER

Error is on the execute.

alorentz

Can you post more code from the page...doesn't look like there should be anything wrong with that.

rhawk

ASKER

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

alorentz

What datatype is NoDupeCheck?

rhawk

ASKER

Yes/No.
However I also tried to update a text field with "test" to see if that entered into the problem. Same error.

alorentz

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

rhawk

ASKER

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

alorentz

The code I gave is correct...you do not need SET when used like that...

Try my code again, just as it is....

rhawk

ASKER

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

rhawk

ASKER

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

rhawk

ASKER

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?!

alorentz

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

rhawk

ASKER

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

ASKER CERTIFIED SOLUTION

alorentz

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial