Output one field of one record from a SQL database in ASP.NET.

Question

I need to read one record from a database (determined by the url's querystring), and take one field from it ("Cost"). The SQL currently in my datasource for it is just:

SELECT [Cost] from [Cars] WHERE ([RegistrationNumber] = @RegistrationNumber)

...then @RegistrationNumber is defined as coming from the QueryString.

I'm very much still learning ASP.NET, so it is likely (I'm hoping) that there's a very simple way to do this. I could use a ListView or something, but I think that's a bit overkill for just one piece of data.

Also, I'm working in C# if that makes a difference.

Answer 1

Assuming you're using a SqlCommand object to execute your sql, you can use the ExecuteScalar() method, and it will return the first value from the first row that is returned from the command.

There is an example on the documentation page I linked, but for brevity, I'll include it here:

using (SqlConnection conn = new SqlConnection(connString))
    {
        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.Parameters.Add("@RegistrationNumber", SqlDbType.VarChar);
        cmd.Parameters["@RegistrationNumber"].Value = ** your variable here**;
        try
        {
            conn.Open();
            double cost = (double)cmd.ExecuteScalar();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
    }

That's the "raw" method of running your SQL. Since you're using a SqlDataSource, that's what it's doing behind the scenes, except it's not using ExecuteScalar() - it's just getting all the data. You can pick out individual fields using code similar to this:

protected void Page_Load(object sender, EventArgs e)
{

    DataView dv= (DataView)SqlDataSource1.Select(DataSourceSelectArguments.Empty);
    foreach (DataRowView dr in dv)
    {
        Label1.Text = dr["Cost"].ToString();
    }
}

This would assign the field value to Label1. If you don't want to have to write any code, it might be easier just to bind it to a simple Repeater control though.

Answer 2

I believe what you are after is SQLCommand's ExecuteScalar command.

So you would use it like this (Disclaimer: I dont really do much in C# with sql databases)

Int32 Cost = 0;
SQLCommand sqlcmd = New SQLCommand(SELECT [Cost] from [Cars] WHERE ([RegistrationNumber] = @RegistrationNumber), connection);
sqlcmd.Parameters.Add("@RegistrationNumber", SqlDbType.VarChar).Value = RegNumber;
        try
    {
        conn.Open();
        Cost = (Int32)sqlcmd.ExecuteScalar();
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }

That should hopefully work.