Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 515
  • Last Modified:

NULL Values for Parameters in the WHERE clause of an UPDATE query

I have an update statement along these lines

UPDATE myTable Set Field1 = @Field1
WHERE ProductType = @ProductType AND Location = @Location AND Status = @Status

I originally created this command using the code in Example 1 Below, but it failed if the parameter value for either @Location or @Status was NULL.

I worked around this by creating the Code in Example 2, which builds the WHERE clause based on whether the data value is NULL. However if I'm updating multiple rows at a time, building the Command each time like this feels like it would be inefficient.

So, it this the correct approach or is there a better way?


Example 1 - Fails

Dim intRowsAffected as Integer
Using cmd As New SqlCommand("", SQLConn)
 cmd.commandtext = "UPDATE myTable Set Field1 = @Field1 
WHERE ProductType = @ProductType AND Location = @Location AND Status = @Status"
  SQLConn.Open()
  For each rw as datarow in changes.rows     cmd.parameters.addwithvalue("@Field1",rw,item("Field1"))
cmd.parameters.addwithvalue("@ProductType",rw,item("ProductType", DataRowVersion.Original)))
cmd.parameters.addwithvalue("@Location",rw,item("Location", DataRowVersion.Original)))
cmd.parameters.addwithvalue("@Status",rw,item("Status", DataRowVersion.Original)))
 intRowsAffected = cmd.ExecuteNonQuery
 cmd.Parameters.Clear()
Next
End using

Example 2 - Works

Dim intRowsAffected as Integer
Using cmd As New SqlCommand("", SQLConn)
  SQLConn.Open()
  For each rw as datarow in changes.rows
    cmd.commandtext = "UPDATE myTable Set Field1 = @Field1 
WHERE ProductType = @ProductType"
 If isdbnull(rw,item("Location", DataRowVersion.Original)) then
 cmd.commandtext &= " AND Location IS NULL "
 else
  cmd.commandtext &= " AND Location = @Location "
end if
 If isdbnull(rw,item("Status", DataRowVersion.Original)) then
 cmd.commandtext &= " AND Status IS NULL "
 else
  cmd.commandtext &= " AND Status = @Status "
end if
   cmd.parameters.addwithvalue("@Field1",rw,item("Field1"))
cmd.parameters.addwithvalue("@ProductType",rw,item("ProductType", DataRowVersion.Original)))
cmd.parameters.addwithvalue("@Location",rw,item("Location", DataRowVersion.Original)))
cmd.parameters.addwithvalue("@Status",rw,item("Status", DataRowVersion.Original)))
 intRowsAffected = cmd.ExecuteNonQuery
 cmd.Parameters.Clear()
Next
End using

Open in new window

0
WestcountryBusiness
Asked:
WestcountryBusiness
  • 4
  • 2
  • 2
3 Solutions
 
Philip PinnellCommented:
tryUPDATE myTable Set Field1 = @Field1 WHERE( ProductType = @ProductType OR @ProductType  IS NULL)AND (Location = @Location OR @Location IS NULL)AND (Status = @Status OR @Status IS NULL)
0
 
antonybrahinCommented:
Instead u can make a stored proc to do the updation and pass empty string or null  to the stored proc.
0
 
antonybrahinCommented:
what is the actual error message u r getting?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
WestcountryBusinessAuthor Commented:
Thanks for your feedback.

Andy - If I use the OR option, then an update where (for example) @Location = 'MyBay', would also update any other rows where ProductType & Status match but Location is NULL - and I wouldn't want to update them. I'd only want to update where Location = 'MyBay'

Antony - As stored procedure is an option, but I'd still have to do the test within the stored procedure, wouldn't I?  Is than any more efficient than doing them in the program?

Also I don't get an error.  I just get zero matching records found to update.

I just thought there must be a way because if I were to create a tableadapter & allow it to use Optimistic Concurrency then the Update Command that is automatically created tests for a match on all the original values of all fields retrieved, many of which will have NULL values.  So if that works, why can't I reproduce the functionality myself?
0
 
WestcountryBusinessAuthor Commented:
Problem sorted by creating a function to create the Update Command with all parameters which I call within a loop.  So for each row to be updated I create the Update command, passing the datarow to the function which creates the command needed, then perform the Update.

I'm still not convinced there isn't a better way, but it works & I've moved on.  Thanks for the help guys - just going to share points.
0
 
WestcountryBusinessAuthor Commented:
?
0
 
WestcountryBusinessAuthor Commented:
My comment accepted as solution as it was the action taken to solve problem I was facing
0
 
Philip PinnellCommented:
Thanks
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now