[SOLVED] Data type mismatch in criteria expression

Question

5.00/5 (1 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:

VB

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 ('" &amp; txtName.Text &amp; "'," &amp; txtAge.Text &amp; "," &amp; txtSpouseAge.Text &amp; "," &amp; txtNumberChildren.Text &amp; ")"
        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
            MyCon.Open()
            cmd.CommandText = MySqlDelete
            result = cmd.ExecuteNonQuery()
            MyCon.Close()
        End If
        'If result &lt;&gt; -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

        
    End Sub

Posted 1-Jan-11 1:31am

clausont5

Updated 3-Apr-17 19:40pm

Henry Minute

v3

Add a Solution

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

clausont5 · Answer 1 · 2011-01-01T08:12:00

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.

Posted 1-Jan-11 8:12am

clausont5

Updated 1-Jan-11 8:14am

v2

Comments

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. :)

OriginalGriff · Answer 2 · 2011-01-01T01:59:00

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.

Manfred Rudolf Bihy · Answer 3 · 2011-01-01T05:52:00

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,

Manfrd

clausont5 · Answer 4 · 2011-01-01T05:45:00

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.
Thanks

[SOLVED] Data type mismatch in criteria expression

4 solutions

Solution 4

Solution 1

Solution 3

Solution 2

Add your solution here

Preview 0