在查询中使用sqldatareader和where子句

I need to read a specific value from the table, to use it in my field.
But when I insert the where clause in query, it gets me null value, nothing more.

我需要从表中读取一个特定的值，以便在我的字段中使用它。但是当我在查询中插入where子句时，它只获取null值，仅此而已。

My code is:

我的代码是：

string query_select_id = "SELECT * from table1 WHERE column1=@value";

SqlConnection con = new SqlConnection(ConString);
try
{
    con.Open();
    SqlCommand createCommand = new SqlCommand(query_select_id, con);
    createCommand .Parameters.Add("@value", SqlDbType.Int).Value  =Convert.ToInt32(textbox.Text);

    SqlDataReader dr = createCommand.ExecuteReader();
    while (dr.Read())
    {
        intvalue= dr.GetInt32(12); 
        // 12 is the index of a column in table of the value I wanna get
    }
    con.Close();

    textboxvalue.Text = intvalue.ToString();

}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
}

3 个解决方案

#1

If you need only one value, you should use ExecuteScalar method. I use TOP 1 and column name to ensure that only one value is returned from select statement.

如果只需要一个值，则应使用ExecuteScalar方法。我使用TOP 1和列名来确保select语句只返回一个值。

string query_select_id = "SELECT TOP 1 COLUMN12 from table1 WHERE column1=@value";
SqlConnection con = new SqlConnection(ConString);
int intvalue;
try
{
    con.Open();
    SqlCommand createCommand = new SqlCommand(query_select_id, con);
    createCommand.Parameters.Add("@value", SqlDbType.Int).Value  =Convert.ToInt32(textbox.Text);
    object result = cmd.ExecuteScalar();
    if(result == null)
    {
        textboxvalue.Text = "Result is NULL";
    }
    else
    {

        intvalue = (int) result; 
        textboxvalue.Text = intvalue.ToString();
    }
    con.Close();


}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
}

#2

It is very bad idea to use SELECT * instead of SELECT column1 and dr.GetInt32(12) (constant index of value in the result). Try SELECT column1 FROM table1 WHERE column1=@value and use ExecuteScalar() to get the only result instead of SqlDataReader and while loop.

使用SELECT *而不是SELECT column1和dr.GetInt32（12）（结果中的值的常量索引）是非常糟糕的主意。尝试SELECT column1 FROM table1 WHERE column1 = @ value并使用ExecuteScalar（）获取唯一的结果，而不是SqlDataReader和while循环。

#3

Try this:

尝试这个：

Change createCommand.Parameters.Add into createCommand.Parameters.AddWithValue

将createCommand.Parameters.Add更改为createCommand.Parameters.AddWithValue

    string query_select_id = "SELECT * from table1 WHERE column1=@value";

    SqlConnection con = new SqlConnection(ConString);

    try
    {
        con.Open();
        SqlCommand createCommand = new SqlCommand(query_select_id, con);
        //createCommand .Parameters.AddWithValue("@value", SqlDbType.Int).Value=Convert.ToInt32(textbox.Text);
        createCommand .Parameter.AddWitheValue("@value",textbox.Text);
        SqlDataReader dr = createCommand.ExecuteReader();
        while (dr.Read())
        {
            intvalue= dr.GetInt32(12); 
        // 12 is the index of a column in table of the value I wanna get

        }
        con.Close();

        textboxvalue.Text = intvalue.ToString();

    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }

#1