p_QueueID is defined in your SP as a VARCHAR which does not allow NULL values.
So the chances are that the value you are loading from your drop down list is
null
because the user has not selected an item yet:
m_indexData.SelectedQueue = ddlQueue.SelectedItem.Value;
If so, it will pass the nulkl value to SQL and SQL will not accept this as a value for your SP.
Either allow NULL values, set a DEFAULT value, or check for nulls in your C# code.
That's not quite accurate. NULL is perfectly valid value for VARCHAR parameter. Consider the following:
CREATE PROCEDURE NullTest @sometext VARCHAR(100) AS
BEGIN
IF @sometext IS NULL PRINT 'Value is null'
ELSE PRINT 'Value is not null'
END;
EXEC NullTest @sometext=null
EXEC NullTest @sometext=''
EXEC NullTest @sometext='A'
Yes - but now try calling that from C#:
using (SqlConnection con = new SqlConnection(strConnect))
{
con.Open();
using (SqlCommand cmd = new SqlCommand("NullTest", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@sometext", "A");
using (SqlDataReader read = cmd.ExecuteReader())
{
while (read.Read())
{
Console.WriteLine(read[0]);
}
}
}
using (SqlCommand cmd = new SqlCommand("NullTest", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@sometext", "");
using (SqlDataReader read = cmd.ExecuteReader())
{
while (read.Read())
{
Console.WriteLine(read[0]);
}
}
}
using (SqlCommand cmd = new SqlCommand("NullTest", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@sometext", null);
using (SqlDataReader read = cmd.ExecuteReader())
{
while (read.Read())
{
Console.WriteLine(read[0]);
}
}
}
}
You will get:
Value is not null
Value is not null
A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll
Because the framework doesn't create the NULL parameter.