checking null value in database through sqlquery C#

Question

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();

Mitch Wheat · Accepted Answer · 2013-05-09 08:55:30Z

3

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.

answered May 9, 2013 at 8:55

Mitch Wheat

302k44 gold badges482 silver badges552 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Habib · Accepted Answer · 2013-05-09 08:56:30Z

2

For comparing a field against null use empMiddleName IS NULL

You should use Parameterized queries to prevent SQL injection.

answered May 9, 2013 at 8:56

Habib

224k30 gold badges420 silver badges446 bronze badges

Comments

kgu87 · Accepted Answer · 2013-05-09 08:56:17Z

1

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

Change it to empMiddleName is null .

answered May 9, 2013 at 8:56

kgu87

2,05714 silver badges12 bronze badges

Comments

Rohit Vyas · Accepted Answer · 2013-05-09 08:58:45Z

0

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();

answered May 9, 2013 at 8:58

Rohit Vyas

1,9793 gold badges19 silver badges28 bronze badges

Comments

Sain Pradeep · Accepted Answer · 2013-05-09 09:41:51Z

0

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() + "' ");

answered May 9, 2013 at 9:41

Sain Pradeep

3,1351 gold badge24 silver badges34 bronze badges

Collectives™ on Stack Overflow

checking null value in database through sqlquery C#

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related