Click here to Skip to main content
15,885,141 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
Hi my name is vishal for past 3 days i have been breaking my head on how to include 3 fields from table to display member property of combobox in c# windows forms with sql server2008?
I have a form named:frmDialyzer and in it is a combobox named:cboPatientID.
Given below is c# code on how i populate my combobox(cboPatientID) from tables named:patient_id and patient_name in sql server2008.:
C#
namespace DRRS_CSharp
{
    public partial class frmDialyzer : Form
    {
int pUserID;
public frmDialyzer()
        {
            InitializeComponent();
string Sql = "Select p.patient_id as patient_id,(n.patient_first_name+' '+n.patient_last_name) as Name from patient_id p,patient_name n where n.patient_id=p.patient_id and n.status=1 and not exists(Select * from dialyser where dialyser.deleted_status=0 and dialyser.closed_status=0 and dialyser.patient_id=p.patient_id)";
            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection("Data Source=NPD-4\\SQLEXPRESS;Initial Catalog=DRRS;Integrated Security=true");
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            SqlCommand cmd = new SqlCommand(Sql, conn);
            dt.Load(cmd.ExecuteReader());
            cboPatientID.DataSource = dt;
            cboPatientID.ValueMember = "patient_id";
            cboPatientID.DisplayMember = "Name";
            cboPatientID.SelectedValue = 0;

Since i am using SelectedValue property of cboPatientID in terms of selecting item in combobox(cboPatientID) in frmDialyzer. I have also enabled DropDownStyle property of cboPatientID to DropDownList in frmDialyzer.
Given below is structure of tables in sql server2008:
table name:patient_id
ColumnName DataType AllowNulls
patient_id(primary key) Int No(since auto-increment)
patient_sex nvarchar(15) Yes
patient_dob date Yes
row_upd_date datetime Yes

table name:patient_name
ColumnName DataType AllowNulls
patient_id int Yes
patient_first_name nvarchar(50) Yes
patient_middle_name nvarchar(50) Yes
patient_last_name nvarchar(50) Yes
status bit Yes
row_upd_date datetime Yes

As you can see in below c# code of frmDialyzer:
C#
namespace DRRS_CSharp
{
    public partial class frmDialyzer : Form
    {
int pUserID;
 public frmDialyzer()
        {
            InitializeComponent();
string Sql = "Select p.patient_id as patient_id,(n.patient_first_name+' '+n.patient_last_name) as Name from patient_id p,patient_name n where n.patient_id=p.patient_id and n.status=1 and not exists(Select * from dialyser where dialyser.deleted_status=0 and dialyser.closed_status=0 and dialyser.patient_id=p.patient_id)";
            DataTable dt = new DataTable();
            SqlConnection conn = new SqlConnection("Data Source=NPD-4\\SQLEXPRESS;Initial Catalog=DRRS;Integrated Security=true");
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            SqlCommand cmd = new SqlCommand(Sql, conn);
            dt.Load(cmd.ExecuteReader());
            cboPatientID.DataSource = dt;
            cboPatientID.ValueMember = "patient_id";
            cboPatientID.DisplayMember = "Name";
            cboPatientID.SelectedValue = 0;
}

In above code i am able to display patient first and last name as Name in DisplayMember property of cboPatientID.
Suppose i want to display patient_id along with patient first and last name as Name in DisplayMember property of cboPatientID? Is it possible?! If so should i need to do any conversions regarding patient_id and then display it along with patient first and last name as Name in DisplayMember property of cboPatientID.
Can anyone help me please on how to display patient_id with patient first and last name as Name in DisplayMember property of cboPatientID?!
Can anyone help me please?! Any help/guidance in solving of this problem would be greatly appreciated!
Posted

1 solution

You have to convert the int type of patient_id to varchar before concatenation, modify:
(n.patient_first_name+' '+n.patient_last_name) 

to:
(convert(varchar, n.patient_id) + ' ' + n.patient_first_name+' '+n.patient_last_name) 
 
Share this answer
 
v2
Comments
Member 10248768 6-Jun-14 5:06am    
Thanks Mr.Peter Leow Your solution worked perfectly for me! Thanks your help. I have only one last question is it possible to have a trial of zeros proceeding with patient_id in the same query? Is it possible? Reply please!?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900