checking if id and reg already exists

[PetroTiburcio]

I have this table Profile which has fields with user_Id and regNo and I want to check first if id and email are already exists before proceed to inserting datas. In my codes, I am able to validate only one row (either id or reg number), but if I am going to validate the two of them, it gives me an error, saying "Must declare the scalar variable @userid". I dont know if it is with my select that is wrong or something in my codes

SqlConnection con = new SqlConnection("Data Source=GATE-PC\\SQLEXPRESS;Initial Catalog=dbProfile;Integrated Security=True");
    con.Open();
    SqlCommand cmdd = new SqlCommand("select * from Profile where user_Id = @userid AND RegNo = @reg", con);

    SqlParameter param = new SqlParameter();
    //SqlParameter param1 = new SqlParameter();
    param.ParameterName = "@userid";
    param.ParameterName = "@reg";

    param.Value = txtid.Text;
    param.Value = txtregNo.Text;

    cmdd.Parameters.Add(param);
    //cmdd.Parameters.Add(param1);


    SqlDataReader reader = cmdd.ExecuteReader();


        if (reader.HasRows)
        {
            MessageBox("User Id/Registry Number already exists");
        }


        else
        {
            SqlConnection con = new SqlConnection("Data Source=GATE-PC\\SQLEXPRESS;Initial Catalog=dbProfile;Integrated Security=True");
            SqlCommand cmd = new SqlCommand("qry", con);
            cmd.CommandType = System.Data.CommandType.Text;

            cmd.Parameters.AddWithValue("@id", txtid.Text);
            cmd.Parameters.AddWithValue("@regno", txtregNo.Text);
            cmd.Parameters.AddWithValue("@name", txtname.Text);

            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            con.Open();
            cmd.ExecuteNonQuery();
            MessageBox("successfully saved!");

        }

Any help would be appreciated. I am using c# with asp.net. Thank you in advance and God Bless.

[Frinavale]

Oh it looks like you are only declaring one parameter.
You need to declare 2...one for each parameter in your query:

SqlConnection con = new SqlConnection("Data Source=GATE-PC\\SQLEXPRESS;Initial Catalog=dbProfile;Integrated Security=True");
    con.Open();
    SqlCommand cmdd = new SqlCommand("select * from Profile where user_Id = @userid AND RegNo = @reg", con);
 
    SqlParameter userIDParam = new SqlParameter();
    SqlParameter regParam = new SqlParameter();
   
    userIDParam.ParameterName = "@userid";
    regParam.ParameterName = "@reg";
 
    userIDParam.Value = txtid.Text;
    regParam.Value = txtregNo.Text;

    cmdd.Parameters.Add(userIDParam);
    cmdd.Parameters.Add(regParam);

//.......

Happy coding

-Frinny

[PetroTiburcio]

Hello Frinavale, thanks for replying. I have tried your code suggestion. But it is just validating the reg no, the user id is not validating, it is saving even id is already exists

[Frinavale]

Well, first you should check to see if the UserID and RegNo exist in the system....if so, then proceed with whatever you need to save, if not then exit the method.

I would use the [url]count() function[/icode] to return the number of rows that have the UserID and RegNo. If this returns 0, then you know it doesn't exist, if it returns more than 0, then it is valid and you can continue.

For example, here is a function that uses the SQL count() function to determine if any rows match the UserID and RegNo...it returns true or false accordingly (please note that this code has not been tested as I am not usually using C# syntax):

private Boolean IsUserAndRegNoValid()
{
    Boolean isValid = false;
    SqlConnection con = new SqlConnection("Data Source=GATE-PC\\SQLEXPRESS;Initial Catalog=dbProfile;Integrated Security=True");
    SqlCommand cmdd = new SqlCommand("Select count(*) As ValidUsers From Profile where user_Id = @userid AND RegNo = @reg", con);
 
    SqlParameter userIDParam = new SqlParameter();
    SqlParameter regParam = new SqlParameter();
 
    userIDParam.ParameterName = "@userid";
    regParam.ParameterName = "@reg";
 
    userIDParam.Value = txtid.Text;
    regParam.Value = txtregNo.Text;
 
    cmdd.Parameters.Add(userIDParam);
    cmdd.Parameters.Add(regParam);

    using (con) {
        using (cmdd){
            con.Open();
            using (DbDataReader dr = cmdd.ExecuteReader()) {
                isValid = dr.HasRows;
            }
        }
    }
    return isValid;
}

-Frinny