How to save the result of a SQL query in a variable in C#.net?

Question

namespace Hotel
{
    public partial class Billing : Form
    {
        SqlConnection con = new SqlConnection();
        SqlDataAdapter da;
        SqlCommand cmd = new SqlCommand();
        DataTable dt = new DataTable();
        public Billing()
        {
            InitializeComponent();
        }

        private void Billing_Load(object sender, EventArgs e)
        {
            con.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=D:\\Projects\\c# assignments\\Hotel Manager\\Hotel\\database\\master.mdf;Integrated Security=True;User Instance=True";
            //loadData();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            con.Open();
            int rno = Int32.Parse(txtRoom.Text);


            cmd.CommandText = "SELECT SUM(ItemRate) FROM logs WHERE RoomNo=" + rno +"";
            int amt = (int)cmd.ExecuteScalar();   //arror is at this part

       //ExecuteScalar: Connection property has not been initialized.

            cmd.CommandText = "INSERT INTO bill VALUES('" + txtBillNo.Text.ToString() + "','" + txtRoom.Text.ToString() + "','" + amt.ToString() + "')";
            con.Close();
            txtBillNo.Text = "";
            txtRoom.Text = "";
            BillView bv = new BillView();
            bv.ShowDialog();
        }
    }
}

please help me with this error i am not able to store the the SQL query result into a variable???

Answer 1

1. You are open for SQL-Injection. Don't concatenate strings to build your query. Instead use SQL-Parameters.
2. Use using-statement for you connection (and everything else implementing IDisposable). Dispose will also close the connection, with using even on error.
3. The reason for the exception is that you don't have initialized the connection of the SqlCommand since you have't specified the connection. You can use the property or the appropriate constructor.

Here's an example:

int amt;  
using (var con = new SqlConnection(ConnectionString)) {
    var sql = "SELECT SUM(ItemRate) FROM logs WHERE RoomNo = @RoomNo";
    using (var cmd = new SqlCommand(sql, con)) {
        cmd.Parameters.AddWithValue("@RoomNo", Int32.Parse(txtRoom.Text));
        con.Open();
        amt = (int)cmd.ExecuteScalar();
    }
}

Answer 2

It's not enough to just open the connection;
You need to associate con with cmd.

Answer 3

It's exactly as the error described, you haven't set the Connection property of your SQLCommand.

Try adding:

cmd.Connection = con;

before you call ExecuteScalar().