UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
3 Pages V  1 2 3 >  (Go to first unread post)
   Reply to this topicStart new topic
> Return + Use the output of a stored procedure in mdb    
 
   
champagne_charly
post Feb 10 2007, 01:01 PM
Post#1



Posts: 477
Joined: 31-January 05
From: Blackheath, London


IS it possible to get the output value of a stored procedure back from sql server so that it can be used in vb
.g I want to create a new record
The ID for that table is NOT autonumber si Have to find max number +1
I want then to use the return value to open a form where the ID = new ID Value
How would I go about doing this? Prob v simple but at mo have not used stored procs so excuse my ignorance.
Thanks
Go to the top of the page
 
LPurvis
post Feb 10 2007, 01:45 PM
Post#2


UtterAccess Editor
Posts: 16,086
Joined: 27-June 06
From: England (North East / South Yorks)


Hello again!
Are you retrieving the new ID from an insert statement in an SP? (Rather than taking the slightly flaky Max+1)
You can use an ADO command object to retrieve an Output parameter from an SP.
Are you familiar with Command object use - or would ya like an example? :-)
Go to the top of the page
 
champagne_charly
post Feb 10 2007, 01:52 PM
Post#3



Posts: 477
Joined: 31-January 05
From: Blackheath, London


Thats exactly what I want to do. Honestly i'm clueless so an example would be fantastic sad.gif Thanks
Go to the top of the page
 
LPurvis
post Feb 10 2007, 02:28 PM
Post#4


UtterAccess Editor
Posts: 16,086
Joined: 27-June 06
From: England (North East / South Yorks)


You managed to give me two email notifications with that one post - weird.
Odunno - none when you want em - two when you don't... it's like busses.
Can you show the SP you're using - to provide an appropriate example?
Go to the top of the page
 
LPurvis
post Feb 10 2007, 07:20 PM
Post#5


UtterAccess Editor
Posts: 16,086
Joined: 27-June 06
From: England (North East / South Yorks)


Hi

At a loose end - so thought I'd just offer a generic example - and you can adapt to your own needs.

CODE
Function fADOGrabOutput()
    Dim cnn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim prm As ADODB.Parameter

    Set cnn = New ADODB.Connection
    cnn.Open "Provider=SQLOLEDB.1;Data Source=ServerName;User ID=UN;Password=PW;Initial Catalog=YourDatabase;"

    Set cmd = New ADODB.Command
    Set prm = New ADODB.Parameter

    With cmd
        .ActiveConnection = cnn
        .CommandType = adCmdStoredProc
        .CommandText = "sp_AddRecord"
        
        'Pass any required input parameters
        .Parameters.Append .CreateParameter("@ParamName1", adVarChar, adParamInput, 50, "ValueX")
        .Parameters.Append .CreateParameter("@ParamName2", adVarChar, adParamInput, 50, "ValueY")
        'Create return parameter for the output value
        Set prm = .CreateParameter("@intReturnParam", adInteger, adParamOutput, 4)
        .Parameters.Append prm
        'Execute the SP
        .Execute
        'Grab the output parameter value
        fADOGrabOutput = prm.Value
        Debug.Print prm.Value

    End With
    
    Set cmd = Nothing
    Set cnn = Nothing

End Function
Go to the top of the page
 
Maxer
post Feb 12 2007, 12:35 PM
Post#6


Utter Access VIP
Posts: 2,434
Joined: 14-March 06



LPurvis: Why the use of command over connection?
For example in an Access only DB one could do:
CurrentProject.connect.Execute: blah...
Or
cnn1.execute
(at least as I understand it).
Could you fill me in on the command object's usage here? (Haven't had a chance to work with SQL server directly, but that doesn't mean I'm not desperately interested in how to do so)
Go to the top of the page
 
LPurvis
post Feb 12 2007, 02:05 PM
Post#7


UtterAccess Editor
Posts: 16,086
Joined: 27-June 06
From: England (North East / South Yorks)


