Solved

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

Posted on 2010-09-17
8
504 Views
Last Modified: 2012-05-10
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
Comment
Question by:WestcountryBusiness
  • 4
  • 2
  • 2
8 Comments
 
LVL 13

Assisted Solution

by:Philip Pinnell
Philip Pinnell earned 125 total points
ID: 33699675
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
 
LVL 4

Assisted Solution

by:antonybrahin
antonybrahin earned 125 total points
ID: 33699682
Instead u can make a stored proc to do the updation and pass empty string or null  to the stored proc.
0
 
LVL 4

Expert Comment

by:antonybrahin
ID: 33699685
what is the actual error message u r getting?
0
 
LVL 1

Author Comment

by:WestcountryBusiness
ID: 33699788
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 1

Accepted Solution

by:
WestcountryBusiness earned 0 total points
ID: 34018708
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
 
LVL 1

Author Comment

by:WestcountryBusiness
ID: 34018722
?
0
 
LVL 1

Author Closing Comment

by:WestcountryBusiness
ID: 34049728
My comment accepted as solution as it was the action taken to solve problem I was facing
0
 
LVL 13

Expert Comment

by:Philip Pinnell
ID: 34050220
Thanks
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now