Simple SQL2000 update query w/ parameters not working with SqlCommand

[Nadeem]

Hi,

I have a very simple SQL query I'm trying to run against SQL Server
2000. Here is the code:

Cmd.CommandText = "update Table set Field=@Field where RecID=@RecID";
Cmd.Parameters.Add("Field", SqlDbType.Bit);
Cmd.Parameters.Add("RecID", SqlDbType.Int);
Cmd.Parameters["Field"].Value = 0;
Cmd.Parameters["RecID"].Value = 1;
Cmd.ExecuteNonQuery();

I get an exception:

System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near
'Field'.
Must declare the variable '@Field'.
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at ....
--- End of inner exception stack trace ---

I look at the SQL Trace, and se the folowing query being submitted:

exec sp_executesql N'update Table set Field=Field where RecID=@RecID',
N'Field bit,RecID int', Field = 0, RecID = 1

Everything looks fine, the variable 'Field' seems to be getting
defined, but the query doesn't execute!

Any suggestions would be appreciated. This is so simple, I must be
missing something!

Thanks!

 

[Rajesh Patel]

Cmd.CommandText = "update Table set Field=@Field where RecID=@RecID";
Cmd.Parameters.Add("@Field", SqlDbType.Bit);
Cmd.Parameters.Add("@RecID", SqlDbType.Int);
Cmd.Parameters["@Field"].Value = 0;
Cmd.Parameters["@RecID"].Value = 1;
Cmd.ExecuteNonQuery();

you need "@" sign in parameter definition.
also, I think, you need to open a connection before you call executenonquery

Rajesh Patel

 

[Michael Bray]

(e-mail address removed) (Nadeem) wrote in

I have a very simple SQL query I'm trying to run against SQL Server
2000. Here is the code:

Cmd.CommandText = "update Table set Field=@Field where RecID=@RecID";
Cmd.Parameters.Add("Field", SqlDbType.Bit);
Cmd.Parameters.Add("RecID", SqlDbType.Int);
Cmd.Parameters["Field"].Value = 0;
Cmd.Parameters["RecID"].Value = 1;
Cmd.ExecuteNonQuery();

I think you are confusing SQL by having the actual field name the same as
the parameter that you are passing... try...

Cmd.CommandText = "update Table set Field=@nField where RecID=@RecID";
Cmd.Parameters.Add("nField", SqlDbType.Bit);
Cmd.Parameters["nField"].Value = 0;
....etc...

-mbray

 

[Greg]

#1, you are missing the leading '@' for your sqlparameter names.

 

[Angel Saenz-Badillos[MS]]

Try

Cmd.Parameters.Add("@Field", SqlDbType.Bit);
Cmd.Parameters.Add("@RecID", SqlDbType.Int);

Hope this helps
--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

Hi,

I have a very simple SQL query I'm trying to run against SQL Server
2000. Here is the code:

Cmd.CommandText = "update Table set Field=@Field where RecID=@RecID";
Cmd.Parameters.Add("Field", SqlDbType.Bit);
Cmd.Parameters.Add("RecID", SqlDbType.Int);
Cmd.Parameters["Field"].Value = 0;
Cmd.Parameters["RecID"].Value = 1;
Cmd.ExecuteNonQuery();

I get an exception:

System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near
'Field'.
Must declare the variable '@Field'.
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at ....
--- End of inner exception stack trace ---

I look at the SQL Trace, and se the folowing query being submitted:

exec sp_executesql N'update Table set Field=Field where RecID=@RecID',
N'Field bit,RecID int', Field = 0, RecID = 1

Everything looks fine, the variable 'Field' seems to be getting
defined, but the query doesn't execute!

Any suggestions would be appreciated. This is so simple, I must be
missing something!

Thanks!