Save blank fields in database (date and integer) - Error

[Øyvind Isaksen]

I have a page with an optional integer-field, and one asp:calendar control.
I use a stored procedure to save the data in SQL Server.
When all fields contains data, the code works great! But if the user dont
fill in the optional "price-field" (integer value), or if the user dont
choose a date in the asp:calendar control, I get the message "Input string
was not in a correct format".

How do I save "Null" value if the price-field is blank, and how do I save
"Null" value if a date in the calendar is NOT choosen?


----------------------------------
Stored procedure:
---------------------------------

CREATE PROCEDURE spSaveProject
(
@title varchar(512),
@price int,
@date smalldatetime = null,
)
AS
insert into tblProject (title,price,date) values (@title,@price,@date)
GO


-------------------------
Code:
-------------------------

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnSave.Click
Dim conn As New SqlConnection(variables.ConnString)
Dim cmd As New SqlCommand("spSaveProject", conn)
cmd.CommandType = CommandType.StoredProcedure

Dim parTitle As New SqlParameter("@title", Data.SqlDbType.VarChar,
512)
parTitle.Value = Me.txtTitle.Text.ToString
cmd.Parameters.Add(parTitle)

cmd.Parameters.Add(New SqlParameter("@price", Data.SqlDbType.Int))
cmd.Parameters("@price").Value = Me.txtPrice.Text

cmd.Parameters.Add(New SqlParameter("@date",
Data.SqlDbType.SmallDateTime))
cmd.Parameters("@date").Value = Me.calDate.SelectedDate

cmd.Connection.Open()
cmd.ExecuteNonQuery()
cmd.Connection.Close()
cmd.Dispose()
conn.Dispose()
End Sub


THANK YOU!!!!!!

 

[S. Justin Gengo [MCP]]

Øyvind,

When using a stored procedure it's as simple as not setting the value. Or
alternatively you may specifically set the value to NULL:

By the way there is a shorter way to set your parameters instead of:

cmd.Parameters.Add(New SqlParameter("@date", Data.SqlDbType.SmallDateTime))
cmd.Parameters("@date").Value = Me.calDate.SelectedDate

Use:

'---When no date has been selected in the calendar then it returns:
"1/1/0001 12:00:00 AM"
If Not Me.calDate.SelectedDate > New Date(1, 1, 1) Then
cmd.Parameters.Add("@date", Data.SqlDbType.SmallDateTime).Value =
Me.calDate.SelectedDate
End If

'---Checking for length of string is more efficient than checking against
"".
If Me.txtPrice.Text.Length > 0 Then
cmd.Parameters.Add("@price", Data.SqlDbType.Int).Value =
Me.txtPrice.Text.Trim
End If

'---The alternative specifying the null (but this is not necessary)
If Me.txtPrice.Text.Length > 0 Then
cmd.Parameters.Add("@price", Data.SqlDbType.Int).Value =
Me.txtPrice.Text.Trim
Else
cmd.Parameters.Add("@price", Data.SqlDbType.Int).Value = DBNull.Value
End If


--
Sincerely,

S. Justin Gengo, MCP
Web Developer / Programmer

www.aboutfortunate.com

"Out of chaos comes order."
Nietzsche

 

[Christopher Reed]

Test for blanks and then use DBNull.Value as your parameter value. Make
sure that these fields accept null values.

 

[Alec MacLean]

You're nearly there already:

....
@price int = NULL, -- Add the NULL for the default value (your
table design must allow this)
@date smalldatetime = NULL -- (Remove the trailing comma)
)
AS ...

If you're only going to allow an int for the price, cast the value of the
textbox to the int datatype. (Shown below). Prices might be decimal
fractions (e.g. 10.75), in which case you might want to consider using the
money, smallmoney, float, or decimal datatypes (depending on how accurately
you need it stored).

To check if you need to use NULL in the sproc, you can do this in a couple
of ways;
1. Add System.DBNull.Value as the param value, or
2. With the default values in the sproc set as null (as shown above), don't
add the param at all to make the sproc use the default.

Both techniques are demonstrated below (using 1.1 framework):

'...
cmd.Parameters.Add(New SqlParameter("@price", Data.SqlDbType.Int))

'Trim the textbox in case of whitespaces being present
If Me.txtPrice.Text.Trim.Length = 0 Then
'Add NULL as the para value
cmd.Parameters("@price").Value = System.DBNull.Value
Else
'Add the real value
cmd.Parameters("@price").Value = CInt(Me.txtPrice.Text)
End If

'If user hasn't selected a date, the calendar returns midday time value.
'If so, don't bother adding param, the sproc will use the default value
(NULL) for that column.
If Me.calDate.SelectedDate <> #12:00:00 AM# Then
cmd.Parameters.Add(New SqlParameter("@date",
Data.SqlDbType.SmallDateTime))
cmd.Parameters("@date").Value = Me.calDate.SelectedDate
End If
'...


Note that you might also want to consider some validation of the textbox
before trying to add it's content as a param, so you don't end up trying to
convert alpha chars to numbers. Regular expressions can help here, either
through the REGEX class, or try the regular expression validator control.

Hope that helps.

Al

 

[Alec MacLean]

To check if you need to use NULL in the sproc, you can do this in a couple
of ways;

Sorry, that should have read:

To make the sproc use NULL, you cand do this in a couple of ways;


Al

 

[S. Justin Gengo [MCP]]

Øyvind,

Just a quick correction to the length test on the price text box.
I forgot to Trim off spaces in the check but did trim them when adding the
value to the sql parameter:

If txtPrice.Text.Trim.Length > 0 Then


--
Sincerely,

S. Justin Gengo, MCP
Web Developer / Programmer

www.aboutfortunate.com

"Out of chaos comes order."
Nietzsche