0

I need to show a date that I got from the mysql database in the datetimepicker. I'm getting this error when I run this code.

System. FormatException: String was not recognized as a valid DateTime.
at System.DateTimeParse.ParseExact(String s, String format, DateTimeFormatInfo dtfi, DateTimeStyIes style)
at System. DateTime.ParseExact(String s, String format, IFormatProvider provider)

note: when mysql reader reads rdate value is this 2/13/2019 12:00:00 AM rdatecorrected value is 2/13/2019

I saw some answers with similar questions that use one M instead of MM & one d instead of dd to match the format for month & date values that are one numbers. But I have these records every day for all the year so I need a way to get this work even it is a one value month like 9 or two value month like 11

I'm still a beginner programmer so I'd be thankful if you can show me the solution with the edited code.

imports: Imports System.IO
Imports System
Imports MySql.Data.MySqlClient
Imports System.Globalization

    If System.IO.File.Exists("D:\DBSystem\Connection\connconfig.dbs") = True Then

        Dim realstuno As Integer
        realstuno = ESD_Realstudentno.Text

        Dim strFile As String = "D:\DBSystem\Connection\connconfig.dbs"
        Dim sr As New IO.StreamReader(strFile)
        Dim ip As String
        Dim port As String
        Dim userid As String
        Dim password As String
        Dim database As String

        'reading
        ip = sr.ReadLine()
        port = sr.ReadLine()
        userid = sr.ReadLine()
        password = sr.ReadLine()
        database = sr.ReadLine()
        sr.Close()


        'default connection code
        Dim serverstring As String = "server=" & ip & ";port=" & port & ";database=" & database & ";user ID=" & userid & ";password=" & password & ";OldGuids=true"
        Dim sqlconnection As MySqlConnection = New MySqlConnection
        sqlconnection.ConnectionString = serverstring
        Dim Reader As MySqlDataReader
        Dim Rdate As String
        Dim SDA As New MySqlDataAdapter
        Dim DbDataSet As New DataTable
        Dim bSource As New BindingSource
        Dim rdatecorrected As String

        'custom date format
        'rdate
        ESD_Rdate.Format = DateTimePickerFormat.Custom
        ESD_Rdate.CustomFormat = "MM/dd/yyyy"


        Try
            If sqlconnection.State = ConnectionState.Closed Then
                sqlconnection.Open()

                'search
                Dim sqlstatement As String = "Select Registereddate FROM `SDS_Data` WHERE `StudentNo` ='" & realstuno & "'"
                Dim Command = New MySqlCommand(sqlstatement, sqlconnection)
                Reader = Command.ExecuteReader
                If Reader.HasRows() Then
                    While Reader.Read


                        'rdate
                        Rdate = Reader.GetString("Registereddate")
                        rdatecorrected = Rdate.Remove(Rdate.Length - 12, 12)
                        Dim dt As DateTime = DateTime.ParseExact("" & rdatecorrected & "", "MM/dd/yyyy", CultureInfo.InvariantCulture)
                        ESD_Rdate.Value = dt

                    End While
                Else
                    MessageBox.Show("No records found!", "Error")
                End If
            Else
                sqlconnection.Close()
                MsgBox("Connection Error!", "Error")

            End If

        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

    Else
        MessageBox.Show("Do not have any previous database configurations!", "Error",
           MessageBoxButtons.OK)
    End If
barbsan
  • 3,418
  • 11
  • 21
  • 28
Nemar162
  • 30
  • 3
  • 1
    Don't add double-quotes to the string, ParseExact() won't put up with that. And never store dates as strings in a dbase, change the column type to DATETIME. – Hans Passant May 09 '19 at 08:40
  • 1
    "M" signifies one *or* two digits. "MM" signifies exactly two digits. The same goes for "d" and "dd". Also, you really should use [`Option Strict On`](https://stackoverflow.com/a/5076966/1115360), and set it as the default for new projects. – Andrew Morton May 09 '19 at 13:11
  • What is the data type of your Registereddate column in your database? Can you show that? – Chris Dunaway May 09 '19 at 14:40

0 Answers0