Problem With Adding a Date Field to a Database When It is Null

I am having problems adding a date field to a SQL Server Database from a form in ASP.Net. When I leave the date field blank, it automatically inserts Monday, January 01, 1900. I want it to be null when the expiration date is left blank. Can someone please help me with this?
Here's my code for adding information from the table to the database:
'--------------
' name: Button_Click()
'--------------
Sub Button_Click( s As Object, e As EventArgs )



Dim strConnect As String
Dim objConnect As SQLConnection
Dim strInsert As String
Dim cmdInsert As SqlCommand

'Get connection string from Web.Config
strConnect = ConfigurationSettings.AppSettings("ConnectionString")

objConnect = New SqlConnection(strConnect)

strInsert = "Insert DomainName (ClientID, DomainName, Registrar, ExpirationDate ) Values ( @.ClientID, @.DomainName, @.Registrar, @.ExpirationDate )"
cmdInsert = New SqlCommand( strInsert, objConnect)
cmdInsert.Parameters.Add( "@.ClientID", dropClient.SelectedItem.Value )
cmdInsert.Parameters.Add( "@.DomainName", txtDomainName.Text )
cmdInsert.Parameters.Add( "@.Registrar", txtRegistrar.Text )
cmdInsert.Parameters.Add( "@.ExpirationDate", txtExpirationDate.Text )

objConnect.Open()
cmdINsert.ExecuteNonQuery()
objConnect.Close()

'Display the results "page"
DisplayResults()

End Sub
Here's the code for the form:
<form id="frmDomainNames" method="post" runat="server" onSubmit="return InputIsValid()">


<div align="center">
<table border="0" cellpadding="2" cellspacing="2" width="50%" bgcolor="#330099">
<tr>
<td height="37" colspan="2" align="center" valign="middle" bgcolor="#330099"><font color="white" size="5">Domain Name Information</font></td>
<td> </td>
</tr>

<tr>
<td height="42" align="right" valign="top" bgcolor="#e8e8e8"><font face="MS Sans Serif, Arial" size="2" color="#000000"><strong><nobr>
Client's Name:</nobr></strong></font></td>
<td colspan="2" valign="top" bgcolor="#e8e8e8">
<p>
<asp:dropdownlist id="dropClient" runat="server" />
</p>
</td>
</tr>
<tr>
<td height="42" align="right" valign="top" bgcolor="#e8e8e8"><font face="MS Sans Serif, Arial" size="2" color="#000000"><strong><nobr>
Domain Name:</nobr></strong></font></td>
<td colspan="2" valign="top" bgcolor="#e8e8e8">
<p>
<ASP:TextBox id="txtDomainName" runat="server" TextMode="SingleLine" Columns="30" />

</p>
</td>



<tr>
<td height="42" align="right" valign="top" bgcolor="#e8e8e8"><font face="MS Sans Serif, Arial" size="2" color="#000000"><strong><nobr>
Registrar:</nobr></strong></font></td>
<td colspan="2" valign="top" bgcolor="#e8e8e8">
<p>
<ASP:TextBox id="txtRegistrar" runat="server" TextMode="SingleLine" Columns="30" />
</p>
</td>
</tr>
<tr>
<td height="42" align="right" valign="top" bgcolor="#e8e8e8"><font face="MS Sans Serif, Arial" size="2" color="#000000"><strong><nobr>
Expiration Date:</nobr></strong></font></td>
<td colspan="2" valign="top" bgcolor="#e8e8e8">
<p>
<ASP:TextBox id="txtExpirationDate" runat="server" TextMode="SingleLine" Columns="10" />
</p>
</td>
</tr>

<TR>
<TD>
</TD>
<TD align="center">
<asp:Button
Text="Submit"
OnClick="Button_Click"
Runat="Server" />
</TD>
</TR>
</table>
</form>
</div>

Change
cmdInsert.Parameters.Add( "@.ExpirationDate", txtExpirationDate.Text )
to
cmdInsert.Parameters.Add( "@.ExpirationDate", iif(txtExpirationDate.Text="",dbNull.value,txtExpirationDate.text))

Nick

problem with a date parameter in ASP.Net page

The date format in the 'weekending' textbox is 5/5/2007
Here's what I've got:

With cmd.Parameters
.Add(New SqlParameter("@.SQLweekending", SqlDbType.DateTime,weekending.Text))
the error message is:
"Conversion from string "4/8/2007" to type 'Integer' is not valid."

Then, I tried:
.Add(

New SqlParameter("@.SQLweekending", SqlDbType.DateTime,CDate(weekending.Text)))

but, before I even tried it, it gave me a blue squiggly line, with an error message:
"Error 2 Value of type 'Date' cannot be converted to 'Integer'"

How can I get this to work?

Hi,

What is the data type for yourSQLweekending column in your table? Is it a DateTime column or Integer type? You need a DateTime column for this.

