1

I am getting Specified cast is not valid. error message when try to display date from MS Access DB on VB.NET.

The code line I am trying to display the Date is

txtEntyDate.Text = sqlRead.GetDateTime(7)

I want to display ONLY date. Is anyone free to explain to me how I should code to get this right? Also, if you guys have a link of similar question that previously answered, I will highly appreciate it.

Full code:

    Try
    'Setup Connection String
    conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " & Application.StartupPath & "\igcDatabase.accdb"
    sqlCom.Connection = conn

    'Open Data Connection
    conn.Open()

    If rbMemberID.Checked = True Then
        'Query
        sqlCom.CommandText = "SELECT * FROM [Members] WHERE [Member ID] = @MemberID"

        'Parameter is used below to prevent SQL Injection
        sqlCom.Parameters.AddWithValue("MemberID", txtSearchInput.Text)
    Else
        MessageBox.Show("If you are not getting the result you wanted, try again with Member ID.", "", MessageBoxButtons.OK, MessageBoxIcon.Information)
        'Query
        sqlCom.CommandText = "SELECT * FROM [Members] WHERE [Member First Name] = @FirstName"

        'Parameter is used below to prevent SQL Injection
        sqlCom.Parameters.AddWithValue("FirstName", txtSearchInput.Text)
    End If

    Dim sqlRead As OleDbDataReader = sqlCom.ExecuteReader()
    sqlRead.Read()

    If sqlRead.HasRows = True Then
        Dim membershipType As String
        If sqlRead.GetValue(4) = 1 Then
            membershipType = "Deluxe"
        ElseIf sqlRead.GetValue(4) = 2 Then
            membershipType = "Non-Deluxe"
        Else
            membershipType = "Week-Day"
        End If

        txtMemberID.Text = sqlRead.GetValue(0)
        txtFirstName.Text = sqlRead.GetString(1)
        txtLastName.Text = sqlRead.GetString(2)
        txtGender.Text = sqlRead.GetString(3)
        txtContact.Text = sqlRead.GetValue(5)
        txtEmail.Text = sqlRead.GetString(6)
        txtMembershipType.Text = membershipType
        txtEntyDate.Text = sqlRead.GetDateTime(7)
        txtStatus.Text = sqlRead.GetString(8)
    Else
        MessageBox.Show("There Is no such member found in database. Please Try again.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Warning)
        txtMemberID.Clear()
        txtFirstName.Clear()
        txtLastName.Clear()
        txtGender.Clear()
        txtContact.Clear()
        txtEmail.Clear()
        txtMembershipType.Clear()
        txtStatus.Clear()
        txtEntyDate.Clear()
        txtSearchInput.Clear()
        txtSearchInput.Select()
    End If
    sqlCom.Parameters.Clear()
    sqlRead.Close()
    conn.Close()
Catch ex As Exception
    MessageBox.Show(ex.Message)
End Try
Gideon
  • 69
  • 12
  • Please show more of the procedure and be clear about where the error is. – Ňɏssa Pøngjǣrdenlarp Feb 28 '16 at 20:04
  • Before insert the `txtEntyDate.Text = sqlRead.GetDateTime(7)` and relevant code, I got no error message at all. The error message appeared after the last data in textbox is displayed, which is the one before the `txtEntryDate` textbox. Also, I have just updated the full code on the question post. – Gideon Feb 28 '16 at 20:16
  • Sorry, but I would like/need some context is why I asked for more code – Ňɏssa Pøngjǣrdenlarp Feb 28 '16 at 20:18
  • No problem, you can have a look at the question post now. And thanks for your time. – Gideon Feb 28 '16 at 20:20
  • But that is the insert code, `txtEntyDate.Text = sqlRead.GetDateTime(7)` isnt there - that code would be from where you read from the DB – Ňɏssa Pøngjǣrdenlarp Feb 28 '16 at 20:22
  • Sorry for the typo, extremely lack of sleep now. And also, I have just edited the question again. Please check. – Gideon Feb 28 '16 at 20:23

1 Answers1

1

TextBox.Text is string, the return from GetDateTime() is a DateTime type so you cannot just assign one to another.

Remedy A

TextBox2.Text = rdr.GetDateTime(1).ToString("MM/dd/yyyy")

Remedy B

Dim dtTemp As DateTime
...
' how you would read it into a DT var:
dtTemp = rdr.GetDateTime(1)
TextBox2.Text = dtTemp.ToString("MM/dd/yyyy")   ' whatever format you want

Or

TextBox2.Text = Convert.ToDateTime(rdr("zDate")).ToString("MM/dd/yyyy")

Where txtEntyDate.Text = sqlRead.GetDateTime(7) tries to directly assign a Date to a String, the others convert the date to string first as well as specify a format. Note that there are other implicit conversions in your code such as:

txtMemberID.Text = sqlRead.GetValue(0)

Basically, anything other than GetString would be requiring VB to do some guesswork as to the conversion which is not allowed under Option Strict.

Ňɏssa Pøngjǣrdenlarp
  • 38,411
  • 12
  • 59
  • 178
  • Do you mean that I am recommended to do the conversion, although `txtMemberID.Text = sqlRead.GetValue(0)` is working without conversion? – Gideon Feb 28 '16 at 20:33
  • I get a compiler error for lines like that with Option Strict On – Ňɏssa Pøngjǣrdenlarp Feb 28 '16 at 20:36
  • you should also use `Add` rather than `AddWithvalue` which ties into the other question (1 per post please). Post a new Q about the Date only thing - it is worth asking. Also it looks like you are reusing things like the connection and command objects and/or not disposing of them. see http://stackoverflow.com/a/29187199/1070452 – Ňɏssa Pøngjǣrdenlarp Feb 28 '16 at 20:39
  • Alright thanks! I'll start Option Strict On now as a good practicing. – Gideon Feb 28 '16 at 20:40
  • 1
    Opps, misunderstanding your sentence. Sorry about that. – Gideon Feb 28 '16 at 20:47
  • the question about storing only date is worth asking, it is just bad to put multiples in 1 post because a) makes it hard to find answers and b) makes answers too long. The answer covers putting the date only to the form but not saving date only to the DB – Ňɏssa Pøngjǣrdenlarp Feb 28 '16 at 20:49