Professional Web Applications Themes

=> Recordset value in ADO - Microsoft SQL / MS SQL Server

Hello, I have created a recordset in ADO and would like to perform two different operations dependant on if the recordset contains one or greater than one record. However I seem only to receive the .Recordset value of -1, disregardless of how many records are contained in the cursor. How can I get around this? The code I am using is as follows. Thank you for any suggestions you might have. Kind Regards Rhonda Sub displayStaffSurname(inSurname As String) On Error GoTo Err_displayStaffSurname 'Proceedure called from ' => Form: FrmPersonnel ' => CmdBtn: cmdSearch_Click() 'Declaration Dim cnn As ADODB.Connection Dim cmd ...

  1. #1

    Default => Recordset value in ADO

    Hello,

    I have created a recordset in ADO and would like to perform
    two different operations dependant on if the recordset
    contains one or greater than one record. However I
    seem only to receive the .Recordset value of -1,
    disregardless of how many records are contained in the
    cursor. How can I get around this?

    The code I am using is as follows.

    Thank you for any suggestions you might have.

    Kind Regards
    Rhonda


    Sub displayStaffSurname(inSurname As String)
    On Error GoTo Err_displayStaffSurname

    'Proceedure called from
    ' => Form: FrmPersonnel
    ' => CmdBtn: cmdSearch_Click()

    'Declaration
    Dim cnn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim rst As ADODB.Recordset

    Set cnn = New ADODB.Connection
    Set cmd = New ADODB.Command
    Set rst = New ADODB.Recordset

    'Open the connection
    Set cnn = CurrentProject.Connection

    'Set up the Command objects's Connection, SQL and
    parameter types
    With cmd
    .ActiveConnection = cnn
    .CommandText = "SELECT * FROM tblStaff WHERE
    Surname = " & "'" & inSurname & "'"
    End With

    Set rst = cmd.Execute

    If rst.EOF Then
    MsgBox "No Matching Surname, Try Agan :-)",
    vbOKOnly, "Incorrect Entry"
    ElseIf (rst.RecordCount = 1) Then '**** PROBLEM HERE
    'display singular record on Main Personnel Form
    Forms![frmPersonnel]!txtSurname = Trim(rst!Surname)
    Forms![frmPersonnel]!txtFirstName = Trim(rst!
    Firstname)
    Forms![frmPersonnel]!txtDepot = Trim(rst!Depot)
    Forms![frmPersonnel]!txtDepartment = Trim(rst!
    Department)
    ElseIf (rst.RecordCount > 1) Then '**** PROBLEM HERE
    'loop through and populate fields on a different
    'form
    End If


    cnn.Close
    Set cmd = Nothing

    Exit_displayStaffSurname:
    Exit Sub

    Err_displayStaffSurname:
    MsgBox Err.Description
    Resume Exit_displayStaffSurname

    End Sub
    Rhonda Fischer Guest

  2. #2

    Default Re: => Recordset value in ADO

    Rhonda,

    The RecordCount property returns -1 if the number of records is unknown at
    runtime.
    Open the RecordSet as a ForwardOnly or Static cursor instead.

    rst.Open sSQL, cnn, adOpenStatic, adLockReadOnly
    or
    rst.Open sSQL, cnn, adOpenForwardOnly, adLockReadOnly

    Also, I wouldn't bother using the Command object.

    I hope this helps,
    Danny.

    "Rhonda Fischer" <Rhonda.FischerTurners-Distribution.com> wrote in message
    news:073401c3479e$5b38cd80$a601280aphx.gbl...
    > Hello,
    >
    > I have created a recordset in ADO and would like to perform
    > two different operations dependant on if the recordset
    > contains one or greater than one record. However I
    > seem only to receive the .Recordset value of -1,
    > disregardless of how many records are contained in the
    > cursor. How can I get around this?
    >
    > The code I am using is as follows.
    >
    > Thank you for any suggestions you might have.
    >
    > Kind Regards
    > Rhonda
    >
    >
    > Sub displayStaffSurname(inSurname As String)
    > On Error GoTo Err_displayStaffSurname
    >
    > 'Proceedure called from
    > ' => Form: FrmPersonnel
    > ' => CmdBtn: cmdSearch_Click()
    >
    > 'Declaration
    > Dim cnn As ADODB.Connection
    > Dim cmd As ADODB.Command
    > Dim rst As ADODB.Recordset
    >
    > Set cnn = New ADODB.Connection
    > Set cmd = New ADODB.Command
    > Set rst = New ADODB.Recordset
    >
    > 'Open the connection
    > Set cnn = CurrentProject.Connection
    >
    > 'Set up the Command objects's Connection, SQL and
    > parameter types
    > With cmd
    > .ActiveConnection = cnn
    > .CommandText = "SELECT * FROM tblStaff WHERE
    > Surname = " & "'" & inSurname & "'"
    > End With
    >
    > Set rst = cmd.Execute
    >
    > If rst.EOF Then
    > MsgBox "No Matching Surname, Try Agan :-)",
    > vbOKOnly, "Incorrect Entry"
    > ElseIf (rst.RecordCount = 1) Then '**** PROBLEM HERE
    > 'display singular record on Main Personnel Form
    > Forms![frmPersonnel]!txtSurname = Trim(rst!Surname)
    > Forms![frmPersonnel]!txtFirstName = Trim(rst!
    > Firstname)
    > Forms![frmPersonnel]!txtDepot = Trim(rst!Depot)
    > Forms![frmPersonnel]!txtDepartment = Trim(rst!
    > Department)
    > ElseIf (rst.RecordCount > 1) Then '**** PROBLEM HERE
    > 'loop through and populate fields on a different
    > 'form
    > End If
    >
    >
    > cnn.Close
    > Set cmd = Nothing
    >
    > Exit_displayStaffSurname:
    > Exit Sub
    >
    > Err_displayStaffSurname:
    > MsgBox Err.Description
    > Resume Exit_displayStaffSurname
    >
    > End Sub

    Daniel Johnson Guest

  3. #3

    Default Re: => Recordset value in ADO

    Daniel Johnson wrote:
    > Rhonda,
    >
    > The RecordCount property returns -1 if the number of records is
    > unknown at runtime.
    > Open the RecordSet as a ForwardOnly or Static cursor instead.
    ForwardOnly returns -1. Since only a single record is retrieved at a time,
    the provider will not know the number of records until the last record in
    the resultset has been retrieved.

    Bob Barrows


    Bob Barrows Guest

  4. #4

    Default Re: => Recordset value in ADO

    Thank you very much it worked, I'm very happy :)

    Bestest Regards
    Rhonda



    >-----Original Message-----
    >Daniel Johnson wrote:
    >> Rhonda,
    >>
    >> The RecordCount property returns -1 if the number of
    records is
    >> unknown at runtime.
    >> Open the RecordSet as a ForwardOnly or Static cursor
    instead.
    >ForwardOnly returns -1. Since only a single record is
    retrieved at a time,
    >the provider will not know the number of records until
    the last record in
    >the resultset has been retrieved.
    >
    >Bob Barrows
    >
    >
    >.
    >
    Rhonda Fischer Guest

Similar Threads

  1. RECORDSET
    By NAIME ESTEVES in forum ASP.NET
    Replies: 10
    Last Post: February 21st, 03:52 PM
  2. Recordset Help
    By LiquidD33 in forum Dreamweaver AppDev
    Replies: 1
    Last Post: May 12th, 05:23 PM
  3. ASP Recordset Help!
    By longsdale in forum Dreamweaver AppDev
    Replies: 1
    Last Post: March 31st, 09:37 PM
  4. Using a Recordset
    By John Cowen in forum Dreamweaver AppDev
    Replies: 2
    Last Post: March 17th, 12:16 AM
  5. Replies: 8
    Last Post: September 24th, 11:07 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139