CODEWITHSUNDEEP
c#sql
Akshay
Akshay

Reputation: 181

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

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???

Upvotes: 7

Views: 31750

Answers (7)

AleksAnderson IT
AleksAnderson IT

Reputation: 37

using (SqlConnection sqlcon = new SqlConnection("Connection String HERE"))
        {
            using (SqlCommand sqlcmd= new SqlCommand())
            {
                sqlcmd.Connection = sqlcon;            
                sqlcmd.CommandType = CommandType.Text;
                sqlcmd.CommandText = "SELECT SUM(ItemRate) FROM logs WHERE RoomNo=@rno";
                slqcmd.Parameters.AddWithValue("@rno", rno);
                try
                {
                    sqlcon.Open();
                    command.ExecuteNonQuery();
                }
                catch (SqlException)
                {
                    MessageBox.Show("Your Error Here");
                }
                finally
                {
                    connection.Close();
                }
            }

This will be helpful I think and its more safe

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460038

  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();
    }
}

Upvotes: 11

Yahia
Yahia

Reputation: 70369

There are several problems with the code you show - esp. some seroius security problems, I strongly advise you to read up on SQL injection and prepared statements/parameters and using.

Just some quick correction/comments:

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.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=D:\\Projects\\c# assignments\\Hotel Manager\\Hotel\\database\\master.mdf;Integrated Security=True;User Instance=True";
            con.Open();
            int rno = Int32.Parse(txtRoom.Text);

            cmd.Connection = con; // This solves the problem you see
            // HERE you SHOULD use a SQL paramter instead of appending strings to build your SQL !!!
            cmd.CommandText = "SELECT SUM(ItemRate) FROM logs WHERE RoomNo=" + rno +"";
            int amt = (int)cmd.ExecuteScalar();   //arror is at this part


            // HERE you SHOULD use a SQL paramter instead of appending strings to build your SQL !!!
            // Another point: you build an INSERT but never execute it ?!?
            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();
        }
    }
}

Upvotes: 1

Zo Has
Zo Has

Reputation: 13018

You have not provided the connection string inside your button1_click. 

con.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=D:\\Projects\\c# assignments\\Hotel Manager\\Hotel\\database\\master.mdf;Integrated Security=True;User Instance=True";

Also there are many things wrong in your code. It works this way

{
  // Create Connection Object  
  // Provide connection object with Connection string
  // Create command object
  // Open connection
  // Execute command
  // Close connection
  // Dispose connection
}

Upvotes: 1

Rohit Vats
Rohit Vats

Reputation: 81233

You've opened up a SqlConnection, but you haven't told the SqlCommand object to use it. Try adding this line:

cmd.Connection = con;

before you execute your query.

Upvotes: 1

Bridge
Bridge

Reputation: 30651

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().

Upvotes: 1

Yehuda Shapira
Yehuda Shapira

Reputation: 8630

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

Upvotes: 1

Related Questions