Insert to Access DB with ASP problem

S

sac

Have problem trying to INSERT to Access 2003 database. Don't think it's
permissions as can add records to this tables using FrontPage forms but want
to be able to add own code.

Can select and update on Customer table but not insert, e.g. this works:
qString = "UPDATE customer SET Firstname = 'Testchange' WHERE CustomerID =
't1234ft'"
rs.Open qString, conn

but this doesn't:
qString = "INSERT INTO customer (CustomerID, Title, FirstName, LastName,
HouseNum, Street, Town, Postcode, Email, Phone, Mobile, Password, CreditInd,
CustDate) VALUES (rccustomerid, rctitle, rcfirstname, rclastname, rchousenum,
rcstreet, rctown, rcpostcode, rcemail, rcphone, rcmobile, rcpassword,
rccreditind, rccustdate)"
rs.Open qString, conn

All I get is this message :
"Internal Server Error
The server encountered an internal error or misconfiguration and was unable
to complete your request.
Please contact the server administrator to inform of the time the error
occurred and of anything you might have done that may have caused the error.
More information about this error may be available in the server error log."

I get that message anytime there's any problem with my code so it's not
particularly helpful!

New to web development so any help very welcome!
 
M

[MVP] S.Clark

I'm wondering if the recordset method of ADO should be replaced with a
command object, instead.

Other than that, look for silly stuff like a null being stuffed where it
shouldn't or any fields that don't allow zero length strings, unique index
problems, or anything else about the table and/or field properties that
would prevent a record from being entered.

--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
[email protected]
www.fmsinc.com/consulting
 
S

sac

I tried command object but that didn't work either. BUT, your comment made me
look again at the contents of VALUES and I realised I wasn't setting this up
correctly. I needed to put in all the single quotes and commas for VALUES to
be interpreted properly. To do this I created a separate variable
('valstring' below is cut-down version) and this worked (with both recordset
and command object).

Here's how I got it to work:
valstring = "(" & rccustomerid & ", '" & rcfirstname & "', '" & rclastname &
"', '" & rccustdate & "')"
qString = "INSERT INTO cust (CustID, FirstName, LastName, CustDate) VALUES "
&(valstring)
rs.Open qString, conn

Many thanks to S.Clark for pointing me in the right direction!

SAC.
 
Top