CODEWITHSUNDEEP
sqlasp.netexecutescalar
user3345212
user3345212

Reputation: 131

ExecuteScalar - Specific Column

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.";


}

Upvotes: 0

Views: 1721

Answers (2)

D Stanley
D Stanley

Reputation: 152566

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();
        ...
    }
}

Upvotes: 2

Dalorzo
Dalorzo

Reputation: 20014

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,..
}

Upvotes: 1

Related Questions