SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Member
    Join Date
    Nov 2011
    0 Post(s)
    0 Thread(s)

    System.Data.SqlTypes.SqlTypeException: SqlDateTime overflow in parameterized query

    Hey everyone,
    I am lost on the exception that I have getting thrown at me right now.

    SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

    What I am trying to do on my site, which is Windows Authenticated on an internal network, is capture the users ID that is logged in, and then depending on which page(s) the user clicks, store them in a database.

    This is the code to check if the users ID has been set, and to add the entry into the database via a parameterized query.

    Initial Setting of Session for the UsersID

            Dim LoggedIn As String = User.Identity.Name
            If LoggedIn.Contains("PARTNERS") Then
                Session("LoggedIn") = LoggedIn.Substring(9)
                Session("LoggedIn") = LoggedIn
            End If
    Afterwards the user can either go into an "Edit.aspx" page or "View.aspx" page.
    Depending on which page they choose, I want to store that in a database that I have called tblView. HEre is the structure of the table.

    ID : Integer : Primary Key, auto-incremented
    InquiryID_fk : Integer : Foreign Key; the inquiry id that is being viewed or edited
    Page : Varchar(10) : Will store either "View" or "Edit" depending on the page entered
    User : varchar(10) : Stores the userID : which is a max of 6 characters
    Date : Date : Want to store the system date
    Time : Time(7) : want to store the system time

    Here is the code in the "View" page to store into the db, which throws the error

    Dim InquiryID As String = Request.QueryString("id")
            Dim InquirySQL As String
            Session("InquiryID") = InquiryID
            If Not Session("LoggedIn") Is Nothing Then
                Dim ViewSQL As String
                ViewSQL = "Insert into tblView(InquiryID_fk, Page, User, Date, Time)Values(@InqID, @Page, @User, @ActDate, @ActTime)"
                Dim querycommand As New SqlCommand
                querycommand.Connection = Conn
                With querycommand
                    .CommandType = CommandType.Text
                    .CommandText = ViewSQL
                    .Parameters.AddWithValue("@InqID", Session("InquiryID"))
                    .Parameters.AddWithValue("@Page", "View")
                    .Parameters.AddWithValue("@User", Session("LoggedIn"))
                    .Parameters.AddWithValue("@ActDate", Date.Today)
                    .Parameters.AddWithValue("@ActTime", TimeOfDay)
                End With
            End If
    When I debug and put break points throughout the code, as well as after the error is thrown and i just highlight over the "Date.Today" or "TimeOfDay" variables, they are displaying the proper date and time from my machine. Does this mean they are out of sync with my server, running SQL Server 2008?

    Any help would be excellent, this has been pretty frustrating.
    Thank you in advance,

  2. #2
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Washington, DC
    4 Post(s)
    0 Thread(s)
    You really should just use a single field here, no need to break it up -- Sql's DateTime stores both just fine. Your TimeOfDay is probably just setting TimeOfDay but not the day, month or year so you end up with something outside of SQL's acceptable range.

Tags for this Thread


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts