kevind
Posts: 251
|
| Posted: 05/27/2008, 12:28 PM |
|
Hi, I'm using ASP Code to push some SQL commands against an MS Access DB.
Here's the code (assume all variables are DIM'd)
Set conn = new clsDBConnection1
conn.open
SQL="INSERT INTO Payments ([Date],No,InvestigatorID,Created,CUser) VALUES (" + _
conn.ToSQL(CCGetParam("crit_PayDate",empty),ccsDate) +","+ _
cStr(HiPayNum) +", "+ _
conn.ToSQL(rsInvestigators.Fields("ID"),ccsText) + "," + _
conn.ToSQL(now(),ccsDate) + "," + _
conn.ToSQL(CCGetUserLogin(),ccsText) +");"
'try inserting - on error abort pays
Set rsInsertPayNo = conn.Execute(SQL)
the SQL string results as this:
INSERT INTO Payments ([Date],No,InvestigatorID,Created,CUser) VALUES (#27-May-2008#,2, '{07872F33-3593-4AE8-BD91-E160366D9557}',#05/27/2008 15:25:39#,'dba');
conn.Errors.ToString comes back Syntax error in INSERT INTO statement. (Microsoft JET Database Engine)
I can run the SQL statement in Query Designer inside MS Sql without error.
I have tried dropping the date column and reformatting that first date value without any change in results.
Any Ideas anyone?
thanks,
kd
_________________
thanks
Kevin
======================
CCS 3.2.x / ASP / MS Access /mySQL / PHP / jQuery / jQuery UI / jQuery Mobile
|
 |
 |
mljonzs
Posts: 124
|
| Posted: 05/27/2008, 12:46 PM |
|
Try putting single quotes into the Values clause around the cStr(HiPayNum) statement since you aren't using the ToSQL for this parameter.....
So code should be ...ccsDate) + ",'"+ cStr(HiPayNum)+"', "....
_________________
What does not begin WITH God, will end in failure!
|
|
|
|
kevind
Posts: 251
|
| Posted: 05/27/2008, 12:49 PM |
|
duh, needed [square] brackets around all field names (not just date which i thought might be reserved) to make it happy 
_________________
thanks
Kevin
======================
CCS 3.2.x / ASP / MS Access /mySQL / PHP / jQuery / jQuery UI / jQuery Mobile
|
|
|
|
mljonzs
Posts: 124
|
| Posted: 05/27/2008, 12:52 PM |
|
That makes sense - Access is a bit goofy that way. :) Sorry, I didn't think about that one! :)
Glad you got it though! :)
_________________
What does not begin WITH God, will end in failure!
|
|
|
|
|
