How to display sql query results into textbox on c#?

Question

I'm currently making a voting app on C# Windows Form.

So I made a SQL query to count how many people voted for a specific candidate that will be displayed on textBox4

 private void button1_Click(object sender, EventArgs e)
 {
 string idcan = textBox3.Text;
 string score = textBox4.Text;
 Connection con = new Connection();
 SqlConnection sqlcon = con.Sambung();
 sqlcon.Open();
 string cek = "select count (ID_Candidate) as Score from DataVote where ID_Candidate = @idcan";
 using (sqlcon)
 {
 SqlCommand com = new SqlCommand(cek, sqlcon);
 com.Parameters.Add("idcan", SqlDbType.VarChar, 5).Value = score;
 }
 try
 {
 sqlcon.Open();
 }
 catch (Exception ex)
 {
 MessageBox.Show(ex.Message);
 }
 }

How to display the results of the above SQL query to textBox4?

• jLaw: maybe this could help stackoverflow.com/questions/15294239/…

Answer 1

 private void button1_Click(object sender, EventArgs e)
 {
 string idcan = textBox3.Text;
 string score = textBox4.Text;
 Connection con = new Connection();
 SqlConnection sqlcon = con.Sambung();
 sqlcon.Open();
 string cek = "select count (ID_Candidate) as Score from DataVote where ID_Candidate = @idcan";
 using (sqlcon)
 {
 SqlCommand com = new SqlCommand(cek, sqlcon);
 com.Parameters.Add("idcan", SqlDbType.VarChar, 5).Value = score;
 }
 try
 {
 sqlcon.Open();
 textBox4.Text=Convert.ToString(com.ExecuteScalar()); 
 }
 catch (Exception ex)
 {
 MessageBox.Show(ex.Message);
 }
 }

Answer 2

You can actually do this code:

private void button1_Click(object sender, EventArgs e)
 {
 string idcan = textBox3.Text;
 string score = textBox4.Text;
 Datatable dt = new DataTable();
 Connection con = new Connection();
 SqlConnection sqlcon = con.Sambung();
 SqlCommand com ;
 sqlcon.Open();
 string cek = "select count (ID_Candidate) as Score from DataVote where ID_Candidate = @idcan"; 
using(sqlcon)
{
 using(com = new SqlCommand(cek, sqlcon))
 {
 sqlcon.Open();
 com.Parameter.Add("@idcan",score );
 SqlDataAdapter da = new SqlDataAdapter(com);
 da.File(dt);
 if(dt.Rows.Count > 0)
 {
 textBox4.Text = dt.Rows[0]["Score"].ToString();
 }
 }
}
 }

Answer 3

Since your SELECT statement returns one row with one column, you can use ExecuteScalar to get it.

textBox4.Text = com.ExecuteScalar().ToString();

And your code needs a little bit refactoring like;

using(SqlConnection sqlcon = con.Sambung())
using(SqlCommand com = sqlcon.CreateCommand())
{
 com.CommandText = "select count (ID_Candidate) as Score from DataVote where ID_Candidate = @idcan";
 com.Parameters.Add("@idcan", SqlDbType.VarChar, 5).Value = score;
 sqlcon.Open();
 textBox4.Text = com.ExecuteScalar().ToString();
}

By the way, I strongly suspect your ID_Candidate column should be some numeric type instead of VarChar based on it's name.

Answer 4

Expand your question: If your query return as a table (many columns, many rows), you can use this code below. Similar for case return single value.

//Create class to store result value
public class ClassName
{
 public string Col1 { get; set; }
 public int Col2 { get; set; }
}
// In query code
ClassName[] allRecords = null;
SqlCommand command = ...; // your code
using (var reader = command.ExecuteReader())
{
 var list = new List<ClassName>();
 while (reader.Read())
 list.Add(new ClassName {Col1 = reader.GetString(0), Col2 = reader.GetInt32(1)});
 allRecords = list.ToArray();
}