connection.execute problem

[lcscne]

OK whats the number one reason why the connection.execute method would cause a "HTTP 500 Internal Server Error"? Ive checked and rechecked my code.

DBPath = "c:\DataStores\Alumni\Alumni.mdb"
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "PROVIDER=MSDASQL;DRIVER={Microsoft Access Driver (*.mdb)};DBQ="&DBPath&";UID=admin;PWD=;"

qrystring = "insert into cminfo (fname, mname, lname, password, email, yog, memo, website, accepted) VALUES ('"
qrystring = qrystring&request.form("fname")&"', '"
qrystring = qrystring&request.form("mname")&"', '"
qrystring = qrystring&request.form("lname")&"', '"
qrystring = qrystring&request.form("password")&"', '"
qrystring = qrystring&request.form("email")&"', "
qrystring = qrystring&request.form("yog")&", '"
qrystring = qrystring&intvar&"', "
qrystring = qrystring&"false);"
Conn.Execute qrystring

response.write(qrystring) yeilds the following which I can copy directly into an Access query and run without error.

insert into cminfo (fname, mname, lname, password, email, yog, memo, website, accepted) VALUES ('Darin', '', 'Marsh', 'password', 'dogbuddy', 1985, 'dom', false);

if I remark the Conn.Execute qrystring line the code runs without error, but it sure would be nice to be able to add records to the db.

[MichaelM]

The number of fields listed in the INSERT clause does not match the number of fields listed in the VALUES clause...


looks like you if you remove one of
memo, website, accepted
from the INSERT clause (whichever one isn't being gathered from the form), you'll be able to add the records...

[lcscne]

Nope, I pasted the wrong INSERT statement into my prior message. The website field is indeed not in the field list on my live version of the code. Any other ideas?

[MichaelM]

Is the field [yog] a text datatype in the DB and you're passing it a Number?
Or some other such datatype mismatch sort of an error?

Is IIS not giving you any clue in the error, or is it just dumping the standard Error 500 page to you?

[CardboardHammer]

Comment out the Conn.Execute, response.write your querystring, paste what you get into Access, run it and see what Access has to say about the matter.

[lcscne]

IIS makes me mad, sometimes it gives me useful ASP messages with line numbers and simetimes stupid meaningless 500 messages as is the case here. yog is year of graduation stored as a number.

Access runs the SQL just fine (minus the 'website' field in prior message).

[lcscne]

This must be a permissions thing. I can change the INSERT statement to a SELECT and it works fine. I see there is a 'Mode' property to the connection object, does this normally need set before writing to the db?