Click here to Skip to main content
15,868,306 members
Please Sign up or sign in to vote.
3.60/5 (2 votes)
hi my name is vishal for 14 days i have been breaking on head on how to show a value/item in combobox that has not been passed to another table.
I have a table named:dialyser in sql server2008.Given below it's structure:
ColumnName DataType AllowNulls
mfr_ref_number nvarchar(20) Yes
mfr_lot_number nvarchar(20) Yes
mfr_date date Yes
exp_date date Yes
packed_volume Int Yes
patient_id Small Int Yes
start_date datetime Yes
end_date datetime Yes
row_upd_date datetime Yes
manufacturer nvarchar(50) Yes
dialyzer_size nvarchar(20) Yes
user_id Int Yes
deleted_status bit Yes
deleted_date datetime Yes
dialyserID nvarchar(20) Yes
closed_status bit Yes
closed_date datetime Yes
agn Int No(since auto-increment primary key)
Given below is code in c# on how i insert values into table:dialyser from c# windows
forms
C#
 private void btnNext_Click(object sender, EventArgs e)
        {
SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd = new SqlCommand("Select * from patient_name where status=1 and patient_id=" + cboPatientID.GetItemText(cboPatientID.SelectedIndex) + "'", conn);
                if (txtDID.Text.Length > 5)
                {
                    txtDID.Text = txtDID.Text.Substring(5);
                }
                lblPID.Text = cboPatientID.Text;
                cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                string str;
                string value = ("Select max(agn) from dialyser");
                cmd = new SqlCommand(value, conn);
                str = cmd.ExecuteScalar().ToString();
                conn.Close();
                if (str == DBNull.Value.ToString())
                {
                    txtDID.Text = string.Format("0000"+"1");
                }
                else
                {
                    txtDID.Text =  (Convert.ToInt32(str) + 1).ToString();
                }
}
private void btnAssign_Click(object sender, EventArgs e)
        {
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();
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            int autoGenId = -1;
            cmd = new SqlCommand("Insert into dialyser(dialyserID,manufacturer,mfr_ref_number,mfr_lot_number,mfr_date,exp_date,start_date,packed_volume,dialyzer_size,deleted_status,patient_id,row_upd_date,user_id)" + "Values(@dialyserID,@manufacturer,@mfr_ref_number,@mfr_lot_number,@mfr_date,@exp_date,@start_date,@packed_volume,@dialyzer_size,@deleted_status,@patient_id,GetDate(),@user_id);Select @autoGenId = SCOPE_IDENTITY();", conn);
            cmd.Parameters.AddWithValue("@dialyserID", string.Format("0000"+txtDID.Text.ToString()));
            cmd.Parameters.AddWithValue("@manufacturer", cboManufacturer.Text.ToString());
            cmd.Parameters.AddWithValue("@mfr_ref_number", txtMFRRefNo.Text.ToString());
            cmd.Parameters.AddWithValue("@mfr_lot_number", txtMFRLotNo.Text.ToString());
            cmd.Parameters.AddWithValue("@mfr_date", dtMFRDate.Value);
            cmd.Parameters.AddWithValue("@exp_date", dtExpDate.Value);
            cmd.Parameters.AddWithValue("@start_date", dtStartDate.Value);
            cmd.Parameters.AddWithValue("@packed_volume", txtPVol.Text.ToString());
            cmd.Parameters.AddWithValue("@dialyzer_size", cboequipmentType.Text.ToString());
            cmd.Parameters.AddWithValue("@deleted_status", 0);
            cmd.Parameters.AddWithValue("@patient_id",cboPatientID.SelectedIndex);
            cmd.Parameters.AddWithValue("@user_id", pUserID);
            cmd.Parameters.Add("@autoGenId", SqlDbType.Int).Direction = ParameterDirection.Output;
            cmd.ExecuteNonQuery();
            autoGenId = Convert.ToInt32(cmd.Parameters["@autoGenId"].Value);
            ((MDIParent1)this.MdiParent).updateUserActivities(autoGenId, 4, cboManufacturer.Text.ToString());
MessageBox.Show("Dialyzer data was successfully added", "DRRS", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();

Given below is code of how i populate my combobox named:cboPatientID in my form named:frmDialyzer in c# windows forms
C#
namespace DRRS_CSharp
{
    public partial class frmDialyzer : Form
    {
 public frmDialyzer()
        {
            InitializeComponent();
            SqlConnection conn = new SqlConnection("Data Source=NPD-4\\SQLEXPRESS;Initial Catalog=DRRS;Integrated Security=true");
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
             string PatientPull = ("Select p.patient_id,n.patient_first_name,n.patient_last_name from patient_id p,patient_name n where p.patient_id=n.patient_id and n.status=1 and NOT EXISTS(Select patient_id from dialyser where deleted_status=0)");
            SqlCommand cmd = new SqlCommand(PatientPull);
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            SqlDataReader dr = cmd.ExecuteReader();
            cboPatientID.Items.Clear();
            while (dr.Read())
            {
                PatientPull = dr[1].ToString() + "_" + dr[2].ToString() + "(" + "0000" + dr[0].ToString() + ")";
                
                    cboPatientID.Items.Add(PatientPull);
            }
            dr.Close();
}

Given below is structure of table named:patient_id in sql server2008
ColumnName DataType AllowNulls
patient_id Int No(since auto-increment primary key)
row_upd_date datetime Yes

Given below is structure of table named:patient_name in sql server2008
ColumnName DataType AllowNulls
patient_id Int No(since auto-increment primary key)
patient_first_name nvarchar(50) Yes
patient_middle_name nvarchar(50) Yes
patient_last_name nvarchar(50) Yes
row_upd_date datetime Yes

The problem i am facing is once my patient_id has been passed from tables(patient_id, patient_name) into table(dialyser) through c# windows forms.The same patient_id should not appear in my combobox(cboPatientID) in my form:frmDialyzer once the form loads.But i keep getting the same patient_id which i have already passed to table(dialyser) in my combobox(cboPatientID) which i should not get.
Can anyone help me? Any help/guidance in solving of this problem would be greatly appreciated.
Posted
Comments
Francine DeGrood Taylor 21-May-14 13:16pm    
How do you select data in your combo box? And what does this data represent?

It sounds like you are saying the combo box contains a list of patients who have not been "processed". The user selects one to process, and after that, the patient with that patient ID should not appear in combo box. Is this correct?
Member 10248768 22-May-14 0:03am    
Yes that is exactly what i want.Tell me how to achieve it?Reply please!

1 solution

Hi my name is vishal at last i have solved my problem through following code:
C#
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.IO;
using System.Drawing.Printing;
using System.Runtime.InteropServices;
using System.Drawing.Imaging;
namespace DRRS_CSharp
{
    public partial class frmDialyzer : Form
    {
 public frmDialyzer()
        {
            InitializeComponent();
            this.FillDropDownList();
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();
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;
            string ManufacturerPull = ("Select distinct(ManufacturerName) from EquipmentData");
            SqlCommand mcd = new SqlCommand(ManufacturerPull);
            mcd.Connection = conn;
            mcd.CommandType = CommandType.Text;
            SqlDataReader cdr = mcd.ExecuteReader();
            while (cdr.Read())
            {
                ManufacturerPull = cdr[0].ToString();
                cboManufacturer.Items.Add(ManufacturerPull);
            }
            cdr.Close();
        }
private void btnNext_Click(object sender, EventArgs e)
        {
 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();
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;
                    string str;
                    string value = ("Select max(agn) from dialyser");
                    cmd = new SqlCommand(value, conn);
                    str = cmd.ExecuteScalar().ToString();
                    conn.Close();
                    if (str == DBNull.Value.ToString())
                    {
                        txtDID.Text = string.Format("0000" + "1");
                    }
                    else
                    {
                        txtDID.Text = (string.Format("0000" + (Convert.ToInt32(str) + 1).ToString()));
                    }
}
 private void btnAssign_Click(object sender, EventArgs e)
        {
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();
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            int autoGenId = -1;
            cmd = new SqlCommand("Insert into dialyser(dialyserID,manufacturer,mfr_ref_number,mfr_lot_number,mfr_date,exp_date,start_date,packed_volume,dialyzer_size,deleted_status,closed_status,patient_id,row_upd_date,user_id)" + "Values(@dialyserID,@manufacturer,@mfr_ref_number,@mfr_lot_number,@mfr_date,@exp_date,@start_date,@packed_volume,@dialyzer_size,@deleted_status,@closed_status,@patient_id,GetDate(),@user_id);Select @autoGenId = SCOPE_IDENTITY();", conn);
            cmd.Parameters.AddWithValue("@dialyserID", string.Format("0000" + txtDID.Text.ToString()));
            cmd.Parameters.AddWithValue("@manufacturer", cboManufacturer.Text.ToString());
            cmd.Parameters.AddWithValue("@mfr_ref_number", txtMFRRefNo.Text.ToString());
            cmd.Parameters.AddWithValue("@mfr_lot_number", txtMFRLotNo.Text.ToString());
            cmd.Parameters.AddWithValue("@mfr_date", dtMFRDate.Value);
            cmd.Parameters.AddWithValue("@exp_date", dtExpDate.Value);
            cmd.Parameters.AddWithValue("@start_date", dtStartDate.Value);
            cmd.Parameters.AddWithValue("@packed_volume", txtPVol.Text.ToString());
            cmd.Parameters.AddWithValue("@dialyzer_size", cboequipmentType.Text.ToString());
            cmd.Parameters.AddWithValue("@deleted_status", 0);
            cmd.Parameters.AddWithValue("@closed_status", 0);
            cmd.Parameters.AddWithValue("@patient_id", cboPatientID.SelectedValue);
            cmd.Parameters.AddWithValue("@user_id", pUserID);
            cmd.Parameters.Add("@autoGenId", SqlDbType.Int).Direction = ParameterDirection.Output;
            cmd.ExecuteNonQuery();
            autoGenId = Convert.ToInt32(cmd.Parameters["@autoGenId"].Value);
            ((MDIParent1)this.MdiParent).updateUserActivities(autoGenId, 4, cboManufacturer.Text.ToString());
MessageBox.Show("Dialyzer data was successfully added", "DRRS", MessageBoxButtons.OK, MessageBoxIcon.Information);
            this.Close();
        }
public void FillDropDownList()
        {
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;
        }

I want to thank everyone who tried to help me through this forum.God Bless.
 
Share this answer
 

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