Click here to Skip to main content
12,953,937 members (43,538 online)
Rate this:
Please Sign up or sign in to vote.
See more:
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

I am using a master page here if it makes any difference in this instance. :-O :-O
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
        Dim result As Integer = -1
        'Use a variable to hold the SQL statement.

        If MyCon.State = ConnectionState.Closed Then
            cmd.CommandText = MySqlDelete
            result = cmd.ExecuteNonQuery()
        End If
        'If result <> -1 Then
        If MyCon.State = ConnectionState.Closed Then
            cmd.CommandText = MySqlInsert
        End If
        'End If
        Dim StartCov As String = DirectCast(Me.PreviousPage.Master.FindControl("menu2").FindControl("txtCalendar1"), TextBox).Text
    End Sub
Posted 1-Jan-11 1:31am
Updated 3-Apr-17 19:40pm
Henry Minute225.6K
Rate this: bad
Please Sign up or sign in to vote.

Solution 4

I found the problem: I had declared the textbox as a string rather than a date.
This thread can be closed - it is answered.
Henry Minute 1-Jan-11 14:27pm
If you mark this answer as such (if you can, not sure if you can actually mark one of your own as the answer??) it will remove the question from the 'active' list. :)
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

Firstly, use Parameters.AddWithValue instead of Parameters.Add - the later is depreciated.

Secondly, instead of reading a date from a text box into a string, use DateTime.TryParseExact instead to convert it to a DateTime - you can then use that directly with the "startday" instead of using a CDate conversion. This also means you can control the date format much better, and report problems with it before you try to insert into the database.
Rate this: bad
Please Sign up or sign in to vote.

Solution 3

Problem is OleDBCommand does not work with named parameters.
You have to insert a question mark instead. When you add the parameters via:

myOleDBCmd.Parameters.Add(Object obj, SqlDBType sqltype)

You have to add them in the exact order the question marks appear in. Since named parameters are not possible they are matched by their index in the list of parameters for every ? in the SQL the parameter with the next index is chosen.

This case is related to yours:
How to store jpeg in Access 2007[^]

Best Regards,

Rate this: bad
Please Sign up or sign in to vote.

Solution 2

Thank you for the suggestion here but apparently I don't know how to use .AddWithValue as when I try to change .add to .AddWithValue, it gives me an error bc30455 argument not specified.
Will using DateTime.TryParseExact, will that insert the date into the database in the format "MM/DD/YYYY"? If so, how do I use it?
I am not all that experienced as you can tell.

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

    Print Answers RSS
Top Experts
Last 24hrsThis month
OriginalGriff 6,464
CHill60 3,490
Maciej Los 3,123
ppolymorphe 2,020
Jochen Arndt 1,975

Advertise | Privacy | Mobile
Web02 | 2.8.170525.1 | Last Updated 4 Apr 2017
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100