Error updating a DateTime field

Hi, I'm having trouble updating a DateTime field in my SQL database. Here is what I'm trying to do...I retrieve the existing value in the DateTime field (usually a bum date like 1/1/1900 00:00:00:00), then put it in a variable. Later, depending on some conditions, I'll either update the DateTime field to today's date (which works great) or set it back equal to the existing value from the variable (this one messes up and says "SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM. "). There is a ton more than this but here are the relevant snippets:
<code>
Dim CompDate As DateTime
Dim aComm As SQLCommand
Dim aReader As SQLDataReader
Dim bSQL,bConn As String
bSQL= "SELECT CompleteDate,StatusOfMarkout FROM Tickets WHERE TicketName=" _ & CHR(39) & Trim(Ticket.Text) & CHR(39)
bConn = serverStuff aConn = New SQLConnection(bConn)
aComm = New SQLCommand(bSQL,aConn)
aConn.Open()
result = aComm.ExecuteReader()
'fills controls with data
While result.Read()
CompDate = result("CompleteDate")
PreviousMarkoutStatus.Text = result("StatusOfMarkout")
End While
result.Close()
aConn.Close()
sSqlCmd ="Update OneCallTickets CompleteDate=@.CompleteDate, StatusOfMarkout=@.StatusOfMarkout WHERE TicketFileName=@.TicketFileName"
dim SqlCon as New SqlConnection(serverStuff)
dim SqlCmd as new SqlCommand(sSqlCmd, SqlCon)
If Flag1List.SelectedItem.Value = "No Change" Then
SqlCmd.Parameters.Add(new SqlParameter("@.Flag1", SqlDbType.NVarChar,35))
SqlCmd.Parameters("@.Flag1").Value = PreviousMarkoutStatus.Text
SqlCmd.Parameters.Add(new SqlParameter("@.CompleteDate", SqlDbType.DateTime, 8))
SqlCmd.Parameters("@.CompleteDate").Value = CompDate
Else
SqlCmd.Parameters.Add(new SqlParameter("@.Flag1", SqlDbType.NVarChar,35))
SqlCmd.Parameters("@.Flag1").Value = CurrentStatus.Text
SqlCmd.Parameters.Add(new SqlParameter("@.CompleteDate", SqlDbType.DateTime, 8))
SqlCmd.Parameters("@.CompleteDate").Value = Today()
End If
SqlCon.Open()
SqlCmd.ExecuteNonQuery()
SqlCon.Close()
</code>
Can anybody help me with this? Thanks a bunchIs your CompDate a DateTime or is it a string? #1 make sure it is a DateTime.
You might also try using the Convert function in your procedure to convert the argument to a SQL DateTime and see what happens.|||

Yes, the value I'm reading from the database is 100% a datetime. Could you give me an example of how to convert? Thanks

|||

I think you need the DateDiff function of both VB .NET and SQL Server and also change your data type to SmallDateTime it has less resolution if the seconds are not important. Try the links below for sample code using TimeSpan with DateDiff. Hope this helps.
http://blogs.msdn.com/vbfaq/

http://www.stanford.edu/~bsuter/sql-datecomputations.html

|||Hmm, I guess I don't understand. I read through those links and all I could find was how to calculate the difference between two dates. I couldn't really find an answer in there?|||CONVERT(DATETIME, Your_Date_Field)|||

When I try that here:
SqlCmd.Parameters.Add(new SqlParameter("@.CompleteDate", SqlDbType.DateTime, 8))
SqlCmd.Parameters("@.CompleteDate").Value = CONVERT(DATETIME, CompDate)
I get a Compiler Error Message: BC30684: 'CONVERT' is a type and cannot be used as an expression.
Should I be using it somewhere else?

|||I was assuming you are using a stored procedure to do the work. The CONVERT function is a T_SQL function and should be done inside of a stored procedure or SQL statement.