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

[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!

 

[Peter Duniho]

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?

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

 

[Jeff Johnson]

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?

You can't use named variables in ODBC. Change that to "VALUES(?, ?)" and try
again.

 

[J.B. Moreno]

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?

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.

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.

 

[alexey stepanyan]

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

 

[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]

I don't see how.

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.

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.

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).

 

[Peter Duniho]

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.

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]

Hi,

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

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

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!

 

[J.B. Moreno]

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.

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]

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.

 

[xmaurer]

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

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!