CODEWITHSUNDEEP
c#asp.netsql-server-2008
Seema
Seema

Reputation: 107

checking null value in database through sqlquery C#

I have entered a user's name that exists in the DB. The name has only first and last name. so the middle name is empty and it is stored in the DB as null. To get the employee id of the user, i have used the following query. but the value of obj after executing the query is null. The first and last names are stored in an array. I have checked their values. Their values are ok.

Can you please tell me why the object is not fetching nay value.

SqlCommand cmm1= new SqlCommand ();
cmm1.CommandText = ("Select empID from empPersonalInfo where empFirstName= '" + arr[0].ToString() + "' and empMiddleName= '" + System.DBNull.Value + "' and empLastName= '" + arr[2].ToString() + "' ");            
cmm1.Connection = conn;
obj = cmm1.ExecuteScalar();

Upvotes: 1

Views: 1597

Answers (5)

Sain Pradeep
Sain Pradeep

Reputation: 3125

You need to check that middle name should be equal null or middle name should be equal to value you have passed in query.

I hope blow query query can full fill your requirement.

cmm1.CommandText = ("Select empID from empPersonalInfo where empFirstName= '" + 
                  arr[0].ToString() + "' and ((empMiddleName IS NULL) or(empMiddleName='"+arr[1].ToString()+"')) and empLastName= '" + 
                  arr[2].ToString() + "' ");

Upvotes: 0

Rohit Vyas
Rohit Vyas

Reputation: 1969

Use SQLParameter object for passing parameters to the query as below:

SqlCommand command = 
        new SqlCommand("SELECT * FROM Users WHERE Username = @Username", conn);

    command.Parameters.Add(new SqlParameter("@Username", "Justin Niessner"));
    obj = command.ExecuteScalar();

Upvotes: 0

Habib
Habib

Reputation: 223402

For comparing a field against null use empMiddleName IS NULL

You should use Parameterized queries to prevent SQL injection.

Upvotes: 2

kgu87
kgu87

Reputation: 2057

empMiddleName= '" + System.DBNull.Value + "' is incorrect. There is no = operator for NULL value.

Change it to empMiddleName is null .

Upvotes: 1

Mitch Wheat
Mitch Wheat

Reputation: 300769

You can't compare NULL using equals '=', use 'IS NULL' instead:

cmm1.CommandText = ("Select empID from empPersonalInfo where empFirstName= '" + 
              arr[0].ToString() + "' and empMiddleName IS NULL and empLastName= '" + 
              arr[2].ToString() + "' ");

BTW, that query really doesn't make a lot of sense. Perhaps :

empMiddleName IS NULL OR empMiddleName =  @somevalue

Plus, please note: you are possibly open to SQL Injection attacks. Use parameters rather than concatenating user input values.

Upvotes: 3

Related Questions