Rocko_roll Rocko_roll - 1 month ago 6
C# Question

If Exists Display a Message, If not, do something else ASP

I'm new to C# programming, and I've been researching in all the site and I found an answer that was sort of the answer that I was searching, but at the moment I put it in practice it didn't work.

The issue is that I have a page where you have to type your User Number and your Contract Type, this two merged are the key in one table, so when you click the button, if the user exists already in the table it needs to display a message "The User Already Exists!" else it needs to send the data to another page.

Here's the code I already have:

protected void Button1_Click(object sender, EventArgs e)
{
SqlConnection cn = new SqlConnection("Data Source=;Initial Catalog=Sales;User ID=;Password=");

SqlCommand cmd = new System.Data.SqlClient.SqlCommand("CheckUserDataBase", cn);
cmd.CommandType = CommandType.StoredProcedure;

SqlParameter parm = new SqlParameter("@User",SqlDbType.VarChar);
parm.Value = "'" + EmployeeNo.Text + "" + ContractType.Text + "'";
parm.Size=25;
parm.Direction = ParameterDirection.Input ;
cmd.Parameters.Add(parm);

SqlParameter parm2 = new SqlParameter("@Exists",SqlDbType.Int);
parm2.Direction = ParameterDirection.Output;
cmd.Parameters.Add(parm2);

cn.Open();
cmd.ExecuteNonQuery();
cn.Close();

int IsExists = Convert.ToInt32(cmd.Parameters["@Exists"].Value.ToString());

if (IsExists == 0)
{
Page.ClientScript.RegisterStartupScript(this.GetType(),"Scripts","<script>alert('The User already Exists!')</script>");
}
else if (IsExists == 1)
{
Response.Redirect("Data.aspx?NumEmp=" + EmployeeNumber+ "&ContractType=" + ContractType);
}


And this is the stored procedure I found:

ALTER PROCEDURE [dbo].[CheckUserDataBase]
(@User AS VARCHAR(25),
@Exists AS INT OUT)
AS
BEGIN
IF EXISTS (SELECT *
FROM GeneralData
WHERE EmployeeeNo+ContractType = @User)
BEGIN
SET @Exists = 1
END
END


All of my other parts of the code already work, but now I'm stuck in the validation with the SQL, I would appreciate your help.

Regards!

Answer

It's not clear to me exactly where your problem is, but I do see several issues in this code. Perhaps one of these will solve your problem.

First, when using query parameters, you don't need to enclose your parameter values in single quotes. I also noticed a misnamed parameter on this line:

SqlParameter parm= new SqlParameter("@User, cn",SqlDbType.VarChar);

We can select a count of records and check that, rather than using an output parameter, for a more natural query and to avoid needing to convert the result to an integer in the case of NULLs.

Finally, I see from the stored procedure that the two fields you are combining are actually separate columns in the table. With that in mind, you'll also get MUCH better performance by actually sending your two fields as separate parameters.

Here's code that accounts for those changes. I had to guess on how to divide upt he 25 characters between the two fields:

ALTER PROCEDURE [dbo].[CheckUserDataBase]
 (
   @EmpNo AS VARCHAR(15), @ContractType AS VARCHAR(10)
 )
 AS 
 BEGIN
    select count(*) 
    from GeneralData 
    where EmployeeeNo = @EmpNo 
        AND ContractType= @ContractType
 END 

.

protected void Button1_Click(object sender, EventArgs e)
{
    int results = 0;
    using (var cn = new SqlConnection("Data Source=;Initial Catalog=Sales;User ID=;Password="))
    using (var cmd = new SqlCommand("CheckUserDataBase", cn))
    {
        cmd.CommandType=CommandType.StoredProcedure; 
        cmd.Parameters.Add("@EmpNo", SqlDbType.VarChar, 15).Value = EmployeeNo.Text;
        cmd.Parameters.Add("@ContractType", SqlDbType.VarChar, 10).Value = ContractType.Text;

        cn.Open();
        results = (int)cmd.ExecuteScalar();
    }
    if (results > 0)
    {
       Page.ClientScript.RegisterStartupScript(this.GetType(),"Scripts","<script>alert('The User already Exists!')</script>");
    }
    else
    {
        Response.Redirect(String.Format("Data.aspx?NumEmp={0}&ContractType={1}", 
          Server.UrlEncode(EmployeeNumber), Server.UrlEncode(ContractType)));
    }
}
Comments