Re: Setting control value based on a SQL Select statement

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: 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: OraOLEDB.Oracle : How to get an updatable ADODB Recordset from
    ... if not i think the microsoft API adds the rowid in the background to ... Reading data from a stored procedure in an ADODB Recordset ... Modify the disconnected Recordset on the Client ...
    (microsoft.public.data.ado)
  • Calling serverside code from client
    ... I'm wondering if it is possible to hide some business logic from client. ... Something like stored procedure call. ... is there any way to create recordset not bound to the page ... I simply do not want to show any query string or logic to the client. ...
    (microsoft.public.access.dataaccess.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: vbscript logon script getting return data from sql sp
    ... Loop ... > One way to retrieve values from a stored procedure is with a Recordset ...
    (microsoft.public.scripting.vbscript)