0

I have a scenario where i have a DataSet bound to a combo box,
and i would like to read a row from the DataSet based on the item
selected in the Combo Box.

For e.g.
I have a dataset that has 1 table containing 2 fields say ID & Name.

This dataset is bound to a Combo Box with ID as a DisplayMember.
The Combo Box fills up perfectly with the ID field.

The problem i am facing is, when i select a ID from the Combo Box, i
would like to read the Name for that ID from the DataSet.

I did find a few samples but they would go through the loop to read
rows in a DataSet, but i could not find a sample where i can read a
row from a DataSet for a selected value.

Any help on this one will be much appreciated. Thanks.


Here's My Code

SqlConnection conn = new SqlConnection(" server="server_name"; database= "database_name"; integrated security= SSPI");
SqlCommand com1 = new SqlCommand();
com1 = new SqlCommand("SELECT EName FROM Emp_basic_det WHERE EID=@EID", conn);
com1.Parameters.Add("@EID", SqlDbType.Int);
com1.Parameters["@EID"].Value = EID_cmbox.SelectedItem;

// Here the above line is not giving correct output.Its giving System.Data.Datarowview instead of ID. 


com1.Connection.Open();
com1.ExecuteNonQuery();
SqlDataAdapter adap1 = new SqlDataAdapter(com1);
DataSet ds1 = new DataSet();
adap1.Fill(ds1);
com1.Connection.Close();

//Now i Have to take Name based on ID Selection.

EName_Text= ?

Can anyone kindly solve!!!!!!!

Edited by __avd: Added [code] tags. Encase your code in: [code] and [/code] tags.

2
Contributors
1
Reply
3
Views
7 Years
Discussion Span
Last Post by sknake
0

You could do something like this:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace daniweb
{
  public partial class frmSelectEmployee : Form
  {
    const string connStr = "Data Source=apex2006sql;Initial Catalog=Leather;Integrated Security=True;";
    private bool loaded;
    public frmSelectEmployee()
    {
      InitializeComponent();
    }

    private void frmSelectEmployee_Load(object sender, EventArgs e)
    {
      const string query = @"Select RepNumber From UserTable (NOLOCK) Order By RepNumber";
      DataTable dt = null;

      comboBox1.BeginUpdate();
      try
      {
        using (SqlConnection conn = new SqlConnection(connStr))
        {
          conn.Open();
          using (SqlCommand cmd = new SqlCommand(query, conn))
          {
            using (SqlDataReader dr = cmd.ExecuteReader())
            {
              dt = new DataTable();
              dt.Load(dr);
            }
          }
          conn.Close();
        }
        //Free up all the sql stuff before we process the data
        for (int i1 = 0; i1 < dt.Rows.Count; i1++)
        {
          comboBox1.Items.Add(dt.Rows[i1]["RepNumber"]);
        }
      }
      finally
      {
        if (dt != null)
        {
          dt.Dispose();
          dt = null;
        }
        comboBox1.EndUpdate();
      }

      loaded = true;
    }

    private void SetRepNumber(int RepNumber)
    {
      if (RepNumber <= 0)
      {
        textBoxEmployeeName.Text = string.Empty;
        textBoxUserName.Text = string.Empty;
        return;
      }
      else
      {
        const string query = @"Select UserName, EmpName From UserTable (NOLOCK) Where RepNumber = @RepNumber Order By RepNumber";
        DataTable dt = null;
        try
        {
          using (SqlConnection conn = new SqlConnection(connStr))
          {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand(query, conn))
            {
              cmd.Parameters.Add(new SqlParameter("@RepNumber", SqlDbType.Int)).Value = RepNumber;
              using (SqlDataReader dr = cmd.ExecuteReader())
              {
                dt = new DataTable();
                dt.Load(dr);
              }
            }
            conn.Close();
          }
          if (dt.Rows.Count > 0)
          {
            string userName = Convert.ToString(dt.Rows[0]["UserName"]);
            string employeeName = Convert.ToString(dt.Rows[0]["EmpName"]);
            textBoxUserName.Text = userName;
            textBoxEmployeeName.Text = employeeName;
          }
          else
          {
            SetRepNumber(-1); //The record must have been deleted by someone else
          }
        }
        finally
        {
          if (dt != null)
          {
            dt.Dispose();
            dt = null;
          }
        }
      }
    }

    private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
    {
      if (!loaded)
        return;
      if (comboBox1.SelectedIndex >= 0)
        SetRepNumber(Convert.ToInt32(comboBox1.Items[comboBox1.SelectedIndex]));
      else
        SetRepNumber(-1);
    }
  }
}

I think using a data adapter and dataset might be overkill for what you're trying to accomplish.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.