Date and timestamps error : The Official Microsoft ASP.NET Forums

Reply

Date and timestamps error

Jan 30, 2013 09:17 AM|LINK

Hi guys ,

I am trying to import a excel file into my database and i have checked the date and time stamps in the database

based on that have made the template and still i get an error please assist

Server Error in '/xxx' Application.
 String was not recognized as a valid DateTime.
 Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
 
 Exception Details: System.FormatException: String was not recognized as a valid DateTime.
 
 Source Error:
 
 Line 100:    cmd.Parameters.Add("@opentime", SqlDbType.DateTime)
 Line 101:    cmd.Parameters("@opentime").Value = GridView2.Rows.Item(i).Cells(4).Text
 Line 102:    cmd.ExecuteNonQuery()
 Line 103:    If i = GridView2.Rows.Count - 1 Then
 Line 104:    Response.Redirect("dispatcher.aspx")
 
 [FormatException: String was not recognized as a valid DateTime.]
    System.DateTimeParse.Parse(String s, DateTimeFormatInfo dtfi, DateTimeStyles styles) +2291962
    System.DateTime.Parse(String s, IFormatProvider provider) +26
    System.Convert.ToDateTime(String value, IFormatProvider provider) +56
    System.String.System.IConvertible.ToDateTime(IFormatProvider provider) +10
    System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider) +2560945
    System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType) +896
 
 [FormatException: Failed to convert parameter value from a String to a DateTime.]
    System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType) +943
    System.Data.SqlClient.SqlParameter.GetCoercedValue() +29
    System.Data.SqlClient.SqlParameter.Validate(Int32 index, Boolean isCommandProc) +97
    System.Data.SqlClient.SqlCommand.BuildParamList(TdsParser parser, SqlParameterCollection parameters) +166
    System.Data.SqlClient.SqlCommand.BuildExecuteSql(CommandBehavior behavior, String commandText, SqlParameterCollection parameters, _SqlRPC& rpc) +253
    System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1005
    System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +132
    System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +149
    System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135
    dispatcher.Button2_Click(Object sender, EventArgs e) in c:\inetpub\wwwroot\xxx\dispatcher.aspx.vb:102
    System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105
    System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107
    System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
    System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
    System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
    System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1746

Reply

Re: Date and timestamps error

Jan 30, 2013 11:21 AM|LINK

Hi,

The error seems to be coming from the "@opentime" variable and the data for that variable is coming from here "GridView2.Rows.Item(i).Cells(4).Text".

Try converting it to datetime using Convert.ToDateTime(GridView2.Rows.Item(i).Cells(4).Text)

Thanks,

Marked as answer by Rashaad786 on Feb 12, 2013 12:21 PM

Reply

Re: Date and timestamps error

Jan 30, 2013 11:40 AM|LINK

hi sorry i still get the same error

here is the code please advice what can i fix here :S

Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
        For i = 0 To GridView2.Rows.Count - 1

            GridView2.Rows.Item(i).Cells(11).Text = usrnamelbl.text

            GridView2.Rows.Item(i).Cells(13).Text = Now()
            Dim connection As String
            connection = GetConnString()
            cn = New SqlClient.SqlConnection(connection)
            cn.Open()
            cmd = New SqlClient.SqlCommand("SELECT agentname FROM [dailytickets] WHERE callid=@tktnumber", cn)
            cmd.Parameters.Add("@tktnumber", SqlDbType.VarChar)
            cmd.Parameters("@tktnumber").Value = GridView2.Rows.Item(i).Cells(5).Text
            dr = cmd.ExecuteReader()
            If dr.Read = 0 Then
                cn.Close()
                cn.Open()
                cmd = New SqlClient.SqlCommand("INSERT INTO [dailytickets] (agentname, callid, username, country, contact, dispatcher, status, title, submitdate, site, dispatchtime, resolution, opentime)VALUES (@name,@tktnumber,@usrname,@region,@contact,@dispatcher,@status,@summary,@submitdate,@site,@dispatchtime,@resolution,@opentime)", cn)
                cmd.Parameters.Add("@name", SqlDbType.VarChar)
                cmd.Parameters("@name").Value = GridView2.Rows.Item(i).Cells(10).Text
                cmd.Parameters.Add("@tktnumber", SqlDbType.VarChar)
                cmd.Parameters("@tktnumber").Value = GridView2.Rows.Item(i).Cells(5).Text
                cmd.Parameters.Add("@usrname", SqlDbType.VarChar)
                cmd.Parameters("@usrname").Value = GridView2.Rows.Item(i).Cells(0).Text
                cmd.Parameters.Add("@region", SqlDbType.VarChar)
                cmd.Parameters("@region").Value = GridView2.Rows.Item(i).Cells(6).Text
                cmd.Parameters.Add("@contact", SqlDbType.VarChar)
                cmd.Parameters("@contact").Value = GridView2.Rows.Item(i).Cells(2).Text
                cmd.Parameters.Add("@dispatcher", SqlDbType.VarChar)
                cmd.Parameters("@dispatcher").Value = GridView2.Rows.Item(i).Cells(11).Text
                cmd.Parameters.Add("@status", SqlDbType.VarChar)
                cmd.Parameters("@status").Value = GridView2.Rows.Item(i).Cells(3).Text
                cmd.Parameters.Add("@summary", SqlDbType.VarChar)
                cmd.Parameters("@summary").Value = GridView2.Rows.Item(i).Cells(7).Text
                cmd.Parameters.Add("@submitdate", SqlDbType.DateTime)
                cmd.Parameters("@submitdate").Value = GridView2.Rows.Item(i).Cells(12).Text
                cmd.Parameters.Add("@site", SqlDbType.VarChar)
                cmd.Parameters("@site").Value = GridView2.Rows.Item(i).Cells(8).Text
                cmd.Parameters.Add("@dispatchtime", SqlDbType.DateTime)
                cmd.Parameters("@dispatchtime").Value = GridView2.Rows.Item(i).Cells(13).Text
                cmd.Parameters.Add("@resolution", SqlDbType.VarChar)
                cmd.Parameters("@resolution").Value = GridView2.Rows.Item(i).Cells(9).Text
                cmd.Parameters.Add("@opentime", SqlDbType.DateTime)
                cmd.Parameters("@opentime").Value = (GridView2.Rows.Item(i).Cells(4).Text)
                cmd.ExecuteNonQuery()
                If i = GridView2.Rows.Count - 1 Then
                    Response.Redirect("dispatcher.aspx")
                End If
            End If

        Next

Reply

JammoD

Member

242 Points

49 Posts

Re: Date and timestamps error

Jan 30, 2013 01:47 PM|LINK

Have you got an example of the data that is in that column of the GridView?

This will help me determine the formatting.

Reply

Rashaad786

Member

6 Points

16 Posts

Re: Date and timestamps error

Jan 30, 2013 02:10 PM|LINK

Hi here with the example data with the fields

and the fields in the excel file are

emailid
contact
status
opentime
callid
country
title
site
resolution
resolution
dispatcher   submitdate dispatchtime

username

emailid

contact

status

opentime

callid

country

title

site

resolution

dispatcher

submitdate

dispatchtime

testuser

xxxxxxxxxxxxx

Open

1/11/2013

CALL3453222

London

no access

