CODEWITHSUNDEEP
c#mysqlparameters
DeMama
DeMama

Reputation: 1202

Mysql multiple addwithvalue: one returned value is enough

I have a query that has multiple addwithvalue values

        IDCheck.CommandText = "SELECT * FROM cloudposgebruikers WHERE id = @id AND licentie1hc = @lc1 AND licentie2hc = @lc2"
        + " AND licentie3hc = @lc3 AND licentie4hc = @lc4 AND licentie5hc = @lc5 AND licentie6hc = @lc6 AND licentie7hc = @LC7 AND licentie8hc = @lc8"
        + " AND licentie9hc = @lc9 AND licentie10hc = @lc10";
        IDCheck.Parameters.AddWithValue("@id", licenseid);
        IDCheck.Parameters.AddWithValue("lc1", GetId);
        IDCheck.Parameters.AddWithValue("lc2", GetId);
        IDCheck.Parameters.AddWithValue("lc3", GetId);
        IDCheck.Parameters.AddWithValue("lc4", GetId);
        IDCheck.Parameters.AddWithValue("lc5", GetId);
        IDCheck.Parameters.AddWithValue("lc6", GetId);
        IDCheck.Parameters.AddWithValue("lc7", GetId);
        IDCheck.Parameters.AddWithValue("lc8", GetId);
        IDCheck.Parameters.AddWithValue("lc9", GetId);
        IDCheck.Parameters.AddWithValue("lc10", GetId);

Of course, this will compare ALL these values and only return true if all values are present

Let's say only "licentie5hc" and "id" matches => return true

or let's say only "id" and licentie1hc" matches.. => return true

is this possible ? I know i can use different query's but i just need "id" and one of the "licentie x hc" parameters to match...

Upvotes: 0

Views: 253

Answers (2)

rs.
rs.

Reputation: 27467

Change your query to use OR instead of AND and you don't have to use different parameters for same value,try this, 

IDCheck.CommORText = "SELECT * FROM cloudposgebruikers WHERE id = @id OR licentie1hc = @lc OR licentie2hc = @lc"
        + " OR licentie3hc = @lc OR licentie4hc = @lc OR licentie5hc = @lc OR licentie6hc = @lc OR licentie7hc = @LC OR licentie8hc = @lc"
        + " OR licentie9hc = @lc OR licentie10hc = @lc";
        IDCheck.Parameters.AddWithValue("@id", licenseid);
        IDCheck.Parameters.AddWithValue("@lc", GetId);

Upvotes: 2

Mike C.
Mike C.

Reputation: 3114

Try this:

IDCheck.CommandText = "SELECT * FROM cloudposgebruikers 
    WHERE id = @id AND (licentie1hc = @lc1 OR licentie2hc = @lc2 
        OR licentie3hc = @lc3 OR licentie4hc = @lc4 OR licentie5hc = @lc5 
        OR licentie6hc = @lc6 OR licentie7hc = @LC7 OR 
        licentie8hc = @lc8 OR licentie9hc = @lc9 OR licentie10hc = @lc10)";

which equates to:

SELECT * FROM TABLE
WHERE id=@id AND(lic1=@lic1 OR [email protected] lic10=@lc10);

NOTE: I understand that you probably cannot change your database structure, but having ten columns as you do, indicates that your database could benefit from some refactoring.

Upvotes: 2

Related Questions