Why can't I get this to work?

[mopacfan]

I'm trying to post the values of a large form to an access database. I have the code writing the sql string to the screen for debugging. If I cut and paste the code into an access query in sql design mode, it accepts the data without any problem. But in my asp code, I keep getting

Microsoft JET Database Engine error '80040e14'

Syntax error in INSERT INTO statement.

Is there some special way to open the ado connection in order to make this work? Here is my current connection

connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mdbpath 
set objconn = server.CreateObject("adodb.connection")
objconn.Open connstr

Where mdbpath is a valid path to the database file.

This is the sql string with the test data

Insert Into employee (CreateDate,position, datetoday, ssno, 
phonework, license, seccity, mreduties, smrephone, yearsknown, 
refrelation, yearsknown2, refrelation2, otherdegreetype, fname, crcity, 
state2, availother, mrespvname, smrecity, tmretodate, hsdegree, cgcity, 
mnapplicants, lname, phonecell, seccity2, seccity3, mrecity, mrestate, 
smrefromdate, tmrejobtitle, refname, mi, phonehome, crstreet1, crstreet2, 
prcity, startdate, secincedent, secincedent2, secincedent3, mrephone, 
tmrefax, tmrecompany, highschool, authsig, crstate, dlstate, smrestate, 
smresalary, cgdegree, authssno, authcity, authstate, crzip, mretodate, 
smretodate, lsmreleave, tmrestate, tmresalary, tmreleave, hscity, 
prstreet1, prstreet2, tmrephone, refname2, college, otheredcity, 
authprintname, authaddress, authaddress2, przip, smrefax, smrecompany, 
tmrecity, othered, otheredstate, signature, sigdate, dlno, dltype, 
mrefax, tmrespvname, tmreduties, refphone, refphone2, hsstate, 
mrecompany, mrejobtitle, mresalary, smrejobtitle, cgstate, autgsigdate, 
authbdate, caapplicants, mrefromdate, mreleave, smrespvname, smreduties, 
tmrefromdate, schoolname) Values 
('8/17/2007','abc', '8/17/2007', 'abc', 'abc', 'abc', 'abc', 'abc', 'abc',
 2, 'abc', 5, 'abc', 'abc', 'abc', 'abc', 'abc', 'whenever I feel like 
it', 'abc', 'abc', '01/01/06', 'abc', 'abc', 
1, 'abc', 'abc', 'abc', 'abc', 'abc', 'abc', '01/01/03', 'abc', 'abc', 'ab
c', 'abc', 'abc', 'abc', 'abc', '8/17/2007', 'abc', 'abc', 'abc', 'abc', '
abc', 'abc', 'abc', 'Signature', 'abc', 'abc', 'abc', 'abc', 'abc', 'abc',
 'abc', 'abc', 'abc', '01/01/02', '01/01/04', 'abc', 'abc', 'abc', 'abc', 
'abc', 'abc', 'abc', 'abc', 'abc', 'abc', 'abc', 'abc', 'abc', 'abc', 'abc
', 'abc', 'abc', 'abc', 'abc', 'abc', 'abc', '8/17/2007', 'abc', 'abc', 'a
bc', 'abc', 'abc', 'abc', 'abc', 'abc', 'abc', 'abc', 'abc', 'abc', 'abc',
 '8/17/2007', '8/17/2007', 
1, '01/01/01', 'abc', 'abc', 'abc', '01/01/05', 'abc');

This is for a very basic, barebones application form for a client.

If anyone can shed some light on this, I would be extremely grateful.

 

[ChrisHirst]

Only thing I can see is maybe use # around the date literals instead of '

other than that nothing stands out

Chris.

Indifference will be the downfall of mankind, but who cares?
Woo Hoo! the cobblers kids get new shoes.
People Counting Systems

So long, and thanks for all the fish.

 

[mopacfan]

Yea, I tried $ and it did not like that at all. So I went back to ' and it works in the query screen. Very strange.

 

[DotNetGnat]

1) response.write sql would be your best bet to debug
2) look for any single quotes that need to be doubled up
3) watch out for key or reserved words and put square braces around them

-DNG

 

[ChrisHirst]

use a # (pound symbol) not a $ (dollar, string symbol)



Chris.

Indifference will be the downfall of mankind, but who cares?
Woo Hoo! the cobblers kids get new shoes.
People Counting Systems

So long, and thanks for all the fish.

 

[mopacfan]

Yea, I actually did use #, it seems that I just wasn't paying attention when I typed my response, above. I'm not really getting used to this new ms natural keyboard. The curvature of the keyboard is very radical compared to what I was used to.

The sql string I posted above is the response.write of the string. If I copy and paste it into access, it works fine. That's what I'm trying to get across.

 

[bslintx]

as dotnetgnat mentioned...


..."watch out for key or reserved words and put square braces around them"


with that said i see you still have not checked for that...i think you will be pleasantly surprised if you put brackets around position [position]...for it is a reserved word

 

[mopacfan]

Well, what I ended up doing was switching to a recordset addnew and recordset update after setting all the fields. What's interesting is that I did find some fields not formatted just right with this process even though in my previous version, entering the data directly through the sql query entered the data without any problem. Go figure.