Friday, March 23, 2012

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

No comments:

Post a Comment