How to validate existing records?

Question

0.00/5 (No votes)

See more:

Hi!

In my page, I have a drop down, a text box,2 image buttons(1 for search and 1 for save button) and a grid view. the scenario here is that after filling up the information needed, when I click the Save button, the system will check first if the information inputted were existing in the database . If yes, an error message will display. If not, it will display in the grid view. But when I try the information inputted exist, nothing happens. It goes to the next condition which should not be.Also, the error message do not display.

here's the code:

C#

amicassaCon_repgen.Open();
        string amic = "";
        string amicc = "";
        SqlCommand get_no = new SqlCommand("SELECT ContractNo FROM MC.tblContracts WHERE ContractNo='"+company_code.SelectedValue+"' ", amicassaCon_repgen);
        SqlDataReader drno = get_no.ExecuteReader();
        while (drno.Read())
        {
            amic = drno[0].ToString();

        }
        drno.Close();

        SqlCommand get_nno = new SqlCommand("SELECT ContractPrjUnitDesc FROM MC.tblContracts Where ContractPrjUnitDesc='"+unit_desc.Text+"' ", amicassaCon_repgen);
        SqlDataReader drnno = get_nno.ExecuteReader();
        while (drnno.Read())
        {
            amicc = drnno[0].ToString();

        }
       
        if (company_code.SelectedValue == amic && unit_desc.Text == amicc)
        {
ScriptManager.RegisterStartupScript(this, this.GetType(), "popup", "alert('Error: Already had a Contract!');", true);
}
else
{
//my codes for inserting data and displaying to the grid view
}

Posted 4-Aug-14 19:48pm

DarkDreamer08

Updated 4-Aug-14 22:58pm

v4

Add a Solution

Comments

BulletVictim 5-Aug-14 2:36am

Why not do an IF EXISTS with the sql commands?
or just do an IF exists in a database stored procedure to return a true or false value if it exists or not and then using that result to manage the if in your code behind?

DarkDreamer08 5-Aug-14 4:56am

I do not know that IF EXISTS in SQL. I will read about that. Cheers!

BulletVictim 5-Aug-14 4:59am

basically it is
IF EXISTS(select TEST from TESTERS where TEST = 1)
begin
'do some things'
end
else
begin
'do some other things'
end

[no name] 5-Aug-14 6:36am

Unrelated to your question, but I really hope you're going to update your code to prevent SQL injection attacks? e.g.

SqlCommand get_no = new SqlCommand("SELECT ContractNo FROM MC.tblContracts WHERE ContractNo='"+company_code.SelectedValue+"' ", amicassaCon_repgen);

company_code.SelectedValue = "';SELECT * FROM MC.tblContracts;--", or
company_code.SelectedValue = "';DELETE FROM MC.tblContracts;--"

2 solutions

Solution 1

Your approach is error prone. There is no need to have two round trips for just checking the existance of values. Bullet Victim is right, you should check this in the SP before inserting the data in the table and return error in case if values already present which you can check. But lets be just on your side and advice you how to do it your way. Please see below the pseudo non tested code.

C#

amicassaCon_repgen.Open();

string sql = "select COUNT(*) from (Select COUNT(*) TOTAL from MC.tblContracts
where MC.tblContracts WHERE ContractNo = '{0}' UNION ALL SELECT COUNT(*) FROM MC.tblContracts Where ContractPrjUnitDesc='{1}') A";

sql = string.Format(sql, company_code.SelectedValue, unit_desc.Text);

SqlCommand get_no = new SqlCommand(sql, amicassaCon_repgen);

int valuePresent = (int)get_no.ExecuteScalar();

regen.Close();

if (valuePresent > 0)
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "popup", "alert('Error: Already had a Contract!');", true);

}
else
{
//my codes for inserting data and displaying to the grid view
}

Posted 5-Aug-14 1:08am

_Asif_

Updated 5-Aug-14 19:15pm

v2

Comments

DarkDreamer08 5-Aug-14 20:33pm

What do you mean by SP? and in your pseudo non tested code, can I change the WHERE condition?Can I just directly set the field name to the value being selected in the drop down list? I mean like this: WHERE ContractNo='"+company_code.SelectedValue+"' and not WHERE ContractNo='{0}'. What is the purpose of 0 and 1 in the WHERE condition?

