Inserting Date info into Sql Server

  • Thread starter Thread starter Michael Albanese
  • Start date Start date
M

Michael Albanese

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
 
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 said:
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
Click to expand...
 
Back
Top