dcsimg
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 10 of 10

Thread: Run-time error'-2147217913 (80040e07)'

  1. #1
    Join Date
    Nov 2005
    Posts
    56

    Run-time error'-2147217913 (80040e07)'

    its says

    Run-time error'-2147217913 (80040e07)'
    Data type mismatch in criteria expression.

    i believe it was due to some data type that im trying to access. I'm doing a search field on the date and my datatype in the database is date/time, my textfield is is juz normal text.

    my sql statement is as follows :
    RefundDatesql = "SELECT * FROM tblRefund WHERE CreatedDate='" & txtRefundDate & "'"

    anyone knows how to rectify that?

  2. #2
    Join Date
    Jan 2003
    Location
    7,107 Islands
    Posts
    2,487

    Re: Run-time error'-2147217913 (80040e07)'

    if it is an mdb try..

    "SELECT * FROM tblRefund WHERE CreatedDate=#" & txtRefundDate & "#"
    Busy

  3. #3
    Join Date
    Nov 2005
    Posts
    56

    Re: Run-time error'-2147217913 (80040e07)'

    thanks for replying thread1 but i have 2 errors here

    if i use this
    RefundDatesql = "SELECT * FROM tblRefund WHERE CreatedDate='" & txtRefundDate & "#"

    the error is

    Run-time error'-2147217900 (80040e14)':
    Syntax error in string in query expression 'CreatedDate='#'.



    and if i use this
    RefundDatesql = "SELECT * FROM tblRefund WHERE CreatedDate=#" & txtRefundDate & "#"

    the error is

    Run-time error'-2147217913 (80040e07)':
    Syntax error in date in query expression 'CreatedDate=##'

  4. #4
    Join Date
    Jun 2004
    Location
    Kashmir, India
    Posts
    6,808

    Re: Run-time error'-2147217913 (80040e07)'

    The error shows that txtRefundDate is blank.
    Before running the query, you should check if the textbox is blank and if not, does it contain a valid date. Then only you should run the query.

  5. #5
    Join Date
    Nov 2005
    Posts
    56

    Re: Run-time error'-2147217913 (80040e07)'

    i got it already .. for the benefit of all in future .. this is the sql statement i used and it works

    RefundDatesql = "SELECT * FROM tblRefund WHERE CreatedDate LIKE '" & txtRefundDate & "'"

  6. #6
    Join Date
    Jan 2003
    Location
    7,107 Islands
    Posts
    2,487

    Re: Run-time error'-2147217913 (80040e07)'

    yeah that is right.

    the simplest and easiest way to validate the date input is by enabling the error handling "On Error..."

    Code:
    On Local Error Resume Next  '<-- enable error handling
    
    Set rs = cn.Execute("SELECT * FROM tblRefund WHERE CreatedDate=#" & txtRefundDate & "#")
    
    'check error
    if err.number = -2147217913 then
      msgbox "invalid date format"
    elseif err.number then
      msgbox "unexpected error"
    end if
    
    On Local Error Goto 0 '<-- disable error handling
    Busy

  7. #7
    Join Date
    Jun 2004
    Location
    Kashmir, India
    Posts
    6,808

    Re: Run-time error'-2147217913 (80040e07)'

    Quote Originally Posted by Thread1
    On Local Error Resume Next '<-- enable error handling

    Set rs = cn.Execute("SELECT * FROM tblRefund WHERE CreatedDate=#" & txtRefundDate & "#")

    'check error
    if err.number = -2147217913 then
    msgbox "invalid date format"
    elseif err.number then
    msgbox "unexpected error"
    end if

    On Local Error Goto 0 '<-- disable error handling
    This is not a good way of doing it. Although the result will be same, you are going through extra steps to check the validity of the data. I would first validate the data before sending it to database, that will save me a round trip when I am working with Server based databases (say SQL Server)

    This is what I would do
    Code:
    If txtRefundDate.Text = "" Or Not IsDate(txtRefund.Date.Text) Then
    Code:
      'If the data present in the textbox is not a valid date or the data is blank, don't do anything, just exit
      MsgBox "The Date entered is not valid, enter the correct date and continue"
      txtRefundDate.SetFocus
      Exit Sub
    End If
    'if the data is fine, then go ahead and run the query

  8. #8
    Join Date
    Jan 2003
    Location
    7,107 Islands
    Posts
    2,487

    Re: Run-time error'-2147217913 (80040e07)'

    Quote Originally Posted by Shuja Ali
    This is not a good way of doing it. Although the result will be same, you are going through extra steps to check the validity of the data. I would first validate the data before sending it to database, that will save me a round trip when I am working with Server based databases (say SQL Server)[/color]

    This is what I would do
    Code:
    If txtRefundDate.Text = "" Or Not IsDate(txtRefund.Date.Text) Then
    Code:
      'If the data present in the textbox is not a valid date or the data is blank, don't do anything, just exit
      MsgBox "The Date entered is not valid, enter the correct date and continue"
      txtRefundDate.SetFocus
      Exit Sub
    End If
    'if the data is fine, then go ahead and run the query
    well, it really depends on the situation. if the returning rowset/recordset is of importance in the application (ie display it in grid) then why not fetch them directly. the chance a user can input an invalid date will not be frequent overtime, and the performance difference of pre-validating and taking advantage of the database syntax analyzer is negligible especially if you have good file access (mdb is a file-based database).

    for other type of databases (server-based), it is a good practice to use stored procedure and take advantange of the server/database capability. IsDate function will not work for other database date formats.
    Last edited by Thread1; November 23rd, 2005 at 05:19 AM.
    Busy

  9. #9
    Join Date
    Jun 2004
    Location
    Kashmir, India
    Posts
    6,808

    Re: Run-time error'-2147217913 (80040e07)'

    Quote Originally Posted by Thread1
    well, it really depends on the situation. if the returning rowset/recordset is of importance in the application (ie display it in grid) then why not fetch them directly. the chance a user can input an invalid date will not be frequent overtime, and the performance difference of pre-validating and taking advantage of the database syntax analyzer is negligible especially if you have good file access (mdb is a file-based database).
    You are right, In File based access this might not have that much impact on performance. But still the proper way of writing this piece of code would have been
    Code:
    On Error Goto retError
    If txtRefundDate.Text = "" Or Not IsDate(txtRefund.Date.Text) Then
      'If the data present in the textbox is not a valid date or the data is blank, don't do anything, just exit
      MsgBox "The Date entered is not valid, enter the correct date and continue"
      txtRefundDate.SetFocus
      Exit Sub
    End If
    Set rs = cn.Execute("SELECT * FROM tblRefund WHERE CreatedDate=#" & txtRefundDate.Text & "#")
    'other code for populating the data or whatever
    Exit Sub
    
    retError:
    MsgBox "Error: " + Err.Description
    Exit Sub

    Quote Originally Posted by Thread1
    for other type of databases (server-based), it is a good practice to use stored procedure and take advantange of the server/database capability. IsDate function will not work for other database date formats.
    I also agree here that you should take advantage of stored procedures in the server based databases. Howevere, IsDate is a VB built-in function, so you can always use this to validate whether the data entered is a valid date or not.

  10. #10
    Join Date
    Nov 2005
    Posts
    56

    Re: Run-time error'-2147217913 (80040e07)'

    hi guys,

    actually im not doing a very high end program. so as long as it works im fine. actually i need you guys to help me with another problem. I posted in the main, on listview and checkboxes. Please help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width




On-Demand Webinars (sponsored)