Data type mismatch in criteria expression

[clausont]

This should be an easy one but I am missing something and have been working on this for two days now.
I am trying to insert a date/time in a short date format (date/time mm/dd/yyyy) into an access database and I get the "Data type mismatch in criteria expression" every time. If I comment out the "startday" I get the "No value Given" which is what I would expect.
Any help here would be greatly appreciated.
Thanks in advance

Here is the complete code except the connection string which I have changed:

Sub CrossPage_FirstPage()
        
        Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=MyConnectionString"
        Dim MyCon As New OleDbConnection(strConn)
        Dim MySqlDelete As String = "delete from problemdefinition"
        Dim MySqlInsert As String = "INSERT INTO problemdefinition (memberage, sex, spouseage, numberchildren, countyid, zipcoderange, startday, dayscov) VALUES (@memberage, @sex, @spouseage, @numberchildren, @countyid, @zipcoderange, @startday, @DaysCov)"
        'Dim MySqlInsert As String = "INSERT INTO problemdefinition (client, memberage, spouseage, numberchildren, County,zipcode, startcoverage) VALUES ('" & txtName.Text & "'," & txtAge.Text & "," & txtSpouseAge.Text & "," & txtNumberChildren.Text & ")"
        Dim textboxName As TextBox = Me.PreviousPage.Master.FindControl("menu2").FindControl("txtName")
        Dim MyDate As String = DirectCast(Me.PreviousPage.Master.FindControl("menu2").FindControl("txtCalendar1"), TextBox).Text
        Dim cmd As New OleDbCommand(MySqlDelete, MyCon)
        With cmd.Parameters
            '.Add(New OleDbParameter("@client", textboxName.Text))
            '.Add(New OleDbParameter("@memberage", DirectCast(Me.PreviousPage.Master.FindControl("ContentPlaceHolder1").FindControl("txtname"), TextBox).Text))
            .Add(New OleDbParameter("@memberage", DirectCast(Me.PreviousPage.Master.FindControl("menu2").FindControl("txtAge"), TextBox).Text))
            .Add(New OleDbParameter("@sex", DirectCast(Me.PreviousPage.Master.FindControl("menu2").FindControl("rbgGender1"), RadioButtonList).Text))
            .Add(New OleDbParameter("@spouseage", DirectCast(Me.PreviousPage.Master.FindControl("menu2").FindControl("txtSpouseAge"), TextBox).Text))
            .Add(New OleDbParameter("@numberchildren", DirectCast(Me.PreviousPage.Master.FindControl("menu2").FindControl("txtNumberChildren"), TextBox).Text))
            .Add(New OleDbParameter("@countyid", DirectCast(Me.PreviousPage.Master.FindControl("menu2").FindControl("lblhidden1"), Label).Text))
            .Add(New OleDbParameter("@zipcoderange", DirectCast(Me.PreviousPage.Master.FindControl("menu2").FindControl("drpZip1"), TextBox).Text))
            '.Add(New OleDbParameter("@deductible", DirectCast(Me.PreviousPage.Master.FindControl("menu2").FindControl("DrpDed1"), DropDownList).SelectedItem.Value))
            '.Add(New OleDbParameter("@startday", DirectCast(Me.PreviousPage.Master.FindControl("menu2").FindControl("txtCalendar1"), TextBox).Text))
            .Add(New OleDbParameter("@DaysCov", DirectCast(Me.PreviousPage.Master.FindControl("menu2").FindControl("txtNumDays1"), TextBox).Text))
            .Add(New OleDbParameter("@startday", "CDate('" + MyDate + "')"))

        End With

        'Session("client") = textboxName.Text
        'Session("client") = DirectCast(Me.PreviousPage.Master.FindControl("ContentPlaceHolder1").FindControl("txtAge"), TextBox).Text

        Session("memberage") = DirectCast(Me.PreviousPage.Master.FindControl("menu2").FindControl("txtAge"), TextBox).Text
        Session("spouseage") = DirectCast(Me.PreviousPage.Master.FindControl("menu2").FindControl("txtSpouseAge"), TextBox).Text
        Session("numberchildren") = DirectCast(Me.PreviousPage.Master.FindControl("menu2").FindControl("txtNumberChildren"), TextBox).Text
        Session("sex") = DirectCast(Me.PreviousPage.Master.FindControl("menu2").FindControl("rbgGender1"), RadioButtonList).SelectedItem.Text
        Session("countyid") = DirectCast(Me.PreviousPage.Master.FindControl("menu2").FindControl("lblhidden1"), Label).Text
        Session("zipcoderange") = DirectCast(Me.PreviousPage.Master.FindControl("menu2").FindControl("drpZip1"), TextBox).Text
        'Session("deductible") = DirectCast(Me.PreviousPage.Master.FindControl("menu2").FindControl("DrpDed1"), DropDownList).SelectedItem.Value
        Session("startday") = DirectCast(Me.PreviousPage.Master.FindControl("menu2").FindControl("txtCalendar1"), TextBox).Text
        Session("DaysCov") = DirectCast(Me.PreviousPage.Master.FindControl("menu2").FindControl("txtNumDays1"), TextBox).Text

        Dim result As Integer = -1

        'Use a variable to hold the SQL statement.




        If MyCon.State = ConnectionState.Closed Then
            MyCon.Open()
            cmd.CommandText = MySqlDelete
            result = cmd.ExecuteNonQuery()
            MyCon.Close()
        End If

        'If result <> -1 Then
        If MyCon.State = ConnectionState.Closed Then
            MyCon.Open()
            cmd.CommandText = MySqlInsert
            cmd.ExecuteNonQuery()
            MyCon.Close()
        End If

        'End If


        Dim StartCov As String = DirectCast(Me.PreviousPage.Master.FindControl("menu2").FindControl("txtCalendar1"), TextBox).Text



        'lblName.Text = textboxName.Text
        lblage.Text = DirectCast(Me.PreviousPage.Master.FindControl("menu2").FindControl("txtAge"), TextBox).Text
        lblspouseage.Text = DirectCast(Me.PreviousPage.Master.FindControl("menu2").FindControl("txtSpouseAge"), TextBox).Text
        lblnumberchildren.Text = DirectCast(Me.PreviousPage.Master.FindControl("menu2").FindControl("txtNumberChildren"), TextBox).Text
        lblDate.Text = DateTime.Now.ToShortDateString()
        lblZip.Text = DirectCast(Me.PreviousPage.Master.FindControl("menu2").FindControl("drpZip1"), TextBox).Text
        'lbldrpstart.Text = DirectCast(Me.PreviousPage.Master.FindControl("menu2").FindControl("DrpStartCov1"), DropDownList).SelectedItem.Value
        lbldrpstart.Text = DirectCast(Me.PreviousPage.Master.FindControl("menu2").FindControl("txtcalendar1"), TextBox).Text
        'lbldrpstart.Text = StartCov
       
    End Sub

[propertywant]

I thought you had to put '#' around date fields

Add(New OleDbParameter("@startday", "CDate('#" + MyDate + "#')"))

I could be wrong - been a long time since I did access

[clausont]

Thanks for the try on this. I found the problem was that I had declared the textbox as a string rather than a date. Now I just need to figure out why I get no value given when the fields are all filled....
Thanks again.