473,326 Members | 2,081 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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 5111
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
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...
3
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...
2
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...
15
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...
1
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...
0
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...
6
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...
2
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...
19
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...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.