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 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 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: 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...
|
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...
|
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...
|
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...
|
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...
|
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....
|
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
|
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...
|
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...
| |