update statement with DSN works, DSN-less fails

[scott]

the following SQL statement works when executing via a DSN connection
but fails when using the DSN-less method.


sql statement:

UPDATE tblApplicants SET ApplicantName='116th Pennylvania Volunteer
Infantry', ContactName='John Smith', Address='1234 S. Main St. #23',
City='Cityville ', State='CA', Zip='12345-',
DaytimePhone='(555)555-5555', PhoneExt='',
EveningPhone='(555)555-5555', Email='jsmith@whatever.com',
PreviousEntry=1, YearsInParade=1, Accepted=1, Insurance=1,
Commercial=0, Year=2003, EntryScript='blah, blah, blah. more blah',
NumberInGroup=45, DblPull=1, Wagon=1, PartnerDblPull=0, Horses=0,
LineUpFootage=0, Description='116th blah text text - blah.blah.blah
,blah and blah', Notes='', UnitSizeBanners='', DivisionID=1, Active=1
where ApplicantID=283;


asp code:

var oConn = Server.CreateObject('ADODB.Connection');
oConn.Mode = 3;
oConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=F:\\myDir\\website\\myDB.mdb";
oConn.Open();
oRS = oConn.Execute(sql);
.....


error returned:

Microsoft JET Database Engine error '80040e14'
Syntax error in UPDATE statement.


platform:
win2k, access 2k, ADO 2.6


i can read from the DB just fine and i can also execute other UPDATE
SQL statements elsewhere in my application. i have set IUSR_machine
read/write permissions to the db dir + file, read thru the many posts
regarding this error and have not found a solution. exactly what is
the difference between using DSN/DSN-less w/ the provided sql
statement?!?!?!

any help would be extremely appreciated!!!

cheers,
scott

[Bob Barrows]

This type of error usually means you've used a reserved word for one of your
column or table names. Let's see ... yep, "Year" is a reserved word and
should be avoided object names. If you can't change the column name, you
will have to remember to surround it with brackets []:

" ... [Year] = .... "

Why does it work with ODBC? I'm not sure - it should be reserved with ODBC
as well, at least, according to this page:
[url]http://www.aspfaq.com/show.asp?id=2080[/url]

BTW, you should not be using the recordset object when running an action
query that returns no records. In fact, you shold be telling ADO not to
create a recordset object by using the adExecuteNoRecords constant like
this:

oConn.Execute(sql,,129);
/*I almost didn't notice you were using jscript - I was going to comment on
the lack of the Set keyword ...*/

129 is the combination of two constants:
1 = adCmdText which tells ADO that you are executing a sql statement passed
as a string
128 = adExecuteNoRecords
1 + 128 = 129


scott wrote:

> the following SQL statement works when executing via a DSN connection
> but fails when using the DSN-less method.
>
>
> sql statement:
> Commercial=0, Year=2003, EntryScript='blah, blah, blah. more blah',

[scott]

thank you bob!! this did the trick...



"Bob Barrows" <reb_01501@yahoo.com> wrote in message news:<ubV0RERZDHA.1640@TK2MSFTNGP10.phx.gbl>...

> This type of error usually means you've used a reserved word for one of your
> column or table names. Let's see ... yep, "Year" is a reserved word and
> should be avoided object names. If you can't change the column name, you
> will have to remember to surround it with brackets []:
>
> " ... [Year] = .... "
>
> Why does it work with ODBC? I'm not sure - it should be reserved with ODBC
> as well, at least, according to this page:
> [url]http://www.aspfaq.com/show.asp?id=2080[/url]
>
> BTW, you should not be using the recordset object when running an action
> query that returns no records. In fact, you shold be telling ADO not to
> create a recordset object by using the adExecuteNoRecords constant like
> this:
>
> oConn.Execute(sql,,129);
> /*I almost didn't notice you were using jscript - I was going to comment on
> the lack of the Set keyword ...*/
>
> 129 is the combination of two constants:
> 1 = adCmdText which tells ADO that you are executing a sql statement passed
> as a string
> 128 = adExecuteNoRecords
> 1 + 128 = 129
>
>
> scott wrote:

> > the following SQL statement works when executing via a DSN connection
> > but fails when using the DSN-less method.
> >
> >
> > sql statement:
> > Commercial=0, Year=2003, EntryScript='blah, blah, blah. more blah',