Passing a combo value over filter on the server

[scliffe]

I've looked at stored procedures but still cannot find the best way to filter by a combobox at the server end - I'm transferring as much across to Views as I can so that I can eventually go for an adp. Unfortunately, I can't find a way to transfer an ID number (or group of numbers) across to use as the filter. Any suggestions? I can provide code if required.

 

[jrbarnett]

Use a stored procedure instead of a view, something like:

CREATE PROCEDURE dbo.uspMyProc
 @intKey int
AS
 SELECT f1, f2, f3
 FROM table
 WHERE f1 = @intkey

From your application, use something like:

Dim cmd As ADODB.Command
Dim rst As ADODB.RecordSet

With Cmd
 .Connection = ' Your ADODB connection object
 .CommandType = adCmdStoredProc
 .CommandText = "uspMyProc"
 .Parameters("@intkey") = 123
 Set rst = .Execute
End With

You can then use your recordset object as normal to access or process the data.

John

 

[scliffe]

I've found some commented out code that a colleague had entered before going away... Would it be easier to adjust this? As you can tell, I'm rather inexperienced with regards SPs

'Write the admissions number to the stored procedure

' add records to Stored Procedure
'
'strsqlread = "SELECT * FROM @TEMPStudID"
'Dim recread As Recordset
'Set recread = New Recordset
'recread.Open (strsqlread), cn, adOpenDynamic, adLockOptimistic
'
'Dim recID As Recordset
'Set recID = New Recordset
'Dim strsqlID As String

'strsqlID = "SELECT DISTINCT dbo.tbl_Student_Name.AdmissionsNumber " & _
' "FROM dbo.tbl_Student_Name " & _
' "WHERE (((dbo.tbl_Student_Name.AdmissionsNumber)= '" & Forms!frm_StudentInfo!AdmissionsNumber & "')"
'
'recID.Open (strsqlID), cn, adOpenDynamic, adLockOptimistic
'
'recID.MoveFirst
'
'Do Until recID.EOF = True
'
'recread.AddNew
'
'recread!owner = recID!tut_code
'recread!stuc = AdmissionsNumber
'
'recread.Update
'recID.MoveNext
'Loop
'
'MsgBox "One pastoral log entry added!"
'
'pastlist.Requery