Perhaps you're refering to an example you might have seen where a single value can be returned from a SQL statement.
e.g.
Debug.Print CurrentProject.Connection.Execute("SELECT FieldName FROM tblName WHERE ID = 23")(0)
That works for one simple reason. It returns a recordset.
It's absolutely implicit - but it's there.
Just as you can use
Set rst = CurrentProject.Connection.Execute("SELECT FieldName FROM tblName WHERE ID = 23")
debug.Print rst.Fields(0)
or
debug.Print rst.Fields("FieldName")
However not every Stored Procedure returns a set of records.
It can of course be written that way (to return the desired value as a select result) - but it's very common procedure to use an output parameter.
With either a Jet or SQL Server BE you can return a value as above.
But only SP's can return an Output parameter.
And to use parameters then you need a command object (and to use it as exampled).
Go to the top of the page
 
Maxer
post Feb 12 2007, 02:11 PM
Post#8


Utter Access VIP
Posts: 2,434
Joined: 14-March 06



Interesting, I didn't realize that you HAD to use a command object to pass parameters in SQL.
I am extemely fascinated by param passing to SQL server but as I said haven't had a chance to play with it.
Just to be sure I can COMPLETELY hijack the OP's thread... do you have any suggested reading about ADO and SQL Server command object and parameter passing?
Thanks!
Go to the top of the page
 
LPurvis
post Feb 12 2007, 02:52 PM
Post#9


UtterAccess Editor
Posts: 16,086
Joined: 27-June 06
From: England (North East / South Yorks)


