4 Replies - 587 Views - Last Post: 12 September 2011 - 08:50 AM Rate Topic: -----

#1 4th idiot  Icon User is offline

  • D.I.C Head

Reputation: -6
  • View blog
  • Posts: 73
  • Joined: 23-January 10

More than 1 SQLCommand statements 1 below other in c#

Posted 11 September 2011 - 11:42 AM

SqlConnection objcon = new SqlConnection(@"Data Source=VAIBHAV-0EC092B\SQLEXPRESS;Initial Catalog=Navodayagiri;Integrated Security=True");
objcon.Open();

SqlCommand objcommand1 = new SqlCommand("select Name from HolderaNames where Flat_No= " + txtflatno.Text, objcon);
txtname.Text = Convert.ToString(objcommand1.ExecuteScalar());

SqlCommand objcommand = new SqlCommand("select Remaining_Dues from DuesMaster where Flat_No= " + txtflatno.Text, objcon);
 txtoutstandingdues.Text=Convert.ToString(objcommand.ExecuteScalar());
objcommand.Dispose();
objcommand1.Dispose();

objcon.Dispose();


Is this right?Can I write 2 sqlcommand statements 1 below the other?

This post has been edited by CodingSup3rnatur@l-360: 11 September 2011 - 11:43 AM
Reason for edit:: Please use code tags when posting code


Is This A Good Question/Topic? 0
  • +

Replies To: More than 1 SQLCommand statements 1 below other in c#

#2 Momerath  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1010
  • View blog
  • Posts: 2,444
  • Joined: 04-October 09

Re: More than 1 SQLCommand statements 1 below other in c#

Posted 11 September 2011 - 12:29 PM

You can write as many SqlCommands as you want, but only one can be 'executing' per connection.
Was This Post Helpful? 0
  • +
  • -

#3 4th idiot  Icon User is offline

  • D.I.C Head

Reputation: -6
  • View blog
  • Posts: 73
  • Joined: 23-January 10

Re: More than 1 SQLCommand statements 1 below other in c#

Posted 11 September 2011 - 09:29 PM

Now if I write

SqlConnection objcon = new SqlConnection(@"Data Source=VAIBHAV-0EC092B\SQLEXPRESS;Initial Catalog=Navodayagiri;Integrated Security=True");
                objcon.Open();
                SqlCommand objcommand = new SqlCommand("select Remaining_Dues from DuesMaster where Flat_No= " + txtflatno.Text, objcon);
                txtoutstandingdues.Text=Convert.ToString(objcommand.ExecuteScalar());
                objcommand.Dispose();
                objcon.Dispose();

                SqlConnection objcon1 = new SqlConnection(@"Data Source=VAIBHAV-0EC092B\SQLEXPRESS;Initial Catalog=Navodayagiri;Integrated Security=True");
                objcon1.Open();
                SqlCommand objcommand1 = new SqlCommand("SELECT Name from HolderaNames where Flat_No= " + txtflatno.Text, objcon1);
                txtname.Text = Convert.ToString(objcommand1.ExecuteScalar());
                objcommand1.Dispose();
                objcon1.Dispose();


mod edit: use [CODE] tags when posting code.
:code:
--Curtis Rutland


Here 2 different connections..kkk
But I am not able to see result in textbox named txtname
Was This Post Helpful? 0
  • +
  • -

#4 Momerath  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1010
  • View blog
  • Posts: 2,444
  • Joined: 04-October 09

Re: More than 1 SQLCommand statements 1 below other in c#

Posted 11 September 2011 - 11:38 PM

Do you get an error? Did you check that the SQL statement returns a value?
Was This Post Helpful? 0
  • +
  • -

#5 T3hC13h  Icon User is offline

  • D.I.C Regular

Reputation: 65
  • View blog
  • Posts: 337
  • Joined: 05-February 08

Re: More than 1 SQLCommand statements 1 below other in c#

Posted 12 September 2011 - 08:50 AM

I'm going to head you off before you get too far down this path of madness, don't prefix your variable names with their type, the IDE will tell you what they are. Just give your variables meaningful names within the context of the problem your trying to solve.

What you're trying to do can be done many different ways but here are a few I came up with.

In this method I've combined the two statements into one DB call, added a parameter to prevent SQL injection and wrapped the sqlconnection and datareader in using statements so I don't have to manually close them.
using (var conn = new SqlConnection(@"Data Source=VAIBHAV-0EC092B\SQLEXPRESS;Initial Catalog=Navodayagiri;Integrated Security=True")) {
	conn.Open();
	var cmd = new SqlCommand("select Remaining_Dues from DuesMaster where Flat_No=@0;SELECT Name from HolderaNames where Flat_No=@0;", conn);
	cmd.Parameters.AddWithValue("@0", txtflatno.Text.Trim());
	using (var dr = cmd.ExecuteReader()) {
		if (dr.HasRows) {
			dr.Read();
			txtoutstandingdues.Text = dr.GetString(0);
			dr.NextResult();
			dr.Read();
			txtname.Text = dr.GetString(0);
		}
	}
}


In this method I'm making two calls to the DB but reuse the same Command object and connection.
            var conn = new SqlConnection(@"Data Source=VAIBHAV-0EC092B\SQLEXPRESS;Initial Catalog=Navodayagiri;Integrated Security=True");
            conn.Open();
			cmd.Parameters.AddWithValue("@0", txtflatno.Text.Trim());
            var cmd = new SqlCommand("select Remaining_Dues from DuesMaster where Flat_No=@0;",conn);
            txtoutstandingdues.Text = cmd.ExecuteScalar().ToString();
            cmd.CommandText = "SELECT Name from HolderaNames where Flat_No=@0;";
            txtname.Text = cmd.ExecuteScalar().ToString();
            conn.Close();


And my personal favorite is to use the amazing Simple.Data library.

var db = Database.OpenConnection(@"Data Source=VAIBHAV-0EC092B\SQLEXPRESS;Initial Catalog=Navodayagiri;Integrated Security=True");
var flatNo = txtflatno.Text.Trim();
txtoutstandingdues.Text = db.DuesMaster.FindByFlat_No(flatNo).Remaining_Dues;
txtname.Text = db.HolderaNames.FindByFlat_No(flatNo).Name;

Was This Post Helpful? 1
  • +
  • -

Page 1 of 1