Stored Procedure as String and rowsource

[Carol Williams]

Hello, everyone.

Within the past few days there was a post about using a
stored procedure with a parameter as thr recordsource for
a listbox. An answer was supplied by SFAxess. I have
included part of that answer below.

I tried something similiar with a string variable, and
found out I was having problems passing strings as a
variable. If the string happened to be numeric, no
problem, but a string doesn't work. I am pretty sure that
the problem is that the ADP wants a string surrounded
by "", but SQL server wants a string surrounded by single
quotes like ''.

Replace lngParam below as long with strParam as string,
and then strProc="EXEC WL_Nxt_stp_sfrm_sp " & strParam
ignores any string that is also not a number. In my case,
when I use a string security Cusips like 001123A11 or
2295W1222 do not provide me with a result set.

Can someone help me with the syntax so that I can return
strings as well?

Thanks




Private Sub SetListSource()
Dim lngParam as long
Dim strProc as string

lngParam=Forms!frmWatchlist!lstWLCriteria
strProc="EXEC WL_Nxt_stp_sfrm_sp " & lngParam

me.lstYourListBox.RowSource=strProc
me.lstYourListBox.requery
End Sub

 

[Steve Jorgensen]

Hello, everyone.

Within the past few days there was a post about using a
stored procedure with a parameter as thr recordsource for
a listbox. An answer was supplied by SFAxess. I have
included part of that answer below.

I tried something similiar with a string variable, and
found out I was having problems passing strings as a
variable. If the string happened to be numeric, no
problem, but a string doesn't work. I am pretty sure that
the problem is that the ADP wants a string surrounded
by "", but SQL server wants a string surrounded by single
quotes like ''.

Replace lngParam below as long with strParam as string,
and then strProc="EXEC WL_Nxt_stp_sfrm_sp " & strParam
ignores any string that is also not a number. In my case,
when I use a string security Cusips like 001123A11 or
2295W1222 do not provide me with a result set.

Can someone help me with the syntax so that I can return
strings as well?

Thanks




Private Sub SetListSource()
Dim lngParam as long
Dim strProc as string

lngParam=Forms!frmWatchlist!lstWLCriteria
strProc="EXEC WL_Nxt_stp_sfrm_sp " & lngParam

me.lstYourListBox.RowSource=strProc
me.lstYourListBox.requery
End Sub

So long as you can be sure the string will not contain any apostrophe
characters, just add the surrounding single quotes when you build the
string - as follows...

strProc="EXEC WL_Nxt_stp_sfrm_sp '" & strParam & "'"

If the string might contain apostrophes, you'll want to write a function to
escape them for you. Here's some code similar to what I've used before
(uses Replace function only available in A2002 and newer) to escape singe
quotes and also add the surrounding single quotes.

Public Function StringToSingleQuotedExpr(str As String) As String
StringToSingleQuotedExpr = "'" & Replace(str, "'", "''") & "'"
End Function

Also, it is not necessary (and adds unnecessary processing time) to requery
a listbox after setting its Rowsource property. Setting the property
causes it to requery automatically.