I have a part of code that I want to check if a value exists before I add it to my database. The result is always that my record does not exist and I don't why?

public bool IsClientExist(string valuetocheck)
{
    bool result = false;

    try
    {
        string strQuery = "SELECT * FROM [" + strFileNamenopath + "] WHERE client = '"  + valuetocheck + "'";
        OleDbCommand cmd = new OleDbCommand(strQuery, conn);
        OleDbDataReader reader = cmd.ExecuteReader();

        result = reader.Read();

        if (reader.Read())
        {
            result = true;
        }

        reader.Close();
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.StackTrace.ToString() + " " + ex.Message);
    }

    return result;
}

EDIT : I get error after changing code by sqlparamers

 public bool IsClientExist(string valuetocheck)

    {
       .....

        SqlParameter param = new SqlParameter
        {
                ParameterName = "@client",
                Value = valuetocheck,
            };


            cmd.Parameters.Add(param);
            var data = cmd.ExecuteScalar();
             result = (int)data > 0;

            }
        catch (Exception ex)
        {
            MessageBox.Show(ex.StackTrace.ToString() + " " + ex.Message);
        }



        return result;

    }

ERROR:

"The OleDbParameterCollection only accepts non-null OleDbParameter type objects, not SqlParameter objects."

EDIT2 : the problem is hapenning with one database (i try to create new one but same problem), i have other bases to check duplicat value and it's just work fine the code below is work with all databases except this.

public bool IsRecordExist(string valuetocheck)

    {
        bool result = false;
        try
        {
            string strQuery = "SELECT * FROM [" + strFileNamenopath + "] WHERE code = '" + valuetocheck + "'";
            OleDbCommand cmd = new OleDbCommand(strQuery, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
            if (reader.Read())
            {
                result = true;
            }
            reader.Close();

        }
        catch (Exception ex)
        {
            MessageBox.Show("Duplicate value " + ex.Message);
        }

        return result;

    }

2 Answers

0
Rahul On

You should change your query to be a scalar one rather. As well parameterize your query to avoid SQL Injection attack

string strQuery = "SELECT count(*) FROM [" + strFileNamenopath + "] WHERE client = @client";

Use ExecuteScalar() then

OleDbCommand cmd = new OleDbCommand(strQuery, conn);
SqlParameter param  = new SqlParameter 
 {
 ParameterName = "@client",
 Value         = valuetocheck,
 };
 cmd.Parameters.Add(param);
var data = cmd.ExecuteScalar();

Set your flag accordingly

result = data > 0;
0
user943998 On

I found the answer !

the problem is in adding record to database, when i add 'accidentally' a space to the name , so rather to add data like AddData ("New Client") the code was like this AddData (" New Client") and whene comparing the inputs in database will be false "New Client" != " New Client"