How to Pass Variables to SqlCommand

Question

I have searched thoroughly, but could not find a way to SET my column data to the variable that I have initialized using a SQL UPDATE statement.

Here is the piece of code that is causing problem:

int maxId;
SqlConnection dataConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["RegConnectionString"].ConnectionString) ;
SqlCommand dataCommand = new SqlCommand("select max(UserID) from Registration", dataConnection) ;
dataConnection.Open();
maxId = Convert.ToInt32(dataCommand.ExecuteScalar());
string Id = maxId.ToString();

// this next line is not working
SqlCommand _setvin = new SqlCommand("UPDATE Registration SET VIN=maxId , Voted=0  WHERE UserID=maxId", dataConnection);
_setvin.ExecuteNonQuery();

dataConnection.Close();

Please guide me on how I can correct the commented line above.

Arion · Accepted Answer · 2012-04-06 12:38:24Z

2

Maybe something like this:

SqlCommand _setvin = new SqlCommand("UPDATE Registration SET VIN=@maxId, Voted=0  WHERE UserID=@maxId", dataConnection);
_setvin.Parameters.Add("@maxId", SqlDbType.Int).Value = maxId;
_setvin.ExecuteNonQuery();

edited Apr 6, 2012 at 12:38

answered Apr 6, 2012 at 12:24

Arion

31.3k10 gold badges74 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Arion Over a year ago

Sorry about that.. Updated the answer

Yuck · Accepted Answer · 2012-04-06 12:23:33Z

1

I think you're trying to do this:

var _setvin = new SqlCommand("UPDATE Registration SET VIN = @maxId, Voted = 0 WHERE UserID = @maxId", dataConnection);
_setvin.Parameters.AddWithValue("@maxId", maxId);
_setvin.ExecuteNonQuery();

You need to first change your SQL statement to include a parameter for the maxId; I'm calling that @maxId in your SQL. Then you need to supply a value for that parameter to the command. This is done with the Parameters.AddWithValue() method.

answered Apr 6, 2012 at 12:23

Yuck

51.1k13 gold badges108 silver badges136 bronze badges

4 Comments

Sana Over a year ago

with ur solution its giving error "Invalid column name" .. PLEASE NOTE that maxId is the variable name not column name ... **and i want to set the column whose name is VIN with the value contained in maxId **

Yuck Over a year ago

@Sana I am using @maxId as a variable, not a column. The columns involved here are VIN, Voted, and UserID.

Sana Over a year ago

it does not work here ... "invalid column name 'maxId' exception being catched with the solution u jx gave

Yuck Over a year ago

@Sana Then you're not copying my code correctly, as I don't make any reference to maxId as a column.

Collectives™ on Stack Overflow

How to Pass Variables to SqlCommand

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related