Print Attributes in Stored Procedure

I've upgraded MS-Access 2002 to a MS-Access Project (adp), so now I have to
deal with more sophisticated queries (may I call them so?) like stored
procedures. I have a form with a combobox for selections and a textbox to
enter a certain value. Let us say I call the combobox @.select and the
textbox @.find. The combobox always shows the first of the items to select.
Now I want to return a message if nothing is found, or if nothing has been
entered at all. The stored procedure reads for instance:

ALTER PROCEDURE OPC
@.select nvarchar(20), @.find nvarchar (100)

AS

SET NOCOUNT ON

IF
@.find IS NULL
BEGIN
PRINT 'You didn't enter any value'
RETURN
END

IF
@.select = 'Author'
BEGIN
SELECT *
FROM Books
WHERE Author LIKE '%' + @.find + '%'
ORDER BY Author, Title, Publisher, Year
END

[And so on]

RETURN

This works correct when I enter something, but when I don't enter any value,
a message box pops up saying that the stored procedure has been executed,
but no records were found. I want to see an error message like above. It's
as simple as

ALTER PROCEDURE Hello
AS
BEGIN
PRINT 'Hello'
END
and nothing is seen. Does anybody know? Thank you.

Wim"Wim van Rosmalen" <wvanrosm@.tiscali.nl> wrote in message
news:410789c7$0$62355$5fc3050@.dreader2.news.tiscal i.nl...
> I've upgraded MS-Access 2002 to a MS-Access Project (adp), so now I have
to
> deal with more sophisticated queries (may I call them so?) like stored
> procedures. I have a form with a combobox for selections and a textbox to
> enter a certain value. Let us say I call the combobox @.select and the
> textbox @.find. The combobox always shows the first of the items to select.
> Now I want to return a message if nothing is found, or if nothing has been
> entered at all. The stored procedure reads for instance:
> ALTER PROCEDURE OPC
> @.select nvarchar(20), @.find nvarchar (100)
> AS
> SET NOCOUNT ON
> IF
> @.find IS NULL
> BEGIN
> PRINT 'You didn't enter any value'
> RETURN
> END
> IF
> @.select = 'Author'
> BEGIN
> SELECT *
> FROM Books
> WHERE Author LIKE '%' + @.find + '%'
> ORDER BY Author, Title, Publisher, Year
> END
> [And so on]
> RETURN
>
> This works correct when I enter something, but when I don't enter any
value,
> a message box pops up saying that the stored procedure has been executed,
> but no records were found. I want to see an error message like above. It's
> as simple as
> ALTER PROCEDURE Hello
> AS
> BEGIN
> PRINT 'Hello'
> END
> and nothing is seen. Does anybody know? Thank you.
> Wim

I'm not at all sure, but if your code works as expected in Query Analyzer,
but you're running from an ADP, then perhaps Access is not handling the
PRINT output correctly - it's returned as an informational error (see "Using
PRINT" in Books Online). You might want to investigate how Access handles
this specific output - posting in an Access newsgroup might be useful.

Simon|||Wim van Rosmalen (wvanrosm@.tiscali.nl) writes:
> I've upgraded MS-Access 2002 to a MS-Access Project (adp), so now I have
> to deal with more sophisticated queries (may I call them so?) like
> stored procedures. I have a form with a combobox for selections and a
> textbox to enter a certain value. Let us say I call the combobox @.select
> and the textbox @.find. The combobox always shows the first of the items
> to select. Now I want to return a message if nothing is found, or if
> nothing has been entered at all. The stored procedure reads for
> instance:
>...
> IF @.find IS NULL
> BEGIN
> PRINT 'You didn't enter any value'
> RETURN
> END
>...
> This works correct when I enter something, but when I don't enter any
> value, a message box pops up saying that the stored procedure has been
> executed, but no records were found. I want to see an error message like
> above. It's as simple as

SQL Server is not Access, so a PRINT statement does not by itself outupt
something anywhere. It is up to the client to pick it up. And then you
will have to code for it in the client. From Access, you usually access
SQL Server from ADO, and ADO is not very good making messages avilable
anyway.

You may want to try to use RAISERROR instead:

RAISERROR('You didn''t enter any value', 11, 1)

this causes ADO to raise an error as well.

Disclaimer: I don't know Access or ADP, but I'm speaking from general
experience of ADO programming.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland! I knew about RAISERROR, but in a S.P. like this, PRINT is
returned by the same configuration (MSDE, which doesn't have a
query-analyser):

ALTER PROCEDURE StoredProcedure1
AS
SET NOCOUNT ON

