I am building an application to report on-the-job injuries
and incidents. There are a lot of Date fields, some of
which are optional and can be left blank by the user. I
have allowed Nulls on these fields in my SQL Server DB, as
well as in my stored proc.
The problem is that when I submit this data i get an error
that states "Input not in date format" or a similar
statement when there arevnull date fields.
I have a function called chkParam() that will test for the
presence of data on the field and if there is none, will
set the parameter value to "". This works fime for the
text data, but gennerates an error with the date fields.
Any ideas out there as to how I should handle this?
any guidance would be appreciated.
Michael Albanese
Here is a simplified example of the page:
<%@ Page Language="VB" %>
<script runat="server">
' Insert page code here
'
Sub Button1_Click(sender As Object, e As EventArgs)
'ConnString is in appSettings of webConfig
Dim oCN As New SqlConnection(connString)
Dim oCMD As New SqlCommand
oCMD.CommandType = CommandType.StoredProcedure
oCMD.Connection = oCN
oCN.Open()
Dim vname As String
Dim vgender As String
Dim vincome As String
Dim vDate As String
vname = Trim(name.Text)
vgender = Trim(Gender.SelectedValue)
vincome = Trim(income.SelectedValue.ToString)
vDate = (Trim(createDate.Text))
'Name
oCMD.Parameters.Add(New SqlClient.SqlParameter
("@Name", SqlDbType.VarChar, 50))
oCMD.Parameters("@Name").Direction =
ParameterDirection.Input
chkParam(vname.ToString, oCMD.Parameters
("@Name"))
'bDate
oCMD.Parameters.Add(New SqlClient.SqlParameter
("@bDate", SqlDbType.DateTime))
oCMD.Parameters("@bDate").Direction =
ParameterDirection.Input
chkParam(vDate.ToString, oCMD.Parameters
("@bDate"))
'Density
oCMD.Parameters.Add(New SqlClient.SqlParameter
("@Name", SqlDbType.VarChar, 50))
oCMD.Parameters("@Name").Direction =
ParameterDirection.Input
chkParam(vname.ToString, oCMD.Parameters
("@Name"))
oCMD.CommandText = "insert_Test_1"
oCMD.ExecuteNonQuery()
End Sub
'================================================= =====
====================
Function chkParam(ByVal str As String, ByRef
sqlParam As SqlParameter)
' if there is informaton in the field, assign
it to the param, else leave param "null"
If str.ToString = "n/a" Then
sqlParam.Value = "Null"
Else
sqlParam.Value = str
End If
End Function
</script>
<html>
<head>
</head>
<body>
<form runat="server">
<table cellspacing="0" cellpadding="4" border="1">
<tbody>
<tr>
<td>
<strong>Name :</strong></td>
<td>
<asp:TextBox id="TextBox1"
runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>
<strong>Birthday:</strong>
</td>
<td>
<asp:TextBox id="TextBox2"
runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td valign="top">
<strong>Density:</strong></td>
<td>
<asp:RadioButtonList
id="RadioButtonList1" runat="server">
<asp:ListItem
Value="grp1">There's Still Hope</asp:ListItem>
<asp:ListItem Value="grp2">Get
Some Therapy</asp:ListItem>
<asp:ListItem
Value="grp3">Terminal</asp:ListItem>
</asp:RadioButtonList>
</td>
</tr>
<tr>
<td align="middle" colspan="2">
<asp:Button id="Button1"
onclick="Button1_Click" runat="server"
Text="Submit"></asp:Button>
</td>
</tr>
</tbody>
</table>
</form>
</body>
</html>
Here is the Stored Proc:
CREATE PROCEDURE [insert_Test_1]
(
@Name [varchar](50)=Null,
@Gender [varchar](2)=Null,
@Income [varchar](10)=Null,
@CreateDate [datetime]=Null)
AS INSERT INTO [OSHA].[dbo].[Test]
(
[Name],
[Gender],
[Income],
[CreateDate])
VALUES
( @Name,
@Gender,
@Income,
@CreateDate)
GO
Here is the table info:
CREATE TABLE [Test] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Gender] [varchar] (2) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Income] [varchar] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[CreateDate] [datetime] NULL ,
CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO 1 5117
If you want to pass a null value down to the dB, you should use
DBNull.Value
eg:
SqlCmd.Parameters.Add("@UOWDate", SqlDbType.DateTime).Value = DBNull.Value;
José
"Michael Albanese" <ma*******@ci.stamford.ct.us> a écrit dans le message de
news:10****************************@phx.gbl... I am building an application to report on-the-job injuries and incidents. There are a lot of Date fields, some of which are optional and can be left blank by the user. I have allowed Nulls on these fields in my SQL Server DB, as well as in my stored proc.
The problem is that when I submit this data i get an error that states "Input not in date format" or a similar statement when there arevnull date fields.
I have a function called chkParam() that will test for the presence of data on the field and if there is none, will set the parameter value to "". This works fime for the text data, but gennerates an error with the date fields.
Any ideas out there as to how I should handle this?
any guidance would be appreciated.
Michael Albanese
Here is a simplified example of the page: <%@ Page Language="VB" %> <script runat="server">
' Insert page code here '
Sub Button1_Click(sender As Object, e As EventArgs)
'ConnString is in appSettings of webConfig Dim oCN As New SqlConnection(connString)
Dim oCMD As New SqlCommand oCMD.CommandType = CommandType.StoredProcedure oCMD.Connection = oCN oCN.Open()
Dim vname As String Dim vgender As String Dim vincome As String Dim vDate As String
vname = Trim(name.Text) vgender = Trim(Gender.SelectedValue) vincome = Trim(income.SelectedValue.ToString) vDate = (Trim(createDate.Text))
'Name oCMD.Parameters.Add(New SqlClient.SqlParameter ("@Name", SqlDbType.VarChar, 50)) oCMD.Parameters("@Name").Direction = ParameterDirection.Input chkParam(vname.ToString, oCMD.Parameters ("@Name"))
'bDate oCMD.Parameters.Add(New SqlClient.SqlParameter ("@bDate", SqlDbType.DateTime)) oCMD.Parameters("@bDate").Direction = ParameterDirection.Input chkParam(vDate.ToString, oCMD.Parameters ("@bDate"))
'Density oCMD.Parameters.Add(New SqlClient.SqlParameter ("@Name", SqlDbType.VarChar, 50)) oCMD.Parameters("@Name").Direction = ParameterDirection.Input chkParam(vname.ToString, oCMD.Parameters ("@Name"))
oCMD.CommandText = "insert_Test_1" oCMD.ExecuteNonQuery()
End Sub
'================================================= ===== ====================
Function chkParam(ByVal str As String, ByRef sqlParam As SqlParameter) ' if there is informaton in the field, assign it to the param, else leave param "null"
If str.ToString = "n/a" Then sqlParam.Value = "Null" Else sqlParam.Value = str End If
End Function
</script> <html> <head> </head> <body> <form runat="server"> <table cellspacing="0" cellpadding="4" border="1"> <tbody> <tr> <td> <strong>Name :</strong></td> <td> <asp:TextBox id="TextBox1" runat="server"></asp:TextBox> </td> </tr> <tr> <td> <strong>Birthday:</strong> </td> <td> <asp:TextBox id="TextBox2" runat="server"></asp:TextBox> </td> </tr> <tr> <td valign="top"> <strong>Density:</strong></td> <td> <asp:RadioButtonList id="RadioButtonList1" runat="server"> <asp:ListItem Value="grp1">There's Still Hope</asp:ListItem> <asp:ListItem Value="grp2">Get Some Therapy</asp:ListItem> <asp:ListItem Value="grp3">Terminal</asp:ListItem> </asp:RadioButtonList> </td> </tr> <tr> <td align="middle" colspan="2"> <asp:Button id="Button1" onclick="Button1_Click" runat="server" Text="Submit"></asp:Button> </td> </tr> </tbody> </table> </form> </body> </html>
Here is the Stored Proc:
CREATE PROCEDURE [insert_Test_1] ( @Name [varchar](50)=Null, @Gender [varchar](2)=Null, @Income [varchar](10)=Null, @CreateDate [datetime]=Null)
AS INSERT INTO [OSHA].[dbo].[Test] ( [Name], [Gender], [Income], [CreateDate])
VALUES ( @Name, @Gender, @Income, @CreateDate) GO
Here is the table info: CREATE TABLE [Test] ( [ID] [int] IDENTITY (1, 1) NOT NULL , [Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Gender] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Income] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [CreateDate] [datetime] NULL , CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED ( [ID] ) ON [PRIMARY] ) ON [PRIMARY] GO This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Rainmaker |
last post by:
Greetings,
I have not been able to find the documentation that will allow me to
insert
php code inside the html code in the $message block in the mail() function.
Using the example in the help...
|
by: J. Muenchbourg |
last post by:
while inserting new records into SQL, i'm using the folloinwg
sqlstatement>
Dim MySQL as string = "Insert into roster (pname, pnotes, thedate)
values (@pname, @pnotes, @thedate)"
the sql...
|
by: Rick |
last post by:
How do I go about doing this? Basically I have a field where the user
inputs a doctors name, a field where the user inputs the doctors bio, and
then a picturebox where they select an image from...
|
by: Khurram |
last post by:
I have a problem while inserting time value in the datetime Field.
I want to Insert only time value in this format (08:15:39) into the SQL
Date time Field.
I tried to many ways, I can extract...
|
by: Michael Albanese |
last post by:
I am building an application to report on-the-job injuries
and incidents. There are a lot of Date fields, some of
which are optional and can be left blank by the user. I
have allowed Nulls on...
|
by: Niketa Mahana |
last post by:
Hi,
We are in the process of making product in windows forms that is localized
presently for all european countries.We are supposed to support all eastern
and western european date , time and...
|
by: fniles |
last post by:
I am using VB.NET 2003 and SQL Server 2000.
I have a table with a datetime column type. When inserting into the table
for that column, I set it to Date.Now.ToString("T")
, which is something like...
|
by: Serious_Practitioner |
last post by:
Hi, and thank you in advance for any assistance.
I would like to use a script to determine if a date meets a condition, and,
if it does, then create a table row with a couple of cells, and then...
|
by: RP |
last post by:
I have a DateTimePicker with format dd-MM-yyyy. While attempting to
insert this date in SQL Server Date column, following exception is
thrown:
The conversion of a char data type to a datetime...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
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: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
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: 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,...
| |