Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL DateTime Errors
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. Blush | :O Blush | :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
            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
 
        
    End Sub
Posted 1-Jan-11 1:31am
Edited 1-Jan-11 8:22am
Henry Minute223.7K
v3
Rate this: bad
good
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.
  Permalink  
v2
Comments
Henry Minute at 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
good
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.
  Permalink  
Rate this: bad
good
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,
 
Manfrd
  Permalink  
v3
Rate this: bad
good
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.
Thanks
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 5

incase of dates you should put it this way
 
DATE BETWEEN #01 Jan 2013# AND #31 Dec 2013#
(The Date format should match the format in your DB)
Smile | :)
Happy coding.
  Permalink  

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



Advertise | Privacy | Mobile
Web01 | 2.8.141022.2 | Last Updated 27 Apr 2013
Copyright © CodeProject, 1999-2014
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