Insert statement not working... Not executing using Access and ASP

chosaru
chosaru used Ask the Experts™
on
My Code:

<%
Dim sConnStr, OBJdbConn, NewDomain, insertDomain
sConnStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source= xxx"

NewDomain = lcase(trim(Request("newDomain")))

insertDomain = "INSERT INTO tbDomain (domain) VALUES ('" & NewDomain & "')"

Set OBJdbConn = Server.CreateObject("ADODB.Connection")
OBJdbConn.Open sConnStr
OBJdbConn.Execute insertDomain    <------- Line 11
OBJdbConn.close
set OBJdbConn = Nothing
%>
----------------------

I get the Following Error:

Microsoft JET Database Engine error '80040e14'

Syntax error in INSERT INTO statement.

/CRAPDOESNTWORK.asp, line 11
----------------------

I only have 2 fields in the Database which are

id > Primary Key
  ---and
domain > Text and the size is 75
----------------------

What gives? I have been looking through tons of articals on Inserting files. Am I misssing something with the Primary Key? I only try inserting the one record, shouldn't the primary key be automatic? Any help on this would be great.

Chosaru
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
try writing your sql statement in the browser and check for syntax error... a table you defined maybe mis spelled...
or better yet type an insert statement in the SQL query analyzer and copy that to your code....

is your primary key an identity?

let us know how that works...

Commented:
Is your id autonumber type?  If not, you must insert a value for the id field.  To get the next available number:

Dim recSet

Set recSet = Server.CreateObject("Adodb.RecordSet")

recSet.open "SELECT id FROM tbDomain ORDER BY id", OBJdbConn

recSet.movelast

varNewRecordID = recSet("id") + 1

recSet.close

OBJdbConn.Execute "insert into tbDomain (id, domain) values (" & varNewRecordID & ", '" & NewDomain & "')"

Red

Commented:
check this link
http://www.adopenstatic.com/faq/80040e14.asp#scenario2a

This commonly occurs when your field name is a reserved word (see scenario 1 above). Adjust your field names and SQL statement accordingly and you should avoid the problem.

If you can't adjust your fieldnames you can use [ ] marks to delimit the field names, eg

INSERT INTO table1
([field], [password])
VALUES ('value1', 'value2')
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Commented:
I would say from the error that the statement is not valid SQL.

For example, you do not escape single quotes in the string to input:

insertDomain = "INSERT INTO tbDomain (domain) VALUES ('" & Replace(NewDomain,"'","''") & "')"

If you use a non-autonumber id-field, and you use ACCESS, then better get the new id by using

iNewId = connection.execute("SELECT MAX(id) FROM tbl")(0) + 1


Do a response.write newDomain statement and see what you get.  There is something wrong with the string.

Author

Commented:
Ok I made sure all my connections were good. I used the same connection for my other scripts. I checked all the spelling. I changed both of the column names to TESTid and TESTdomain. And I changed all the refferences to those in my script, which is only the TESTdomain. Also, I looked at the TESTid and it says "(AutoNumber)". I rewrote the insert statement so that it looks like this:
--------------------------------------
<%
Dim sConnStr, OBJdbConn, NewDomain,
sConnStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source= The Location Here is Correct"

'NewDomain = lcase(trim(Replace(Request("newDomain"),"'",""))) <--- Commented Line

Set OBJdbConn = Server.CreateObject("ADODB.Connection")
OBJdbConn.Open sConnStr
OBJdbConn.Execute "INSERT INTO tbDomain (TESTdomain) VALUES ('ThisBetterShowUp')"
OBJdbConn.close
set OBJdbConn = Nothing
%>
--------------------------------------

I'm still not getting it to update and still getting the same error. I looked for a query analizer but didn't seem to find one. Also, When I was requesting the "NewDomain" I printed that in the browser with out running the insert script, it came out fine. I posted everything in my script. As far as I can tell, this should be rather simple. The last time I did anything with databases was 2 years ago with Microsoft SQL 7.0 and had no problems. Arg! I'm ripping out my hair fellas! It looks so simple but giving me such a hed ache... I think I'm going to throw my computer out the window. Calm Down.. Calm Down.. Warm Happy thoughts... Do I need to throw on more points??? Gesh... I think I may.

Chosaru

Author

Commented:
Ohh and this is my error message:
------------
Microsoft JET Database Engine error '80004005'

Operation must use an updateable query.

/TESTnewDomain.asp, line 9
------------

Do I have to Increment the Primary Key if it is already on autonumber?

Chosaru
Commented:
You don't have permissions set up correctly if you are getting the "updateable query" error.  The anonomous user must have read and write access to the directory that the DB resides in as well as the DB itself.

Red

Author

Commented:
Arg that's when I put the brakets around the TESTdomain. When I take them off, I get the following error:

Microsoft JET Database Engine error '80040e14'

Syntax error in INSERT INTO statement.

/TESTnewDomain.asp, line 9

Author

Commented:
I'll also check the permissions! Thanks.

Author

Commented:
Thanks. I love this place! If yo were here, I'd hump your leg :).

Commented:
when you put brackets around testdomain the error "Syntax error in insert into ..." was gone and new error "operation must be...".

the cause of this error is permissions only. you need to give permissions on the directory too, not only the mdb file.

check the following  link.

http://www.aspemporium.com/aspEmporium/help/helpsys.asp?PRB022

Commented:
oops, i was the first to give u the solution to the actual problem and u awarded points to the person who gave you the solution to the secondary problem :(

it happens here:)

Commented:
I'm glad I'm not there, but I am flattered.  Good luck.

Red

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial