input strings are not in a correct format exception

Question

0.00/5 (No votes)

See more:

am developing an application for which excel sheet is taken as input and chart is being drawn through that file.so am getting an exception as input string notr ina correct format..
the code goes like this

VB

  Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            Dim a, b As String
            a = ""
            b = ""
            Dim data_time(3) As String
            Dim data_date(3) As String
            connString = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=C:\Users\SoftwareDeveloper\Desktop\value1.xls;" & "Extended Properties=""Excel 12.0;HRD=Yes;"";"

            Dim excelConnection As OleDb.OleDbConnection = New OleDb.OleDbConnection(connString)
            excelConnection.ConnectionString = connString

            Using excelConnection
                excelConnection.Open()

                Dim StrSql As OleDbCommand = New OleDbCommand("")
                'Dim DeviceId1 As OleDbDataReader
                Dim reader As OleDbDataReader
                Dim dev As OleDbCommand = New OleDbCommand("select DeviceId from [value$]")

                StrSql.Connection = excelConnection
               

                StrSql.CommandText = "select * from [value$]"
                reader = StrSql.ExecuteReader()

                dev.Connection = excelConnection

                'DeviceId1 = dev.ExecuteReader()


                Try
                    While (reader.Read())
                        If reader(1).ToString() <> "" And reader(2).ToString() <> "" Then
                            myarrlst11_1.Add(reader("sno").ToString)    'sno



                            data_date = reader(1).ToString().Split("/")
                            data_time = reader(2).ToString().Split(":")

                            myarrlst_dt_1.Add(reader("Date").ToString)  'Date

                            myarrlst21_1.Add(data_date(0))
                            myarrlst22_1.Add(data_date(1))
                            myarrlst23_1.Add(data_date(2))

                            

                            
                            myarrlst31_1.Add(data_time(0))
                            myarrlst32_1.Add(data_time(1))
                            myarrlst33_1.Add(data_time(2))

                            Dim datetime1 As DateTime = New DateTime(Convert.ToInt32(data_date(2)), Convert.ToInt32(data_date(1)), Convert.ToInt32(data_date(0)), Convert.ToInt32(data_time(0)), Convert.ToInt32(data_time(1)), Convert.ToInt32(data_time(2)))



' THE ABOVE STATEMENT FOR datetime1 IS THE LINE FOR WHICH THIS EXCEPTION IS COMING 






                            myarrlst_dt_1.Add(reader(datetime1))

                            'data_time = reader(1).ToString.Split(":")


                            myarrlst13_1.Add(reader("DeviceId").ToString)   'Device id
                        End If
                    End While
                Catch ex As Exception

                End Try

                reader.Close()
                excelConnection.Close()



            End Using

            FormatChart()
            AutoFormatChart()

            Chart1.Series(0).ChartType = DataVisualization.Charting.SeriesChartType.Column
            Chart1.Series(Chart1.Series.Count - 1).Points.DataBindXY(myarrlst11_1, myarrlst12_1)
            Chart1.Series(Chart1.Series.Count - 1).Color = Color.Red
        Catch ex As Exception

        End Try
    End Sub

plz help me what should i do to over come this exception
THE ABOVE STATEMENT FOR datetime1 IS THE LINE FOR WHICH THIS EXCEPTION IS COMING

[edit]Code block added - OriginalGriff[/edit]

Posted 29-Jun-12 21:05pm

Member 8953292

Updated 29-Jun-12 21:06pm

OriginalGriff

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2012-06-29T21:16:00

Why the heck are you storing dates and times in a database as strings?
Why are you returning SELECT * FROM, and then using them as if you can be sure of the order of you values?
Why have you assumed that the date information in your database string is in any kind of format you understand?

First: Change your database. Replace all the string based date and time values with SQL DATETIME values, and validate them and convert them before you enter them into the DB. It's too late after that! Once your date value is in as a string "12/11/10" you have no idea if that is Dec 11 2010, Nov 12 2010, 10 Dec 2012 or any other date format. Bear in mind that I could happily enter "30/JUN/2012" or "30-06-12" and the only clue you have is the settings on my PC when I do it!

Second: Don't use SELECT * - it wastes bandwidth returning values you aren;t interested in, and leaves you wide open to future problems when someone adds columns to your database. List the values you want, and refer to them by name, not number:

SQL

SELECT Id, name, joinDate FROM myTable

C#

name = reader("Name")

Then read the date and time value into a DateTime object and use it from there.