Re: Setting control value based on a SQL Select statement

Oh, you are using the DoCmd.RunSQL command; this explains why the water was
muddy. This command is an old relic from previous version of Access, is
still around for backward compatibility and can only be used to sent a quick
sql instruction to SQL-Server without expecting any result in return.

ADP use ADO and when you want a quick result back, you should use the
..Execute function of the connection object CurrentProject.Connection:

CurrentProject.Connection.Execute ("Insert ...")

or:
Dim rs as ADODB.Recordset
Set rs = CurrentProject.Connection.Execute ("Select * ...")

This connection object is a standard ADO connection object and the
..Execute() function has other optional arguments for which I don't remember
the values. Take a look at the ADO documentation.

Personnally, I usually prefer to create an ADO command object and use it to
pass arguments forth and back with my stored procedures. As you want some
arguments back, this is also the method that you should use; unless you
don't want to get back any Select result. If this is the case, then you can
use a Select statement to get back the desired values.

SQL Server doesn't make any distinction between INPUT/OUTPUT or OUTPUT
values, so all you to do is to declare them as OUTPUT and they will then
also be used as INPUT values:
CREATE Procedure dbo.spCRR_GetCrrNumber (
@ClientID int INPUT,
@CRRNumber int OUTPUT,
@DebtorID int OUTPUT
)As

Select * from blblaalalal

Return 0



And to call it:

Set cmd = New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "pr_IsRoleMember_IMMS"
cmd.CommandType = adCmdStoredProc
Set prm = cmd.CreateParameter("RetASWSQC", adInteger, adParamReturnValue)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@RoleASWSQC", adBSTR, adParamInput, 11,
"ASWS_QC")
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("RetManagers", adInteger, adParamReturnValue)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@RoleManagers", adBSTR, adParamInput, 11,
"Managers")
cmd.Parameters.Append prm
cmd.Execute



--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


"Guy Kerr" <GuyKerr@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C911066B-751C-4781-925B-EDC6ACC0EAA3@xxxxxxxxxxxxxxxx
Okay I think I need to step back a little and explain why I seem so lost
with
this.