_Asif_ 6-Aug-14 1:20am

SP => Stored Procedure
Yes you can change the where condition.
It seems you have not debug the code otherwise you have known what is 0 and 1. Google string.Format for a very detail understanding of 0 and 1.

DarkDreamer08 5-Aug-14 20:47pm

I followed the pseudo code above. an error message displays saying, "
Must declare the scalar variable "@MC".
Incorrect syntax near ')'."

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ClimerChinna · Accepted Answer · 2014-08-05T02:26:00

to achieve this you need to use IF EXISTS.
Either you can use directly in SQLCommand or else you can write a stored procedure.i will give both of the codes as an example you can use either of them according to your convinience but its always better to write a stored procedure in this type of situations and its a best practice also.

(1) Using SQLCommand:

amicassaCon_repgen.Open();
        SqlCommand get_no = new SqlCommand("DECLARE @GetNo int IF EXISTS(SELECT ContractNo FROM MC.tblContracts WHERE ContractNo='"+company_code.SelectedValue+"' and ContractPrjunitdesc='"+unit_desc.Text+"') BEGIN SET @GetNo=0 END ELSE BEGIN SET @GetNo=1 END SELECT @DeptNo", amicassaCon_repgen);
        SqlDataReader drno = get_no.ExecuteReader();
        if (drno.Read())
        {
            if(drno[0].toString()!=0)  \\contract not exists
		{
			//my codes for inserting data and displaying to the grid view
		}
	    else
		{
			ScriptManager.RegisterStartupScript(this, this.GetType(), "popup", "alert('Error: Already had a Contract!');", true);
		}
 
        }
        drno.Close();

(2) Using StoredProcedure

first write stored procedure in sql and execute it
Stored Procedure:

CREATE Procedure SearchCOntract
@ContractNo int,
@ContractPrjunitdesc nvarchar(50),
@Getno int output
AS
BEGIN
	IF EXISTS(SELECT ContractNo FROM MC.tblContracts WHERE ContractNo=@ContractNo and ContractPrjunitdesc=@ContractPrjunitdesc) 
	BEGIN 
		SET @GetNo=0 
	END
	ELSE 
	BEGIN 
		SET @GetNo=1 
	END
END

Code

amicassaCon_repgen.Open();
        SqlCommand get_no = new SqlCommand("SearchCOntract", amicassaCon_repgen);
	get_no.CommandType = CommandType.StoredProcedure;
        SqlParameter[] p=new SqlParameter[3];
        p[0]= new SqlParameter("@ContractNo", company_code.SelectedValue);        
        p[1]= new SqlParameter("@ContractPrjunitdesc", unit_desc.Text);        
        p[2]= new SqlParameter("@Getno", SqlDbType.Int);
        p[2].Direction = ParameterDirection.Output;
        foreach (SqlParameter val in p)
        {
            get_no.Parameters.Add(val);
        }
	get_no.ExecuteNonQuery();
        if ( int.Parse(get_no.Parameters["@Getno"].Value.ToString())!=0)
        {
           //my codes for inserting data and displaying to the grid view
        }
        else        
        {            
            ScriptManager.RegisterStartupScript(this, this.GetType(), "popup", "alert('Error:                                                          Already had a Contract!');", true);
        }

OR
you can also write above code like this

amicassaCon_repgen.Open();
        SqlCommand get_no = new SqlCommand("SearchCOntract", amicassaCon_repgen);
	get_no.CommandType = CommandType.StoredProcedure;
        get_no.Parameters.AddWithValue("@ContractNo", company_code.SelectedValue);        
        get_no.Parameters.AddWithValue("@ContractPrjunitdesc", unit_desc.Text);        
        get_no.Parameters.AddWithValue("@Getno", SqlDbType.Int).Direction = ParameterDirection.Output;
        get_no.ExecuteNonQuery();
        if ( int.Parse(get_no.Parameters["@Getno"].Value.ToString())!=0)
        {
           //my codes for inserting data and displaying to the grid view
        }
        else 
       {            
            ScriptManager.RegisterStartupScript(this, this.GetType(), "popup", "alert('Error: Already had a Contract!');", true);
        }