"Wim van Rosmalen" <wvanrosm@tiscali.nl> wrote in message
news:410789c7$0$62355$5fc3050@dreader2.news.tiscali.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'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

> 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/techinfo/productdoc/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):

> 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/techinfo/productdoc/2000/books.asp

"Wim van Rosmalen" <wvanrosm@tiscali.nl> wrote in message
news:410a5f89$0$62373$5fc3050@dreader2.news.tiscali.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/techinfo/productdoc/2000/books.asp
>
>