|||Nope - it's datetime also|||

Here is sample in VB.NET that inserts date to a datetime column:

Dim MySQLAsString ="Insert into Dates (id, tDate) values (@.id, @.tDate)"Dim myConnAs SqlConnection =New SqlConnection(ConfigurationManager.ConnectionStrings("forumConnectionString").ConnectionString.ToString())Dim CmdAsNew SQLCommand(MySQL, MyConn)

Cmd.Parameters.Add(

New SqlParameter("@.id", txtid.Text))

Cmd.Parameters.Add(

New SqlParameter("@.tDate",txttDate.Text))

MyConn.Open()

cmd.ExecuteNonQuery()

MyConn.Close()sql

Problem with a date function

Hello All!

I have a table with a date column. I would like to be able to DELETE the rows based on the date column. The condition is 30 days from todays date. So anything older than 30 days from todays date, it will delete those rows.

Any suggesttion the best way to do this. I was thinking of a simple select statement, but can't figure it out.

TIA!!

Rudy

Hi there,

Is your date column of data type DateTime? If so, try something like:

DELETE FROM [Table Name]
WHERE DATEADD(d, -30, GETDATE()) > [Date Column]

What happens is DATEADD(d, -30, GETDATE()) is used to obtain a date that is 30 days from todays date. Then, anything in the table where the entry in the Date Column (which I assume is of data type DateTime) is older than DATEADD(d, -30, GETDATE()), i.e. older than 30 days from today's date, gets deleted.

Hope that helps a bit, but sorry if it doesn't.
|||Thank you! Just what I needed!

problem with convert 2000 server 2000

we want to insert date into table testDate in Datetime or another dataType in field testDate and extract it in hijry Date format And compare it with two Date.
acording to the articles we insert it in to the table in nchar dataType and then we want to extract it in datetime dataType using convert function in sql server :

(( SELECT CONVERT(datetime, ' testDate', 131)
FROM testDate ))
that gives this error:

(( Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string. ))

if we want to insert Date in datetime dataType it enter in gregorian Date format.
while we want Date in hijry format.try this, it may work? You will have to sort the hours and minutes etc...

convert( smalldatetime, ( datename( yy, testdate) + ' '
+ datename( mm, testdate) + ' '
+ datename( dd, testdate)
))

Problem using CAST with Zeroed out Field Values

Hello --
I have a field with date values like: 20050714. When using SQL Server
to convert it to a Date in a View, I use the following:
CAST(MyDate as DateTime)

That works great. The problem is that some rows have the value:
00000000

When it hits one of these, I get a SQL Server Enterprise Manager Error:

Database Server: Microsoft SQL Server
Version: 08.00.0760
Runtime Error: [Microsoft][ODBC SQL Server Driver][SQL Server]The
conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.

Is there a workaround for this? Any assistance greatly appreciated.

RBollinger1) Quit storing dates as strings and numbers. This is the REAL answer.
And kill the guy who did this so he will not screw up the rest of the
database.

2) You did not give us specs; did you want the non-existent date
0000-00-00 to be a NULL or what? The error is exactly right ad shows
that your design has some serious problems. .

CAST (CASE @.my_string_date
WHEN '00000000'
THEN NULL -- or dummy value
ELSE @.my_string_date END AS DATETIME)

A better would be to do an update on the column, get rid of the
mythical dates and then replace that column with a temporal one after
validating the existing dates with a procedure. Don't just mop the
floor; fix the leak!|||robboll (robboll@.hotmail.com) writes:
> I have a field with date values like: 20050714. When using SQL Server
> to convert it to a Date in a View, I use the following:
> CAST(MyDate as DateTime)
> That works great. The problem is that some rows have the value:
> 00000000
> When it hits one of these, I get a SQL Server Enterprise Manager Error:
> Database Server: Microsoft SQL Server
> Version: 08.00.0760
> Runtime Error: [Microsoft][ODBC SQL Server Driver][SQL Server]The
> conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value.
> Is there a workaround for this? Any assistance greatly appreciated.

You can use the isdate() function to check whether a string is a
legal date value or not.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Combining your answer with the second guys answer, I think I will try
something like:

CAST(CASE@.my_string_date
WHEN isdate(my_string_date) = True
THEN NULL
ELSE @.my_string_date END AS DATETIME)

Thanks for your help!
RBollinger|||"isdate(my_string_date) = TRUE " is wrong; there is no Boolean datatype
or constants in SQL. You need a numeric value Did you fix the leak?|||--CELKO-- wrote:
> "isdate(my_string_date) = TRUE " is wrong; there is no Boolean
datatype
> or constants in SQL. You need a numeric value Did you fix the leak?

Yes I did "fix the leak". I ended up removing all invalid date values
as part of the append process -- the recommended solution.

Thanks.