CODEWITHSUNDEEP
c#sql-server
Rastin Radvar
Rastin Radvar

Reputation: 143

Print My Search Results From SQL Server on label

I'm just trying to print the sum of my search on a label in form.

Story is I have 2 textboxes that will give me 2 date and searching in my database, and printing the answer of sum cost between that 2 date.

My code is :

private void button1_Click(object sender, EventArgs e)
{
    SqlConnection con = new SqlConnection("Data Source=localhost;Initial Catalog=SuperCalc;Integrated Security=True");

    SqlCommand com = new SqlCommand();

    if (con.State == ConnectionState.Closed)
    {
        con.Open();

        com = new SqlCommand("select sum (Cost) as JameKol From TBL_Cost Where CostDate between '" + textBox1.Text + "' and '" + textBox2.Text + "' ", con);
        label5.Text = com();

        con.Close();
        MessageBox.Show("Search is done", "Done");
    }
}

com can't use as a method, so, how can I do this?

Upvotes: 2

Views: 73

Answers (1)

Soner Gönül
Soner Gönül

Reputation: 98810

Just use ExecuteScalar which is exactly what this for. It gets first column of the first row which fits SUM function.

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

But more important, you should always use parameterized queries. This kind of string concatenations are open for SQL Injection attacks.

And use using statement to dispose your connection and command automatically instead of calling Close method manually.

By the way, looks like your CostDate column is character typed. Don't do it. This is a bad habit to kick. You should never keep your DateTime values as a character. Change it to datetime or better datetime2 type and pass your DateTime values directly to your parameterized query. That's why I used DateTime.Parse to parse your Text values. If it can't parse them, you can use ParseExact as well.

string conString = "Data Source=localhost;Initial Catalog=SuperCalc;Integrated Security=True";
using(var con = new SqlConnection(conString))
using(var com = con.CreateCommand())
{
    com.CommandText = @"select sum (Cost) as JameKol From TBL_Cost 
                        Where CostDate between @date1 and @date2";
    com.Parameters.Add("@date1", SqlDbType.DateTime2).Value = DateTime.Parse(textBox1.Text);
    com.Parameters.Add("@date2", SqlDbType.DateTime2).Value = DateTime.Parse(textBox2.Text);
    con.Open();
    label5.Text = com.ExecuteScalar().ToString();
}

Upvotes: 3

Related Questions