<p>I have a control that, upon postback, saves form results back to the database. It populates the values to be saved by iterating through the querystring. So, for the following SQL statement (vastly simplified for the sake of discussion)...</p>

<pre><code>UPDATE MyTable
SET MyVal1 = @val1,
    MyVal2 = @val2
WHERE @id = @id
</code></pre>

<p>...it would cycle through the querystring keys thusly:</p>

<pre><code>For Each Key As String In Request.QueryString.Keys
    Command.Parameters.AddWithValue("@" &amp; Key, Request.QueryString(Key))
Next
</code></pre>

<p>HOWEVER, I'm now running into a situation where, under certain circumstances, some of these variables may not be present in the querystring. If I don't pass along val2 in the querystring, I get an error: <code>System.Data.SqlClient.SqlException: Must declare the scalar value "@val2"</code>.</p>

<p>Attempts to detect the missing value in the SQL statement...</p>

<pre><code>IF @val2 IS NOT NULL
UPDATE MyTable
SET MyVal1 = @val1,
    MyVal2 = @val2
WHERE @id = @id
</code></pre>

<p>... have failed.</p>

<p>What's the best way to attack this? Must I parse the SQL block with RegEx, scanning for variable names not present in the querystring? Or, is there a more elegant way to approach?</p>

<p>UPDATE: Detecting null values in the VB codebehind defeats the purpose of decoupling the code from its context. I'd rather not litter my function with conditions for every conceivable variable that might be passed, or not passed.</p>

[{'answer_id': 65629, 'author': 'Sander Rijken', 'author_id': 5555, 'author_profile': 'https://Stackoverflow.com/users/5555', 'pm_score': 3, 'selected': False, 'text': '<p>First of all, I would suggest against adding all entries on the querystring as parameter names, I\'m not sure this is unsafe, but I wouldn\'t take that chance.</p>\n\n<p>The problem is you\'re calling</p>\n\n<pre><code>Command.Parameters.AddWithValue("@val2", null)\n</code></pre>\n\n<p>Instead of this you should be calling:</p>\n\n<pre><code>If MyValue Is Nothing Then\n    Command.Parameters.AddWithValue("@val2", DBNull.Value)\nElse\n    Command.Parameters.AddWithValue("@val2", MyValue)\nEnd If\n</code></pre>\n'}, {'answer_id': 65635, 'author': 'Gulzar Nazim', 'author_id': 4337, 'author_profile': 'https://Stackoverflow.com/users/4337', 'pm_score': 0, 'selected': False, 'text': '<p>Update: The solution I gave is based on the assumption that it is a stored proc.\n<em>Will giving a default value of Null to the SQL Stored proc parameters work?</em></p>\n\n<p>If it is dynamic sql, always pass the correct number of params, whether it is null or the actual value or specify default values.</p>\n'}, {'answer_id': 65676, 'author': 'user7658', 'author_id': 7658, 'author_profile': 'https://Stackoverflow.com/users/7658', 'pm_score': 0, 'selected': False, 'text': '<p>I like using the <code>AddWithValue</code> method.</p>\n\n<p>I always specify default SQL parameters for the "optional" parameters.  That way, if it is empty, ADO.NET will not include the parameter, and the stored procedure will use it\'s default value.</p>\n\n<p>I don\'t have to deal with checking/passing in DBNull.Value that way.</p>\n'}, {'answer_id': 66698, 'author': 'dansays', 'author_id': 1923, 'author_profile': 'https://Stackoverflow.com/users/1923', 'pm_score': 1, 'selected': True, 'text': '<p>After struggling to find a simpler solution, I gave up and wrote a routine to parse my SQL query for variable names:</p>\n\n<pre><code>Dim FieldRegEx As New Regex("@([A-Z_]+)", RegexOptions.IgnoreCase)\nDim Fields As Match = FieldRegEx.Match(Query)\nDim Processed As New ArrayList\n\nWhile Fields.Success\n    Dim Key As String = Fields.Groups(1).Value\n    Dim Val As Object = Request.QueryString(Key)\n    If Val = "" Then Val = DBNull.Value\n    If Not Processed.Contains(Key) Then\n        Command.Parameters.AddWithValue("@" &amp; Key, Val)\n        Processed.Add(Key)\n    End If\n    Fields = Fields.NextMatch()\nEnd While\n</code></pre>\n\n<p>It\'s a bit of a hack, but it allows me to keep my code blissfully ignorant of the context of my SQL query.</p>\n'}]
['https://Stackoverflow.com/questions/65566', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/1923/']