I can't get a SQL QUery to accept a null for a parameter

I have straight forward Insert Query, which takes values mainly from text boxes, however I am having trouble when the value is null.

eg
cmdP0.Parameters["@.EmpID"].Value=txtEmpNo.Text;

When I run the Query I get...
Message="Parameterized Query '(@.ID int,@.EmpID int,@.Photo nvarchar(260),@.DoB smalldatetime,@.Med' expects parameter @.EmpID, which was not supplied."

I also get this when the parameter can be a string.
I have set the parameter properties so that SourceColumnNullMaping to true (it was set as false - so hoped this might fix it!)

No luck.

Have you tried to set DBNull.Value as value of the parameter, if the text is returned as null?|||

That worked for the strings it took a bit of effort forn integers.

Thanks to someone eles code, which I don't quite understand...

string inputGap = txtGap.Text;
Int32? searchGap = null;
if(!string.IsNullOrEmpty(inputGap)) {
Int32 Gap;
if(Int32.TryParse(inputGap , out Gap))
searchGap = Gap;
}
if(searchGap!=null) {
cmdP0.Parameters["@.MedicalGap"].Value=searchGap;
} else {
cmdP0.Parameters["@.MedicalGap"].Value=DBNull.Value;}

I don't know what Int32? searchGap = null; does

|||

Int32 is a struct (a value type). That means it can't be null. .NET 2.0 adds so called Nullable Types. That means you can set an Int32 null, by using a special syntax. Int32? (with the question mark) means that this is a nullable. It means that this variable can be set to null.

This piece of code is to complex. You can make it a lot easier by doing this:

int value = 0;
if (int.TryParse(txtGap.Text, out value))
cmdP0.Parameters["@.MedicalGap"].Value = value;
else
cmdP0.Parameters["@.MedicalGap"].Value = DBNull.Value;

|||

Thank you

A nice clear explanation and some nice code

Much appreciated, this has been driving me nuts!