error message - "Must declare the scalar variable "@state"

  • Thread starter Thread starter Ed Hochberg
  • Start date Start date
E

Ed Hochberg

Hi,

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!
 
Ed said:
Hi,

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?
Click to expand...

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
 
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?
Click to expand...

You can't use named variables in ODBC. Change that to "VALUES(?, ?)" and try
again.
 
Peter Duniho said:
Ed said:
Hi,

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?
Click to expand...

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. :)
Click to expand...

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.
 
Hi!

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 = ...;
 
J.B. Moreno said:
This appears to be a .net/C# question, not SQL.
Click to expand...

I don't see how.
There is definitely a problem with the above C# code, in that he's not
adding the parameter @sku.
Click to expand...

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.
Click to expand...

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
 
Peter Duniho said:
I don't see how.
Click to expand...

Because the problem isn't with the SQL, it's with the C# code.
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.
Click to expand...

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.
True, it could be some query language other than SQL. Nevertheless, the
question is specific to the query language, not C# or .NET.
Click to expand...

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.
Click to expand...

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 said:
Because the problem isn't with the SQL, it's with the C# code.
Click to expand...

The same problem exists with the same sequence of calls to the class in
any other language. It is _definitely_ not a C# problem.
Assuming that it *is* a SQLCommand that he's using, then it's a
requirement of the SQLCommand class.
Click to expand...

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. [...]
Click to expand...

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
 
Hi,

Actually it is bound I just didn't copy all of the code here as it wasn't
necassary.



alexey stepanyan said:
Hi!

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 = ...;

Ed Hochberg said:
Hi,

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!
Click to expand...
Click to expand...
 
Peter Duniho said:
The same problem exists with the same sequence of calls to the class in
any other language. It is _definitely_ not a C# problem.
Click to expand...

Only in languages that have that class -- which makes it a framework
problem.
Why? How can you say for sure that the requirement isn't something the
SqlCommand class inherits from SQL itself?
Click to expand...

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).
Click to expand...

The syntax for declaring a parameter will be defined in the class
interface.

-snip
That makes it a .Net problem. [...]
Click to expand...

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.
Click to expand...

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.
Click to expand...

Well, it looks like the point is rather moot as the OP doesn't seem to
be following the thread.
 
Ed Hochberg said:
Hi,

Actually it is bound I just didn't copy all of the code here as it wasn't
necassary.
Click to expand...

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.
 
I happened to be searching for a solution to the same problem, and spent goog time reading this useless discussion, eventually I found the answer somewhere else and I want to share it, hope this helps somebody in the future.

just modify the code below into this:
cmd.CommandText = "INSERT into Cust_Sku(state,cust_sku) VALUES
(? , ?)";

cmd.Parameters.Add("@state", OdbcType.NChar).Value = words[0];
cmd.Parameters.Add("@cust_sku", OdbcType.NChar).Value = words[1];
cmd.ExecuteNonQuery();

so the trick is the question marks '?' instead of the parameter name. Why? I really don't know but it works on Odbc connections on C#.

Regards

Xavier

Ed Hochberg said:
Hi,

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!
Click to expand...
 
Back
Top