CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2012
    Posts
    13

    "Data type mismatch in criteria expression" in SQL statement

    hey there,

    Here's my complete code for a delete button that deletes a record in a Hierarchical Flex Grid.

    Code:
        Dim rsTour As New ADODB.Recordset
        Dim cn As New ADODB.Connection
        Dim strSQL As String
        cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
          "Data Source=" & App.Path & "\Luckshan Tours & Travels.mdb;" & _
          "Persist Security Info:False"
        cn.Open
        msg = MsgBox("Delete Record?", vbYesNo)
        If msg = vbYes Then
          strSQL = "Delete From Tour Where [Tour ID] = '" & fgdTour.TextMatrix(fgdTour.Row, 1) & "'"
          cn.Execute strSQL
          strSQL = "Select * From Tour"
          rsTour.Open strSQL, cn, adOpenStatic, adLockPessimistic
          Set fgdTour.DataSource = rsTour
        End If
        Set fgdTour.DataSource = rsTour
        If rsTour.RecordCount <> 0 Then
          rsTour.Update
        End If
    The code works great for two of my forms, but for the other two it doesn't. It gives me an error saying "Data type mismatch in criteria expression" and highlights the statement :

    Code:
    cn.Execute strSQL
    Please help.... I've gone over and over the code for errors but could find none, I mean it works perfectly for the other two forms.
    Last edited by WizBang; June 28th, 2012 at 03:03 PM. Reason: Formatted code with indentation for clarity

  2. #2
    Join Date
    Dec 2001
    Posts
    6,332

    Re: "Data type mismatch in criteria expression" in SQL statement

    Try adding a line immediately after defining the strSQL string (just above the execute line) to print the string to the Immediate/Debug window.
    Code:
    Debug.Print strSQL
    Does the code reside in a module for each form/button to access, or does each button have a duplicate copy of the code?
    Please remember to rate the posts and threads that you find useful.
    How can something be both new and improved at the same time?

  3. #3
    Join Date
    Jul 2006
    Location
    Germany
    Posts
    3,725

    Re: "Data type mismatch in criteria expression" in SQL statement

    Spontaneaously I'd suppose, the [Tour ID] field being numeric, probably being an index key, too.

    Therefore your SQL statement should rather be
    Code:
    strSQL = "Delete From Tour Where [Tour ID] = " & Val(fgdTour.TextMatrix(fgdTour.Row, 1))
    No quotes and using the Val function because TextMatrix() property always returns a string.
    Hm... come to think of it...
    This should work, too:
    Code:
    strSQL = "Delete From Tour Where [Tour ID] = " & fgdTour.TextMatrix(fgdTour.Row, 1)
    Simply leave away the single quotes which male SQL think the argument is of type string.
    It works since we build an sQL-string and append the numeric value in form of string characters. The & operator when using Cal() anyway converts to a string when appending to other characters.

Tags for this Thread

Posting Permissions

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





Click Here to Expand Forum to Full Width

Featured