hi my name is vishal.
For past 10days i have been breaking my head on how to use Not in sql select query from c# windows forms with sql server2008?
Given below is code of one of my form:
frmPatient:
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;
namespace DRRS_CSharp
{
public partial class frmPatient : Form
{
int pUserID;
public frmPatient()
{
InitializeComponent();
SqlConnection conn = new SqlConnection("Data Source=NPD-4\\SQLEXPRESS;Initial Catalog=DRRS;Integrated Security=true");
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
string SqlDataPull = ("Select p.doctor_id as doctor_id,n.doctor_first_name as doctor_fname,n.doctor_last_name as doctor_lname,n.doctor_middle_name as doctor_mname from doctordetail n,doctor p where n.doctor_id=p.doctor_id and n.status=1");
SqlCommand cmd = new SqlCommand(SqlDataPull);
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
SqlDataPull = dr[0].ToString() + dr[1].ToString() + dr[2].ToString() + dr[3].ToString();
cboDoctor.Items.Add(SqlDataPull);
}
dr.Close();
}
private void btnCreate_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 patient_id(patient_sex,patient_dob,row_upd_date,user_id)" + "Values(@patient_sex,@patient_dob,GetDate(),@user_id);Select @autoGenId = SCOPE_IDENTITY();",conn);
if (cboSex.SelectedIndex == 0)
{
cmd.Parameters.AddWithValue("@patient_sex", "M");
}
else
{
cmd.Parameters.AddWithValue("@patient_sex", "F");
}
cmd.Parameters.AddWithValue("@patient_dob", dtDOB.Value);
cmd.Parameters.AddWithValue("@user_id", pUserID);
cmd.Parameters.Add("@autoGenId", SqlDbType.Int).Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
autoGenId = Convert.ToInt32(cmd.Parameters["@autoGenId"].Value);
cmd = new SqlCommand("Update patient_name set status=0 where patient_id=" + patientID, conn);
cmd = new SqlCommand("Insert into patient_name(patient_id,patient_first_name,patient_middle_name,patient_last_name,virology,status,row_upd_date,user_id)" + "Values(@patient_id,@patient_first_name,@patient_middle_name,@patient_last_name,@virology,@status,GetDate(),@user_id)", conn);
cmd.Parameters.AddWithValue("@patient_id",autoGenId);
cmd.Parameters.AddWithValue("@patient_first_name", txtFName.Text.ToString());
cmd.Parameters.AddWithValue("@patient_middle_name", txtMName.Text.ToString());
cmd.Parameters.AddWithValue("@patient_last_name", txtLName.Text.ToString());
cmd.Parameters.AddWithValue("@virology", cboVirology.SelectedIndex);
cmd.Parameters.AddWithValue("@status", 1);
cmd.Parameters.AddWithValue("@user_id", pUserID);
cmd.ExecuteNonQuery();
if ((txtHNumber.Text != "") || (txtMNumber.Text != ""))
{
cmd = new SqlCommand("Update patient_contact set status=0 where patient_id=" +patientID, conn);
}
cmd = new SqlCommand("Insert into patient_contact(patient_id,homenumber,mobilenumber,row_upd_date,status,user_id)" + "Values(@patient_id,@homenumber,@mobilenumber,GetDate(),@status,@user_id)", conn);
cmd.Parameters.AddWithValue("@patient_id",autoGenId);
cmd.Parameters.AddWithValue("@homenumber", txtHNumber.Text);
cmd.Parameters.AddWithValue("@mobilenumber", txtMNumber.Text);
cmd.Parameters.AddWithValue("@status", 1);
cmd.Parameters.AddWithValue("@user_id", pUserID);
cmd.ExecuteNonQuery();
}
cmd = new SqlCommand("Update address set status=0 where patient_id=" + patientID, conn);
cmd = new SqlCommand("Insert into address(apartment_name,door_number,street_name_1,Street_name_2,Street_name_3,village,city,state,country,apartment_number,row_upd_date,patient_id,status,pincode,user_id)" + "Values(@apartment_name,@door_number,@street_name_1,@Street_name_2,@Street_name_3,@village,@city,@state,@country,@apartment_number,GetDate(),@patient_id,@status,@pincode,@user_id)", conn);
cmd.Parameters.AddWithValue("@apartment_name", txtApartmentName.Text.ToString());
cmd.Parameters.AddWithValue("@door_number", txtDoorNo.Text);
cmd.Parameters.AddWithValue("@street_name_1", txtStreet1.Text.ToString());
cmd.Parameters.AddWithValue("@Street_name_2", txtStreet2.Text.ToString());
cmd.Parameters.AddWithValue("@Street_name_3", txtStreet3.Text.ToString());
cmd.Parameters.AddWithValue("@village", txtVillageArea.Text.ToString());
cmd.Parameters.AddWithValue("@city", txtCity.Text.ToString());
cmd.Parameters.AddWithValue("@state", txtState.Text.ToString());
cmd.Parameters.AddWithValue("@country", txtCountry.Text.ToString());
cmd.Parameters.AddWithValue("@apartment_number", txtApartmentNo.Text);
cmd.Parameters.AddWithValue("@patient_id",autoGenId);
cmd.Parameters.AddWithValue("@status", 1);
cmd.Parameters.AddWithValue("@pincode", txtPCode.Text);
cmd.Parameters.AddWithValue("@user_id", pUserID);
cmd.ExecuteNonQuery();
cmd = new SqlCommand("Update doctorpatient set status=0 where patient_id=" + patientID, conn);
cmd = new SqlCommand("Insert into doctorpatient(patient_id,doctor_id,row_upd_date,status,user_id)" + "Values(@patient_id,@doctor_id,GetDate(),@status,@user_id)", conn);
cmd.Parameters.AddWithValue("@patient_id",autoGenId);
cmd.Parameters.AddWithValue("@doctor_id", cboDoctor.GetItemText(cboDoctor.SelectedIndex));
cmd.Parameters.AddWithValue("@status", 1);
cmd.Parameters.AddWithValue("@user_id", pUserID);
cmd.ExecuteNonQuery();
((MDIParent1)this.MdiParent).updateUserActivities(autoGenId, 1, txtFName.Text.ToString() + "patient detail was added successfully");
MessageBox.Show("patient Detail was added successfully", "DRRS", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
}
As you can see how i populate my combobox(cboPatientID) in frmDialyzer from tables patient_id and patient_name from sql server2008 into c# windows forms
sing 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;
namespace DRRS_CSharp
{
public partial class frmDialyzer : Form
{
int dStepIndex;
int pUserID;
string PatientPull = ("select p.patient_id as patient_id,n.patient_first_name as patient_fname,n.patient_last_name as patient_lname from patient_name n,patient_id p where n.patient_id=p.patient_id and n.status =1 and p.patient_id Not in (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();
while (dr.Read())
{
PatientPull = dr[1].ToString() + "_" + dr[2].ToString() + "(" + "0000" + dr[0].ToString() + ")";
cboPatientID.Items.Add(PatientPull);
}
dr.Close();
}
Given below is my query in sql select query in
frmDialyzer which is name of my form which has a combobox named:
cboPatientID
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;
namespace DRRS_CSharp
{
public partial class frmDialyzer : Form
{
public frmDialyzer()
{
InitializeComponent();
string PatientPull = ("select p.patient_id as patient_id,n.patient_first_name as patient_fname,n.patient_last_name as patient_lname from patient_name n,patient_id p where n.patient_id=p.patient_id and n.status =1 and p.patient_id Not In(Select patient_id from dialyser where deleted_status=0 and closed_status=0)");
SqlCommand cmd = new SqlCommand(PatientPull);
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
SqlDataReader dr = cmd.ExecuteReader();
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 my table
dialyser in sql server2008.
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 Int Yes
start_date datetime Yes
end_date datetime Yes
row_upd_date datetime Yes
manufacturer nvarchar(50) Yes
dialyzer_size nvarchar(20) 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 structure of table
patient_id in sql server2008
ColumnName DataType AllowNulls
patient_id Int No(Since auto-increment primary key)
patient_sex nvarchar(10) Yes
patient_dob date Yes
row_upd_date datetime Yes
user_id Int Yes
Given below is structure of table
patient_name in sql server2008.
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
virology Int Yes
row_upd_date datetime Yes
status bit Yes
agn Int No(since auto-increment primary key)
user_id Int Yes
Given below is my code in
frmDialyzer in which i insert values into table
dialyser and assigning dialyzer to patient:
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,closed_status,deleted_status,packed_volume,dialyzer_size,patient_id,row_upd_date,user_id)" + "Values(@dialyserID,@manufacturer,@mfr_ref_number,@mfr_lot_number,@mfr_date,@exp_date,@start_date,@closed_status,@deleted_status,@packed_volume,@dialyzer_size,@patient_id,GetDate(),@user_id);Select @autoGenId = SCOPE_IDENTITY();", conn);
cmd.Parameters.AddWithValue("@dialyserID", 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("@closed_status", 0);
cmd.Parameters.AddWithValue("@deleted_status", 0);
cmd.Parameters.AddWithValue("@packed_volume", txtPVol.Text.ToString());
cmd.Parameters.AddWithValue("@dialyzer_size", cboequipmentType.Text.ToString());
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 + "Dialyzer detail was added successfully");
MessageBox.Show("Dialyzer data was successfully added to patient", "DRRS", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
}
The above code works OK.
But the problem i am facing is after assigning dialyzer to a particular patient into table dialyser from combobox(cboPatientID) in frmDialyzer through c# windows forms with sql server2008
I should not get the same patient in list of my combobox(
cboPatientID) in
frmDialyzer once my
frmDialyzer forms loads again which i get.
Are there any other details required? If so reply please!?
Can anyone help me please?Any help/guidance in solving of my problem would be greatly appreciated.