Pass null parameter in to parameterized query

Script version:1.0
Language:ASP.net
Rating:
9 (3 votes cast)
Category:Database
Description:If you try to use a parameterizes query like this:

SqlCommand comm = new SqlCommand();
comm.CommandText = "insert into Authors (firstname, surname) values (@firstname,@surname)"
comm.Parameters.AddWithValue("firstname", firstName.Text);
comm.Parameters.AddWithValue("surname", surName.Text);

and the text fields for firstName or surName are left blank, then you will get this message:

The parameterized query '(@firstname nvarchar(100)' expects the parameter '@firstname', which was not supplied.

This is because the value of the text field is 'null' but to insert a null value in to a database, you have to use DBNull.Value.

The easiest way round this is to insert the simple code below
Instructions:Insert this code just after the last comm.Parameters.Add or comm.Parameters.AddWithValue line. This will convert the null values from the object layer to DBNull values that are acceptable to the database.
Code:




Comments

Name: *
Email: (not shown on website)
Website:
Rate this script:
1 2 3 4 5 6 7 8 9 10
worse better
Comment: *
CAPTCHA Image
Change Image
Write the characters in the image above


* = required field

Related Scripts:

ASP.Net Backup and Restore Database Tool

Tags

[ null ] [ database ] [ c# ] [View tag cloud]

Back to main scripts page








Tag Cloud    Submit a script


Latest Scripts Added

jQuery image swap on mouseover (jQuery)
04 January 2012
This simple bit of code allows you to quickly create image rollovers. The code was modified from a version by Sam Colletts on http://webdevel.blogspot.com/2008/04/rollover-images-with-jquery.html ...

replace XML characters in a string using ASP.Net (ASP.Net)
12 December 2011
Sometimes you need to insert a string into an xml document. Before doing this, you need to ensure that your string does not contain any special xml characters. It is possible to use a CDATA section ...

How to remove title tag in ASP.Net (ASP.Net)
14 October 2011
This is not really a script, but a little trick that you can use to remove the title tag in ASP.net. In ASP.net, when the head tag is rendered, the title tag is automatically added. One of the real ...

ASP sort numerical array (ASP)
12 September 2011
This code is similar to the sort array script which sorts an array alphabetical, however it also sorts arrays based on numberical ordering, for example: dim numbers(4) numbers(0) = ...

Force file download in ASP.Net / c# (ASP.Net)
02 September 2011
this script allows you to force browsers to display a file save dialogue for any file type. It contains a string of allowed file types so users cannot change the querystring to downoad your ...