Formatting textbox string into date format - I would like to know how to do this

[emaduddeen]

Hi Everyone,

My user wants to have textbox fields that don't have any masks but wants those text fields to reformat any text that is entered as mm/dd if it is a real date entered. They won't be using the year.

Could you show me what coding I need to use to do this?

Thanks.

Truly,
Emad

 

[jmcilhinney]

You first need to define the rules. You say "any text". What if the user enters "11"? Is that January 1? In all but the most trivial cases, you don't go from problem to code. The code is not the solution. The code is an implementation of the solution. You start with the problem and you come up with a solution first, then you write code to implement that solution. So, what's your solution? In order to solve the problem you have to define the problem. In that case, that means working out EXACTLY what the rules are for what constitutes valid input and how that input gets converted to a Date for ALL possible cases.

 

[emaduddeen]

Hi,

Thanks for the reply.

Here are the rules.

User can enter anything in the entry field including incorrect dates. I can check that using the IsDate function. Using your sample of 11, that would fail the IsDate test.

Lets say the user enters a valid date such as 30/12/2010 or something like Jan 14 2011 but has no punctuation. The user wants the program to strip out any year entered and just display in the entry field the month and day using this particular format:

MM/DD

The coding will be in a function that returns a formatted date.

If an invalid date is entered, the function will return "Invalid Date" so the calling coding can respond accordingly.

Hope this will give you and idea of what we are trying to do.

Here is the coding we used so far but the message box only returns the text "MM/dd" not the actual date so I know we are missing something.

    Private Function DateFormat(ByVal pStringToFormat As String)  As String

        ' No sense in going on if the date is invalid.
        '---------------------------------------------
        If IsDate(pStringToFormat) Then

            ' Make sure the date is formatted correctly.
            '-------------------------------------------
            pStringToFormat = Format(pStringToFormat, "MM/dd")

            MessageBox.Show(pStringToFormat)

        Else
            pStringToFormat = "Invalid Date"
        End If

        Return pStringToFormat
    End Function

Thanks again.

Truly,
Emad

 

[jmcilhinney]

You wouldn't write a method that returned a String, unless you want to put that "Invalid Date" message into the the TextBox and just let the user worry about it. The correct option is to handle the Validating and Validated events of the TextBox. The Validating event will test the input and refuse to let the user leave the field if it's invalid. The Validated event will convert the input to the correct format if it's valid:

