Filling in a listbox from a table in SQL Server from Stored Procedure

[Ron]

I am asking this question again from earlier today with new info now
because I am still having trouble...
I have a listbox lets call is lstsearch and I also have a textbox with
a studentid on a form.

When a button, Search, is clicked I want the lstbox to be populated
with info for the current student id that is in the textbox.

So for example....
I have a table in SQLServer called tblpayment and in there are fields
like:
STUDENT ID CHARGE TYPE
=======================
11111 250.00 card
11111 100.00 Cash
11222 100.00 card

so then If the textbox on the form is set to the student id 11111 I
want the listbox to display:
250.00 card
100.00 cash

I have got some code and a stored procedure in sql server...

The stored procedure, just a select statment looks like this
CREATE PROCEDURE sp_GetPayments
(
@studentid int
)
as
select *
from tblpayments
where studentid = @studentid
GO

the code that I have is this:

Private Sub Command18_Click()
Option Compare Database
Dim rst As New ADODB.Recordset
Dim Cnn As New ADODB.Connection
Dim cmd As ADODB.Command


Set rst = New ADODB.Recordset
Set Cnn = New ADODB.Connection
Set cmd = New ADODB.Command
ConnectionString = "Provider=SQLOLEDB.1;Integrated
Security=SSPI;PersistSecurity Info=False;User
ID=test;password=tester;Initial Catalog=test;Data Source = psbeh18"

Cnn.Open ConnectionString
Set cmd.ActiveConnection = Cnn

cmd.CommandText = "sp_getPayments"
cmd.CommandType = adCmdStoredProc

rst.Open cmd
lstsearch.RowSource = sp_getpayments

rst.Close
Cnn.Close
Set rst = Nothing
Set Cnn = Nothing
Set cmd = Nothing
End Sub


Now my textbox that has the student ID in it is called txtsearchd on
the form.

Anyone offer any help?

 

[Albert D. Kallal]

Any reason you not useing a linked table?

all you need is:

lst.RowSource = "SELECT [TYPE], [PAYMENT] " & _
"FROM Table " & _
"WHERE studentid =" & txtUserID.Value


You can put the above code in the after update event of the text box (called
txtUSerID in above).

When a button, Search, is clicked I want the lstbox to be populated
with info for the current student id that is in the textbox.

Or, as above, move the code behind your button. You only need one line of
code here...not exactly sure why you are writing up a recordset, and using
ADO...it is not needed...

 

[Ron]

yes because for this assignment we are supposed to use ado and use the
stored procedure. I think I have successfully connected to the sql db,
well i know because i can write data to it from a form but i can not
get this to work, basically for this stored procedure to run.

Any reason you not useing a linked table?

all you need is:

lst.RowSource = "SELECT [TYPE], [PAYMENT] " & _
"FROM Table " & _
"WHERE studentid =" & txtUserID.Value


You can put the above code in the after update event of the text box (called
txtUSerID in above).

When a button, Search, is clicked I want the lstbox to be populated
with info for the current student id that is in the textbox.

Or, as above, move the code behind your button. You only need one line of
code here...not exactly sure why you are writing up a recordset, and using
ADO...it is not needed...