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!!!!!! 5 3034
Ø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
"Øyvind Isaksen" <oy****@webressurs.no> wrote in message
news:%2*****************@TK2MSFTNGP15.phx.gbl... 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!!!!!!
Test for blanks and then use DBNull.Value as your parameter value. Make
sure that these fields accept null values.
--
Christopher A. Reed
"The oxen are slow, but the earth is patient."
"Øyvind Isaksen" <oy****@webressurs.no> wrote in message
news:%2*****************@TK2MSFTNGP15.phx.gbl... 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!!!!!!
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
> 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
Ø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
"S. Justin Gengo [MCP]" <justin@[no_spam_please]aboutfortunate.com> wrote in
message news:uH**************@TK2MSFTNGP12.phx.gbl... Ø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 "Øyvind Isaksen" <oy****@webressurs.no> wrote in message news:%2*****************@TK2MSFTNGP15.phx.gbl...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!!!!!!
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Andras Gilicz |
last post by:
Hi VB fans
I'm working on a relatively large project in VB6 with about a dozen
forms, including graphs, labels, text boxes, etc. The software itself
is actually a flow simulator with more or...
|
by: Peter Kleiner |
last post by:
Greetings all,
I have a database with two tables: docs and edocs. For each record in
docs there can be zero to unlimited records in edocs. Both tables have
an integer primary key named index. ...
|
by: MacDermott |
last post by:
I have a query, which gathers up information, which is subsequently dumped
into an instance of Excel using recordsetcopy.
For one of the query fields, I have written what should be a pretty...
|
by: hartley_aaron |
last post by:
Hi,
I was trying to blank out (null out?) a field of a record that is a
date/time data type. Here is code excerpt:
Dim D As Database
Dim rsRacks As Recordset
Dim strSQL As String
strSQL...
|
by: Corobori |
last post by:
I am getting an error message "Description: Value cannot be null.
Parameter name: encoder" when performing this instruction:
frmPicView.pic.Image.Save(ms, frmPicView.pic.Image.RawFormat)
Here is...
|
by: Jerome |
last post by:
Hallo,
I know a lot has already been told about date/time fields in a database but
still confuses me, specif when dealing with SQLserver(Express).
It seems that sqlserver only accepts the date in...
|
by: M Skabialka |
last post by:
I am creating my first Visual Studio project, an inventory database. I have
created a form and used written directions to add data from a table to the
form using table adapters, data sets, etc.
...
|
by: Gilles Ganault |
last post by:
Hello
Out of curiosity, is there a smarter, easier way to read data sent by
a form, and save them into a database? I have about 20 fields, and
it'd be easier if I could just use a loop to go...
|
by: squrel |
last post by:
Hello everyone,
I m using some button using toolbar such as Add,Save,View,.... my save button is not working.... it doesnt give me any error but does not save to my database.... or showing in my...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
| |