UPDATEABLE QUERY error on INSERT ??

  • Thread starter Thread starter Cynth-
  • Start date Start date
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

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

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

----------------------------------------------------------------
 
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-
 
Back
Top