Display SQL query result in a label in asp.net

Question

I'm trying to display the SQL query result in a label but it's not showing. This is my code:

     string result = "SELECT ACTIVE FROM [dbo].[test] WHERE ID = '" + ID.Text + "' ";
     SqlCommand showresult = new SqlCommand(result, conn);
     conn.Open();
     showresult.ExecuteNonQuery();
     string actresult = ((string)showresult.ExecuteScalar());
     ResultLabel.Text = actresult;
     conn.Close();

Need help please. Thanks!

Have you tried debugging? Is your query actually returning data? If so I would'nt recommend to use (string) first receive the data as it comes int, double, etc and then when you assign it to the label do myVariable.ToString(); — Xtian Macedo, Jan 11 '12 at 01:46
Yes, query is returning data. i've tried to use int and byte and still not working. — Eximus, Jan 11 '12 at 01:48

score 9 · Answer 1 · answered Jan 11 '12 at 02:01

9

Try this one.

   string result = "SELECT ACTIVE FROM [dbo].[test] WHERE ID = '" + ID.Text + "' ";
   SqlCommand showresult = new SqlCommand(result, conn);
   conn.Open();
   ResultLabel.Text = showresult.ExecuteScalar().ToString();
   conn.Close();

answered Jan 11 '12 at 02:01

hallie

2,760
19
27

4

Please do not concatenate your query as string. Using parameters with the `SqlCommand` prevents sql injection. – JP Hellemons Nov 19 '13 at 10:58

score 1 · Answer 2 · answered Jun 01 '12 at 13:15

using (SqlConnection conn = new SqlConnection(connectionString))
{
    string result = "SELECT ACTIVE FROM [dbo].[test] WHERE ID = @id";
    SqlCommand showresult = new SqlCommand(result, conn);
    showresult.Parameters.AddWithValue("id", ID.Text);

    conn.Open();
    ResultLabel.Text = showresult.ExecuteScalar().ToString();
    conn.Close();
}

This will dispose the connection and has no string concatenation in the query.

score 1 · Answer 3 · answered Jan 11 '12 at 01:54

Is there a typo in there? You have two calls to the database:

showresult.ExecuteNonQuery();

This won't return a value and I'm not sure why you would have it there

string actresult = ((string)shresult.ExecuteScalar());

Unless you have a shresult variable, this query should error. What is the shresult variable?

KV Prajapati · Answer 4 · 2012-01-11T02:18:33.553

1

Use SqlParameter to filter the result and call ExecuteScalar() or ExecuteReader() method.

 string result = "SELECT ACTIVE FROM [dbo].[test] WHERE ID=@ID";
 SqlCommand showresult = new SqlCommand(result, conn);
 // If ID is int type
 showresult.Parameters.Add("@ID",SqlDbType.Int).Value=ID.Txt; 

 // If ID is Varchar then 
 //showresult.Parameters.Add("@ID",SqlDbType.VarChar,10).Value=ID.Txt; 

  conn.Open();
  string actresult = (string)showresult.ExecuteScalar(); 
  conn.Close();
  if(!string.IsNullOrEmpty(actresult))
       ResultLabel.Text = actresult;
  else
       ResultLabel.Text="Not found";

edited Jan 11 '12 at 02:18

answered Jan 11 '12 at 02:01

KV Prajapati

93,659
19
148
186

1

this will result in an exception...shresult is not declared – CCBlackburn Jan 11 '12 at 02:17
1

@CCBlackburn - Thanks! I've just copied text from OP. – KV Prajapati Jan 11 '12 at 02:19

score 0 · Answer 5 · answered Nov 21 '13 at 07:21

0

 conn.Open(); 
 string result = "SELECT ACTIVE FROM test WHERE ID = '" + ID.Text + "' ";
 SqlCommand showresult = new SqlCommand(result, conn);

 showresult.ExecuteNonQuery();
 int actresult = ((int)showresult.ExecuteScalar());
 ResultLabel.Text = actresult.Tostring();
 conn.Close();

answered Nov 21 '13 at 07:21

Govind Tiwari

1

Please see the OP's Comment he had tried int but it didnt solve it – Carbine Nov 21 '13 at 07:40

Display SQL query result in a label in asp.net

5 Answers5

Linked