syntax for exec sp w/ varchar parameter as cbo rowsource?

[NKTower]

I have a working stored proc that returns alpha list properly when given
first character. I want to use it as row source for a combo box. This code
doesn't return anything. Where am I off?

Private Sub txt_FirstLetter_KeyUp(KeyCode As Integer, Shift As Integer)
Dim str_KeyStroke As String

str_KeyStroke = UCase$(Chr$(KeyCode))
If (str_KeyStroke >= "A") And (str_KeyStroke <= "Z") Then
Me.cbo_SubsetNames.RowSource = _
"EXEC sp_cbo_source_EmployeeName '" & str_KeyStroke & "'"
Me.cbo_SubsetNames.SetFocus
SendKeys str_KeyStroke ' put the char into the combo
Me.cbo_SubsetNames.Dropdown
End If
End Sub


The sp takes care of the "like X%"
Those are apostrophes being wrapped around str_KeyStroke.
The sp arg is @FirstKey varchar(1)
and works when the sp is executed from the Access Query page.

 

[Sylvain Lafontaine]

Probably because you have forgot to put a SET NOCOUNT ON at the beginning of
your SP. Also, don't use the prefix sp_ for your SPs; as it has a special
meaning for SQL-Server, will change the compilation model and can lead to
subtil bugs.

 

[NKTower]

Still no go. I did have the SET NOCOUNT ON. I changed the name etc., did
refresh in Access to make sure revised sp was visible, etc.
I still get empty record set. But opening the p_cbo_source_EmployeeName
from the Query page and providing a letter to the prompt/dialog box results
in the correct results.
Could it be inconsistency in how the arg is defined in sp vs. how it is
being called? I've tried both apostrophes and quotes (chr$(34) around the
letter. I've tried adding
Me.cbo_SubsetNames.Requery
- no change.

It DOES work if I generate an SQL statement on the fly in this routine, but
I'd rather not. Here's the create script, stripped of comments

USE [Orion545]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[p_cbo_source_EmployeeName]
@FirstLetter varchar(1)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT [last_name] + ', ' + [first_name] + ' ' +
IsNull([middle_name_or_initial],'')
AS MemberName,
ID
FROM dbo.tblEmployee
WHERE ( last_name like @FirstLetter + '%' )
ORDER BY last_name,first_name,middle_name_or_initial;
END


Private Sub txt_FirstLetter_KeyUp(KeyCode As Integer, Shift As Integer)
Dim str_KeyStroke As String

str_KeyStroke = UCase$(Chr$(KeyCode))
If (str_KeyStroke >= "A") And (str_KeyStroke <= "Z") Then
Me.cbo_SubsetNames.RowSource = "EXEC p_cbo_source_EmployeeName '" &
str_KeyStroke & "'"
Me.cbo_SubsetNames.SetFocus
SendKeys str_KeyStroke ' put the char into the combo
Me.cbo_SubsetNames.Dropdown
End If
End Sub

 

[Sylvain Lafontaine]

Maybe a schema problem: try adding the prefix dbo. before the name of your
SP:

EXEC dbo.p_dbo_ ...

Are you sure that the schema is empty or if you don't see the result in the
combobox? Check the properties of your combobox are OK: number of columns,
width of each column, etc. If you change the SP to be sure to return one or
more rows, for example by removing the LIKE condition, what are you seeing
in the combobox? (For example, what number of rows are displayed by the
combobox.)

