UPDATEABLE QUERY error on INSERT ??

C

Cynth-

Greetings and thank you!

I am performing a very simple insert into an MS Access DB located on a
Microsoft server hosted by GoDaddy. For the sake of testing, I have
hardcoded the data being inserted (normally comes from a form) - see
error and code sample below.

I am getting a wierd error I can't explain or find anything about. I
have scoured my books and can find nothing on this...and the host will
not help.

Any thoughts? Thank you kindly.
Cynthia-

Here's the error...
-----------------------------------------------------------
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
ODBC Microsoft Access Driver - Operation must use an updateable query.


Here's the code......it's blowing up on the EXECUTE stmt at the end.
----------------------------------------------------------------
'*** prep for connection to the DB
adCmdText = 1
sDSNDir = Server.MapPath("../_dsn")
connectstr = "filedsn=" & sDSNDir & "\" & dsn_name

'*** create & open DB connection object
Set dbConn = Server.CreateObject("ADODB.Connection")
dbConn.Open connectstr

'*** create SQL insert statement
sSql = "INSERT into registrations
VALUES('firstname','middle','last','F','address1','addr2','myCity','NC','28803','usa','2223334444','5556667777','8889990000','(e-mail address removed)','','indiv','my
family members','yes I can','3');"

'*** create DB command object & execute insert
Set dbCmd = Server.CreateObject("ADODB.Command")
Set dbCmd.ActiveConnection = dbConn
dbCmd.CommandType = adCmdText ' text str not stored proc
dbCmd.CommandText = sSql
error >>>> dbCmd.Execute

'*** cleanup
Set dbCmd = nothing
dbConn.Close
Set dbConn = nothing

----------------------------------------------------------------
 
G

Guest

Hi Cynth,

It appears as if you are using ASP....is this correct? If so, I'm not
likely to be able to offer much help. Here are some things that you might
look into:

1.) How many fields are in your registration table? It appears as if you
should have 19 fields.
2.) Try copying the following code into a new module in the target Access
(actually JET) database. Run it, and then open the immediate window (Ctrl
G). You should see a valid SQL statement that starts with the INSERT keyword:

Option Compare Database
Option Explicit

Sub Newtest()

Dim sSQL As String

'*** create SQL insert statement
sSQL = "INSERT into registrations VALUES('firstname','middle'," _
& "'last','F','address1','addr2','myCity','NC','28803','usa'," _
&
"'2223334444','5556667777','8889990000','(e-mail address removed)'," _
& "'','indiv','my family Members ','yes I can','3');"

Debug.Print sSQL

End Sub


Does this SQL statement run without any errors if you copy it from the
immediate window, and paste it into the SQL view of a new query?

3.) Do you have a field named "last", or is this a value that you are
attempting to insert? Note that the word "last" is considered a reserved
word. If this is the name of a field, I'd try renaming the field.

Reserved Words
Reserved Words in Microsoft Access
http://support.microsoft.com/?id=286335

List of reserved words in Jet 4.0
http://support.microsoft.com/?id=321266

4.) I'm unsure about your use of the following statement at the beginning:
adCmdText = 1

When run from within Access, this line of code generates the error "Compile
Error: Assignment to constant not permitted".

5.) Try sprinkling in some debug.print statements, to see if you are getting
the expected results for variables such as sDSNDir and connectstr.


Tom
________________________________________

:

Greetings and thank you!

I am performing a very simple insert into an MS Access DB located on a
Microsoft server hosted by GoDaddy. For the sake of testing, I have
hardcoded the data being inserted (normally comes from a form) - see
error and code sample below.

I am getting a wierd error I can't explain or find anything about. I
have scoured my books and can find nothing on this...and the host will
not help.

Any thoughts? Thank you kindly.
Cynthia-

Here's the error...
-----------------------------------------------------------
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
ODBC Microsoft Access Driver - Operation must use an updateable query.


Here's the code......it's blowing up on the EXECUTE stmt at the end.
----------------------------------------------------------------
'*** prep for connection to the DB
adCmdText = 1
sDSNDir = Server.MapPath("../_dsn")
connectstr = "filedsn=" & sDSNDir & "\" & dsn_name

'*** create & open DB connection object
Set dbConn = Server.CreateObject("ADODB.Connection")
dbConn.Open connectstr

