469,927 Members | 1,947 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,927 developers. It's quick & easy.

Inserting Date info into Sql Server

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

Jul 21 '05 #1
1 4936
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

Jul 21 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by Rainmaker | last post: by
3 posts views Thread by J. Muenchbourg | last post: by
1 post views Thread by Michael Albanese | last post: by
6 posts views Thread by fniles | last post: by
2 posts views Thread by Serious_Practitioner | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.