C# how to pass user input to a parameter in where clause

Question

I want to pass an user input to a where clause in a method. The method has sql query and it uses parameter, but it seems like the parameter is not passed to the query. (I debugged and saw it does not go into the while loop. My code is below:

    Console.WriteLine("Enter your name: ");
    string name = Console.ReadLine();
    string prm = "\"" + name + "\"";  // Doublequote a string

      //execute method
      CheckCustomer(prm);



    private static string CheckCustomer(string cusName)
    {
        string cust = "null";

        try
        {
            Console.WriteLine("\nChecking custoemr...\n");
            // Sql Select Query
            string sql = "SELECT * FROM Customer WHERE CustomerName = @CusName";
            SqlCommand cmd = new SqlCommand(sql, sqlConnection);
            cmd.Parameters.AddWithValue("@CusName", cusName);
            SqlDataReader dr;
            dr = cmd.ExecuteReader();

            string strCusname = "Customer Name Found";
            Console.WriteLine("{0}", strCusname.PadRight(25));
            Console.WriteLine("==============================");

            while (dr.Read())
            {
                ////reading from the datareader

               cust = dr["CustomerName"].ToString();

            }
            dr.Close();
            return cust;

        }
        catch (SqlException ex)
        {
            // Display error
            Console.WriteLine("Error: " + ex.ToString());
            return null;
        }
    }

When I execute CheckCustomer() without the where clause, it works perfect. However, once I add a parameter, does not go inside while loop; it goes to dr.Close(); directly.

What is wrong with this code?

are you passing in the name to method: CheckCustomer? Seems like that method is empty.. which means your value is empty when inside that method. You can use if/else to make sure its not null — Jawad, Dec 08 '19 at 22:03
@Jawad Sorry I forgot to put ```prm``` as parameter to ```CheckCustomer```. I edited the post please check. Even though I put it in the method, it does not work. — qoDoq, Dec 08 '19 at 22:08
If you use parameters you should **not** put quotes or apostrophes around the parameter value unless you are actually searching for a quote or apostrophe in the value. I suspect you want `CheckCustomer(name)`, not `CheckCustomer(prm)`. — Dour High Arch, Dec 08 '19 at 22:15
@qoDoq Could you please give us some of `CustomerName` column values example, and also provide the actual used database datatype (with exact length). — iSR5, Dec 08 '19 at 22:28

score 0 · Answer 1 · answered Dec 08 '19 at 22:12

0

To check for nulls in SQL server you use "is null" instead of "where field = null"

if you tried the query in sql server management studio u will not get any result

since string cust = "null"; that means ur code checks for customerName = null, but as i stated that this is not the right way to check for null and this query will not return any result, and since there is no result that means dr.Read() will evaluate to false and the while loop won't be executed

answered Dec 08 '19 at 22:12

Mostafa Mohamed Ahmed

616
3
11

I put ```string cust = "null";``` because I thought I need to put some default value to ```cust``` , and if it does not return anything I will get ```null```. I wanted to use the return value for the other method to check if a customer exista in the table. If the customer does not exist, insert the info to the customer table. – qoDoq Dec 08 '19 at 22:37

score 0 · Answer 2 · answered Dec 08 '19 at 22:20

You don't need to wrap the string value in quote. You can remove this line, since SqlParameter will handle that for you.

string prm = "\"" + name + "\""; // Doublequote a string

Also, if you want your query to support optional null values (i.e. where NULL implies that you DO NOT want to filter on customer name then you can simpy do:

SELECT * FROM Customer WHERE CustomerName = ISNULL(@CusName, CustomerName)

In your parameter section you can do something like:

cmd.Parameters.AddWithValue("@CusName", string.IsNullOrWhiteSpace(cusName) ? DbNull.Value: cusName);

If you don't want to allow nulls then you can leave the SQL query as-is as a throw a new ArgumentNullException at the top of your query method (i.e. add a guard clause):

if (string.IsNullOrWhiteSpace(CustomerName)) throw new ArgumentNullException(nameof(CustomerName));

Your query appears to be searching for the first customer with matching name. In that case you should probably add a "TOP 1" to avoid needless overhead:

SELECT TOP 1 * FROM Customer WHERE CustomerName = ISNULL(@CusName, CustomerName)

Thank you for the answer. I put ```ISNULL(@CusName, CustomerName)``` in the where clause and it worked. I did not change anything except for that. I do not get why this made it work because the parameter is not null. — qoDoq, Dec 08 '19 at 22:39

score 0 · Answer 3 · answered Dec 11 '19 at 08:22

Console.WriteLine("Enter your name: ");
    string name = Console.ReadLine();
    string prm = "\"" + name + "\"";  // Doublequote a string

      //execute method
      CheckCustomer(prm);



    private static string CheckCustomer(string cusName)
    {
        string cust = "null";

        try
        {
            Console.WriteLine("\nChecking custoemr...\n");
            // Sql Select Query
            string sql = "SELECT * FROM Customer WHERE CustomerName = @CusName";
            SqlCommand cmd = new SqlCommand(sql, sqlConnection);
            cmd.Parameters.AddWithValue("@CusName", cusName);
            SqlDataReader dr;
            dr = cmd.ExecuteReader();

            string strCusname = "Customer Name Found";
            Console.WriteLine("{0}", strCusname.PadRight(25));
            Console.WriteLine("==============================");

            while (dr.Read())
            {
                ////reading from the datareader

               cust = dr["CustomerName"].ToString();

            }
            dr.Close();
            return cust;

        }
        catch (SqlException ex)
        {
            // Display error
            Console.WriteLine("Error: " + ex.ToString());
            return null;
        }
    }

try this.

C# how to pass user input to a parameter in where clause

3 Answers3