How do i check ID if it is already stored in database?

Question

I have this code that Inserts data to database. I want to check if the ID going to be inserted is already in database. but if it is not in database it should be inserted. But has some errors, can you please help me out with it?

    public void Add()
    {
        sc.Open();
        try
        {
            cmd = new SqlCommand("Select idnum from TableVotersInfo Where idnum=@idnum", sc);

            cmd.Parameters.AddWithValue("@idnum", _idnum);

            SqlDataReader rd = cmd.ExecuteReader();
            if (rd.Read() == true)
            {
                MessageBox.Show("ID number already exist!");
                rd.Close();
            }
            else
            {
                cmd = new SqlCommand("INSERT INTO TableVotersInfo (Education, idnum, FirstName, MiddleName, LastName, SchoolYear, ControlNum, VResult) VALUES (@ed, @idnum, @firstname, @middlename, @lastname, @schoolyear, @controlnum, 'Not Voted');", sc);
                cmd.Parameters.AddWithValue("@id", _id);
                cmd.Parameters.AddWithValue("@ed", _ed);
                cmd.Parameters.AddWithValue("@idnum", _idnum);
                cmd.Parameters.AddWithValue("@firstname", _firstname);
                cmd.Parameters.AddWithValue("@middlename", _middlename);
                cmd.Parameters.AddWithValue("@lastname", _lastname);
                cmd.Parameters.AddWithValue("@schoolyear", _schoolyear);
                cmd.Parameters.AddWithValue("@controlnum", _controlnum);

                cmd.ExecuteNonQuery();
                MessageBox.Show("Data Stored Successfully!");
                FAddVoters._cleardata = cleardata;
            }

        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
        finally
        {
            sc.Close();
        }
    }

Edit My error now is There is already an open DataReader associated with this command which must be closed first..

Update

    public void Update()
    {
        sc.Open();

        try
        {
            cmd = new SqlCommand("UPDATE TableVotersInfo SET Education=@ed, idnum=@idnum, FirstName=@firstname, MiddleName=@middlename, LastName=@lastname, SchoolYear=@schoolyear, ControlNum=@controlnum WHERE id=@id", sc);
            cmd.Parameters.AddWithValue("@id", _id);
                cmd.Parameters.AddWithValue("@ed", _ed);
                cmd.Parameters.AddWithValue("@idnum", _idnum);
                cmd.Parameters.AddWithValue("@firstname", _firstname);
                cmd.Parameters.AddWithValue("@middlename", _middlename);
                cmd.Parameters.AddWithValue("@lastname", _lastname);
                cmd.Parameters.AddWithValue("@schoolyear", _schoolyear);
                cmd.Parameters.AddWithValue("@controlnum", _controlnum);

            int res = cmd.ExecuteNonQuery();
            if (res > 0)
            {
                MessageBox.Show("Successfully Updated!");
                FAddVoters._cleardata = cleardata;
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
        finally
        {
            sc.Close();
        }
    }

This answer shows the "INSERT if not exists" best practice - http://stackoverflow.com/questions/5288283/sql-server-insert-if-not-exists-best-practice — Samuel O'Malley, Oct 30 '13 at 06:05
just remove single quotations from idnum='@idnum' to be idnum=@idnum — Mohammad abumazen, Oct 30 '13 at 06:07
i edited my code. sorry i messed up i thought i just copied the Add method. I did copied add and update method lol — Lyndon Broz Tonelete, Oct 30 '13 at 06:12
after the if (rd.Read() == true), close the reader before executing it again. — DevEstacion, Oct 30 '13 at 06:24

Grundy · Answer 1 · 2013-10-31T10:42:27.843

try remove single quotes in query like this

cmd = new SqlCommand("Select idnum from TableVotersInfo Where idnum=@idnum", sc);

and change type of variable _idnum from string to int

for solve error in insert try this code

public void Add()
{
    sc.Open();
    try
    {
        cmd = new SqlCommand("Select idnum from TableVotersInfo Where idnum=@idnum", sc);
        cmd.Parameters.AddWithValue("@idnum", _idnum);

        if (cmd.ExecuteScalar() != null)
        {
            MessageBox.Show("ID number already exist!");
        }
        else
        {
            cmd = new SqlCommand("INSERT INTO TableVotersInfo (Education, idnum, FirstName, MiddleName, LastName, SchoolYear, ControlNum, VResult) VALUES (@ed, @idnum, @firstname, @middlename, @lastname, @schoolyear, @controlnum, 'Not Voted');", sc);
            cmd.Parameters.AddWithValue("@id", _id);
            cmd.Parameters.AddWithValue("@ed", _ed);
            cmd.Parameters.AddWithValue("@idnum", _idnum);
            cmd.Parameters.AddWithValue("@firstname", _firstname);
            cmd.Parameters.AddWithValue("@middlename", _middlename);
            cmd.Parameters.AddWithValue("@lastname", _lastname);
            cmd.Parameters.AddWithValue("@schoolyear", _schoolyear);
            cmd.Parameters.AddWithValue("@controlnum", _controlnum);

            cmd.ExecuteNonQuery();
            MessageBox.Show("Data Stored Successfully!");
            FAddVoters._cleardata = cleardata;
        }

    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
    finally
    {
        sc.Close();
    }
}

now i have an error `There is already an open DataReader associated with this Command which must be closed first.` — Lyndon Broz Tonelete, Oct 30 '13 at 06:16
Has an error, `Object reference not set to an instance of an object.` — Lyndon Broz Tonelete, Oct 31 '13 at 10:03
@LyndonBrozTonelete oh,yes! forget del unused row `rd.Close();` see update — Grundy, Oct 31 '13 at 10:43

score 1 · Answer 2 · answered Oct 30 '13 at 06:32

Instead of two queries you could do it in one by changing values to a select and adding a where clause. After doing that you just need to check the returned value of ExecuteNonQuery() to see if 0 or 1 row was updated.

public void Add()
{
    sc.Open();
    try
    {
        using(cmd = new SqlCommand(@"INSERT INTO TableVotersInfo (Education, idnum, FirstName, MiddleName, LastName, SchoolYear, ControlNum, VResult)
                                        SELECT @ed, @idnum, @firstname, @middlename, @lastname, @schoolyear, @controlnum, 'Not Voted' 
                                        WHERE @idNum NOT IN (SELECT idNum FROM TableVotersInfo);", sc))
        {
            cmd.Parameters.AddWithValue("@id", _id);
            cmd.Parameters.AddWithValue("@ed", _ed);
            cmd.Parameters.AddWithValue("@idnum", _idnum);
            cmd.Parameters.AddWithValue("@firstname", _firstname);
            cmd.Parameters.AddWithValue("@middlename", _middlename);
            cmd.Parameters.AddWithValue("@lastname", _lastname);
            cmd.Parameters.AddWithValue("@schoolyear", _schoolyear);
            cmd.Parameters.AddWithValue("@controlnum", _controlnum);

            var rowsAffected = cmd.ExecuteNonQuery();
            if(rowsAffected == 0)
            {
                MessageBox.Show("ID number already exist!");
            }
            else
            {
                MessageBox.Show("Data Stored Successfully!");
                FAddVoters._cleardata = cleardata;
            }
        }

    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
    finally
    {
        sc.Close();
    }
}

I edited my post. Can you help me in my Update too? Coz if same thing happens, if idnum is already in database it just adds anyway. — Lyndon Broz Tonelete, Oct 30 '13 at 07:19
@LyndonBrozTonelete You did not do the same query as me, you need to learn the basics of SQL and you will understand what the difference between our two queries are. — Scott Chamberlain, Oct 30 '13 at 13:44

score 0 · Answer 3 · answered Oct 30 '13 at 06:24

0

try cmd.Dispose(); in else {} before again initializing the command object. This should work in your case.

answered Oct 30 '13 at 06:24

Vikash Singh

804
1
10
11

score 0 · Answer 4 · answered Oct 30 '13 at 06:26

0

Close the datareader object in else also

answered Oct 30 '13 at 06:26

R S P

997
5
8

How do i check ID if it is already stored in database?

4 Answers4