Error '-2147217913' performing sql query in access VBA-CodePudding

I have a form in Excel and I need to return data from a table in access. When executing an instruction like the image it returns the error "Data Type Mismatch in Criterion Expression". I already reviewed the data types in the table and still could not resolve. What could be happening?

    Sub pesquisar()

Set rs = New ADODB.Recordset

conectdb

rs.Open "SELECT * FROM TbApolice WHERE Contrato='" & UserForm.txt_certificado.Value & "'", db, adOpenKeyset, adLockReadOnly


    If UserForm.txt_certificado.Value <> "" Then
        UserForm.txt_nome = rs!Nome
        UserForm.txt_cpf = rs!CPF
        UserForm.txt_iniciovigencia = rs!Inicio_vigencia
        UserForm.txt_fimvigencia = rs!Fim_de_vigencia
        UserForm.txt_premio = rs!Premio
    Else
        MsgBox "Segurado não localizado", vbInformation, "LOCALIZAR"
    End If

If Not rs Is Nothing Then
    rs.Close
    Set rs = Nothing
End If

fechadb

End Sub

I've already made some attempts to point break and debug the code, in addition to validating all fields and data types, but I didn't get any results.

CodePudding user response：

Try using a parameterized query.

Option Explicit

Sub pesquisar()

    Const SQL = "SELECT * FROM TbApolice WHERE Contrato = ?"

    Dim Db As ADODB.Connection, cmd As ADODB.Command
    Dim rs As ADODB.Recordset, sContrato As String, n As Long
   
    With UserForm
        sContrato = Trim(.txt_certificado.Value)
        If Len(sContrato) > 0 Then
            
            Set Db = conectdb("Database11.accdb")
            Set cmd = New ADODB.Command
            With cmd
                .ActiveConnection = Db
                .CommandText = SQL
                .Parameters.Append .CreateParameter("p1", adVarWChar, adParamInput, 255)
                Set rs = .Execute(n, sContrato)
            End With
        
            If rs.EOF Then
                MsgBox "Segurado não localizado", vbInformation, "LOCALIZAR"
            Else
                .txt_nome = rs!Nome
                .txt_cpf = rs!CPF
                .txt_iniciovigencia = rs!Inicio_vigencia
                .txt_fimvigencia = rs!Fim_de_vigencia
                .txt_premio = rs!Premio
                rs.Close
                Set rs = Nothing
            End If
            
        End If
    End With
    'fechadb

End Sub

Function conectdb(s As String) As ADODB.Connection
    Set conectdb = New ADODB.Connection
    conectdb.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & s
End Function

CodePudding user response：

As stated in these question How to deal with single quote in Word VBA SQL query? your SQL query is missing a single quote:

rs.Open "SELECT * FROM TbApolice WHERE Contrato='" & UserForm.txt_certificado.Value & "''", db, adOpenKeyset, adLockReadOnly

And as it says, your code is vulnerable to a SQL injection attack.