Click here to Skip to main content
15,895,799 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi my name is Vishal. For the past 2 days, I have been breaking my head on how to have a trail of four zeros proceeding a field name from a table in display member property of combobox in c# windows forms with sql server 2008?
I have a form named:frmDialyzer and in it I populate my 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+'| '+convert(varchar,n.patient_id)) 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+'| '+convert(varchar,n.patient_id)) 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 ,last name and patient_id as Name in DisplayMember property of cboPatientID
Suppose i want to display a trail of four zeros proceeding patient_id(such as if patient_id=2 then i want patient_id to be displayed as 00002) along with patient first,last name and patient_id as Name in DisplayMember property of cboPatientID.
So I am getting patient_id along with patient first,last name in DisplayMember property of cboPatientID.
But what i want is my patient_id must have trail of four zeros proceeding my patient_id
for example: my patient_id:3 then i want my patient_id to be displayed as 00003.

Can anyone help me please on how to display trail of four zeros proceeding my patient_id along with patient first,last name as Name in DisplayMember property of cboPatientID. Tell me what should i need to do to achieve my required result.?

Can anyone help me please?! Any help/guidance in solving of this problem would be greatly appreciated!
Posted
Updated 6-Jun-14 1:45am
v3
Comments
[no name] 6-Jun-14 7:40am    
You need to look up 'String.PadLeft'

1 solution

Continue from my previous solution, modify this:
convert(varchar, n.patient_id)

to
right('00000' + convert(varchar, n.patient_id), 5)
 
Share this answer
 
Comments
Member 10248768 6-Jun-14 7:52am    
Dear Peter Leow
Thank you for solution.It worked perfectly! Just what i needed. My God you are a genius in sql queries and string functions! Thank you.You saved me hell a lot of trouble.God bless you.
Peter Leow 6-Jun-14 8:11am    
You are welcome.

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