Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Return value of a select statement

I want to retrieve the resulting value of a select statement into a string variable. Like this:

OleDbCommand cmd1 = new OleDbCommand();
cmd1.Connection = GetConnection();
cmd1.CommandText = "SELECT treatment FROM appointment WHERE patientid = " + text;
cmd1.ExecuteNonQuery();
        

I want to place the selected treatment value into a string variable. How can I do this?

like image 377
mac007 Avatar asked Dec 26 '12 13:12

mac007


People also ask

What is the return value of SELECT?

RETURN VALUE top On success, select() and pselect() return the number of file descriptors contained in the three returned descriptor sets (that is, the total number of bits that are set in readfds, writefds, exceptfds). The return value may be zero if the timeout expired before any file descriptors became ready.

What is the result of a SELECT statement?

The SELECT statement is used to select data from a database. The data returned is stored in a result table, called the result-set.

How do you assign a value to a SELECT statement in SQL?

When a variable is first declared, its value is set to NULL. To assign a value to a variable, use the SET statement. This is the preferred method of assigning a value to a variable. A variable can also have a value assigned by being referenced in the select list of a SELECT statement.

Does SELECT statement return a table?

An SQL SELECT statement retrieves records from a database table according to clauses (for example, FROM and WHERE ) that specify criteria. The syntax is: SELECT column1, column2 FROM table1, table2 WHERE column2='value';


3 Answers

Use ExecuteReader() and not ExecuteNonQuery(). ExecuteNonQuery() returns only the number of rows affected.

try
{
    SqlDataReader dr = cmd1.ExecuteReader();
}
catch (SqlException oError)
{

}
while(dr.Read())
{
    string treatment = dr[0].ToString();
}

Or better, use a using statement for it.

using(SqlDataReader dr = cmd1.ExecuteReader())
{
    while(dr.Read())
    {
        string treatment = dr[0].ToString();
    }
}

But if your SqlCommand returns only 1 column, you can use the ExecuteScalar() method. It returns first column of the first row as follows:-

cmd.CommandText = "SELECT treatment FROM appointment WHERE patientid = " + text;
string str = Convert.ToString(cmd.ExecuteScalar());

Also you can open your code to SQL Injection. Always use parameterized queries. Jeff has a cool blog article called Give me parameterized SQL, or give me death. Please read it carefully. Also read DotNetPerl SqlParameter article. SQL Injection very important when you are working queries.

like image 141
Soner Gönül Avatar answered Oct 23 '22 21:10

Soner Gönül


Execute Scalar: Getting Single Value from the Database method to retrieve a single value (for example, an aggregate value) from a database.

cmd1.Connection = GetConnection();
cmd1.CommandText = "SELECT treatment FROM appointment WHERE patientid = " + text;
if(cmd.ExecuteScalar()==null)
{
    var treatment = cmd.ExecuteScalar();
}

Other Way: ExecuteReader()

try
{
    cmd1.CommandText ="SELECT treatment FROM appointment WHERE patientid=@patientID";
    cmd1.Parameters.AddWithValue("@patientID", this.DropDownList1.SelectedValue);

    conn.Open();
    SqlDataReader dr = cmd1.ExecuteReader();
    while (dr.Read())
    {
        int PatientID = int.Parse(dr["treatment"]);
    }
    reader.Close();
    ((IDisposable)reader).Dispose();//always good idea to do proper cleanup
}
catch (Exception exc)
{
    Response.Write(exc.ToString());
}
like image 24
Vishal Suthar Avatar answered Oct 23 '22 23:10

Vishal Suthar


the answer:

String res = cmd1.ExecuteScalar();

the remark: use parametrized query to prevent sql injection

like image 20
tschmit007 Avatar answered Oct 23 '22 23:10

tschmit007