`S-

access given

{"(28/01/13 12:45:52 xxxxxxxxxx (Resolution))", "This will not be possible in a short while as already written arrange nwe laptop!!!", , "(28/01/13 13:03:19 HELPDESK xxxxxxxxxxxxxxxx (Rejected from CAL

Edited by Rashaad786 on Jan 30, 2013 02:13 PM
Edited by Rashaad786 on Jan 30, 2013 02:15 PM

Reply

JammoD

Member

242 Points

49 Posts

Re: Date and timestamps error

Jan 30, 2013 02:37 PM|LINK

Can you try adding "GridView2.Rows.Item(i).Cells(4).Text.ToString("yyyy-MM-dd")

Marked as answer by Rashaad786 on Feb 12, 2013 12:20 PM

Reply

Rashaad786

Member

6 Points

16 Posts

Re: Date and timestamps error

Jan 30, 2013 03:07 PM|LINK

pecified argument was out of the range of valid values.
Parameter name: index

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.ArgumentOutOfRangeException: Specified argument was out of the range of valid values.
Parameter name: index

this is what i get now sir

Reply

Rashaad786

Member

6 Points

16 Posts

Re: Date and timestamps error

Jan 30, 2013 03:12 PM|LINK

[CODE]

Imports System.Data
Imports System.Data.OleDb
Imports System.IO
Imports System.Web.Configuration
Partial Class dispatcher
    Inherits System.Web.UI.Page
    Public cn As SqlClient.SqlConnection
    Public dr As SqlClient.SqlDataReader
    Public cmd As SqlClient.SqlCommand
    Public myname As String
    Public password As String
    Public role As String
    Public name As String
    Public Shared Function GetConnString() As String

        Return WebConfigurationManager.ConnectionStrings("trackerconnection").ConnectionString

    End Function

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim connectionString As String = ""
        If FileUpload1.HasFile Then
            Dim fileName As String = Path.GetFileName(FileUpload1.PostedFile.FileName)
            Dim fileExtension As String = Path.GetExtension(FileUpload1.PostedFile.FileName)
            Dim fileLocation As String = Server.MapPath("~/App_Data/" & fileName)
            FileUpload1.SaveAs(fileLocation)

            'Check whether file extension is xls or xslx

            If fileExtension = ".xls" Then
                connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & fileLocation & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=2"""
            ElseIf fileExtension = ".xlsx" Then
                connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & fileLocation & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=2"""
            End If

            'Create OleDB Connection and OleDb Command

            Dim con As New OleDbConnection(connectionString)
            Dim cmd As New OleDbCommand()
            cmd.CommandType = System.Data.CommandType.Text
            cmd.Connection = con
            Dim dAdapter As New OleDbDataAdapter(cmd)
            Dim dtExcelRecords As New DataTable()
            con.Open()
            Dim dtExcelSheetName As DataTable = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
            Dim getExcelSheetName As String = dtExcelSheetName.Rows(0)("Table_Name").ToString()
            cmd.CommandText = "SELECT * FROM [" & getExcelSheetName & "]"
            dAdapter.SelectCommand = cmd
            dAdapter.Fill(dtExcelRecords)
            con.Close()
            GridView2.DataSource = dtExcelRecords
            GridView2.DataBind()
        End If

    End Sub

    Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
        For i = 0 To GridView2.Rows.Count - 1

            GridView2.Rows.Item(i).Cells(11).Text = usrnamelbl.text

            GridView2.Rows.Item(i).Cells(13).Text = Now()
            Dim connection As String
            connection = GetConnString()
            cn = New SqlClient.SqlConnection(connection)
            cn.Open()
            cmd = New SqlClient.SqlCommand("SELECT agentname FROM [dailytickets] WHERE callid=@tktnumber", cn)
            cmd.Parameters.Add("@tktnumber", SqlDbType.VarChar)
            cmd.Parameters("@tktnumber").Value = GridView2.Rows.Item(i).Cells(5).Text
            dr = cmd.ExecuteReader()
            If dr.Read = 0 Then
                cn.Close()
                cn.Open()
                cmd = New SqlClient.SqlCommand("INSERT INTO [dailytickets] (agentname, callid, username, country, contact, dispatcher, status, title, submitdate, site, dispatchtime, resolution, opentime)VALUES (@name,@tktnumber,@usrname,@region,@contact,@dispatcher,@status,@summary,@submitdate,@site,@dispatchtime,@resolution,@opentime)", cn)
                cmd.Parameters.Add("@name", SqlDbType.VarChar)
                cmd.Parameters("@name").Value = GridView2.Rows.Item(i).Cells(10).Text
                cmd.Parameters.Add("@tktnumber", SqlDbType.VarChar)
                cmd.Parameters("@tktnumber").Value = GridView2.Rows.Item(i).Cells(5).Text
                cmd.Parameters.Add("@usrname", SqlDbType.VarChar)
                cmd.Parameters("@usrname").Value = GridView2.Rows.Item(i).Cells(0).Text
                cmd.Parameters.Add("@region", SqlDbType.VarChar)
                cmd.Parameters("@region").Value = GridView2.Rows.Item(i).Cells(6).Text
                cmd.Parameters.Add("@contact", SqlDbType.VarChar)
                cmd.Parameters("@contact").Value = GridView2.Rows.Item(i).Cells(2).Text
                cmd.Parameters.Add("@dispatcher", SqlDbType.VarChar)
                cmd.Parameters("@dispatcher").Value = GridView2.Rows.Item(i).Cells(11).Text
                cmd.Parameters.Add("@status", SqlDbType.VarChar)
                cmd.Parameters("@status").Value = GridView2.Rows.Item(i).Cells(3).Text
                cmd.Parameters.Add("@summary", SqlDbType.VarChar)
                cmd.Parameters("@summary").Value = GridView2.Rows.Item(i).Cells(7).Text
                cmd.Parameters.Add("@submitdate", SqlDbType.DateTime)
                cmd.Parameters("@submitdate").Value = GridView2.Rows.Item(i).Cells(12).Text
                cmd.Parameters.Add("@site", SqlDbType.VarChar)
                cmd.Parameters("@site").Value = GridView2.Rows.Item(i).Cells(8).Text
                cmd.Parameters.Add("@dispatchtime", SqlDbType.DateTime)
                cmd.Parameters("@dispatchtime").Value = GridView2.Rows.Item(i).Cells(13).Text
                cmd.Parameters.Add("@resolution", SqlDbType.VarChar)
                cmd.Parameters("@resolution").Value = GridView2.Rows.Item(i).Cells(9).Text
                cmd.Parameters.Add("@opentime", SqlDbType.DateTime)
                cmd.Parameters("@opentime").Value = GridView2.Rows.Item(i).Cells(4).Text
                cmd.ExecuteNonQuery()
                If i = GridView2.Rows.Count - 1 Then
                    Response.Redirect("dispatcher.aspx")
                End If
            End If

        Next
    End Sub

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Session("admin") = Nothing Then
            Response.Redirect("login.aspx")
        Else
            usrnamelbl.Text = Session("admin")

        End If

    End Sub

    Protected Sub logoutbtn_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles logoutbtn.Click
        Session.RemoveAll()
        Response.Redirect("login.aspx")

    End Sub

    Protected Sub LinkButton1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles LinkButton1.Click
        Response.Redirect("livequeue.aspx")
    End Sub

    Protected Sub GridView1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles GridView1.SelectedIndexChanged
        Session("reassign") = GridView1.SelectedRow.Cells(1).Text
        Response.Redirect("reassign.aspx")

    End Sub

    Protected Sub pendingtkts_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles pendingtkts.Click
        Response.Redirect("reports.aspx")
    End Sub
End Class
[CODE]

Reply

JammoD

Member

242 Points

49 Posts

Re: Date and timestamps error

Jan 30, 2013 06:34 PM|LINK

The issue you was having now seems to have been resolved, I believe this is a seperate issue which may be with the cells/rows count of the GridView.

Does the error give you a line of code that it points to when debugging the code in Visual Studio, if it does then this may help us.

Thanks,

Marked as answer by Rashaad786 on Feb 12, 2013 12:21 PM

Reply

chetan.sarod...

All-Star

65789 Points

11153 Posts

Re: Date and timestamps error

Jan 31, 2013 02:26 AM|LINK

http://forums.asp.net/t/1828868.aspx/1

http://forums.asp.net/t/881467.aspx/1

Chetan Sarode
Senior Software Engineer,
Approva Systems Pvt Ltd, Pune, India.

Date and timestamps error RSS

22 replies

Rashaad786

Date and timestamps error

JammoD

Re: Date and timestamps error

Rashaad786

Re: Date and timestamps error

JammoD

Re: Date and timestamps error

Rashaad786

Re: Date and timestamps error

JammoD

Re: Date and timestamps error

Rashaad786

Re: Date and timestamps error

pecified argument was out of the range of valid values.
Parameter name: index

Rashaad786

Re: Date and timestamps error

JammoD

Re: Date and timestamps error

chetan.sarod...

Re: Date and timestamps error

This site is managed for Microsoft by Neudesic, LLC. | © 2013 Microsoft. All rights reserved.

Follow Us On:

Microsoft

Date and timestamps error RSS

22 replies

Date and timestamps error

Re: Date and timestamps error

Re: Date and timestamps error

Re: Date and timestamps error

Re: Date and timestamps error

Re: Date and timestamps error

Re: Date and timestamps error

pecified argument was out of the range of valid values. Parameter name: index

Re: Date and timestamps error

Re: Date and timestamps error

Re: Date and timestamps error

This site is managed for Microsoft by Neudesic, LLC. | © 2013 Microsoft. All rights reserved.

Follow Us On:

pecified argument was out of the range of valid values.
Parameter name: index