CODEWITHSUNDEEP
c#asp.net
Bryan
Bryan

Reputation: 8697

Unable to insert and check for similar data in database

I'm trying to insert an account into the database. I shall briefly explain what my codes does before adding my syntax below. Generally, I'm checking if the particular textbox is empty. if it's not, it will attempt to insert data into the database. However, if the database contain a data similar to what is being type in the textbox they will prompt different error respectively. Unfortunately, for mine, i'm not even able to insert any data and there's also no error to show that the data in the textbox and database is the same

protected void btnAdd_Click(object sender, EventArgs e)
    {

        if (tbpid.Text.Equals(""))
        {
            lbmsg.Text = "Please generate a police ID for this account";
        }
        else if (tbfullname.Text.Equals(""))
        {
            lbmsg.Text = "Please type the full name of the police officer";
        }
        else if (tbnric.Text.Equals(""))
        {
            lbmsg.Text = "Please enter the NRIC of the police officer";
        }
        else if (ddllocation.SelectedValue.Equals("Select Location"))
        {
            lbmsg.Text = "Please select the location of the policepost he will be posted to";
        }
        else { 

        SqlConnection con = new SqlConnection("Data Source = localhost; Initial Catalog = MajorProject; Integrated Security= SSPI");
        con.Open();
        SqlCommand select = new SqlCommand("Select policeid, nric from PoliceAccount where policeid = @policeid", con);
        SqlDataReader dr;

select.Parameters.AddWithValue("@policeid", tbpid.Text);

        dr = select.ExecuteReader();
        if (dr.Read())
        {
            if (tbpid.Equals(dr["policeid"].ToString()))
            {

                lbmsg.Text = "Police ID already exists. Please generate another new Police ID";

            }
            else if (tbnric.Equals(dr["nric"].ToString()))
            {
                lbmsg.Text = "NRIC already exists. Please ensure the NRIC is correct";
            }

        }

        else
        {

            SqlConnection conn = new SqlConnection("Data Source = localhost; Initial Catalog = MajorProject; Integrated Security= SSPI");
            conn.Open();
            SqlCommand cmd = new SqlCommand("insert into PoliceAccount(policeid, password, nric, fullname, postedto)  values('" + tbpid.Text.Trim() + "','" + tbpid.Text.Trim() + "','" + tbnric.Text.Trim() + "','" + tbfullname.Text.Trim() + "', '" + ddllocation.SelectedValue + "')", conn);
            cmd.ExecuteNonQuery();
            conn.Close();

            Response.Redirect("AdminAddAccount");

        }

        }

    }

Please refer to this [thread for the correct answer][1]

Upvotes: 1

Views: 111

Answers (2)

Aayushi Jain
Aayushi Jain

Reputation: 2879

What The Solution has said is right. Also add cmd.CommandType = CommandType.Text; in your code before cmd.ExecuteNonQuery();.

Also I think that you should add .aspx here Response.Redirect("AdminAddAccount.aspx");

Upvotes: 0

Mike Perrenoud
Mike Perrenoud

Reputation: 67898

It never gets into the else, to insert data, because your select statement isn't filtered. This statement:

Select policeid, nric from PoliceAccount

will return all rows from that table. However, what you really want is:

Select policeid, nric from PoliceAccount where policeid = @policeid

and then, before executing the reader, add this line of code:

select.Parameters.AddWithValue("@policeid", tbpid.Text);

Finally, use that same parameterized syntax on the insert statement, it's safe from SQL Injection.

Upvotes: 1

Related Questions