ExecuteScalar - Specific Column

Question

my plans table has 15 fields, using ExecuteScalar seems to grab the result from the first column only, how do I specify the column name that I want? for example when I run the code below, it will return 1 which is the value in my ID column, but I want plan_rate column.

using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["CustomerDataConnectionString"].ConnectionString))
{
   conn.Open();

    SqlCommand cmd = new SqlCommand("SELECT * FROM [dbo].[plans] WHERE age_group = @age_group AND coverage_type = @coverage_type", conn);
    cmd.Parameters.AddWithValue("@age_group", dd_ageGroup.Text);
    cmd.Parameters.AddWithValue("@coverage_type", dd_coverageType.Text);

    Object result = cmd.ExecuteScalar();

    if (result != null)
        Label1.Text = "Plan rate: $" + result.ToString();

    else
        Label1.Text = "Plan is not available in this state.";


}

D Stanley · Accepted Answer · 2014-10-20T12:54:50.560

2

First line in the documentation:

Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.

If you want just plan_rate you can change your query to:

"SELECT plan_rate FROM [dbo].[plans]...

or use ExecuteReader and read the column you want from the DataReader.

using(DataReader reader = cmd.ExecuteReader())
{
    while(reader.Read())
    {
        ...
        string plan_rate = reader["plan_rate"].ToString();
        ...
    }
}

edited Oct 20 '14 at 12:54

answered Oct 20 '14 at 01:52

D Stanley

149,601
11
178
240

If you will place the `reader` into a `using` block, I will upvote. – John Saunders Oct 20 '14 at 03:51
@JohnSaunders Did so, and fixed a typo as well. – D Stanley Oct 20 '14 at 12:55

Dalorzo · Answer 2 · 2014-10-20T01:55:56.423

1

ExecuteScaler can only be used when your return one single value. It means your query needs to be one column and one row only.

So you need to change your Select * from with a Select myColumn from .

If you need second fastest execution and return more than one column you can use ExecuteReader.

SqlDataReader reader = command.ExecuteReader();
while (reader.Read()){
   Console.WriteLine(String.Format("{0}", reader[0]));//reader[0] column1, reader[1] column2,..
}

edited Oct 20 '14 at 01:55

answered Oct 20 '14 at 01:49

Dalorzo

19,834
7
55
102

What would you recommend me to use? What is the best way to re-write the above code to accomplish the columns names, I do not want to write 10 ExecuteScalar codes for each column. Thank you sir. – user3345212 Oct 20 '14 at 01:53
Well, you _can_ use it when returning multiple columns/rows, but it will only return the value from the first column and first row. – D Stanley Oct 20 '14 at 01:54
@user3345212 you can use `ExecuteReader` – Dalorzo Oct 20 '14 at 01:54
Object result = cmd.ExecuteReader(); is giving me an error – user3345212 Oct 20 '14 at 01:57
@user3345212 I just provided a sample it returns a `SqlDataReader` object – Dalorzo Oct 20 '14 at 02:00

ExecuteScalar - Specific Column

2 Answers2