how to use not in sql select query through c# windows forms with sql server2008

Question

4.00/5 (1 vote)

See more:

SQL-Server-2008

, +

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:

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

C#

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

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;
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:

C#

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.

Posted 18-May-14 20:32pm

Member 10248768

Updated 18-May-14 21:33pm

v4

Add a Solution

Comments

Thanks7872 19-May-14 2:38am

I cant understand the question at all. Remove all the unnecessary code [we need only query]. Remove variable names as it seems confusing[atleast to me]. Explain the table structure and explain what you want to get out of it.

syed shanu 19-May-14 2:54am

Do you mean you want to clear your combo box every time and add items.You mention as you dont want to add the same patient in combo box.

Member 10248768 19-May-14 3:01am

No that is not what i want. As you can see list of patients appear in my combobox(cboPatientID) in frmDialyzer every time when my form loads.But what i want is once i have assigned dialyzer to particular patient into dialyser table in sql server2008 through c# windows forms. I should not get the same patient in my items of combobox(cboPatientID) in frmDialyzer once the form loads to whom i have already assigned a dialyserID. That is what i want

Mycroft Holmes 19-May-14 3:19am

That is not what you want, you need to identify the assignment table in the database and use that in the query not just the last one you assigned. BTW, change the @SepectedPatient to the number (I always use a variable in SQL Proceures that are not supported in Express).

Member 10248768 19-May-14 3:38am

So the assignment table in sql server2008 is dialyser and the field is patient_id in dialyser. i assign pass value of patient_id from tables patient_id and patient_name into table dialyser and the assigned field in dialyser table is patient_id.So tell me/guide me on how to achieve my required result? Reply please?

Mycroft Holmes 19-May-14 3:46am

And PatientID not in (select patientid from dialiser)

Member 10248768 19-May-14 5:09am

Hey Holmes
I again deleted and created a new table named dialyser2 with same fields and respective data types and tried below code:
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 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 d.patient_id from dialyser2 d where d.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();
}
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 dialyser2(dialyserID,manufacturer,mfr_ref_number,mfr_lot_number,mfr_date,exp_date,start_date,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,@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("@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", "DRRS", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
}
But still i am getting the same problem.Tell me how to fix my problem?Reply please?! I hope i get reply from you.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Mycroft Holmes · Answer 1 · 2014-05-18T20:59:00

Solution 1

SQL

where n.patient_id=p.patient_id and n.status =1 --this is a JOIN condition - use and inner join instead

and p.patient_id Not In(Select patient_id from dialyser where deleted_status=0 and closed_status=0)") -- This excludes deleted and closed

Nowhere do you exclude the selected patient! try adding

And n.patientID != @SelectedPatientID

to your where clause

Posted 18-May-14 20:59pm

Mycroft Holmes

Comments

Member 10248768 19-May-14 3:06am

Hi i just now tried your solution and i get run-time error:
SqlException was unhandled:
Must declare the scalar variable "@SelectedPatientID"
Tell me how to fix this error?Reply please?!

V. · Answer 2 · 2014-05-18T21:10:00

a few things here:

First of all, if this is to be your "good" application. Restart and try to setup some structure. Doing DataAccess layers stuff in your GUI classes is generally a very bad idea.

If (and only if) I understand you correctly you get non-unique names in your combobox or your combobox loads the values again and ADDS them instead of replacing.

In the first case change the select statement to only allow unique values for the patients.

In the latter case, if you want to keep the code like it is. You should just call

cboPatientID.Items.Clear();

before filling it.

However, when connecting to the database you can just fill the dataset and then assign the dataset to the combobox datacontext (or datasource in winforms I believe) object.

cboPatientID.DataSource = [yourdataset].Tables[0].DefaultView;
cboPatientID.DisplayMember = "[columnname to the values that should be SHOWN]"; 
cboPatientID.ValueMember = "[columnname to the code values (can be the same as displaymember]";

There are tutorials out there. The advantage of this is that if you reset (re-assign) this dataset to the datasource, the combobox is cleared and refilled for you. (in contradiction with using Items.Add(...))

You could go for the quick-fix (calling Clear function), but I would recommend reviewing your architecture and start over. There are several articles here on CP on N-tier design. (look here[^] eg.)

hope this helps.