Customize Prompt Message for Stored Procudure

[ndn_24_7]

Hello all,
I have a stored procedure that prompts the user for beginning date and
ending date to run a monthly report. The prompt says
Enter_Beginning _Date and Enter_Ending_Da te. I want the prompt to say
Enter Beginning Date (Example:1-1-2003) or something like that. Is
there a way to do this?

CREATE PROCEDURE dbo.MonthlyRepo rt(@Enter_Begin ning_Date datetime,
@Enter_Ending_D ate datetime)
AS SELECT incident, @Enter_Beginnin g_Date AS BeginningDate,
@Enter_Ending_D ate AS EndingDate, COUNT(*) AS Occurances
FROM dbo.Incident
WHERE (DateOccured BETWEEN @Enter_Beginnin g_Date AND
@Enter_Ending_D ate)
GROUP BY incident
GO

[Simon Hayes]

"ndn_24_7" <nd******@yahoo .com> wrote in message
news:11******** *************@c 13g2000cwb.goog legroups.com...

Hello all,
I have a stored procedure that prompts the user for beginning date and
ending date to run a monthly report. The prompt says
Enter_Beginning _Date and Enter_Ending_Da te. I want the prompt to say
Enter Beginning Date (Example:1-1-2003) or something like that. Is
there a way to do this?

CREATE PROCEDURE dbo.MonthlyRepo rt(@Enter_Begin ning_Date datetime,
@Enter_Ending_D ate datetime)
AS SELECT incident, @Enter_Beginnin g_Date AS BeginningDate,
@Enter_Ending_D ate AS EndingDate, COUNT(*) AS Occurances
FROM dbo.Incident
WHERE (DateOccured BETWEEN @Enter_Beginnin g_Date AND
@Enter_Ending_D ate)
GROUP BY incident
GO

MSSQL is purely a server, so it doesn't have any idea about GUIs or
prompts - if you want to present a more user-friendly description of the two
parameters, then you would have to do that in the front-end application
where the users select the dates.

One possible approach would be to add an extended property to the two
parameters which has the description in it, then retrieve that from the
front end when you display the input screen. See "Using Extended Properties
on Database Objects" in Books Online for more details. But I don't know if
that would be a suitable solution for your toolset and design.

Simon

[ndn_24_7]

I'm sorry
I should have been more discriptive. My program has a Access 2000 front
end and a SQL 2000 server backend. I have a button that the user clicks
that brings up the prompt window for the stored proc. So would I do
this on the Access front end, Where would Icustomize this message?

[QueryBuilder]

Not a clue how you are prompting the user thru Stored Procedure. May be
you missed out some valuable info on the post..........!

[Erland Sommarskog]

ndn_24_7 (nd******@yahoo .com) writes:

I should have been more discriptive. My program has a Access 2000 front
end and a SQL 2000 server backend. I have a button that the user clicks
that brings up the prompt window for the stored proc. So would I do
this on the Access front end, Where would Icustomize this message?

Sounds like you should try an Access newsgroup. It is possible that
you can use extended properties for this, but I have no knowledge
what Access makes use of. So try comp.databases. ms-access where the
expertise for this question might hang out.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[QueryBuilder]

As far I understood,

You have a stored procedure that accepts parameters and you have an
Access front end that accepts input parameters thru prompts and pass
them to the backend stored procedure.... Am I correct?

If so, what you are doing is correct so far. You need to find out how
to call a stored procedure thru access and once you know, all you need
to do is, thru access, get those inputs (you may display anyway you
need - that is irrelevent to the backend procedure...) and pass them as
parameters to the backend stored proc.. May be an access guru can show
some light on how to accomplish this..!

[Andy O'Neill]

"ndn_24_7" <nd******@yahoo .com> wrote in message
news:11******** **************@ c13g2000cwb.goo glegroups.com.. .

I'm sorry
I should have been more discriptive. My program has a Access 2000 front
end and a SQL 2000 server backend. I have a button that the user clicks
that brings up the prompt window for the stored proc. So would I do
this on the Access front end, Where would Icustomize this message?

I suggest you want a form that the user enters the dates in and then clicks
the button to run the stored proc.
I'll assume you know a little about VB code.
Otherwise, you got a steep learning curve ahead mate.

You probably want to validate the fields using isdate()

Access now uses ADO, so the code involves using an ado connection and
command.
I found pretty much the below code by using google to search the access
newsgroup.
Not tested it and I had to add the execute line, so this is just to get you
started.
BTW You'll want to get used to doing such searches if you are new to this
lark.
I suggest also take a look at each of the bits in turn and read up using
msdn so you get a better understanding what you're up to.
I have deliberately not changed the parameter type to date and input because
you'd learn stuff all if I just gave you the code.

There's a couple gotchas with datetime. Inside access some bits want this
delimited by # but not with ado. Remember also the time bit of a date is
likely there. Not such a problem with > or < but throw that to the back of
your mind for later.

Anyhow.
You run the thing by using the execute method of the command.
There's a parameter collection associated with a command you add the values
to:

'start untested snippet
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter

Set cmd = New ADODB.Command
cmd.ActiveConne ction = CurrentProject. Connection
cmd.CommandText = "stored procedurename"
cmd.CommandType = adCmdStoredProc
Set prm = cmd.CreateParam eter("@CompanyI D", adInteger, adParamOutput _
, , forms!yourformn ame!text1.Value )
cmd.Parameters. Append prm

cmd.execute

set prm = Nothing
Set cmd = Nothing

' end snippet
HTH

--
Regards,
Andy O'Neill