|
Home > Archive > microsoft.public.sqlserver.server > November 2002 > exec sql sp from access
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
exec sql sp from access
|
|
|
| I was successful yeaterday in exec'ing a very basic parameter-less SQL sp
from an Access query. Easy enough. But now how do I enable my queries to
pass in a parameter to exec my other procs. Im Access ignorant so please be
gentle.
--
Thanks in advance.
Chris
| |
|
| You can use ADO, you pass parameters to execute a stored procedure,
and you get back an OUTPUT parameter from the executed stored procedure.
Kinda like this:
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Set cmd = New ADODB.Command
cmd.ActiveConnection = cnnProducts
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "dbo.procProductExist"
Set prm = cmd.CreateParameter("@CustNo", adInteger, adParamInput, ,
Me!cboCustNo)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@Product", adVarChar, adParamInput, 50,
Me!cboProduct)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@HighestVerNumber", adTinyInt,
adParamOutput, , 0)
cmd.Parameters.Append prm
cmd.Execute , , adExecuteNoRecords
Dim bytVer As Byte
bytVer = IIf(IsNull(cmd.Parameters("@HighestVerNumber")), 0,
cmd.Parameters("@HighestVerNumber"))
If bytVer > 0 Then
' Product already exists
MsgBox Me!cboProduct & " already exists, please choose another
name", vbCritical, "Duplicate Product"
Set prm = Nothing
Set cmd = Nothing
End If
Basically, i pass a CustNo and a Product Name to "procProductExist" and i
check it this product
exists or not, if it does, then i expect to return the "Version" of this
product.
The products in my table like this:
Customer Product Version
ABC 156A4 1
ABC 156A4 2
ABC DEF44 1
....
this is what the "procProductExist" is:
ALTER PROCEDURE procProductExist
@CustNo int,
@Product varchar(50),
@HighestVerNumber tinyint OUTPUT
AS
SET NOCOUNT ON
SELECT @HighestVerNumber = (MAX([Ver])) FROM tblProducts WITH (NOLOCK)
WHERE CustNo = @CustNo AND Product = @Product
IF @HighestVerNumber IS NULL
BEGIN
SET @HighestVerNumber = 0
RETURN @HighestVerNumber
END
ELSE
RETURN @HighestVerNumber
HTH.
"chris" <chris@fin2000.com> wrote in message
news:eSESFomlCHA.1464@tkmsftngp07...
> I was successful yeaterday in exec'ing a very basic parameter-less SQL sp
> from an Access query. Easy enough. But now how do I enable my queries to
> pass in a parameter to exec my other procs. Im Access ignorant so please
be
> gentle.
>
> --
> Thanks in advance.
> Chris
>
>
| |
| chris 2002-11-28, 10:23 am |
| The only programming I know is SQL and Im far from a pro @ it. Does this
somehow tie into Access? I could probably get by on the SQL part with the
knowledge Ive got. But I dont want to have to code the front end. To just
code connections like this Im sure I could manage. I just dont want to have
to become a front end programmer to accomplish my goals.
"serge" <sergea@nospam.ehmail.com> wrote in message
news:5XeF9.8390$l45.996151@news20.bellglobal.com...
> You can use ADO, you pass parameters to execute a stored procedure,
> and you get back an OUTPUT parameter from the executed stored procedure.
>
> Kinda like this:
>
> Dim cmd As ADODB.Command
> Dim prm As ADODB.Parameter
>
> Set cmd = New ADODB.Command
> cmd.ActiveConnection = cnnProducts
> cmd.CommandType = adCmdStoredProc
> cmd.CommandText = "dbo.procProductExist"
> Set prm = cmd.CreateParameter("@CustNo", adInteger, adParamInput, ,
> Me!cboCustNo)
> cmd.Parameters.Append prm
> Set prm = cmd.CreateParameter("@Product", adVarChar, adParamInput, 50,
> Me!cboProduct)
> cmd.Parameters.Append prm
> Set prm = cmd.CreateParameter("@HighestVerNumber", adTinyInt,
> adParamOutput, , 0)
> cmd.Parameters.Append prm
> cmd.Execute , , adExecuteNoRecords
> Dim bytVer As Byte
> bytVer = IIf(IsNull(cmd.Parameters("@HighestVerNumber")), 0,
> cmd.Parameters("@HighestVerNumber"))
> If bytVer > 0 Then
> ' Product already exists
> MsgBox Me!cboProduct & " already exists, please choose another
> name", vbCritical, "Duplicate Product"
> Set prm = Nothing
> Set cmd = Nothing
> End If
>
> Basically, i pass a CustNo and a Product Name to "procProductExist" and i
> check it this product
> exists or not, if it does, then i expect to return the "Version" of this
> product.
>
> The products in my table like this:
> Customer Product Version
> ABC 156A4 1
> ABC 156A4 2
> ABC DEF44 1
> ...
> this is what the "procProductExist" is:
>
> ALTER PROCEDURE procProductExist
>
> @CustNo int,
>
> @Product varchar(50),
>
> @HighestVerNumber tinyint OUTPUT
>
> AS
>
> SET NOCOUNT ON
>
> SELECT @HighestVerNumber = (MAX([Ver])) FROM tblProducts WITH (NOLOCK)
>
> WHERE CustNo = @CustNo AND Product = @Product
>
> IF @HighestVerNumber IS NULL
>
> BEGIN
>
> SET @HighestVerNumber = 0
>
> RETURN @HighestVerNumber
>
> END
>
> ELSE
>
> RETURN @HighestVerNumber
>
>
> HTH.
>
> "chris" <chris@fin2000.com> wrote in message
> news:eSESFomlCHA.1464@tkmsftngp07...
> > I was successful yeaterday in exec'ing a very basic parameter-less SQL
sp
> > from an Access query. Easy enough. But now how do I enable my queries to
> > pass in a parameter to exec my other procs. Im Access ignorant so please
> be
> > gentle.
> >
> > --
> > Thanks in advance.
> > Chris
> >
> >
>
>
| |
| Marc McCotter 2002-11-28, 11:23 am |
| If you don't want to write any code in Access, just create a stored
procedure that requires your parameter. Then click on the Stored Procedures
button in the database window and double click the procedure you want to
run. A box will appear for each parameter, simply type in the necessary
value for each parameter. Here's a simple example:
Create Procedure MyTest
@OrderID INT
As
set nocount on
SELECT * FROM Orders WHERE OrderID = @OrderID
Access is really easy to use once you get the hang of it. Once you write a
few ADO routines to run stored procedures its just a lot of CTRL-C and
CTRL-V'ing.
If you are running SQL Server 2000 you can look into retrieving result sets
via HTTP
Why not learn something new? I think you should just suck it up and take a
few minutes to learn how to do it.....ADO is pretty wide spread. After you
learn how to use it you can talk to your SQL Server via ASP pages, VB,
Access, Excel, etc. Definitely not a waste of time if you use MS products.
Good luck.
--Marc McCotter
"chris" <mysqlstuff@hotmail.com> wrote in message
news:uNogMTvlCHA.1244@tkmsftngp02...
> The only programming I know is SQL and Im far from a pro @ it. Does this
> somehow tie into Access? I could probably get by on the SQL part with the
> knowledge Ive got. But I dont want to have to code the front end. To just
> code connections like this Im sure I could manage. I just dont want to
have
> to become a front end programmer to accomplish my goals.
>
> "serge" <sergea@nospam.ehmail.com> wrote in message
> news:5XeF9.8390$l45.996151@news20.bellglobal.com...
> > You can use ADO, you pass parameters to execute a stored procedure,
> > and you get back an OUTPUT parameter from the executed stored procedure.
> >
> > Kinda like this:
> >
> > Dim cmd As ADODB.Command
> > Dim prm As ADODB.Parameter
> >
> > Set cmd = New ADODB.Command
> > cmd.ActiveConnection = cnnProducts
> > cmd.CommandType = adCmdStoredProc
> > cmd.CommandText = "dbo.procProductExist"
> > Set prm = cmd.CreateParameter("@CustNo", adInteger, adParamInput, ,
> > Me!cboCustNo)
> > cmd.Parameters.Append prm
> > Set prm = cmd.CreateParameter("@Product", adVarChar, adParamInput,
50,
> > Me!cboProduct)
> > cmd.Parameters.Append prm
> > Set prm = cmd.CreateParameter("@HighestVerNumber", adTinyInt,
> > adParamOutput, , 0)
> > cmd.Parameters.Append prm
> > cmd.Execute , , adExecuteNoRecords
> > Dim bytVer As Byte
> > bytVer = IIf(IsNull(cmd.Parameters("@HighestVerNumber")), 0,
> > cmd.Parameters("@HighestVerNumber"))
> > If bytVer > 0 Then
> > ' Product already exists
> > MsgBox Me!cboProduct & " already exists, please choose another
> > name", vbCritical, "Duplicate Product"
> > Set prm = Nothing
> > Set cmd = Nothing
> > End If
> >
> > Basically, i pass a CustNo and a Product Name to "procProductExist" and
i
> > check it this product
> > exists or not, if it does, then i expect to return the "Version" of this
> > product.
> >
> > The products in my table like this:
> > Customer Product Version
> > ABC 156A4 1
> > ABC 156A4 2
> > ABC DEF44 1
> > ...
> > this is what the "procProductExist" is:
> >
> > ALTER PROCEDURE procProductExist
> >
> > @CustNo int,
> >
> > @Product varchar(50),
> >
> > @HighestVerNumber tinyint OUTPUT
> >
> > AS
> >
> > SET NOCOUNT ON
> >
> > SELECT @HighestVerNumber = (MAX([Ver])) FROM tblProducts WITH (NOLOCK)
> >
> > WHERE CustNo = @CustNo AND Product = @Product
> >
> > IF @HighestVerNumber IS NULL
> >
> > BEGIN
> >
> > SET @HighestVerNumber = 0
> >
> > RETURN @HighestVerNumber
> >
> > END
> >
> > ELSE
> >
> > RETURN @HighestVerNumber
> >
> >
> > HTH.
> >
> > "chris" <chris@fin2000.com> wrote in message
> > news:eSESFomlCHA.1464@tkmsftngp07...
> > > I was successful yeaterday in exec'ing a very basic parameter-less SQL
> sp
> > > from an Access query. Easy enough. But now how do I enable my queries
to[co
lor=darkred]
> > > pass in a parameter to exec my other procs. Im Access ignorant so[/color]
please
> > be
> > > gentle.
> > >
> > > --
> > > Thanks in advance.
> > > Chris
> > >
> > >
> >
> >
>
>
| |
| chris 2002-11-29, 10:23 am |
| So am I correct when I say that ADO can be used to program Access to accept
parameters? I do need a front end and Access would work if I could feed in
parameters. Thanks.
"Marc McCotter" <mauimove@hotmail.com> wrote in message
news:uucgupgm9opvb3@corp.supernews.com...
> If you don't want to write any code in Access, just create a stored
> procedure that requires your parameter. Then click on the Stored
Procedures
> button in the database window and double click the procedure you want to
> run. A box will appear for each parameter, simply type in the necessary
> value for each parameter. Here's a simple example:
>
> Create Procedure MyTest
> @OrderID INT
> As
> set nocount on
> SELECT * FROM Orders WHERE OrderID = @OrderID
>
> Access is really easy to use once you get the hang of it. Once you write
a
> few ADO routines to run stored procedures its just a lot of CTRL-C and
> CTRL-V'ing.
>
> If you are running SQL Server 2000 you can look into retrieving result
sets
> via HTTP
>
> Why not learn something new? I think you should just suck it up and take
a
> few minutes to learn how to do it.....ADO is pretty wide spread. After
you
> learn how to use it you can talk to your SQL Server via ASP pages, VB,
> Access, Excel, etc. Definitely not a waste of time if you use MS
products.
>
> Good luck.
>
> --Marc McCotter
>
> "chris" <mysqlstuff@hotmail.com> wrote in message
> news:uNogMTvlCHA.1244@tkmsftngp02...
> > The only programming I know is SQL and Im far from a pro @ it. Does this
> > somehow tie into Access? I could probably get by on the SQL part with
the
> > knowledge Ive got. But I dont want to have to code the front end. To
just
> > code connections like this Im sure I could manage. I just dont want to
> have
> > to become a front end programmer to accomplish my goals.
> >
> > "serge" <sergea@nospam.ehmail.com> wrote in message
> > news:5XeF9.8390$l45.996151@news20.bellglobal.com...
> > > You can use ADO, you pass parameters to execute a stored procedure,
> > > and you get back an OUTPUT parameter from the executed stored
procedure. [colo
r=darkred]
> > >
> > > Kinda like this:
> > >
> > > Dim cmd As ADODB.Command
> > > Dim prm As ADODB.Parameter
> > >
> > > Set cmd = New ADODB.Command
> > > cmd.ActiveConnection = cnnProducts
> > > cmd.CommandType = adCmdStoredProc
> > > cmd.CommandText = "dbo.procProductExist"
> > > Set prm = cmd.CreateParameter("@CustNo", adInteger, adParamInput,[/color]
,[col
or=darkred]
> > > Me!cboCustNo)
> > > cmd.Parameters.Append prm
> > > Set prm = cmd.CreateParameter("@Product", adVarChar, adParamInput,
> 50,
> > > Me!cboProduct)
> > > cmd.Parameters.Append prm
> > > Set prm = cmd.CreateParameter("@HighestVerNumber", adTinyInt,
> > > adParamOutput, , 0)
> > > cmd.Parameters.Append prm
> > > cmd.Execute , , adExecuteNoRecords
> > > Dim bytVer As Byte
> > > bytVer = IIf(IsNull(cmd.Parameters("@HighestVerNumber")), 0,
> > > cmd.Parameters("@HighestVerNumber"))
> > > If bytVer > 0 Then
> > > ' Product already exists
> > > MsgBox Me!cboProduct & " already exists, please choose another
> > > name", vbCritical, "Duplicate Product"
> > > Set prm = Nothing
> > > Set cmd = Nothing
> > > End If
> > >
> > > Basically, i pass a CustNo and a Product Name to "procProductExist"[/color]
and
> i
> > > check it this product
> > > exists or not, if it does, then i expect to return the "Version" of
this[
color=darkred]
> > > product.
> > >
> > > The products in my table like this:
> > > Customer Product Version
> > > ABC 156A4 1
> > > ABC 156A4 2
> > > ABC DEF44 1
> > > ...
> > > this is what the "procProductExist" is:
> > >
> > > ALTER PROCEDURE procProductExist
> > >
> > > @CustNo int,
> > >
> > > @Product varchar(50),
> > >
> > > @HighestVerNumber tinyint OUTPUT
> > >
> > > AS
> > >
> > > SET NOCOUNT ON
> > >
> > > SELECT @HighestVerNumber = (MAX([Ver])) FROM tblProducts WITH (NOLOCK)
> > >
> > > WHERE CustNo = @CustNo AND Product = @Product
> > >
> > > IF @HighestVerNumber IS NULL
> > >
> > > BEGIN
> > >
> > > SET @HighestVerNumber = 0
> > >
> > > RETURN @HighestVerNumber
> > >
> > > END
> > >
> > > ELSE
> > >
> > > RETURN @HighestVerNumber
> > >
> > >
> > > HTH.
> > >
> > > "chris" <chris@fin2000.com> wrote in message
> > > news:eSESFomlCHA.1464@tkmsftngp07...
> > > > I was successful yeaterday in exec'ing a very basic parameter-less[/color]
SQL
> > sp
> > > > from an Access query. Easy enough. But now how do I enable my
queries
> to
> > > > pass in a parameter to exec my other procs. Im Access ignorant so
> please
> > > be
> > > > gentle.
> > > >
> > > > --
> > > > Thanks in advance.
> > > > Chris
> > > >
> > > >
> > >
> > >
> >
> >
>
>
|
|
|
|
|