SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Member
    Join Date
    Nov 2011
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    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

    Code:
            Dim LoggedIn As String = User.Identity.Name
            If LoggedIn.Contains("PARTNERS") Then
                Session("LoggedIn") = LoggedIn.Substring(9)
            Else
                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

    Code:
    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)
                    Conn.Open()
                    .ExecuteScalar()
                    Conn.Close()
                End With
                querycommand.Dispose()
            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,
    NickG

  2. #2
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,623
    Mentioned
    4 Post(s)
    Tagged
    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

Bookmarks

Posting Permissions

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