CODEWITHSUNDEEP
sqlvb.netsql-server-2012
CodeEngine
CodeEngine

Reputation: 296

Not Updating table when everything right and has null fields. when erase the null fields then it works ?

I have a table (fldAbsentes2) with 6 columns([fldEmployeeID],[fldAbsentDate],[fldAbsentCode] ,[fldRuleViolationWarningType] ,[fldRuleViolationIssueDate] ,[fldLOAEndDate]

When I use the fallowing querystring with sqlcommand it doesnt give me an error but it doesn't update the table.

 multstring = "Update tblAbsences2 " & _
                                "Set fldEmployeeID = '103', " & _
                                "fldAbsentDate =  '1/2/2013', " & _
                                "fldAbsentCode = 'X', " & _
                                "fldRuleViolationWarningType = NULL, " & _
                                "fldRuleViolationIssueDate = NULL, " & _
                                "fldLOAEndDate = NULL " & _
                                "Where (fldEmployeeID ='100') AND " & _
                                "(fldAbsentDate ='1/2/2013') AND " & _
                                "(fldAbsentCode ='NA') AND " & _
                                "(fldRuleViolationWarningType = NULL) AND " & _
                                "(fldRuleViolationIssueDate = NULL) AND " & _
                                "(fldLOAEndDate = NULL)"

If I erase the last three lines (avoiding the where fldname = NULL) And it works. The problem is that the user should be able to update those values too if they are Null. The same thing happens when i use sql Server management studio. Below is also the code on sql managment VS It would only work if I erase the where fldname = NULL. Any help would be appreciated

UPDATE  [DBName].[dbo].[tblAbsences2] 
SET                fldAbsentCode = 'works', fldEmployeeID = '1', fldAbsentDate = '1/2/2013    00:00:00', fldRuleViolationWarningType = NULL, fldRuleViolationIssueDate = NULL, 
                     fldLOAEndDate = NULL
WHERE        (fldEmployeeID = '1') AND (fldAbsentDate = '1/2/2013 00:00:00') AND (fldAbsentCode =   'test') AND (fldRuleViolationWarningType = NULL) AND 
                     (fldRuleViolationIssueDate = NULL) AND (fldLOAEndDate = NULL)

Upvotes: 0

Views: 30

Answers (2)

HLGEM
HLGEM

Reputation: 96600

@dai is correct about IS NULL vice = NULL. That shoudl fix the first part of waht is worng with your query. But without seeing the data, I cannot predict if it will fix everything that is wrong. It coudl also be that you simply don't have any records that meet all of those conditions. You may need to add test records on dev to adequately test the accuracy of a query.

I also wanted to provide a technique to help you understand why a query is going wrong, so you can fix it without having to resort to the Internet.

UPDATE  [DBName].[dbo].[tblAbsences2] 
SET                fldAbsentCode = 'works'
                    , fldEmployeeID = '1'
                    , fldAbsentDate = '1/2/2013    00:00:00'
                    , fldRuleViolationWarningType = NULL
                    , fldRuleViolationIssueDate = NULL, 
                     fldLOAEndDate = NULL
--select * from      [DBName].[dbo].[tblAbsences2]                 
WHERE        (fldEmployeeID = '1') 
                    AND (fldAbsentDate = '1/2/2013 00:00:00') 
                    AND (fldAbsentCode =   'test') 
                    AND (fldRuleViolationWarningType = NULL) 
                    AND (fldRuleViolationIssueDate = NULL) 
                    AND (fldLOAEndDate = NULL)

You will see that I have a select embedded in the update that is commented out. Run just the select part and you will see the results of what you would be affecting with the update. Look at the records to see if they make sense. In this case you can eliminate the last 3 where conditions ( or other suspect conditions for other queries)and see what the values for those fields are in the database. If they are null, then you know there is something wrong with how you are checking for a null. If they are not, then it could be the data doesn't actually give any results with all of these conditions. To check then add some records (in dev only!!) to add the conditions you are looking for and then run the select and see if you get the correct answer.

It is irrelevant to this problem but when checking for the accuracty of a query returning the correct results, you may need to also drop off joins or change them temporarily to left joins to see what is happening with the data that is causing your problem.

You should never create an update or delete query without checking what the select for that query would return. The meaning of the data drives the query syntax, if you don't know exactly what records you are affecting, you cannot know if you have correctly written the query. As you see in this case, the query runs, it doesn't have a syntax error. It is simply not returning the correct results. Whether something passes a syntax check does not mean it does what it should be doing. You need to learn to query by meaning.

Upvotes: 2

Dai
Dai

Reputation: 155418

The operator for NULL comparisons in SQL is IS and not = (similarly IS NOT instead of <> for inequality).

Change your queries to use WHERE column IS NULL instead of WHERE column = NULL.

Upvotes: 3

Related Questions