DECLARE @.msg varchar(255)
-- If 53000 is changed into 3000, titles beginning with TE are displayed.
-- If 53000 (or any high number) is not changed, titles beginning with TA
are displayed.
IF (SELECT COUNT(Nr)
FROM RecordTable
WHERE Title LIKE 'Ta%' AND Nr BETWEEN 2000 AND 53000) > 0
BEGIN
SET NOCOUNT ON
SET @.msg = 'Error Message Nr. One'
PRINT @.msg
SELECT title
--If the name "RecordTable" is changed, Error Message Nr. One is displayed
(related to a high number;.
--related to a low number an empty table is displayed).
FROM RecordTable
WHERE title LIKE 'Ta%'
END
ELSE
BEGIN
SET NOCOUNT ON
SET @.msg = 'Error Message Nr. Two'
PRINT @.msg
SELECT title
--If the name "RecordTable" is changed, Error Message Nr. Two is displayed
(related to a low number;.
--related to a high number titles beginning with TA are displayed).
FROM RecordTables
WHERE title LIKE 'Te%'
END

Best regards,

Wim van Rosmalen

"Erland Sommarskog" <esquel@.sommarskog.se> schreef in bericht
news:Xns9534F3AE283C2Yazorman@.127.0.0.1...
> Wim van Rosmalen (wvanrosm@.tiscali.nl) writes:
> > I've upgraded MS-Access 2002 to a MS-Access Project (adp), so now I have
> > to deal with more sophisticated queries (may I call them so?) like
> > stored procedures. I have a form with a combobox for selections and a
> > textbox to enter a certain value. Let us say I call the combobox @.select
> > and the textbox @.find. The combobox always shows the first of the items
> > to select. Now I want to return a message if nothing is found, or if
> > nothing has been entered at all. The stored procedure reads for
> > instance:
> >...
> > IF @.find IS NULL
> > BEGIN
> > PRINT 'You didn't enter any value'
> > RETURN
> > END
> >...
> > This works correct when I enter something, but when I don't enter any
> > value, a message box pops up saying that the stored procedure has been
> > executed, but no records were found. I want to see an error message like
> > above. It's as simple as
> SQL Server is not Access, so a PRINT statement does not by itself outupt
> something anywhere. It is up to the client to pick it up. And then you
> will have to code for it in the client. From Access, you usually access
> SQL Server from ADO, and ADO is not very good making messages avilable
> anyway.
> You may want to try to use RAISERROR instead:
> RAISERROR('You didn''t enter any value', 11, 1)
> this causes ADO to raise an error as well.
> Disclaimer: I don't know Access or ADP, but I'm speaking from general
> experience of ADO programming.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Wim van Rosmalen (wvanrosm@.tiscali.nl) writes:
> Thanks Erland! I knew about RAISERROR, but in a S.P. like this, PRINT is
> returned by the same configuration

Yes, ADO is inconsistent in how it handles PRINT statements. It does
slightly better on errors.

>(MSDE, which doesn't have a query-analyser):

You can use the command-line tool OSQL to run the queries. There you
will always see the PRINT statements.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||O.K. Erland, thanks again!

Wim

"Erland Sommarskog" <esquel@.sommarskog.se> schreef in bericht
news:Xns9535EFF62F45CYazorman@.127.0.0.1...
> Wim van Rosmalen (wvanrosm@.tiscali.nl) writes:
> > Thanks Erland! I knew about RAISERROR, but in a S.P. like this, PRINT is
> > returned by the same configuration
> Yes, ADO is inconsistent in how it handles PRINT statements. It does
> slightly better on errors.
> >(MSDE, which doesn't have a query-analyser):
> You can use the command-line tool OSQL to run the queries. There you
> will always see the PRINT statements.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||For MS verbiage on the topic, take a look at

http://support.microsoft.com/defaul...&NoWebContent=1

"Wim van Rosmalen" <wvanrosm@.tiscali.nl> wrote in message
news:410a5f89$0$62373$5fc3050@.dreader2.news.tiscal i.nl...
> O.K. Erland, thanks again!
> Wim
> "Erland Sommarskog" <esquel@.sommarskog.se> schreef in bericht
> news:Xns9535EFF62F45CYazorman@.127.0.0.1...
> > Wim van Rosmalen (wvanrosm@.tiscali.nl) writes:
> > > Thanks Erland! I knew about RAISERROR, but in a S.P. like this, PRINT
is
> > > returned by the same configuration
> > Yes, ADO is inconsistent in how it handles PRINT statements. It does
> > slightly better on errors.
> > >(MSDE, which doesn't have a query-analyser):
> > You can use the command-line tool OSQL to run the queries. There you
> > will always see the PRINT statements.
> > --
> > Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> > Books Online for SQL Server SP3 at
> > http://www.microsoft.com/sql/techin.../2000/books.asp