Storing SQL Select result into String Variable

Question

I am trying to store the result from my SQL query into a string variable. This is what I have:

  string strName = dt.Rows[i][name].ToString();
  string selectBrandID = "SELECT [Brand_ID] FROM [myTable] WHERE [real_name] = '" + strName + "'";

  using (SqlCommand sqlCmdSelectBrandID = new SqlCommand(selectBrandID, sqlConn))
        {
           sqlCmdSelectBrandID .Connection.Open();
           using (SqlDataReader reader = sqlCmdSelectBrandID.ExecuteReader())
                 {
                      if (reader.HasRows)
                        {
                           reader.Read();
                           string newBrandID = reader.GetString(reader.GetOrdinal("Brand_ID"));
                        }
                       sqlCmdSelectBrandID.Connection.Close();
                 }
        }

This currently throws the exception Unable to cast object of type 'System.Int32' to type 'System.String'. On string newBrandID =reader.GetString(reader.GetOrdinal("Brand_ID")); line.

Any advice on how to fix this?

Steve · Accepted Answer · 2015-02-11 00:25:13Z

2

If your Brand_ID is stored in an integer field, then you should keep it as an integer. The GetString fails because the underlying field is not a string, you could simply use the GetInt32 (see the SqlDataReader docs)

int newBrandID = reader.GetInt32(reader.GetOrdinal("Brand_ID"));

Then, if for whatever purpose you want it as a string, it is just a matter to apply the ToString() method to your integer

string brandID = newBrandID.ToString();

edited Feb 11, 2015 at 0:25

answered Feb 11, 2015 at 0:08

Steve

217k22 gold badges242 silver badges296 bronze badges

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

6 Comments

codeFinatic Over a year ago

Ok, so now it is stored and then I just tried to use the variable in an insert statement to place in a sql table and it says "newBrandID doesn't exist in the current context". How can I make that global?

Steve Over a year ago

Could you simply return it from this code and pass to the other method? Globals are always a necessary evil. If you could avoid them.... otherwise just declare the newBrandID at the class level

Steve Over a year ago

Or, if the insert is following this same block of code, move the declaration of newBrandID at the same level of the strName

codeFinatic Over a year ago

Can you show me an example of what you're talking about exactly in your answer?

codeFinatic Over a year ago

Weird thing is, is that they are in the same IF statement

|

Collectives™ on Stack Overflow

Storing SQL Select result into String Variable

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related