Private Sub dateField_Validating(ByVal sender As System.Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles dateField.Validating
    e.Cancel = Not Date.TryParse(Me.dateField.Text, New Date) AndAlso
               Not Date.TryParseExact(Me.dateField.Text, "MM/dd", Nothing, Globalization.DateTimeStyles.None, New Date)

    If e.Cancel Then
        MessageBox.Show("Please enter a valid date.")
    End If
End Sub

Private Sub dateField_Validated(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles dateField.Validated
    Dim dateValue As Date

    If Date.TryParse(Me.dateField.Text, dateValue) Then
        Me.dateField.Text = dateValue.ToString("MM/dd")
    End If
End Sub

Note that the code has been written to allow for the fact that the field may already contain a formatted value, which would otherwise fail validation.

 

[emaduddeen]

Hi jmcilhinney,

Thanks for the useful coding and suggestions.

I will use them.

Truly,
Emad.

 

[emaduddeen]

Hi,

Your coding works very well, but I have what is probably a stupid question.

After doing a lot of searching I can find how to get that date saved to the MS Access database because of a "Data Type mismatch in criteria expression" error when an insert statement is used.

This is the parameter I'm using for the date:

        ObjCommand.Parameters.Add(New  _
           OleDb.OleDbParameter("Anniversary", CDate(EditBoxAnniversary.Text)))

This is the insert statement:

                SqlStatement =
                   "INSERT INTO Customers " & _
                      "(FirstName, LastName, Address1, Address2, City, " & _
                    "StateRegion, Zip, PhoneHome, PhoneCell, " & _
                    "PhoneWork, Email, Notes, Spouse, " & _
                    "Anniversary, RingSize) " & _
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"

This is the code I'm using to execute the insert statement:

        Try
            ' Execute the SQL statement.
            '---------------------------
            ObjConnection.Open()

            ObjCommand.ExecuteNonQuery()

        Catch exSqlErrors As OleDbException

            MessageBox.Show("Sorry, I can't save your data " & _
                            "because of this SQL error:" & vbCrLf & vbCrLf & exSqlErrors.Message, _
                            "Other Error")

        Catch exErrors As Exception
            MessageBox.Show("Sorry, I can't save your data " & _
                            "because of this error:" & vbCrLf & vbCrLf & exErrors.Message, _
                            "Other Error")
        End Try

Can you tell me what I'm missing?

Thanks.

Truly,
Emad

 

[jmcilhinney]

First, this:

ObjCommand.Parameters.Add(New  _
           OleDb.OleDbParameter("Anniversary", CDate(EditBoxAnniversary.Text)))

can be written more succinctly like this:

ObjCommand.Parameters.AddWithValue("Anniversary", CDate(EditBoxAnniversary.Text))

As for the question, are you sure that you are adding the parameters in the same order as they appear in the SQL code? OLE DB relies on parameter position, not name.

 

[emaduddeen]

Hi,

I believe the order is ok as there is 15 table columns, 15 question marks, and 15 add parameter statements (I change them to AddWithValue soon).

It's probably something really simple I'm missing that you can locate quickly that I need to learn.

Here's the procedure:

    Private Sub ButtonSaveChanges_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonSaveChanges.Click

        Dim SqlStatement As String = Nothing
        Dim ObjConnection As OleDbConnection
        Dim ObjCommand As New OleDbCommand

        ' Create the connection object.
        '------------------------------
        ObjConnection = New OleDbConnection(FormMain.strDatabaseConnection)

        Select Case strFormMode

            Case "Insert"

                ' Compose the INSERT statement.
                '------------------------------
                SqlStatement =
                   "INSERT INTO Customers " & _
                      "(FirstName, LastName, Address1, Address2, City, " & _
                       "StateRegion, Zip, PhoneHome, PhoneCell, " & _
                       "PhoneWork, Email, Notes, Spouse, " & _
                       "Anniversary, RingSize) " & _
                   "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"

            Case "Update"

            Case Else

        End Select

        ' Make a Command for this connection.
        '------------------------------------
        ObjCommand = New OleDbCommand(SqlStatement, ObjConnection)

        ' Tell the command what to execute.
        '----------------------------------
        ObjCommand.CommandText = SqlStatement

        ' Create the needed parameters.
        '------------------------------
        ObjCommand.Parameters.Add(New  _
           OleDb.OleDbParameter("FirstName", _
              StrConv(editBoxFirstName.Text, VbStrConv.ProperCase)))

        ObjCommand.Parameters.Add(New  _
           OleDb.OleDbParameter("LastName", _
              StrConv(EditBoxLastName.Text, VbStrConv.ProperCase)))

        ObjCommand.Parameters.Add(New  _
           OleDb.OleDbParameter("Address1", _
              StrConv(EditBoxAddress1.Text, VbStrConv.ProperCase)))

        ObjCommand.Parameters.Add(New  _
           OleDb.OleDbParameter("Address2", _
              StrConv(EditBoxAddress2.Text, VbStrConv.ProperCase)))

        ObjCommand.Parameters.Add(New  _
           OleDb.OleDbParameter("City", _
              StrConv(EditBoxCity.Text, VbStrConv.ProperCase)))

        ObjCommand.Parameters.Add(New  _
           OleDb.OleDbParameter("StateRegion", _
              StrConv(EditBoxStateRegion.Text, VbStrConv.ProperCase)))

        ObjCommand.Parameters.Add(New  _
           OleDb.OleDbParameter("Zip", _
              StrConv(EditBoxZip.Text, VbStrConv.ProperCase)))

        ObjCommand.Parameters.Add(New  _
           OleDb.OleDbParameter("PhoneHome", _
              StrConv(EditBoxPhoneHome.Text, VbStrConv.ProperCase)))

        ObjCommand.Parameters.Add(New  _
           OleDb.OleDbParameter("PhoneCell", _
              StrConv(EditBoxPhoneCell.Text, VbStrConv.ProperCase)))

        ObjCommand.Parameters.Add(New  _
           OleDb.OleDbParameter("PhoneWork", _
              StrConv(EditBoxPhoneWork.Text, VbStrConv.ProperCase)))

        ObjCommand.Parameters.Add(New  _
           OleDb.OleDbParameter("Email", _
              StrConv(EditBoxEmail.Text, VbStrConv.ProperCase)))

        ObjCommand.Parameters.Add(New  _
           OleDb.OleDbParameter("Notes", _
              StrConv(editBoxNotes.Text, VbStrConv.ProperCase)))

        ObjCommand.Parameters.Add(New  _
           OleDb.OleDbParameter("Spouse", _
              StrConv(EditBoxSpouse.Text, VbStrConv.ProperCase)))

        ObjCommand.Parameters.Add(New  _
           OleDb.OleDbParameter("Anniversary", CDate(EditBoxAnniversary.Text)))

        ObjCommand.Parameters.Add(New  _
           OleDb.OleDbParameter("RingSize", EditBoxRingSize.Text))

        Try
            ' Execute the SQL statement.
            '---------------------------
            ObjConnection.Open()

            ObjCommand.ExecuteNonQuery()

        Catch exSqlErrors As OleDbException

            MessageBox.Show("Sorry, I can't save your data " & _
                            "because of this SQL error:" & vbCrLf & vbCrLf & exSqlErrors.Message, _
                            "Other Error")

        Catch exErrors As Exception
            MessageBox.Show("Sorry, I can't save your data " & _
                            "because of this error:" & vbCrLf & vbCrLf & exErrors.Message, _
                            "Other Error")
        End Try

        ' Close out not needed stuff.
        '----------------------------
        ObjConnection.Close()
        ObjConnection.Dispose()

    End Sub

Truly,
Emad

 

[emaduddeen]

Hi,

It looks like my issue is a conversion one.

I commented out all parts of the code that dealt with the Anniversary date then got the same error so I commented out the ring size stuff. As soon as I did that, everything saved to the database. That's why it looks like a conversion issue because the remaining table columns are all Text ones.

Can you tell me how to change the parameter statements so both the date and the ring size can be saved to the database? The Anniversary is a DateTime and the ring size is a currency because it has 2 decimal places needed for it in the database table.

Truly,
Emad

 

[jmcilhinney]

All you have to do is make sure you are providing data of the right type for each parameter. Only pass Strings to parameters for text columns. If a columns stores dates, make sure that you're passing Dates and not strings in date format. The same goes for numbers. A String containing digits is not a number.

 

[emaduddeen]

Hi,

Thanks. I tried this and it worked.

        ObjCommand.Parameters.AddWithValue("Anniversary", Convert.ToDateTime(EditBoxAnniversary.Text))

Truly,
Emad