Parameter query only working first time.

[Sypher2]

Hello,

I'm trying my hand at the Parameter collection to hopefully make repetitive queries easier. I have a list box of employees and I use the SSN as the parameter for a stored procedure. The query gets a number and puts it into a text box. The number is correct for the first query, but gives the same number for any subsequent queries.

Private Sub lstNames_Click()
Dim i As Long
    
    lblSSN.Caption = "": txtUns.Text = ""
    i = lstNames.ItemData(lstNames.ListIndex)
    lblSSN.Caption = Employee(i).SSN
    With cmd
        .CommandText = "_ParamAttendStats"
        .CommandType = adCmdStoredProc
        .Parameters("SocSecNum") = lblSSN.Caption
    End With
    rs.Open cmd, , adOpenStatic, adLockReadOnly
    txtUns.Text = rs.RecordCount
    rs.Close
    Set rs = Nothing
End Sub

I created the parameter and set the active connection in the Form_Load event.

Thanks for any help.

 

[gigsvoo]

Should be this:

Private Sub lstNames_Click()
Dim i As Long
txtUns.Text = lstNames.Text
With cmd
.CommandText = "_ParamAttendStats"
.CommandType = adCmdStoredProc
.Parameters("SocSecNum&quot = txtUns.Text
End With
rs.Open cmd, , adOpenStatic, adLockReadOnly
rs.Close
Set rs = Nothing
End Sub
Thanks

Neo

http://communities.msn.com.my/VisualBasicwithNeo