error message - "Must declare the scalar variable "@state"
Ed Hochberg
Sorry to trouble you. I know this is spoken about on the web but I have
yet to find an answer that works. I have the following code whichs adds a
value to a sql insert statement:
cmd.CommandText = "INSERT into Cust_Sku(state,cust_sku) VALUES
(@state , @sku)";
cmd.Parameters.Add("@state", OdbcType.NChar).Value = words[0];
cmd.ExecuteNonQuery();
When it hits the first line I receive the error message in the Subject of
this post. Where am I not declaring it? What am I doing wrong?
Thanks in advance!
Peter Duniho
I'd say the main thing you're doing wrong is posting a SQL question to a
C# newsgroup. You might get an answer here, but you'll have a much
higher chance of success posting to the correct newsgroup.
Based on the error message, I'd guess that before you're allowed to use
a variable named "state" in your command text, you need to declare it
somehow. Not knowing much about the rules of SQL, the first thing I'd
try is setting the command text after the parameters. But really, I'm
here because of the C#...the chances of my guess being correct for your
SQL question are pretty low. :)
Pete
Mark Rae [MVP]
news:F9D2B3E3-7D61-4CAC...@microsoft.com...
It's a little difficult to say, as you don't mention what RDBMS you're
targetting.
I'm guessing it's a seriously outdated (or even obsolete) one since you're
using ODBC to interface with it. It may be that it requires different SQL
syntax... As has been pointed out, you're in the wrong newsgroup. The best
place to post would be in a forum dedicated to the RDBMS you're using.
--
Mark Rae
ASP.NET MVP
http://www.markrae.net
Jeff Johnson
news:F9D2B3E3-7D61-4CAC...@microsoft.com...
> Sorry to trouble you. I know this is spoken about on the web but I have
You can't use named variables in ODBC. Change that to "VALUES(?, ?)" and try
again.
J.B. Moreno
This appears to be a .net/C# question, not SQL.
There is definitely a problem with the above C# code, in that he's not
adding the parameter @sku.
As an example of his problem, it is lacking in that we don't see the
declaration of cmd and so don't know what type it is.
If it's not the missing parameter that is causing the problem, it's
probably that the type doesn't allow named parameters.
--
J.B. Moreno
stepanyan@discussions.microsoft.com alexey stepanyan
The second variable @sku should also be bound in order to excute query,
other wise in insert statement is not complete:
cmd.Parameters.Add("@sku", ...).Value = ...;
Peter Duniho
> This appears to be a .net/C# question, not SQL.
I don't see how.
> There is definitely a problem with the above C# code, in that he's not
> adding the parameter @sku.
If failing to call the SqlCommand.Parameters.Add() method with the value
"@sku" is an error, it's only because of the requirements of SQL. It's
not a .NET requirement, and it's _definitely_ not a C# requirement.
> As an example of his problem, it is lacking in that we don't see the
> declaration of cmd and so don't know what type it is.
True, it could be some query language other than SQL. Nevertheless, the
question is specific to the query language, not C# or .NET.
Maybe some answer in this newsgroup will solve his problem, but he'll
have much better luck posting to a newsgroup where the problem is on-topic.
Pete
J.B. Moreno
> J.B. Moreno wrote:
> > This appears to be a .net/C# question, not SQL.
>
> I don't see how.
Because the problem isn't with the SQL, it's with the C# code.
> > There is definitely a problem with the above C# code, in that he's not
> > adding the parameter @sku.
>
> If failing to call the SqlCommand.Parameters.Add() method with the value
> "@sku" is an error, it's only because of the requirements of SQL. It's
> not a .NET requirement, and it's _definitely_ not a C# requirement.
Assuming that it *is* a SQLCommand that he's using, then it's a
requirement of the SQLCommand class. That makes it a .Net problem. It
is an interface/protocol error. No different than reversing the order
of the parameters to the Parameters.Add method.
> > As an example of his problem, it is lacking in that we don't see the
> > declaration of cmd and so don't know what type it is.
>
> True, it could be some query language other than SQL. Nevertheless, the
> question is specific to the query language, not C# or .NET.
No, it's not SQL specific -- it's provider specific. If it's a
SQLCommand that's one thing, if it's an ODBCCommand that's another, and
OLECOmmand is again something else. These are .Net classes, not SQL
objects.
So, it's not about the SQL -- if it was about the SQL, he'd be saying
that the command was returning the wrong resultsset. Instead he's
saying that the provider won't let him set the commandtext. Which
makes me suspect that he's NOT using a SQLCommand...
> Maybe some answer in this newsgroup will solve his problem, but he'll
> have much better luck posting to a newsgroup where the problem is on-topic.
What .Net class to use and how to use it, is much more of a C# problem
than a SQL problem (now if you were saying that the framework group was
where he should be asking, I couldn't really argue that as that is the
more appropriate group).
--
J.B. Moreno
Peter Duniho
> Peter Duniho <no.pet...@no.nwlink.spam.com> wrote:
>
>> J.B. Moreno wrote:
>>> This appears to be a .net/C# question, not SQL.
>> I don't see how.
>
> Because the problem isn't with the SQL, it's with the C# code.
The same problem exists with the same sequence of calls to the class in
any other language. It is _definitely_ not a C# problem.
>>> There is definitely a problem with the above C# code, in that he's not
>>> adding the parameter @sku.
>>
>> If failing to call the SqlCommand.Parameters.Add() method with the value
>> "@sku" is an error, it's only because of the requirements of SQL. It's
>> not a .NET requirement, and it's _definitely_ not a C# requirement.
>
> Assuming that it *is* a SQLCommand that he's using, then it's a
> requirement of the SQLCommand class.
Why? How can you say for sure that the requirement isn't something the
SqlCommand class inherits from SQL itself?
Note: make sure you express that justification using only information
that would be known to someone without any knowledge about SQL
(including such knowledge as might be obtained through inspection of the
SqlCommand class implementation).
(Replace "SQL" as appropriate to whatever database the OP is actually
using. His post claims he's using SQL, but it really doesn't affect my
point if it's something else)
> That makes it a .Net problem. [...]
No, it doesn't. At best, it's a "SQL [or other database] via .NET"
problem. There's a newsgroup for that, and this isn't it.
Frankly, it's no skin of my nose. There's no real effect on me. But
you're doing the OP a great disservice by implying that his question is
on-topic here. He could actually start to believe this is in fact the
best place for him to find an answer to his question.
Pete
Ed Hochberg
Actually it is bound I just didn't copy all of the code here as it wasn't
necassary.
J.B. Moreno
> J.B. Moreno wrote:
> > Peter Duniho <no.pet...@no.nwlink.spam.com> wrote:
> >
> >> J.B. Moreno wrote:
> >>> This appears to be a .net/C# question, not SQL.
> >> I don't see how.
> >
> > Because the problem isn't with the SQL, it's with the C# code.
>
> The same problem exists with the same sequence of calls to the class in
> any other language. It is _definitely_ not a C# problem.
Only in languages that have that class -- which makes it a framework
problem.
> >>> There is definitely a problem with the above C# code, in that he's not
> >>> adding the parameter @sku.
> >>
> >> If failing to call the SqlCommand.Parameters.Add() method with the value
> >> "@sku" is an error, it's only because of the requirements of SQL. It's
> >> not a .NET requirement, and it's _definitely_ not a C# requirement.
> >
> > Assuming that it *is* a SQLCommand that he's using, then it's a
> > requirement of the SQLCommand class.
>
> Why? How can you say for sure that the requirement isn't something the
> SqlCommand class inherits from SQL itself?
Because it's a generic parameter/argument matching requirement -- if
you declare a parameter in one place you usually have to match that
with an argument somewhere else.
> Note: make sure you express that justification using only information
> that would be known to someone without any knowledge about SQL
> (including such knowledge as might be obtained through inspection of the
> SqlCommand class implementation).
The syntax for declaring a parameter will be defined in the class
interface.
-snip
> > That makes it a .Net problem. [...]
>
> No, it doesn't. At best, it's a "SQL [or other database] via .NET"
> problem. There's a newsgroup for that, and this isn't it.
No. It's only SQL related in so far as the class is supposed to output
SQL. Here's proof: the commandtext for a properly parameterized call
isn't valid SQL. It's the job of the class to take non-SQL code and
turn it into SQL.
> Frankly, it's no skin of my nose. There's no real effect on me. But
> you're doing the OP a great disservice by implying that his question is
> on-topic here. He could actually start to believe this is in fact the
> best place for him to find an answer to his question.
Well, it looks like the point is rather moot as the OP doesn't seem to
be following the thread.
--
J.B. Moreno
J.B. Moreno
> Hi,
>
> Actually it is bound I just didn't copy all of the code here as it wasn't
> necassary.
It *is* necessary, as is the declaration of cmd. It also wouldn't hurt
if you could give a complete example (if the function is too large,
then break it down to a smaller function).
Also a comment on the line where the error occurs, and the exception
message.
You don't want to include irrelevant information, but not including
sufficient information is just as much of a barrier to getting an
answer as too much.
--
J.B. Moreno