I see all these posts about ADO and DOA coding and quite honestly I don't
know which one I use because I use Visual Studio Tools for MSOffice and I
simply use the File, Connection, Data Link Properties to select my data
source (in this case it's a SQL 2005 backend) within my Access ADP. I
never programatically define my source. I simple use SQL Views as the
source
to my forms and reports and my controls are bound to the fields from the
views.

So that explains why I'm not clear on the syntax on how to do what I'm
trying to accomplish.

The actual code I use to call my stored procedure is as follows:
SQLStmt = "Exec dbo.spCRR_GetCrrNumber " & Me.ClientID & ", '" & Me.Guid &
"'"
DoCmd.RunSQL SQLStmt

I had previously posted a question as to how I could get a return value
from
this stored procedure and I tried adding an OUTPUT on the stored procedure
itself but I was told that it wasn't possible to get a return value using
the
technique above.

So what I was thinking is can I simply run a SQL Select statement similar
to:
Select CRRNumber from [testserver3\testdata].CRRWEB.dbo.crrnumbers where
RecordID ='" & Me.Guid & "'"

As you can see, the CRRNUMBERS table is on a linked server. This table
stores a list of unique CRRNumbers and DebtorID's which are generated by
the
stored procedure. The reason this table is on a remote server is that we
have two applications that need CRRNumbers and DebtorID's generated - my
internal application and a public-facing web portal. So this table allows
us
to make sure we're not generating a CRRNumber or DebtorID that's already
used.

When I initially call the stored procedure from my application I pass the
ClientID and GUID number to the stored procedure. My application needs to
have a CRRNumber and DebtorID which is what the stored procedure does for
me.
This generates a new record in the CRRNumbers table. This new record uses
the GUID I passed it and stores it in the RecordID field. It uses the
ClientID to generate a new unique CRRNumber and DebtorID. These are the
two
fields I want to pass back to my application but am struggling with.

Since I generate the GUID with my application and pass it to the stored
procedure this is my link between my application and the CRRNumbers table
that I was hoping to use in my select statement to pull out the two values
I
need.

Sorry for the lengthy explanation but I'm hoping this makes more sense.

Thanks for your patience.


"Sylvain Lafontaine" wrote:

I'm still not sure that I understand what you are trying to do. I don't
see
why you should have any problem at writing something like

Select @debtorid, @CRRNumber

at the end of your stored procedure. When you call your SP, call it by
using the OPEN statement of a recordset to capture the result in the
recordset.

You say that you are passing some parameters to your stored procedure;
however, you did not say which method you have used to do so. There are
many ways of calling a SP and passing parameters, so I cannot tell you
more
at this moment with the current information that you have provided.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


"Guy Kerr" <GuyKerr@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:258BF8DE-900A-4AE4-A6D5-C057B466C175@xxxxxxxxxxxxxxxx
Sylvain can you give me an example of what you mean by "Don't be so
complicated and make a SELECT statement at the end of your SP and
capture
the
result in a recordset."

This would probably point me in the right direction.

Thanks in advance

Guy

"Sylvain Lafontaine" wrote:

You can use the output feature of parameters for a SP if you are using
an
ADODB command object. Of course, you cannot do that if you are using
a
SP
bound to a form. For example of using a command object, see the
previous
posts in this newsgroup.

A second way of returning more than a single parameter is simply to
use a
recordset.

I don't understand what you mean by « So as an alternative I use a SQL
Select statement where GUID = linked server GUID in the hope I can
return
these two values. ».

Don't be so complicated and make a SELECT statement at the end of your
SP
and capture the result in a recordset.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


"Guy Kerr" <GuyKerr@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:1367B1D4-9F4B-47FC-83F2-14DE3AF191CA@xxxxxxxxxxxxxxxx
Sorry I'm not too well versed in the terminology but I think I know
what
you
mean. The form is bound to a SQL view and the control is bound to a
field
from the view.

Here's some more detail that may help explain it better.

Each time you select a client from the client combo box a GUID is
generated
using VB code. The clientID (the bound column from the client combo
box)
and
the GUID are passed as parameters to a stored procedure. The stored
procedure creates a new record in the ClientNumbers table on a
linked
server.
The stored procedure also generates a "debtorid" and a "CRRNumber"
which
are
essentially the last generated number + 1 for each of those fields.
What
I
was trying to accomplish is to return those two values (debtorID and
CRRNumber) generated by the stored procedure since they are stored
in
the
ClientNumbers table on the linked server.

I'd posted a prior question on how to return values from the stored
procedure but was told I can't do this using the OUTPUT feature of
SQL
stored
procedures. So as an alternative I use a SQL Select statement where
GUID
=
linked server GUID in the hope I can return these two values.

I hope this makes sense.



"Sylvain Lafontaine" wrote:

What do you mean exactly by setting the value of a control? Are
you
talking
about a bound or an unbound control and a bound or an unbound form?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


"Guy Kerr" <GuyKerr@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:49665F9E-A4D5-4277-92A1-8BBAA6157FDB@xxxxxxxxxxxxxxxx
This may sound like a stupid question but for the life of me I
can't
figure
this out. I found a pseudo-workaround by setting my control to a
ListBox
and
then using .RowSourceType and .RowSource= SQLStmt to set the
value
of
the
control but it's not what I'd call a good solution. Am I missing
something
here or is there a way to use a SQL Select statement to set the
value
of a
control (other than combobox or listbox)?












.

Relevant Pages

  • Re: using Command to set Parameters and Recordset to retrive the Query
    ... doesn't the rsData will be interpretate as an input parameter in the SP? ... >> Query and retrive the Recordset so I can use the Paging property ... > Even if this technique of using the parameters in the ORDER BY does work for> you, I suspect that this will defeat your objective of preventing sql> injection. ... See below for a more efficient solution> using a stored procedure. ...
    (microsoft.public.inetserver.asp.general)
  • Re: Setting control value based on a SQL Select statement
    ... SQL Server doesn't make any distinction between INPUT/OUTPUT or OUTPUT ... @CRRNumber int OUTPUT, ... The actual code I use to call my stored procedure is as follows: ... ClientID and GUID number to the stored procedure. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Setting control value based on a SQL Select statement
    ... The actual code I use to call my stored procedure is as follows: ... to make sure we're not generating a CRRNumber or DebtorID that's already used. ... ClientID and GUID number to the stored procedure. ... using the OPEN statement of a recordset to capture the result in the ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Setting control value based on a SQL Select statement
    ... ClientID) ... The actual code I use to call my stored procedure is as follows: ... Select CRRNumber from.CRRWEB.dbo.crrnumbers ... using the OPEN statement of a recordset to capture the result in the ...
    (microsoft.public.access.adp.sqlserver)
  • Re: How does ADO 2 update a SQL stored procedure recordset?
    ... > Generally when I want to use ADO 2 to update a recordset based on a MSSQL ... > call a second stored procedure to perform the update. ... Well if you have SELECTed the columns in the recordset that form the Primary ... You are running SQL Server. ...
    (microsoft.public.data.ado)