DaniWeb Forum Index > Software Development > C#

C# RSS

changing multiple lables with one sql query

Please support our C# advertiser: Intel Parallel Studio Home

•

Join Date: Mar 2008

Posts: 22

Reputation: Yellowdog428 is an unknown quantity at this point

Solved Threads: 0

Yellowdog428 Yellowdog428 is offline

Offline

Newbie Poster

changing multiple lables with one sql query

Jun 25th, 2009

I am trying to figure out how to set multiple text boxes/labels with one sql query.

I dont need help connecting to the database or anything like that I just need to know how I can write one query to and set multiple text boxes with that one query.

here is what I have been doing, and there has got to be an easier way.

 Help with Code Tags 
 c# Syntax (Toggle Plain Text) 
string selectAddress = "select cust_address from customers where customer_id = " + customerID;
string selectCity = "select customer_city from customer where customer_id = " + customerID;
 
SqlCommand cmdAddress = new SqlCommand(selectAddress, con);
SqlCommand cmdCity = new SqlCommand(selectCity, con);
 
this.lblAddress1.Text = cmdAddress.ExecuteScalar().ToString();
this.lblCity.Text = cmdCity.ExecuteScalar().ToString();
string selectAddress = "select cust_address from customers where customer_id = " + customerID;
string selectCity = "select customer_city from customer where customer_id = " + customerID;

SqlCommand cmdAddress = new SqlCommand(selectAddress, con);
SqlCommand cmdCity = new SqlCommand(selectCity, con);

this.lblAddress1.Text = cmdAddress.ExecuteScalar().ToString();
this.lblCity.Text = cmdCity.ExecuteScalar().ToString();

I would like to be able to write one query (actually just put it in a procedure) and set the text boxes/labels to the results of the query.

Thanks in advance for any help!
Cheers

•

Join Date: Feb 2009

Posts: 3,200

Reputation: sknake has much to be proud of

Solved Threads: 571

sknake

Offline

.NET Enthusiast

Re: changing multiple lables with one sql query

Jun 25th, 2009

Instead of calling ExecuteScalar you should open a DataReader and populate a DataTable.

 Help with Code Tags 
 c# Syntax (Toggle Plain Text) 
    private void button1_Click(object sender, EventArgs e)
    {
      const string connStr = "Data Source=apex2006sql;Initial Catalog=Leather;Integrated Security=True;";
      const string query = "Select * From Invoice Where InvNumber = @InvNumber";
      using (DataTable dt = new DataTable())
      {
        using (SqlConnection conn = new SqlConnection(connStr))
        {
          conn.Open();
          using (SqlCommand cmd = new SqlCommand(query, conn))
          {
            cmd.Parameters.Add(new SqlParameter("@InvNumber", 1100));
            using (SqlDataReader dr = cmd.ExecuteReader())
            {
              dt.Load(dr);
            }
          }
          conn.Close();
        }
        if (dt.Rows.Count != 1)
          throw new Exception("Row not found");
 
        DataRow row = dt.Rows[0];
        string invNumber = Convert.ToString(row["InvNumber"]);
        string custName = Convert.ToString(row["CustomerName"]);
 
        System.Diagnostics.Debugger.Break();
      }
    }
    private void button1_Click(object sender, EventArgs e)
    {
      const string connStr = "Data Source=apex2006sql;Initial Catalog=Leather;Integrated Security=True;";
      const string query = "Select * From Invoice Where InvNumber = @InvNumber";
      using (DataTable dt = new DataTable())
      {
        using (SqlConnection conn = new SqlConnection(connStr))
        {
          conn.Open();
          using (SqlCommand cmd = new SqlCommand(query, conn))
          {
            cmd.Parameters.Add(new SqlParameter("@InvNumber", 1100));
            using (SqlDataReader dr = cmd.ExecuteReader())
            {
              dt.Load(dr);
            }
          }
          conn.Close();
        }
        if (dt.Rows.Count != 1)
          throw new Exception("Row not found");

        DataRow row = dt.Rows[0];
        string invNumber = Convert.ToString(row["InvNumber"]);
        string custName = Convert.ToString(row["CustomerName"]);

        System.Diagnostics.Debugger.Break();
      }
    }

Scott Knake
Custom Software Development
Apex Software, Inc.

•

Join Date: Nov 2006

Posts: 436

Reputation: JerryShaw is on a distinguished road

Solved Threads: 72

JerryShaw

Offline

Posting Pro in Training

Re: changing multiple lables with one sql query

Jun 26th, 2009

 Help with Code Tags 
 C# Syntax (Toggle Plain Text) 
DataTable tbl = new DataTable();
SqlDataAdapter adapt = new SqlDataAdapter(string.Format("select cust_address,customer_city  from customers where customer_id = {0}", customerID), con);
adapt.Fill(tbl);
if (tbl.Rows.Count > 0)
{
       lblAddress1.Text = (string)tbl.Rows[0]["cust_address"];
       lblCity.Text = (string)tbl.Rows[0]["customer_city"];
}
tbl.Dispose();
DataTable tbl = new DataTable();
SqlDataAdapter adapt = new SqlDataAdapter(string.Format("select cust_address,customer_city  from customers where customer_id = {0}", customerID), con);
adapt.Fill(tbl);
if (tbl.Rows.Count > 0)
{
       lblAddress1.Text = (string)tbl.Rows[0]["cust_address"];
       lblCity.Text = (string)tbl.Rows[0]["customer_city"];
}
tbl.Dispose();

Just one of many ways of doing this.