Just the usual Access Developer's Guide to SQL Server.
You don't *have* to use a command object to pass parameters.
You can just execute them in line
CurrentProject.Connection.Execute "SPName 'Param1', 'Param2'"
But to retrieve an output parameter you use a command object.
(And many prefer to use a command object anyway - it's more formal and structured.
Go to the top of the page
 
Maxer
post Feb 12 2007, 02:54 PM
Post#10


Utter Access VIP
Posts: 2,434
Joined: 14-March 06



Ok, that makes sense then.
Thanks.
(Do you have the ISBN for that book, as I often return MANY results when searching for that)
Go to the top of the page
 
LPurvis
post Feb 12 2007, 02:59 PM
Post#11


UtterAccess Editor
Posts: 16,086
Joined: 27-June 06
From: England (North East / South Yorks)


It comes back No. 1 for me on Amazon (com and co.uk) searching Books for "access developer guide SQL Server"
My copy is currently out of reach - but from Amazon
ISBN-10: 0672319446
ISBN-13: 978-0672319440
Go to the top of the page
 
Maxer
post Feb 12 2007, 03:03 PM
Post#12


Utter Access VIP
Posts: 2,434
Joined: 14-March 06



Ah, thanks.
henever I went looking for Access Developers Guide I always got a ton of results. However, I was thinking of just stand alone Access not the SQL Server version (yeah, I wasn't paying attention it seems).
Thanks again!
Go to the top of the page
 
champagne_charly
post Feb 13 2007, 05:25 AM
Post#13



Posts: 477
Joined: 31-January 05
From: Blackheath, London


Woow.. Thank you!!!!.. I've had an absolute system nightmare.. been spending days trying o get my machine back up and (nearly) how it should be now. Far to much stuff lingering in the registry causing probs.. So yes 2 notifications instead of 1... I thought I'd done a quick reply so quickly copied pasted and resent.
nyway back to this.. Seems that I have my FULL explanation. Thankyou my only prob is I cant seem to get my head around the SP on tha access side. Maybe you can tell me where I'm goign wrong..
I wanted to start off with soemthing very simple and just run the insert queries specifying the values from within the sp itself....
This is as far as I got.
CODE

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[SPSaleInvNewInv] --, SPSaleInvNewInv, SPSaleInvNewInv>
    -- Add the parameters for the stored procedure here
--    <@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
--    <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
@VarNewMaxIDInv int = 54000000,
     @FctNewIdDocRef int = 500000 ,
@date1 smalldatetime = '10/30/56'
AS
INSERT INTO tblInOut ( IDInvoiceNo, Dte, IDDocRef,IDProdCode ) SELECT @VarNewMaxIDInv , @Date1 +1  , @FctNewIdDocRef ,43  
INSERT INTO TblInvoices ( IDInvoiceNo, Order_Date )SELECT @VarNewMaxIDInv, @Date1
--BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    --SET NOCOUNT ON;
    -- Insert statements for procedure here
--    SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
--
--END

But when trying to execute the query its telling me that I can enter nulls in the primary index field.. I Thought Id entered values and a box also comes up asking me to enter the values.. though it was done in the sql :(
Am realy very lost now..
Go to the top of the page
 
LPurvis
post Feb 13 2007, 06:11 AM
Post#14


UtterAccess Editor
Posts: 16,086
Joined: 27-June 06
From: England (North East / South Yorks)


Hmm there is quite a bit yet to do on that one - the ADO calling code is effective - but pointless until the SP is functional. :-)
Can you describe your exact intention?
Does your table have an Identity (autonumber) Primary Key - or are you having to specify the values?
(Which would make the Output requirement pretty pointless :-S)
Go to the top of the page
 
champagne_charly
post Feb 13 2007, 06:24 AM
Post#15



Posts: 477
Joined: 31-January 05
From: Blackheath, London


Hi,
riginally it was an auto number. Then so that I could find open the form on the new record I created a dmax+1 function which was inserted into the insert statment. Within the formulae a public variabgle was set to the value of the new ID.. Not sure what you thing of this method but I thought at least if the function itself was in the update query then there was no room for error. ie 2 users trying to make the same record.
Do you think better to change back to Autonumber?
Anyway in this case I wanted to start at the simplest part of the procedure. which was just the insert statments and instead of working out the new ID I just wanted to test using any value. But cant seem to even get that right
getting error
Msg 515, Level 16, State 2, Procedure Procedure_Name, Line 16
Cannot insert the value NULL into column 'IDInOut', table 'db.dbo.tblInOut'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Go to the top of the page
 
champagne_charly
post Feb 13 2007, 07:30 AM
Post#16



Posts: 477
Joined: 31-January 05
From: Blackheath, London


mmm well I didnt read the error message properly and it was whilst going through each section bit by bit that I realised that in access tbl idinout is auto and in sql server its int... Now I cant seem to be able to convert back to auto.. Problem was with the other dable default values had not been set.
Go to the top of the page
 
champagne_charly
post Feb 13 2007, 07:42 AM
Post#17



Posts: 477
Joined: 31-January 05
From: Blackheath, London


Well quite simple
utonumber access = int with identity spec = yes and autoincrements = 1
Now I can get back to what I wanted to do in the first place
Go to the top of the page
 
LPurvis
post Feb 13 2007, 08:12 AM
Post#18


UtterAccess Editor
Posts: 16,086
Joined: 27-June 06
From: England (North East / South Yorks)


I'd always look to an Autonumber/Identity.
(That's what they're there for - you're just creating work for yourself otherwise).
!--c1-->
CODE
ALTER PROCEDURE [dbo].[SPSaleInvNewInv]
    @VarNewMaxIDInv int = 54000000,
    @FctNewIdDocRef int = 500000 ,
    @date1 datetime = '10/30/1956'
AS
BEGIN
SET NOCOUNT ON
    INSERT INTO tblInOut ( IDInvoiceNo, Dte, IDDocRef,IDProdCode )
        SELECT @VarNewMaxIDInv , @Date1 +1  , @FctNewIdDocRef , 43  
    INSERT INTO TblInvoices ( IDInvoiceNo, Order_Date )
        SELECT @VarNewMaxIDInv, @Date1
END

But without knowing what your fields are it's difficult to be specific about what will or won't work.
(e.g. don't know which is the PK you're trying to insert yourself)
Go to the top of the page
 
champagne_charly
post Feb 13 2007, 08:42 AM
Post#19



Posts: 477
Joined: 31-January 05
From: Blackheath, London


Thanks, The prob was my tables i.e autonumbers no longer being so.. default values that disapeared and nulls that werent allowed. All thats solved now so am trying to take it further.
date1 = getdate -- but this giveme an error
@VarNewMaxIDInv int output = SELECT (MAX(IDInvoiceNo)
FROM dbo.TblInvoices )+1
not working and secondly and is it really wise to do this? IF in a multiuser enviroment is it actually possible for 2 people run the same SP at the same time?
Go to the top of the page
 
champagne_charly
post Feb 13 2007, 08:51 AM
Post#20



Posts: 477
Joined: 31-January 05
From: Blackheath, London


Well I've solved the @VarNewMaxIDInv issue
VarNewMaxIDInv int output
Has
SELECT @VarNewMaxIDInv = max(IDInvoiceNo)+1 FROM TblInvoices
but the getdate function is still a prob..
Go to the top of the page
 
3 Pages V  1 2 3 >


Custom Search
RSSSearch   Top   Lo-Fi    16th December 2017 - 09:22 PM