Getting error please help with this code.??

[Ron]

I am getting an error that procedure SP_Getpayments expects paramter
@studentid

What I am trying to do with this is execute the stores procedure that
is just this....

(
@studentid int
)
as
select *
from tblpayments
where studentid = @studentid
GO

I want to get everything from tblpayments and display it ina listbox
lstsearch on my form. Here is the code I have and I am getting the
above error. Can anyone tell me what I am doing wrong?

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=xxx;password=xxx;Initial Catalog=xxx;Data Source = psbeh18"

Cnn.Open ConnectionString

Set cmd.ActiveConnection = Cnn

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

Set rst = cmd.Execute

Set prmstudentid = cmd.CreateParameter("@studentid", adBigInt,
adParamInput, , [Forms]![frmPayments]![txtsearchid])

rst.Open cmd


Do Until rst.EOF
With Me.lstsearch
.AddItem rst.Fields(0)
.Column(1, ListCount - 1) = rst.Fields(1)
End With
rst.MoveNext
Loop

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

 

[RoyVidar]

I am getting an error that procedure SP_Getpayments expects paramter
@studentid

What I am trying to do with this is execute the stores procedure that
is just this....

(
@studentid int
)
as
select *
from tblpayments
where studentid = @studentid
GO

I want to get everything from tblpayments and display it ina listbox
lstsearch on my form. Here is the code I have and I am getting the
above error. Can anyone tell me what I am doing wrong?

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=xxx;password=xxx;Initial Catalog=xxx;Data Source = psbeh18"

Cnn.Open ConnectionString

Set cmd.ActiveConnection = Cnn

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

Set rst = cmd.Execute

Set prmstudentid = cmd.CreateParameter("@studentid", adBigInt,
adParamInput, , [Forms]![frmPayments]![txtsearchid])

rst.Open cmd


Do Until rst.EOF
With Me.lstsearch
.AddItem rst.Fields(0)
.Column(1, ListCount - 1) = rst.Fields(1)
End With
rst.MoveNext
Loop

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

What gives you that errormessage, is probably that you execute the
command object prior to feeding it the parameter (you seem to
initialize rst twice, btw).

So, to eliminate that errormessage, simply remove the .Execute
statement, and ensure that when you open rst, it is performed after
assigning the parameter - I think you need to append the parameter
too.

Hovewer, there's a mismatch between what you say you're doing, and
what you're actually doing.

"I want to get everything from tblpayments and display it ina listbox"

vs

"where studentid = @studentid"

If you want everything, then remove the where clause. If you wish to
keep the parameter, and for instance display for one student if a
parameter is given, and all if not, then there are several possible
approaches. One is to use different SPs, another to use optional
parameters (and more...) - here's a go - note - air code

ALTER PROC SP_Getpayments
(
@StudentID Int = NULL
)
AS
SELECT p.StudentID, p.SecondNamedColumn
FROM tblPayments p
WHERE p.StudentID = Coalesce(@StudentID, p.StudentID)

---

With cmd
Set .ActiveConnection = Cnn
.CommandText = "sp_getPayments"
.CommandType = adCmdStoredProc
If Len([Forms]![frmPayments]![txtsearchid] & vbNullString) Then
Set prmstudentid = .CreateParameter("@studentid", _
adBigInt, adParamInput, , _
[Forms]![frmPayments]![txtsearchid])
.Parameters.Append prmstudentid
End If
End With
Set rst = new adodb.recordset
With rst
.CursorLocation = adUseClient
.Open cmd
End With
' requires Access 2002 or later, I think
With Me!lstSearch
.RowSourceType = "Query/Table"
.ColumnCount = 2
Set .Recordset = rst
End With