Operation must use an updateable query error...HELLLP!!

[AncientTiger]

I've been beating my brains out trying to figure this one out....granted I'm new to SQL and Databases, but everything I'm reading says this should work.....

I could take a page and a half trying to explain the problem.. instead I've set up a sample at my site that shows you what's going on. The URL is

http://www.ancienttiger.com/test/test.asp.

I've set up a very simple form that sends results to an asp page containing my script. The source code and DB file are available on the second page....

If anyone can figure this one out, I'll give 'em a BIIIG, juicy KISS! (well....not really.....but I WILL appreciate the help IMMENSELY!!)

Thanks,
AT

 

[tlbroadbent]

Some rules for updateable SQL queries.

1) The table(s) to be updated may require a primary key, unique index or timestamp column (not datetime column!).

2) A query must not contain functions, Union, Group By, Distinct or other aggregate clauses.

3) The "One Side" of an outer join cannot be updated.

If you post your query someone may be able to see why it is not updateable. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[AncientTiger]

Ok, here's the SQL:

==============================================
Set cnn = Server.CreateObject("ADODB.Connection&quot

strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\MyDB.mdb"

cnn.Open strConnectionString

strSQL = "INSERT INTO MyTable (FirstName , LastName , Address , City , State , Zip , Email , Phone)"
strSQL = strSQL + " VALUES ('"+fname + "' , '" + lname + "' , '" + address + "' , '" + city + "' , '" + state + "' , '" + zip + "' , '" + email + "' , '" + phone +"')"

cnn.Execute strSQL,,adCmdText
cnn.close
==============================================

Sorry, didn't even think of posting it (my brain is in full meltdown mode at this point)...

Don't have a clue what you mean by 'The "One Side" of an outer join cannot be updated.'... care to explain that one a bit?

THANKS for the response!!
AT

 

[tlbroadbent]

When you join two tables, if one of the tables has a "one to many" releationship with the other table, you'll not be able to update the table on the one side of the relationship within the query.

I have a more fundamental question. Is the table an Access table? It appears to be from your code. If so, you should post your question in an Access forum rather than SQL Server forum. My first answers assumed you were updating a SQL Server table.

If you are updating an Access database from an ASP page, does the web account have update permissions in the directory where the access database is stored? Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[AncientTiger]

EUREKA!!!!!!!!!

The file permissions (*SLAPS FOREHEAD*).... that was the ticket.... just mark "DENSE" on my forehead in permanent ink.

It works now, and yes it is an access DB, and sorry for the "mis-post". Figured it was an SQL programming syntax error...

Thanks a TON TL, you have no idea how much you've helped out an "old" programmer

AT

(FILE PERMISSIONS!!!.....GEEZ! I need a vacation!!)