'*** create SQL insert statement
sSql = "INSERT into registrations
VALUES('firstname','middle','last','F','address1','addr2','myCity','NC','28803','usa','2223334444','5556667777','8889990000','(e-mail address removed)','','indiv','my
family members','yes I can','3');"

'*** create DB command object & execute insert
Set dbCmd = Server.CreateObject("ADODB.Command")
Set dbCmd.ActiveConnection = dbConn
dbCmd.CommandType = adCmdText ' text str not stored proc
dbCmd.CommandText = sSql
error >>>> dbCmd.Execute

'*** cleanup
Set dbCmd = nothing
dbConn.Close
Set dbConn = nothing

----------------------------------------------------------------
 
G

Guest

Some additional information that might be helpful:

PRB: ASP Returns 'Operation Must Use an Updateable Query' Error
http://support.microsoft.com/kb/175168/EN-US/

and

Troubleshooting guide for 80004005 errors in Active Server Pages
and Microsoft Data Access Components
http://support.microsoft.com/?id=306518



Good Luck!

Tom
________________________________________

:

Hi Cynth,

It appears as if you are using ASP....is this correct? If so, I'm not
likely to be able to offer much help. Here are some things that you might
look into:

1.) How many fields are in your registration table? It appears as if you
should have 19 fields.
2.) Try copying the following code into a new module in the target Access
(actually JET) database. Run it, and then open the immediate window (Ctrl
G). You should see a valid SQL statement that starts with the INSERT keyword:

Option Compare Database
Option Explicit

Sub Newtest()

Dim sSQL As String

'*** create SQL insert statement
sSQL = "INSERT into registrations VALUES('firstname','middle'," _
& "'last','F','address1','addr2','myCity','NC','28803','usa'," _
&
"'2223334444','5556667777','8889990000','(e-mail address removed)'," _
& "'','indiv','my family Members ','yes I can','3');"

Debug.Print sSQL

End Sub


Does this SQL statement run without any errors if you copy it from the
immediate window, and paste it into the SQL view of a new query?

3.) Do you have a field named "last", or is this a value that you are
attempting to insert? Note that the word "last" is considered a reserved
word. If this is the name of a field, I'd try renaming the field.

Reserved Words
Reserved Words in Microsoft Access
http://support.microsoft.com/?id=286335

List of reserved words in Jet 4.0
http://support.microsoft.com/?id=321266

4.) I'm unsure about your use of the following statement at the beginning:
adCmdText = 1

When run from within Access, this line of code generates the error "Compile
Error: Assignment to constant not permitted".

5.) Try sprinkling in some debug.print statements, to see if you are getting
the expected results for variables such as sDSNDir and connectstr.


Tom
________________________________________

:

Greetings and thank you!

I am performing a very simple insert into an MS Access DB located on a
Microsoft server hosted by GoDaddy. For the sake of testing, I have
hardcoded the data being inserted (normally comes from a form) - see
error and code sample below.

I am getting a wierd error I can't explain or find anything about. I
have scoured my books and can find nothing on this...and the host will
not help.

Any thoughts? Thank you kindly.
Cynthia-

Here's the error...
-----------------------------------------------------------
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
ODBC Microsoft Access Driver - Operation must use an updateable query.


Here's the code......it's blowing up on the EXECUTE stmt at the end.
----------------------------------------------------------------
'*** prep for connection to the DB
adCmdText = 1
sDSNDir = Server.MapPath("../_dsn")
connectstr = "filedsn=" & sDSNDir & "\" & dsn_name

'*** create & open DB connection object
Set dbConn = Server.CreateObject("ADODB.Connection")
dbConn.Open connectstr

'*** create SQL insert statement
sSql = "INSERT into registrations
VALUES('firstname','middle','last','F','address1','addr2','myCity','NC','28803','usa','2223334444','5556667777','8889990000','(e-mail address removed)','','indiv','my
family members','yes I can','3');"

'*** create DB command object & execute insert
Set dbCmd = Server.CreateObject("ADODB.Command")
Set dbCmd.ActiveConnection = dbConn
dbCmd.CommandType = adCmdText ' text str not stored proc
dbCmd.CommandText = sSql
error >>>> dbCmd.Execute

'*** cleanup
Set dbCmd = nothing
dbConn.Close
Set dbConn = nothing

----------------------------------------------------------------
 
C

Cynth

Thank you kindly, Tom, for your input.

Yes, I am usinig ASP. The seeming-reserved word (last) is, in fact, a
piece of data, not a column name.

I have triple checked that all fields-to-columns are accounted for -
good there.

I have not run your test re: copying code to a new module.... I need to
look at it more closely....but I did go immediately to the Microsoft
support pages you referenced!!! Excellent. What an invaluable resource.

I can't thank you enough for it. I had no idea.

Thank you again, most kindly and fare thee well,
Cynthia-
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Operation must use an updateable query. 1
must use updateable query 5
Operation must use an updateable query. 3
Query works on dev db, gets a Run-time error '3073': Operation mustuse an updateable query on Pre-pr 1
ASP Error on INSERT Statement 1
insert error 2
ASP Error on INSERT INTO Statement 1
insert query on recordset error? 1

Top