You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@struts.apache.org by Todd Grigsby <st...@tgrigsby.com> on 2003/05/17 05:43:45 UTC
COUNT field incorrect or syntax error
Ok, I've got something weird going on here and I'm hoping some of the folks here
have run into this. I've got a class that, depending on a flag, will either
build an insert SQL statement or an update SQL statement. The statements are
parameterized. When I use the insert statement, all goes well, but when use the
update statement, something similar to:
update MCI set modifiedby=?, modifieddate=?, firstname=? where recid=?
The preparation goes ok, and I can set the parameter values, but the call to
PreparedStatement.execute() returns the following error:
[Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error
Anyone seen this? I *really* don't want to go back to using a non-parameterized
statement, escaping characters, formatting dates, etc! HELP!
Todd Grigsby
---------------------------------------------------------------------
To unsubscribe, e-mail: struts-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: struts-user-help@jakarta.apache.org
Re: COUNT field incorrect or syntax error
Posted by Mike Deegan <mp...@hotmail.com>.
Check this link out also - may shed some light ...
http://www.macromedia.com/support/ultradev/ts/documents/insert_into.htm
----- Original Message -----
From: "Mike Deegan" <mp...@hotmail.com>
To: "Struts Users Mailing List" <st...@jakarta.apache.org>
Sent: Friday, May 16, 2003 9:55 PM
Subject: Re: COUNT field incorrect or syntax error
> I've seen this before ...
>
> The user is trying to insert a record into a database field that has a
> question mark (?) in its field name. For example, one of the tables in the
> database has a field labeled "Retired?" The question mark (?) is a special
> character for some database engines, including Microsoft Access, and
should
> not be used for database table names or field names. Please refer to
> '80040e14' - Syntax error in INSERT INTO statement (TechNote 14622) for
more
> information on reserved words and special characters relating to
databases.
>
> Solution
> Go into the design view of the database and delete the question mark (?)
> from the field name(s).
>
> not sure if it is true in your case ?
>
> Mike
>
> ----- Original Message -----
> From: "Todd Grigsby" <st...@tgrigsby.com>
> To: "Struts Users Mailing List" <st...@jakarta.apache.org>
> Sent: Friday, May 16, 2003 9:43 PM
> Subject: COUNT field incorrect or syntax error
>
>
> > Ok, I've got something weird going on here and I'm hoping some of the
> folks here
> > have run into this. I've got a class that, depending on a flag, will
> either
> > build an insert SQL statement or an update SQL statement. The
statements
> are
> > parameterized. When I use the insert statement, all goes well, but when
> use the
> > update statement, something similar to:
> >
> > update MCI set modifiedby=?, modifieddate=?, firstname=? where recid=?
> >
> > The preparation goes ok, and I can set the parameter values, but the
call
> to
> > PreparedStatement.execute() returns the following error:
> >
> > [Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error
> >
> > Anyone seen this? I *really* don't want to go back to using a
> non-parameterized
> > statement, escaping characters, formatting dates, etc! HELP!
> >
> > Todd Grigsby
> >
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: struts-user-unsubscribe@jakarta.apache.org
> > For additional commands, e-mail: struts-user-help@jakarta.apache.org
> >
> >
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: struts-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: struts-user-help@jakarta.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: struts-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: struts-user-help@jakarta.apache.org
Re: COUNT field incorrect or syntax error
Posted by Todd Grigsby <st...@tgrigsby.com>.
Nevermind, I got it. I didn't set the value for one of the parameters. The
hints I found all over the 'net pointed to a question mark (or other reserved
character) in a field name, but as it turned out, the base reason -- the same
reason that a question mark in a name causes a problem -- is that the SQL Server
engine thinks you gave it X parameters and only X-1 values.
The way my code works, part of the statement is built in one place and the rest
is built in another place. Consequently the values for the parameters are
supplied in two different places as well. The problem I was running into was
that the count was off when the last set of parameters was getting their values,
so the last one didn't get a value. SQL Server gets grouchy when that happens.
Thanks anyway, folks, and thank you again Mike for trying to help.
Todd Grigsby
---------------------------------------------------------------------
To unsubscribe, e-mail: struts-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: struts-user-help@jakarta.apache.org
Re: COUNT field incorrect or syntax error
Posted by Lynn Guy <lg...@yahoo.com>.
cursor_sharing = force. Use at your own risk. On some
versions of the db it has nasty side-effects. As
suggested, fix it in the app and avoid the problems.
--- Todd Grigsby <st...@tgrigsby.com> wrote:
> Actually, I found the problem, but the reason for
> not doing what you mention is
> performance. In Oracle to a large degree and other
> SQL engines to a (usually)
> lesser degree, execution plans are cached. If you
> parameterize the statement
> and reuse it, the engine only caches one plan. If
> you supply values within the
> statement, Oracle will see this as a completely
> separate SQL statement, create a
> new execution plan, and use more memory to store it.
> Each call with different
> values will result in more resources used to store
> another execution plan.
>
> This isn't always the case; there is a flag you can
> set that will cause Oracle
> to "preprocess" the statement and, in essence,
> create a parameterized version of
> your SQL command and then put the values in, but
> that's a drag on performance
> that you can avoid by simply parameterizing your
> statements in your application.
> I don't recall the flag name, but I know it's
> mentioned in that fantastic
> book, "Expert One-on-One Oracle" by Thomas Kyte.
>
> Although, you probably already knew all that. Too
> much coffee this morning,
> sorry....
>
> ;)
>
> T
>
> Lorrin Nelson wrote:
> > I don't have an answer, but can you try replacing
> the ?'s one by one
> > with values spliced into the String so as to
> narrow down what part is
> > barfing?
>
>
>
---------------------------------------------------------------------
> To unsubscribe, e-mail:
> struts-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail:
> struts-user-help@jakarta.apache.org
>
__________________________________
Do you Yahoo!?
The New Yahoo! Search - Faster. Easier. Bingo.
http://search.yahoo.com
---------------------------------------------------------------------
To unsubscribe, e-mail: struts-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: struts-user-help@jakarta.apache.org
Re: COUNT field incorrect or syntax error
Posted by Todd Grigsby <st...@tgrigsby.com>.
Actually, I found the problem, but the reason for not doing what you mention is
performance. In Oracle to a large degree and other SQL engines to a (usually)
lesser degree, execution plans are cached. If you parameterize the statement
and reuse it, the engine only caches one plan. If you supply values within the
statement, Oracle will see this as a completely separate SQL statement, create a
new execution plan, and use more memory to store it. Each call with different
values will result in more resources used to store another execution plan.
This isn't always the case; there is a flag you can set that will cause Oracle
to "preprocess" the statement and, in essence, create a parameterized version of
your SQL command and then put the values in, but that's a drag on performance
that you can avoid by simply parameterizing your statements in your application.
I don't recall the flag name, but I know it's mentioned in that fantastic
book, "Expert One-on-One Oracle" by Thomas Kyte.
Although, you probably already knew all that. Too much coffee this morning,
sorry....
;)
T
Lorrin Nelson wrote:
> I don't have an answer, but can you try replacing the ?'s one by one
> with values spliced into the String so as to narrow down what part is
> barfing?
---------------------------------------------------------------------
To unsubscribe, e-mail: struts-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: struts-user-help@jakarta.apache.org
Re: COUNT field incorrect or syntax error
Posted by Lorrin Nelson <lh...@the-fam.net>.
I don't have an answer, but can you try replacing the ?'s one by one with
values spliced into the String so as to narrow down what part is barfing?
-Lorrin
At 09:33 PM 5/16/2003, you wrote:
>First off, thanks Mike for getting back to me.
>
>Nope, I meant to mention that I'd already thought of that, but my 3 year
>came up just as I was in the middle of writing that letter and asked me to
>get her an apple. Broke my chain of thought.
>
>Anyway, all of the fields exist in the database, none have special
>characters, and yet it's giving me a hard time. Everything on the 'net
>that I found on this mentioned the field name, but that's not it.
>
>Any other ideas?
>
>Todd
>
>Mike Deegan wrote:
>>I've seen this before ...
>>The user is trying to insert a record into a database field that has a
>>question mark (?) in its field name.
>
>
>---------------------------------------------------------------------
>To unsubscribe, e-mail: struts-user-unsubscribe@jakarta.apache.org
>For additional commands, e-mail: struts-user-help@jakarta.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: struts-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: struts-user-help@jakarta.apache.org
[OT] Re: COUNT field incorrect or syntax error
Posted by Erik Price <ep...@ptc.com>.
Todd Grigsby wrote:
> First off, thanks Mike for getting back to me.
>
> Nope, I meant to mention that I'd already thought of that, but my 3 year
> came up just as I was in the middle of writing that letter and asked me
> to get her an apple. Broke my chain of thought.
I recommend a used, sturdy Powerbook G3 for someone of her age. The
keys are easy to rip out of the newer models.
;)
Erik
---------------------------------------------------------------------
To unsubscribe, e-mail: struts-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: struts-user-help@jakarta.apache.org
Re: COUNT field incorrect or syntax error
Posted by Todd Grigsby <st...@tgrigsby.com>.
First off, thanks Mike for getting back to me.
Nope, I meant to mention that I'd already thought of that, but my 3 year came up
just as I was in the middle of writing that letter and asked me to get her an
apple. Broke my chain of thought.
Anyway, all of the fields exist in the database, none have special characters,
and yet it's giving me a hard time. Everything on the 'net that I found on this
mentioned the field name, but that's not it.
Any other ideas?
Todd
Mike Deegan wrote:
> I've seen this before ...
>
> The user is trying to insert a record into a database field that has a
> question mark (?) in its field name.
---------------------------------------------------------------------
To unsubscribe, e-mail: struts-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: struts-user-help@jakarta.apache.org
Re: COUNT field incorrect or syntax error
Posted by Mike Deegan <mp...@hotmail.com>.
I've seen this before ...
The user is trying to insert a record into a database field that has a
question mark (?) in its field name. For example, one of the tables in the
database has a field labeled "Retired?" The question mark (?) is a special
character for some database engines, including Microsoft Access, and should
not be used for database table names or field names. Please refer to
'80040e14' - Syntax error in INSERT INTO statement (TechNote 14622) for more
information on reserved words and special characters relating to databases.
Solution
Go into the design view of the database and delete the question mark (?)
from the field name(s).
not sure if it is true in your case ?
Mike
----- Original Message -----
From: "Todd Grigsby" <st...@tgrigsby.com>
To: "Struts Users Mailing List" <st...@jakarta.apache.org>
Sent: Friday, May 16, 2003 9:43 PM
Subject: COUNT field incorrect or syntax error
> Ok, I've got something weird going on here and I'm hoping some of the
folks here
> have run into this. I've got a class that, depending on a flag, will
either
> build an insert SQL statement or an update SQL statement. The statements
are
> parameterized. When I use the insert statement, all goes well, but when
use the
> update statement, something similar to:
>
> update MCI set modifiedby=?, modifieddate=?, firstname=? where recid=?
>
> The preparation goes ok, and I can set the parameter values, but the call
to
> PreparedStatement.execute() returns the following error:
>
> [Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error
>
> Anyone seen this? I *really* don't want to go back to using a
non-parameterized
> statement, escaping characters, formatting dates, etc! HELP!
>
> Todd Grigsby
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: struts-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: struts-user-help@jakarta.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: struts-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: struts-user-help@jakarta.apache.org