Also, make sure the parameter @FirstLetter is correctly sent by printing it
somewhere (ie, in a table or even by adding it to the resultset. Add to
that the number of rows (Select @@Rowcount).

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Still no go. I did have the SET NOCOUNT ON. I changed the name etc., did
refresh in Access to make sure revised sp was visible, etc.
I still get empty record set. But opening the p_cbo_source_EmployeeName
from the Query page and providing a letter to the prompt/dialog box
results
in the correct results.
Could it be inconsistency in how the arg is defined in sp vs. how it is
being called? I've tried both apostrophes and quotes (chr$(34) around the
letter. I've tried adding
Me.cbo_SubsetNames.Requery
- no change.

It DOES work if I generate an SQL statement on the fly in this routine,
but
I'd rather not. Here's the create script, stripped of comments

USE [Orion545]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[p_cbo_source_EmployeeName]
@FirstLetter varchar(1)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT [last_name] + ', ' + [first_name] + ' ' +
IsNull([middle_name_or_initial],'')
AS MemberName,
ID
FROM dbo.tblEmployee
WHERE ( last_name like @FirstLetter + '%' )
ORDER BY last_name,first_name,middle_name_or_initial;
END


Private Sub txt_FirstLetter_KeyUp(KeyCode As Integer, Shift As Integer)
Dim str_KeyStroke As String

str_KeyStroke = UCase$(Chr$(KeyCode))
If (str_KeyStroke >= "A") And (str_KeyStroke <= "Z") Then
Me.cbo_SubsetNames.RowSource = "EXEC p_cbo_source_EmployeeName '" &
str_KeyStroke & "'"
Me.cbo_SubsetNames.SetFocus
SendKeys str_KeyStroke ' put the char into the combo
Me.cbo_SubsetNames.Dropdown
End If
End Sub

Probably because you have forgot to put a SET NOCOUNT ON at the beginning
of
your SP. Also, don't use the prefix sp_ for your SPs; as it has a
special
meaning for SQL-Server, will change the compilation model and can lead to
subtil bugs.

 

[Sylvain Lafontaine]

You code work properly on my machine; excerpt for the fact that the SendKeys
generate an infinite loop of calls to Combo0_KeyUP. (I didn't use a
seperate text box for my test.)

Did you set the Row Source Type of the combobox to Table/View/StoredProc?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Maybe a schema problem: try adding the prefix dbo. before the name of your
SP:

EXEC dbo.p_dbo_ ...

Are you sure that the schema is empty or if you don't see the result in
the combobox? Check the properties of your combobox are OK: number of
columns, width of each column, etc. If you change the SP to be sure to
return one or more rows, for example by removing the LIKE condition, what
are you seeing in the combobox? (For example, what number of rows are
displayed by the combobox.)

Also, make sure the parameter @FirstLetter is correctly sent by printing
it somewhere (ie, in a table or even by adding it to the resultset. Add
to that the number of rows (Select @@Rowcount).

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Still no go. I did have the SET NOCOUNT ON. I changed the name etc.,
did
refresh in Access to make sure revised sp was visible, etc.
I still get empty record set. But opening the p_cbo_source_EmployeeName
from the Query page and providing a letter to the prompt/dialog box
results
in the correct results.
Could it be inconsistency in how the arg is defined in sp vs. how it is
being called? I've tried both apostrophes and quotes (chr$(34) around
the
letter. I've tried adding
Me.cbo_SubsetNames.Requery
- no change.

It DOES work if I generate an SQL statement on the fly in this routine,
but
I'd rather not. Here's the create script, stripped of comments

USE [Orion545]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[p_cbo_source_EmployeeName]
@FirstLetter varchar(1)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT [last_name] + ', ' + [first_name] + ' ' +
IsNull([middle_name_or_initial],'')
AS MemberName,
ID
FROM dbo.tblEmployee
WHERE ( last_name like @FirstLetter + '%' )
ORDER BY last_name,first_name,middle_name_or_initial;
END


Private Sub txt_FirstLetter_KeyUp(KeyCode As Integer, Shift As Integer)
Dim str_KeyStroke As String

str_KeyStroke = UCase$(Chr$(KeyCode))
If (str_KeyStroke >= "A") And (str_KeyStroke <= "Z") Then
Me.cbo_SubsetNames.RowSource = "EXEC p_cbo_source_EmployeeName '" &
str_KeyStroke & "'"
Me.cbo_SubsetNames.SetFocus
SendKeys str_KeyStroke ' put the char into the combo
Me.cbo_SubsetNames.Dropdown
End If
End Sub

Probably because you have forgot to put a SET NOCOUNT ON at the
beginning of
your SP. Also, don't use the prefix sp_ for your SPs; as it has a
special
meaning for SQL-Server, will change the compilation model and can lead
to
subtil bugs.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


I have a working stored proc that returns alpha list properly when
given
first character. I want to use it as row source for a combo box.
This
code
doesn't return anything. Where am I off?

Private Sub txt_FirstLetter_KeyUp(KeyCode As Integer, Shift As
Integer)
Dim str_KeyStroke As String

str_KeyStroke = UCase$(Chr$(KeyCode))
If (str_KeyStroke >= "A") And (str_KeyStroke <= "Z") Then
Me.cbo_SubsetNames.RowSource = _
"EXEC sp_cbo_source_EmployeeName '" & str_KeyStroke & "'"
Me.cbo_SubsetNames.SetFocus
SendKeys str_KeyStroke ' put the char into the combo
Me.cbo_SubsetNames.Dropdown
End If
End Sub


The sp takes care of the "like X%"
Those are apostrophes being wrapped around str_KeyStroke.
The sp arg is @FirstKey varchar(1)
and works when the sp is executed from the Access Query page.

 

[Sylvain Lafontaine]

Also, can you call this SP by double-clicking on it in the Database Window?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

You code work properly on my machine; excerpt for the fact that the
SendKeys generate an infinite loop of calls to Combo0_KeyUP. (I didn't
use a seperate text box for my test.)

Did you set the Row Source Type of the combobox to Table/View/StoredProc?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Maybe a schema problem: try adding the prefix dbo. before the name of
your SP:

EXEC dbo.p_dbo_ ...

Are you sure that the schema is empty or if you don't see the result in
the combobox? Check the properties of your combobox are OK: number of
columns, width of each column, etc. If you change the SP to be sure to
return one or more rows, for example by removing the LIKE condition, what
are you seeing in the combobox? (For example, what number of rows are
displayed by the combobox.)

Also, make sure the parameter @FirstLetter is correctly sent by printing
it somewhere (ie, in a table or even by adding it to the resultset. Add
to that the number of rows (Select @@Rowcount).

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Still no go. I did have the SET NOCOUNT ON. I changed the name etc.,
did
refresh in Access to make sure revised sp was visible, etc.
I still get empty record set. But opening the p_cbo_source_EmployeeName
from the Query page and providing a letter to the prompt/dialog box
results
in the correct results.
Could it be inconsistency in how the arg is defined in sp vs. how it is
being called? I've tried both apostrophes and quotes (chr$(34) around
the
letter. I've tried adding
Me.cbo_SubsetNames.Requery
- no change.

It DOES work if I generate an SQL statement on the fly in this routine,
but
I'd rather not. Here's the create script, stripped of comments

USE [Orion545]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[p_cbo_source_EmployeeName]
@FirstLetter varchar(1)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT [last_name] + ', ' + [first_name] + ' ' +
IsNull([middle_name_or_initial],'')
AS MemberName,
ID
FROM dbo.tblEmployee
WHERE ( last_name like @FirstLetter + '%' )
ORDER BY last_name,first_name,middle_name_or_initial;
END


Private Sub txt_FirstLetter_KeyUp(KeyCode As Integer, Shift As Integer)
Dim str_KeyStroke As String

str_KeyStroke = UCase$(Chr$(KeyCode))
If (str_KeyStroke >= "A") And (str_KeyStroke <= "Z") Then
Me.cbo_SubsetNames.RowSource = "EXEC p_cbo_source_EmployeeName '" &
str_KeyStroke & "'"
Me.cbo_SubsetNames.SetFocus
SendKeys str_KeyStroke ' put the char into the combo
Me.cbo_SubsetNames.Dropdown
End If
End Sub



:

Probably because you have forgot to put a SET NOCOUNT ON at the
beginning of
your SP. Also, don't use the prefix sp_ for your SPs; as it has a
special
meaning for SQL-Server, will change the compilation model and can lead
to
subtil bugs.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


I have a working stored proc that returns alpha list properly when
given
first character. I want to use it as row source for a combo box.
This
code
doesn't return anything. Where am I off?

Private Sub txt_FirstLetter_KeyUp(KeyCode As Integer, Shift As
Integer)
Dim str_KeyStroke As String

str_KeyStroke = UCase$(Chr$(KeyCode))
If (str_KeyStroke >= "A") And (str_KeyStroke <= "Z") Then
Me.cbo_SubsetNames.RowSource = _
"EXEC sp_cbo_source_EmployeeName '" & str_KeyStroke & "'"
Me.cbo_SubsetNames.SetFocus
SendKeys str_KeyStroke ' put the char into the combo
Me.cbo_SubsetNames.Dropdown
End If
End Sub


The sp takes care of the "like X%"
Those are apostrophes being wrapped around str_KeyStroke.
The sp arg is @FirstKey varchar(1)
and works when the sp is executed from the Access Query page.

 

[Robert Morley]

Your LIKE statement appears to be incorrect. It believe it should be:

WHERE ( last_name like '''' + @FirstLetter + '%''' )

That's four apostrophes before (two surrounding apostrophes plus two inside to produce an actual apostrophe) and three apostrophes
in total after the % (again, two to produce the apostrophe and one to close the quotes).

Try that and see if it works.


Rob

 

[NKTower]

Well, it's resolved. It isn't what anybody thought.

I had tested it in a separate ADP, worked fine. Didn't work in the 'real'
system. Went back to the working ADP to the properties page for the combo -
the Row Source Type property in the working version was
"Table/View/StoredProc"
The Row Source Property in the non-working version was
"Table/Query"
and its drop-down in the property page did not offer "Table/View/StoredProc"
What the ???

I deleted the combo and created it from scratch - and all is well with the
world.

I do not remember how the original combo was created, but I suspect it might
have been with the Wizard.

For anybody who might stumble on this problem in the future - here's what
worked...

cbo_EmployeeName properties (data tab. only important properties shown)

Row Source Type Table/View/StoredProc
Row Source EXEC p_combo_EmployeeName_source Me.txt_FirstLetter
Bound Column 1
LImit To List Yes
Auto Expand Yes

(format tab)
Decimal Places Auto
Column Count 2
Column Heads No
Column Widths 1.5";0"
List Rows 12
List Width Auto


Private Sub txt_FirstLetter_KeyUp(KeyCode As Integer, Shift As Integer)
Dim str_KeyStroke As String

str_KeyStroke = UCase$(Chr$(KeyCode))
If (str_KeyStroke >= "A") And (str_KeyStroke <= "Z") Then
Me.cbo_EmployeeName.RowSource = _
"EXEC p_combo_EmployeeName_source '" & str_KeyStroke & "'"
' thats apostrophe quote ampersand str_keyStroke
' ampersand quote apostrophe quote
Me.cbo_EmployeeName.SetFocus
SendKeys str_KeyStroke
Me.cbo_EmployeeName.Dropdown
End If
End Sub

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
ALTER PROCEDURE [dbo].[p_combo_EmployeeName_source]
@FirstChar varchar(1)
AS
BEGIN
SET NOCOUNT ON;

SELECT TOP (100) PERCENT last_name + ', ' + first_name + ' ' +
IsNull( middle_name_or_initial,'') AS EmployeeName, ID
FROM dbo.tblEmployee
WHERE ( last_name like @FirstChar + '%' )
ORDER BY last_name, first_name

END


"