How to know if a record exists

Question

How to know if a record exists

Navigation

#1 by (1 votes)

0

I work with vb6 and sql server, I need to know if a record exists for that I have a class to connect to the database.

Public cn As ADODB.Connection
Public rs As ADODB.Record

Public Sub Conectar()
  Set cn = New ADODB.Connection
  Set rs = New ADODB.Recordset
  rs.CursorLocation = adUseClient
'  cn.Open "Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=gestvehicular;User=root;Password=123456;Option=3;"
  cn.Open "Provider=SQLNCLI10;Server=.;Database=Northwind;Trusted_Connection=yes;"
End Sub

Public Sub Desconectar()
  On Local Error Resume Next
  rs.Close
  Set rs = Nothing
  cn.Close
  Set cn = Nothing
End Sub

I have created a Function in which I pass the id parameter of type integer to be able to make this query to the database

Public Function Existe(id As Integer) As Boolean


On Error GoTo tratarError
  Dim cmd As New Command
  Conectar
  With cmd
    .ActiveConnection = cn
    .CommandText = "SELECT COUNT(*) FROM Employees WHERE EmployeeID=@id"
    .CommandType = adCmdText
    .NamedParameters = True
    .Parameters.Append .CreateParameter("@id", adInteger, adParamInput, , id)
    .Execute
    If (rs.EOF) Then

  End With
  On Error GoTo 0
  Desconectar
  Exit Function
tratarError:
  MsgBox Err.Description
End Function

With the query sql that I have if it exists in the database it gives me 1 and if it does not exist it gives me 0 then what I want to do is that if I give 1 that returns true and if it gives 0 that returns false I do not know how to do that with ADO.

vb6

asked by Pedro Ávila 09.08.2017 в 06:31

source

1 answer

How to save the data of a Multiple SELECT in a single field of my Database? Why do not I get the error message in the login?

score 1 · Accepted Answer

The problems I find in your role are the following:

The lack of initialization of the recordset rs by Set rs = .Execute
And the lack of return handling to return True or False through the ternary function iif(rs.Fields(0) > 0, True, False)

.

Public Function Existe(id As Integer) As Boolean

On Error GoTo tratarError

    Dim cmd As New Command
    Dim Existe as Boolean
    Conectar
    With cmd
        .ActiveConnection = cn
        .CommandText = "SELECT COUNT(*) FROM Employees WHERE EmployeeID=@id"
        .CommandType = adCmdText
        .NamedParameters = True
        .Parameters.Append .CreateParameter("@id", adInteger, adParamInput, , id)

        Set rs = .Execute
        Existe = iif(rs.Fields(0) > 0, True, False)
    End With
    On Error GoTo 0

    Desconectar
    return Existe
    Exit Function

tratarError:
    MsgBox Err.Description

End Function

The next thing that would improve in your query and that I suggest you use as a good practice is to replace the use of COUNT() with the use of the function EXISTS() , in queries where the existence is determined on a single record as I imagine is your case, the improvement is minimal , but where it is more remarkable, is when the COUNT() can count multiple records, that is due, counted in an academic way, that the EXISTS only need to know that there is at least one row to return a True , however the COUNT() obviously requires "read" all to return the amount. There is a lot of material on this practice, I suggest if you want to read more this . It would be necessary to make some modifications to your routine. On the one hand the SELECT would be like this:

.CommandText = "SELECT CASE WHEN EXISTS(SELECT * FROM Employees WHERE EmployeeID=@id) THEN 1 ELSE 0 END"

And the control of the return to determine the existence would be:

Existe = iif(rs.Fields(0) = 1, True, False)