Simple SQL2000 update query w/ parameters not working with SqlCommand

  • Thread starter Thread starter Nadeem
  • Start date Start date
N

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

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
 
Try
Cmd.Parameters.Add("@Field", SqlDbType.Bit);
Cmd.Parameters.Add("@RecID", SqlDbType.Int);
Click to expand...

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.

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