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
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
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.