CODEWITHSUNDEEP
c#asp.net
user2388316
user2388316

Reputation: 83

Checking if id and reg already exists

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 data.

In my code, 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 don't know if it is with my select that is wrong or something in my code.

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!");

        }

I am using C# with asp.net.

Upvotes: 1

Views: 3096

Answers (4)

Evan L
Evan L

Reputation: 3855

Your problem as per the error is that you are reassigning the parameter to @reg after you assign it to @userid.

Try this:

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


cmdd.Parameters.AddWithValue("@userid", txtid.Text);
cmdd.Parameters.AddWithValue("@reg", txtregNo.Text);

var id = cmdd.ExecuteReader() as string;

if (String.IsNullOrEmpty(id))
{
    //Show error message and exit the method
}
else
{
    //Add the row to the database if it didn't exist
}

EDIT:

I added some code to show how you could check if the userId exists in the table. Then you check against the user id itself instead of checking a reader object. Note, i am not at my dev computer right now so I did not compile this, you may have to do some tweaks but the idea is there.

Upvotes: 0

Mike Perrenoud
Mike Perrenoud

Reputation: 67918

OK, so this isn't going to work:

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

because you're reassigning the value of the same object. Change that to this:

cmdd.Parameters.AddWithValue("@userid", txtid.Text);
cmdd.Parameters.AddWithValue("@reg", txtregNo.Text);

this will add the parameters, two of them, to the SqlCommand object. Now, a little more advice, consider doing this:

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

        using (SqlDataReader reader = cmdd.ExecuteReader())
        {
            ...
        }
    }
}

because right now you're not disposing those object properly.

You see, anything that implements IDisposable should be wrapped in a using statement to ensure the Dispose method is called on it.

Upvotes: 2

Ted
Ted

Reputation: 4067

You are using one instance of sql parameter and passing it two different values thus overriding the first one. Try it like this:

SqlParameter param1 = new SqlParameter("@userid", txtid.Text);

SqlParameter param2 = new SqlParameter("@reg", txtregNo.Text);

Upvotes: 0

Tim Hobbs
Tim Hobbs

Reputation: 2017

param.ParameterName = "@userid";
param.ParameterName = "@reg";

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

You are only declaring 1 parameter and overwriting it for both ParameterName and Value.

As an aside, you should consider looking into some type of data access helper or ORM or something to save you the trouble of all that boilerplate SQL connection code.

You are also opening another connection inside of what should already be an open SQL connection.

Upvotes: 1

Related Questions