Answered Setting primary key in windows form

[codify]

Hello! I am have connected SQL management studio with my visual studio where I am making this hospital management system. I have set the PATIENT ID as the primary key in the SQL management studio. But when I am entering duplicate id, there is no objection being made by the code. What should I do to not allow the winform to accept duplicate patient id. Below is the code of Patient registration form.

private void button1_Click(object sender, EventArgs e)
        {
            SqlConnection con = new SqlConnection(@"Data Source=DESKTOP-A85V0ME\SQLEXPRESS;Initial Catalog=Hospitalmanagement;Integrated Security=True");
            con.Open();
            string gen = string.Empty;
            if (radioButton1.Checked)
            {
                gen = "Male";
            }
            else
            {
                gen = "Female";
            }
            try
            {
                string str = "INSERT INTO patient(id,name,gen,age,date,cont,addr,disease,status,r_type,building,r_no,price) VALUES('" + textBox1.Text +"','" + textBox2.Text + "','" + gen + "','" + textBox3.Text + "','" + textBox4.Text + "','" + textBox5.Text + "','" + textBox6.Text + "','" + textBox7.Text + "','" + textBox8.Text + "','" + textBox10.Text + "','" + textBox9.Text + "','" + textBox11.Text + "','" + textBox12.Text + "'); ";

                SqlCommand cmd = new SqlCommand(str, con);
                cmd.ExecuteNonQuery();
                string str1 = "select max(Id) from patient;";

                SqlCommand cmd1 = new SqlCommand(str1, con);
                SqlDataReader dr = cmd1.ExecuteReader();
                if (dr.Read())
                {
                    MessageBox.Show("Patient Information Saved Successfully..");
                    textBox2.Text = "";
                    textBox3.Text = "";
                    textBox4.Text = "";
                    textBox5.Text = "";
                    textBox6.Text = "";
                    textBox7.Text = "";
                    textBox8.Text = "";
                    textBox9.Text = "";
                    textBox10.Text = "";
                    textBox11.Text = "";
                    textBox12.Text = "";
                                  
                }               
            }
            catch (SqlException excep)
            {
                MessageBox.Show(excep.Message);
            }
            con.Close();
        }

        private void PatientRegistration_Load(object sender, EventArgs e)
        {
            SqlConnection con = new SqlConnection(@"Data Source=DESKTOP-A85V0ME\SQLEXPRESS;Initial Catalog=Hospitalmanagement;Integrated Security=True");
            con.Open();
            string str1 = "select max(id) from patient;";

            SqlCommand cmd1 = new SqlCommand(str1, con);
            SqlDataReader dr = cmd1.ExecuteReader();
            if (dr.Read())
            {
                string val = dr[0].ToString();
                if (val == "")
                {
                    textBox1.Text = "1";
                }
                else
                {
                    int a;
                    a = Convert.ToInt32(dr[0].ToString());
                    a = a + 1;
                    textBox1.Text = a.ToString();
                }
            }
            con.Close();
        }

 

[codify]

So I have got the solution guys! In SQL management studio once you have made a table, and you forget to set primary key, then you cannot modify it in future to set a primary key. It is mandatory for you to set the primary key when you are creating the table. So its was not saving my new primary key. Cheers!

 

[NoUserHere]

You are wide open for SQL injection attacks. Bad start by not using parameters. Always use parameters.

Your connection should be set as a const and/or stored in a getter property. There is no reason to carry the connection with you into every method you wish to use it. Consider creating a Sql class for your Sql objects. Seen lots of room for improvements here.

Use using statements on your connections, commands, and readers etc.

 

[Skydiver]

And also: why are you letting the user pick what patient ID to assign to a patient? You should let the database autogenerate the ID.

In the early days of relational databases, the advice was always that IDs are for databases, not users. If an ID must be given out, give out a different ID from the actual ID used by by the database. Unfortunately, the advice of these relational database gurus have been ignored the same way George Washington's advice about political parties has also been ignored.