|
 |
asp_databases thread: Autonumber Heck
Message #1 by "M. May" <mmay@b...> on Tue, 26 Feb 2002 23:21:52
|
|
Greetings Folks:
I'm having a devil of a time searching throught an Access Db, where the
item i'm searching for is an autogenerated item. I believe I've followed
every suggestion I've found on this board, but I may have overlooked
something.
ID is autogenerated.
ticketNumbFromForm is a number input by the end user.
===begin code snippet===
'open connection to ticket db
set connect = server.createobject("ADODB.connection")
connect.connectionstring = "DSN=cpeTrack"
connect.open
strSql = "SELECT
dateOpened,openedByEmail,openedByTechNumb,jobNumb,typeOfEscalation,cpeType,
cpeDateOfIssue,cpeSerialNumb,acctNumb,billingSys,town,warehouse,fma,escalat
ionSentTo,closedBy,dateClosed,duration,comments,resolution,opNumb FROM
ticketTrack WHERE ID = "&ticketNumbFromForm
set recset = server.createobject("ADODB.Recordset")
recset.open strSql, connect
===end code snippet===
when strSql ends as above, i get a syntax error [Syntax error (missing
operator) in query expression 'ID ='.]
when strSql end like this - WHERE ID = '"&ticketNumbFromForm&"'" - i get
the type mismatch error[Data type mismatch in criteria expression.]
I'd be pulling out my hair, except I shaved it all off...
Message #2 by "Peter Foti (PeterF)" <PeterF@S...> on Tue, 26 Feb 2002 18:25:16 -0500
|
|
I *believe* ID is a reserved word that you should not use. You should
rename the field in your form, perhaps to "TID" (for Ticket ID). I
think that is what your problem is. After you have renamed it, then
since this is a number field, you do not want to surround it with
quotes, so this is the correct way to end the SQL string:
WHERE TID=" & ticketNumbFromForm
Hope this helps.
Pete
> -----Original Message-----
> From: M. May [mailto:mmay@b...]
> Sent: Tuesday, February 26, 2002 11:22 PM
> To: ASP Databases
> Subject: [asp_databases] Autonumber Heck
>
>
> Greetings Folks:
>
> I'm having a devil of a time searching throught an Access Db,
> where the
> item i'm searching for is an autogenerated item. I believe
> I've followed
> every suggestion I've found on this board, but I may have overlooked
> something.
>
> ID is autogenerated.
> ticketNumbFromForm is a number input by the end user.
>
> ===begin code snippet===
> 'open connection to ticket db
> set connect = server.createobject("ADODB.connection")
> connect.connectionstring = "DSN=cpeTrack"
> connect.open
>
> strSql = "SELECT
> dateOpened,openedByEmail,openedByTechNumb,jobNumb,typeOfEscala
> tion,cpeType,
> cpeDateOfIssue,cpeSerialNumb,acctNumb,billingSys,town,warehous
> e,fma,escalat
> ionSentTo,closedBy,dateClosed,duration,comments,resolution,opN
> umb FROM
> ticketTrack WHERE ID = "&ticketNumbFromForm
> set recset = server.createobject("ADODB.Recordset")
> recset.open strSql, connect
> ===end code snippet===
>
> when strSql ends as above, i get a syntax error [Syntax error
> (missing
> operator) in query expression 'ID ='.]
>
> when strSql end like this - WHERE ID =
> '"&ticketNumbFromForm&"'" - i get
> the type mismatch error[Data type mismatch in criteria expression.]
>
> I'd be pulling out my hair, except I shaved it all off...
> $subst('Email.Unsub').
>
Message #3 by "Ken Schaefer" <ken@a...> on Wed, 27 Feb 2002 12:21:56 +1100
|
|
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "M. May" <mmay@b...>
Subject: [asp_databases] Autonumber Heck
: I'm having a devil of a time searching throught an Access Db, where the
: item i'm searching for is an autogenerated item. I believe I've followed
: every suggestion I've found on this board, but I may have overlooked
: something.
:
: ID is autogenerated.
: ticketNumbFromForm is a number input by the end user.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
http://www.adopenstatic.com/faq/80040e10.asp#scenario3
(Incorrect delimiters with SELECT queries) - do not use ' with number type
fields in Access
Also, look at:
http://www.adopenstatic.com/faq/80040e14.asp
as you may have problems latter on (with INSERTs and UPDATEs) with the field
names you are using
Cheers
Ken
Message #4 by "Mitch Swetsky" <mswetsky@r...> on Wed, 27 Feb 2002 07:24:40 -0500
|
|
It looks like you are missing Quotes after your var name WHERE ID
"&ticketNumbFromForm
----- Original Message -----
From: M. May <mmay@b...>
To: ASP Databases <asp_databases@p...>
Sent: Tuesday, February 26, 2002 11:21 PM
Subject: [asp_databases] Autonumber Heck
> Greetings Folks:
>
> I'm having a devil of a time searching throught an Access Db, where the
> item i'm searching for is an autogenerated item. I believe I've followed
> every suggestion I've found on this board, but I may have overlooked
> something.
>
> ID is autogenerated.
> ticketNumbFromForm is a number input by the end user.
>
> ===begin code snippet===
> 'open connection to ticket db
> set connect = server.createobject("ADODB.connection")
> connect.connectionstring = "DSN=cpeTrack"
> connect.open
>
> strSql = "SELECT
>
dateOpened,openedByEmail,openedByTechNumb,jobNumb,typeOfEscalation,cpeType,
>
cpeDateOfIssue,cpeSerialNumb,acctNumb,billingSys,town,warehouse,fma,escalat
> ionSentTo,closedBy,dateClosed,duration,comments,resolution,opNumb FROM
> ticketTrack WHERE ID = "&ticketNumbFromForm
> set recset = server.createobject("ADODB.Recordset")
> recset.open strSql, connect
> ===end code snippet===
>
> when strSql ends as above, i get a syntax error [Syntax error (missing
> operator) in query expression 'ID ='.]
>
> when strSql end like this - WHERE ID = '"&ticketNumbFromForm&"'" - i get
> the type mismatch error[Data type mismatch in criteria expression.]
>
> I'd be pulling out my hair, except I shaved it all off...
$subst('Email.Unsub').
>
Message #5 by "M. May" <mmay@b...> on Wed, 27 Feb 2002 12:41:22
|
|
Well, I renamed ID as tickId, so now the sql statement ends thusly:
WHERE tickId = "&ticketNumbFromForm
Unfortunately, now I'm back to getting the syntax error:
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing
operator) in query expression 'tickId ='.
> I *believe* ID is a reserved word that you should not use. You should
> rename the field in your form, perhaps to "TID" (for Ticket ID). I
> think that is what your problem is. After you have renamed it, then
> since this is a number field, you do not want to surround it with
> quotes, so this is the correct way to end the SQL string:
>
> WHERE TID=" & ticketNumbFromForm
>
> Hope this helps.
> Pete
>
>
> > -----Original Message-----
> > From: M. May [mailto:mmay@b...]
> > Sent: Tuesday, February 26, 2002 11:22 PM
> > To: ASP Databases
> > Subject: [asp_databases] Autonumber Heck
> >
> >
> > Greetings Folks:
> >
> > I'm having a devil of a time searching throught an Access Db,
> > where the
> > item i'm searching for is an autogenerated item. I believe
> > I've followed
> > every suggestion I've found on this board, but I may have overlooked
> > something.
> >
> > ID is autogenerated.
> > ticketNumbFromForm is a number input by the end user.
> >
> > ===begin code snippet===
> > 'open connection to ticket db
> > set connect = server.createobject("ADODB.connection")
> > connect.connectionstring = "DSN=cpeTrack"
> > connect.open
> >
> > strSql = "SELECT
> > dateOpened,openedByEmail,openedByTechNumb,jobNumb,typeOfEscala
> > tion,cpeType,
> > cpeDateOfIssue,cpeSerialNumb,acctNumb,billingSys,town,warehous
> > e,fma,escalat
> > ionSentTo,closedBy,dateClosed,duration,comments,resolution,opN
> > umb FROM
> > ticketTrack WHERE ID = "&ticketNumbFromForm
> > set recset = server.createobject("ADODB.Recordset")
> > recset.open strSql, connect
> > ===end code snippet===
> >
> > when strSql ends as above, i get a syntax error [Syntax error
> > (missing
> > operator) in query expression 'ID ='.]
> >
> > when strSql end like this - WHERE ID =
> > '"&ticketNumbFromForm&"'" - i get
> > the type mismatch error[Data type mismatch in criteria expression.]
> >
> > I'd be pulling out my hair, except I shaved it all off...
> > $subst('Email.Unsub').
> >
Message #6 by "Meinken, Joe" <Joe.Meinken@q...> on Wed, 27 Feb 2002 07:43:48 -0600
|
|
Is ticketID a varchar/char or an integer field? Is ticketNumbFromForm of
the same data type?
-----Original Message-----
From: M. May [mailto:mmay@b...]
Sent: Wednesday, February 27, 2002 6:41 AM
To: ASP Databases
Subject: [asp_databases] RE: Autonumber Heck
Well, I renamed ID as tickId, so now the sql statement ends thusly:
WHERE tickId = "&ticketNumbFromForm
Unfortunately, now I'm back to getting the syntax error:
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing
operator) in query expression 'tickId ='.
> I *believe* ID is a reserved word that you should not use. You should
> rename the field in your form, perhaps to "TID" (for Ticket ID). I
> think that is what your problem is. After you have renamed it, then
> since this is a number field, you do not want to surround it with
> quotes, so this is the correct way to end the SQL string:
>
> WHERE TID=" & ticketNumbFromForm
>
> Hope this helps.
> Pete
>
>
> > -----Original Message-----
> > From: M. May [mailto:mmay@b...]
> > Sent: Tuesday, February 26, 2002 11:22 PM
> > To: ASP Databases
> > Subject: [asp_databases] Autonumber Heck
> >
> >
> > Greetings Folks:
> >
> > I'm having a devil of a time searching throught an Access Db,
> > where the
> > item i'm searching for is an autogenerated item. I believe
> > I've followed
> > every suggestion I've found on this board, but I may have overlooked
> > something.
> >
> > ID is autogenerated.
> > ticketNumbFromForm is a number input by the end user.
> >
> > ===begin code snippet===
> > 'open connection to ticket db
> > set connect = server.createobject("ADODB.connection")
> > connect.connectionstring = "DSN=cpeTrack"
> > connect.open
> >
> > strSql = "SELECT
> > dateOpened,openedByEmail,openedByTechNumb,jobNumb,typeOfEscala
> > tion,cpeType,
> > cpeDateOfIssue,cpeSerialNumb,acctNumb,billingSys,town,warehous
> > e,fma,escalat
> > ionSentTo,closedBy,dateClosed,duration,comments,resolution,opN
> > umb FROM
> > ticketTrack WHERE ID = "&ticketNumbFromForm
> > set recset = server.createobject("ADODB.Recordset")
> > recset.open strSql, connect
> > ===end code snippet===
> >
> > when strSql ends as above, i get a syntax error [Syntax error
> > (missing
> > operator) in query expression 'ID ='.]
> >
> > when strSql end like this - WHERE ID =
> > '"&ticketNumbFromForm&"'" - i get
> > the type mismatch error[Data type mismatch in criteria expression.]
> >
> > I'd be pulling out my hair, except I shaved it all off...
> > $subst('Email.Unsub').
> >
$subst('Email.Unsub').
Message #7 by "M. May" <mmay@b...> on Wed, 27 Feb 2002 14:14:25
|
|
well, everyone - thanks for all of your help. i was seriously pressed for
time, so I had to come up with another solution without using the
autonumber generated field at all.
i'll revisit this in about a week or so when i have more time to deal with
this.
thanks again. ttfn
> Is ticketID a varchar/char or an integer field? Is ticketNumbFromForm of
> the same data type?
>
> -----Original Message-----
> From: M. May [mailto:mmay@b...]
> Sent: Wednesday, February 27, 2002 6:41 AM
> To: ASP Databases
> Subject: [asp_databases] RE: Autonumber Heck
>
>
> Well, I renamed ID as tickId, so now the sql statement ends thusly:
> WHERE tickId = "&ticketNumbFromForm
> Unfortunately, now I'm back to getting the syntax error:
> [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing
> operator) in query expression 'tickId ='.
>
>
> > I *believe* ID is a reserved word that you should not use. You should
> > rename the field in your form, perhaps to "TID" (for Ticket ID). I
> > think that is what your problem is. After you have renamed it, then
> > since this is a number field, you do not want to surround it with
> > quotes, so this is the correct way to end the SQL string:
> >
> > WHERE TID=" & ticketNumbFromForm
> >
> > Hope this helps.
> > Pete
> >
> >
> > > -----Original Message-----
> > > From: M. May [mailto:mmay@b...]
> > > Sent: Tuesday, February 26, 2002 11:22 PM
> > > To: ASP Databases
> > > Subject: [asp_databases] Autonumber Heck
> > >
> > >
> > > Greetings Folks:
> > >
> > > I'm having a devil of a time searching throught an Access Db,
> > > where the
> > > item i'm searching for is an autogenerated item. I believe
> > > I've followed
> > > every suggestion I've found on this board, but I may have overlooked
> > > something.
> > >
> > > ID is autogenerated.
> > > ticketNumbFromForm is a number input by the end user.
> > >
> > > ===begin code snippet===
> > > 'open connection to ticket db
> > > set connect = server.createobject("ADODB.connection")
> > > connect.connectionstring = "DSN=cpeTrack"
> > > connect.open
> > >
> > > strSql = "SELECT
> > > dateOpened,openedByEmail,openedByTechNumb,jobNumb,typeOfEscala
> > > tion,cpeType,
> > > cpeDateOfIssue,cpeSerialNumb,acctNumb,billingSys,town,warehous
> > > e,fma,escalat
> > > ionSentTo,closedBy,dateClosed,duration,comments,resolution,opN
> > > umb FROM
> > > ticketTrack WHERE ID = "&ticketNumbFromForm
> > > set recset = server.createobject("ADODB.Recordset")
> > > recset.open strSql, connect
> > > ===end code snippet===
> > >
> > > when strSql ends as above, i get a syntax error [Syntax error
> > > (missing
> > > operator) in query expression 'ID ='.]
> > >
> > > when strSql end like this - WHERE ID =
> > > '"&ticketNumbFromForm&"'" - i get
> > > the type mismatch error[Data type mismatch in criteria expression.]
> > >
> > > I'd be pulling out my hair, except I shaved it all off...
> > > $subst('Email.Unsub').
> > >
>
> $subst('Email.Unsub').
Message #8 by "Helga Y. Anagnostopoulos" <helga@k...> on Wed, 27 Feb 2002 16:10:04 +0200
|
|
sorry, just a quick solution on this error:
The statement must read:
WHERE tickId = '" & ticketNumbFromForm & "'"
In this case it means that tickid is not an integer but string/varchar kind
of field.
-----Original Message-----
From: M. May [mailto:mmay@b...]
Sent: Wednesday, February 27, 2002 2:14 PM
To: ASP Databases
Subject: [asp_databases] RE: Autonumber Heck
well, everyone - thanks for all of your help. i was seriously pressed for
time, so I had to come up with another solution without using the
autonumber generated field at all.
i'll revisit this in about a week or so when i have more time to deal with
this.
thanks again. ttfn
> Is ticketID a varchar/char or an integer field? Is ticketNumbFromForm of
> the same data type?
>
> -----Original Message-----
> From: M. May [mailto:mmay@b...]
> Sent: Wednesday, February 27, 2002 6:41 AM
> To: ASP Databases
> Subject: [asp_databases] RE: Autonumber Heck
>
>
> Well, I renamed ID as tickId, so now the sql statement ends thusly:
> WHERE tickId = "&ticketNumbFromForm
> Unfortunately, now I'm back to getting the syntax error:
> [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing
> operator) in query expression 'tickId ='.
>
>
> > I *believe* ID is a reserved word that you should not use. You should
> > rename the field in your form, perhaps to "TID" (for Ticket ID). I
> > think that is what your problem is. After you have renamed it, then
> > since this is a number field, you do not want to surround it with
> > quotes, so this is the correct way to end the SQL string:
> >
> > WHERE TID=" & ticketNumbFromForm
> >
> > Hope this helps.
> > Pete
> >
> >
> > > -----Original Message-----
> > > From: M. May [mailto:mmay@b...]
> > > Sent: Tuesday, February 26, 2002 11:22 PM
> > > To: ASP Databases
> > > Subject: [asp_databases] Autonumber Heck
> > >
> > >
> > > Greetings Folks:
> > >
> > > I'm having a devil of a time searching throught an Access Db,
> > > where the
> > > item i'm searching for is an autogenerated item. I believe
> > > I've followed
> > > every suggestion I've found on this board, but I may have overlooked
> > > something.
> > >
> > > ID is autogenerated.
> > > ticketNumbFromForm is a number input by the end user.
> > >
> > > ===begin code snippet===
> > > 'open connection to ticket db
> > > set connect = server.createobject("ADODB.connection")
> > > connect.connectionstring = "DSN=cpeTrack"
> > > connect.open
> > >
> > > strSql = "SELECT
> > > dateOpened,openedByEmail,openedByTechNumb,jobNumb,typeOfEscala
> > > tion,cpeType,
> > > cpeDateOfIssue,cpeSerialNumb,acctNumb,billingSys,town,warehous
> > > e,fma,escalat
> > > ionSentTo,closedBy,dateClosed,duration,comments,resolution,opN
> > > umb FROM
> > > ticketTrack WHERE ID = "&ticketNumbFromForm
> > > set recset = server.createobject("ADODB.Recordset")
> > > recset.open strSql, connect
> > > ===end code snippet===
> > >
> > > when strSql ends as above, i get a syntax error [Syntax error
> > > (missing
> > > operator) in query expression 'ID ='.]
> > >
> > > when strSql end like this - WHERE ID
> > > '"&ticketNumbFromForm&"'" - i get
> > > the type mismatch error[Data type mismatch in criteria expression.]
> > >
> > > I'd be pulling out my hair, except I shaved it all off...
> > > $subst('Email.Unsub').
> > >
>
> $subst('Email.Unsub').
$subst('Email.Unsub').
Message #9 by "Drew, Ron" <RDrew@B...> on Wed, 27 Feb 2002 20:53:34 -0500
|
|
Do as Ken suggested and do a response.write on strSql.
Are you doing a querystring, response.form to get data from form on a
second asp. If so, are you testing to make sure the user entered a
valid number using Javascript when the form is entered?
-----Original Message-----
From: M. May [mailto:mmay@b...]
Sent: Wednesday, February 27, 2002 7:41 AM
To: ASP Databases
Subject: [asp_databases] RE: Autonumber Heck
Well, I renamed ID as tickId, so now the sql statement ends thusly:
WHERE tickId =3D "&ticketNumbFromForm
Unfortunately, now I'm back to getting the syntax error:
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing
operator) in query expression 'tickId =3D'.
> I *believe* ID is a reserved word that you should not use. You should
> rename the field in your form, perhaps to "TID" (for Ticket ID). I
> think that is what your problem is. After you have renamed it, then
> since this is a number field, you do not want to surround it with
> quotes, so this is the correct way to end the SQL string:
>
> WHERE TID=3D" & ticketNumbFromForm
>
> Hope this helps.
> Pete
>
>
> > -----Original Message-----
> > From: M. May [mailto:mmay@b...]
> > Sent: Tuesday, February 26, 2002 11:22 PM
> > To: ASP Databases
> > Subject: [asp_databases] Autonumber Heck
> >
> >
> > Greetings Folks:
> >
> > I'm having a devil of a time searching throught an Access Db,
> > where the
> > item i'm searching for is an autogenerated item. I believe
> > I've followed
> > every suggestion I've found on this board, but I may have overlooked
> > something.
> >
> > ID is autogenerated.
> > ticketNumbFromForm is a number input by the end user.
> >
> > =3D=3D=3Dbegin code snippet=3D=3D=3D
> > 'open connection to ticket db
> > set connect =3D server.createobject("ADODB.connection")
> > connect.connectionstring =3D "DSN=3DcpeTrack"
> > connect.open
> >
> > strSql =3D "SELECT
> > dateOpened,openedByEmail,openedByTechNumb,jobNumb,typeOfEscala
> > tion,cpeType,
> > cpeDateOfIssue,cpeSerialNumb,acctNumb,billingSys,town,warehous
> > e,fma,escalat
> > ionSentTo,closedBy,dateClosed,duration,comments,resolution,opN
> > umb FROM
> > ticketTrack WHERE ID =3D "&ticketNumbFromForm
> > set recset =3D server.createobject("ADODB.Recordset")
> > recset.open strSql, connect
> > =3D=3D=3Dend code snippet=3D=3D=3D
> >
> > when strSql ends as above, i get a syntax error [Syntax error
> > (missing
> > operator) in query expression 'ID =3D'.]
> >
> > when strSql end like this - WHERE ID =3D
> > '"&ticketNumbFromForm&"'" - i get
> > the type mismatch error[Data type mismatch in criteria expression.]
> >
> > I'd be pulling out my hair, except I shaved it all off...
> > $subst('Email.Unsub').
> >
$subst('Email.Unsub').
|
|
|
View Cart
