StoredProcedure -> Value from the Application

[Viktor Popov]

Hi,

I use ASP.NET/C#/MS SQL and I'm trying to do the following but I find it
very hard!
I have Stored Procedure which saves data from the Application into DataBase.
One of its parameters is @Rooms INT. When A user insert in the WebForm a
value everything is allright because I do this:

cmd.Parameters.Add(new SqlParameter("@ROOMS", SqlDbType.Int));
cmd.Parameters["@ROOMS"].Value = Int32.Parse(RoomsTB.Text.Trim());
where cmd is SqlCommand and RoomsTB is the TextBox from where I take the
value.

When the user doesn't insert value in the TextBox there is an error because
the Stored Procedure doesn't receive value for the Parameter @ROOMS. How
could I send to the SP NULL for this parameter when there isn't value in the
TextBox?

Thank you in advance!

Viktor

 

[S. Justin Gengo]

Viktor,

You have a number of choices here:

1. Use a required field validator (and probably a range validator to make
certain the input is a number). This way a user has to fill in the field.


2. Set your database to accept nulls in the given field. Check if the field
contains a value. If it doesn't don't add the parameter.

if (RoomsTB.Text.Trim() != "")
{
cmd.Parameters.Add(new SqlParameter("@ROOMS", SqlDbType.Int));
cmd.Parameters["@ROOMS"].Value = Int32.Parse(RoomsTB.Text.Trim();
}


3. If the text box is empt assign it an integer that won't be used otherwise
such as a zero.

if (RoomsTB.Text.Trim() == "")
{
cmd.Parameters.Add(new SqlParameter("@ROOMS", SqlDbType.Int));
cmd.Parameters["@ROOMS"].Value =0
}


--
Sincerely,

S. Justin Gengo, MCP
Web Developer / Programmer

www.aboutfortunate.com

"Out of chaos comes order."
Nietzsche

 

[John Saunders]

Hi,

I use ASP.NET/C#/MS SQL and I'm trying to do the following but I find it
very hard!
I have Stored Procedure which saves data from the Application into DataBase.
One of its parameters is @Rooms INT. When A user insert in the WebForm a
value everything is allright because I do this:

cmd.Parameters.Add(new SqlParameter("@ROOMS", SqlDbType.Int));
cmd.Parameters["@ROOMS"].Value = Int32.Parse(RoomsTB.Text.Trim());
where cmd is SqlCommand and RoomsTB is the TextBox from where I take the
value.

When the user doesn't insert value in the TextBox there is an error because
the Stored Procedure doesn't receive value for the Parameter @ROOMS. How
could I send to the SP NULL for this parameter when there isn't value in the
TextBox?

Try this (untested):

string roomsTrimmed = RoomsTB.Text.Trim();
if (roomsTrimmed.Length != 0)
{
cmd.Parameters["@ROOMS"].Value = Int32.Parse(roomsTrimmed);
}
else
{
cmd.Parameters["@ROOMS"].Value = DBNull.Value;
}