Getting a Return Value from a Stored Procedure in VBA

Hi all, I have a Stored procedure, here:

PROCEDURE [dbo].[new_tbox]
    -- Add the parameters for the stored procedure here
    @slide_id int,
    @shape_id int,
    @cnt_typ_id int
AS

BEGIN
    DECLARE @cnt_id int, @tbox_id int
    INSERT cnt_info VALUES(@slide_id, @shape_id, @cnt_typ_id)

    SET @cnt_id = @@IDENTITY

    INSERT tbox_cnt VALUES(@cnt_id)
    SET @tbox_id = @@IDENTITY
    RETURN @tbox_id

END
That returns the variable.  This works in SQL Server, and I get the
return value

My VBA code from my ADP is as such:

With cmd
    .ActiveConnection = ConnStr
    .CommandText = "dbo.new_tbox"
    .CommandType = adCmdStoredProc
    .Parameters.Append .CreateParameter("@slide_id", adInteger,
adParamInput, , Forms("new_content").slide_id.Value)
    .Parameters.Append .CreateParameter("@shape_id", adInteger,
adParamInput, , Forms("new_content").Combo15.Value)
    .Parameters.Append .CreateParameter("@cnt_typ_id", adInteger,
adParamInput, , Forms("new_content").Combo0.Value)
    .Parameters.Append .CreateParameter("return_value", adInteger,
adParamReturnValue)
    .Execute
    Debug.Print .Parameters("return_value")
End With

However, this does not work, I get that I have too many arguments
assigned to the stored procedure
what am I doing wrong?
thanks
Michael

0
KoldKay
11/7/2007 8:53:38 AM
access 16762 articles. 3 followers. Follow

2 Replies
6571 Views

Similar Articles

[PageSpeed] 51

KoldKay wrote:
> Hi all, I have a Stored procedure, here:
> 
> PROCEDURE [dbo].[new_tbox]
>     -- Add the parameters for the stored procedure here
>     @slide_id int,
>     @shape_id int,
>     @cnt_typ_id int
> AS
> 
> BEGIN
>     DECLARE @cnt_id int, @tbox_id int
>     INSERT cnt_info VALUES(@slide_id, @shape_id, @cnt_typ_id)
> 
>     SET @cnt_id = @@IDENTITY
> 
>     INSERT tbox_cnt VALUES(@cnt_id)
>     SET @tbox_id = @@IDENTITY
>     RETURN @tbox_id
> 
> END
> That returns the variable.  This works in SQL Server, and I get the
> return value
> 
> My VBA code from my ADP is as such:
> 
> With cmd
>     .ActiveConnection = ConnStr
>     .CommandText = "dbo.new_tbox"
>     .CommandType = adCmdStoredProc
>     .Parameters.Append .CreateParameter("@slide_id", adInteger,
> adParamInput, , Forms("new_content").slide_id.Value)
>     .Parameters.Append .CreateParameter("@shape_id", adInteger,
> adParamInput, , Forms("new_content").Combo15.Value)
>     .Parameters.Append .CreateParameter("@cnt_typ_id", adInteger,
> adParamInput, , Forms("new_content").Combo0.Value)
>     .Parameters.Append .CreateParameter("return_value", adInteger,
> adParamReturnValue)
>     .Execute
>     Debug.Print .Parameters("return_value")
> End With
> 
> However, this does not work, I get that I have too many arguments
> assigned to the stored procedure
> what am I doing wrong?
> thanks
> Michael

Try declaring the return value as the first parameter, i e

      .Parameters.Append .CreateParameter("return_value", adInteger, _
          adParamReturnValue)
      .Parameters.Append .CreateParameter("@slide_id", adInteger, _
          adParamInput, , Forms("new_content").slide_id.Value)
....

-- 
Roy-Vidar
0
RoyVidar
11/7/2007 11:30:47 AM
On Nov 7, 11:30 am, RoyVidar <roy_vidarNOS...@yahoo.no> wrote:
> KoldKay wrote:
> > Hi all, I have a Stored procedure, here:
>
> > PROCEDURE [dbo].[new_tbox]
> >     -- Add the parameters for the stored procedure here
> >     @slide_id int,
> >     @shape_id int,
> >     @cnt_typ_id int
> > AS
>
> > BEGIN
> >     DECLARE @cnt_id int, @tbox_id int
> >     INSERT cnt_info VALUES(@slide_id, @shape_id, @cnt_typ_id)
>
> >     SET @cnt_id = @@IDENTITY
>
> >     INSERT tbox_cnt VALUES(@cnt_id)
> >     SET @tbox_id = @@IDENTITY
> >     RETURN @tbox_id
>
> > END
> > That returns the variable.  This works in SQL Server, and I get the
> > return value
>
> > My VBA code from my ADP is as such:
>
> > With cmd
> >     .ActiveConnection = ConnStr
> >     .CommandText = "dbo.new_tbox"
> >     .CommandType = adCmdStoredProc
> >     .Parameters.Append .CreateParameter("@slide_id", adInteger,
> > adParamInput, , Forms("new_content").slide_id.Value)
> >     .Parameters.Append .CreateParameter("@shape_id", adInteger,
> > adParamInput, , Forms("new_content").Combo15.Value)
> >     .Parameters.Append .CreateParameter("@cnt_typ_id", adInteger,
> > adParamInput, , Forms("new_content").Combo0.Value)
> >     .Parameters.Append .CreateParameter("return_value", adInteger,
> > adParamReturnValue)
> >     .Execute
> >     Debug.Print .Parameters("return_value")
> > End With
>
> > However, this does not work, I get that I have too many arguments
> > assigned to the stored procedure
> > what am I doing wrong?
> > thanks
> > Michael
>
> Try declaring the return value as the first parameter, i e
>
>       .Parameters.Append .CreateParameter("return_value", adInteger, _
>           adParamReturnValue)
>       .Parameters.Append .CreateParameter("@slide_id", adInteger, _
>           adParamInput, , Forms("new_content").slide_id.Value)
> ...
>
> --
> Roy-Vidar

Hi Roy,
Thanks, that worked perfectly!
Much appreciated.
Michael

1
KoldKay
11/7/2007 11:48:22 AM
Reply:

Similar Artilces:

Can I get menus in 2007
Is there any way to get a menu bar w/ word 2007? I like 2007 but the choices in the tabs are not sufficient and I have to fight to get the function I need. thanks john Add what you want to the Quick Access Toolbar. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnews.microsoft.com "John Coltrane" <tendengarci@yahoo.com> wrote in message news:ejbigfJ5KHA.5808@TK2MSFTNGP02.phx.gbl... > Is there any way to get a menu bar w/...

Sumif returning incorrect value
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I have data in columns c to f and rows 2 to 20 <br> I also have data in a2 to a20. <br> I am trying to sum the data in s2 to f20 when the row matches my criteria in a2 to a 20. The formula that I am using is <br><br>sumif(a2:a20,&quot;=12&quot;,c2:f20) <br><br>the result will only sum the values in column c. <br><br>Does anyone know what the problem is? <br><br>Thanks <br><br>Allan SUMIF() has various limitations. this will work,...

Whats the best web based store for integrating with RMS?
Hi, in the past monts I opened a topic about RMS integration with yahoo stores... and i found that fully integration is not a reality at this moment. What other web based stores can be integrated easily or fully or at least well integrated with RMS? (im planning to purchase the QH for RMS too) Thanks Hello Aldo! We have quite a few carts that will work with Microsoft RMS. Our Web Integrator: Professional is quite robust with its feature set, and affordably priced. http://www.newestech.com/POS/RMS/eCommerce/Professional.htm http://www.newestech.com/POS/RMS/eCommerce/Web%20Integrator...

Comparing values of two charts
Thank you in advance for your assistance. I and trying to create a tool I can use to grade class assignments; one in particular uses charts. The class assignment the students are responsible for handing in will be one Excel file. I was hoping there was a way to sort of "extract" certain field values from the charts properties as well as the header/footer properties. If I were able to compare the one student's with my master copy it would save alot of time. If a more detailed explanation is required, I'll try to give you more. g ...

Magento Web Store experience?
Does anyone have any experiences they would like to share with the Magento Web Store and interfacing it to GP? -- Richard L. Whaley Author / Consultant / MVP 2006-2008 Documentation for Software Users Get our Free Tips and Tricks Newsletter and check out our books at http://www.AccoladePublications.com ...

Get $500 to your PAYPAL Account by just one CLICK)
i have hidden the form of getting $500 to your PAYPAL ACCOUNT....CLICK on the IMAGE which is on the RIGHT SIDE of the website http://tricks-4u.4-all.org/ ...

why do i get #### in my formula cell?
I don't really know much about Excel, and my formula (it's just simple arithmetic formulas) returns with ##### displayed on the cell, with a tooltip showing the cell's correct value when I hover over it. airn, make the column wider and see if that helps -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "airn" <airn@discussions.microsoft.com> wrote in message news:C6F4DD7C-3D7D-475C-894D-0F7745735666@microso...

How do I get focus back on an Excel file?
When automating Excel, if there is a picture (graphic) on the worksheet, when the file is opened, the picture has the focus and mouse-clicks anywhere on the Excel window are lost. Is there a way to get the focus back so that other cells can be editted? Insert this line of code into the macro: ActiveSheet.Range("A1").Select You could also rework the code to avoid selecting the shape in the first place. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "Stumpiana" <Stumpiana...

Installed Exchange 2003 Server on new Hardwar... How to get rid of old Exchange 2000 Server?
Hi, Our single domain company (Win 2000 DC's) was running a single Exchange 2000 server. I bought a new server, installed Windows 2003 Server on it, joined it to the domian (non-DC) and then installed Exchange 2003 Server on that and joined it to the site. I then pointed our MX records towards the new box. As soon as I verified connectivity, I began moving user's mailboxes over 1 by 1. Everyone is now running smoothly on the new server. I have not as of yet removed the old server (still on, running, no mialboxes). The problem is, that as soon as I turn off the old Exchange 2000 s...

SBS2003
I am running Small Business Server 2003. The server was built and installed about 2 months ago. Everything worked great till about a week ago (no changes made on server). The information store grew to 16 Gig. I did online defrag and offline defrag. Event log says I have 15 Gig free after defrag. 2 hours later it is out of space again. I have defraged this about 5 times now and need help to fix. I only have 5 users and none of them have more than 500 MB in mailbox. You either have a mail loop set up or are being used as a relay or are just getting spam bombed. You need to enable message ...

Convert time stored as decimalised number to time format
Hello How can I convert 3.5 hours to 3:30:00? -- Emma Emma A1/24 and formate as time Mike Rogers "Emma" wrote: > Hello > How can I convert 3.5 hours to 3:30:00? > -- > Emma ...

How to bold daily max values
I have one year of hourly data in one column and the date in another column. I'd like to write some excel functions to bold the daily max values. Any help is greatly appreciated! Ginger Conditional formatting would do this, but I don't understand the data. Can you post some sample data? -- HTH Bob Phillips "Ginger" <Ginger@discussions.microsoft.com> wrote in message news:34230977-525A-476E-A29F-AA27144E1E10@microsoft.com... > I have one year of hourly data in one column and the date in another column. > I'd like to write some excel functions to bold t...

Copying values from place to place
Hello community: Is it possible say, to type a value in a textbox in one form (Form A), automatically copy that value, then open another form( Form B) and automatically paste that value into another textbox in Form B? Is this possible? Thank you for your help. -Gabriel M. If you open Form B from Form A you can use the OpenArgs of the OpenForm command line to pass a value to Form B. docmd.OpenForm "FormName",,,,,,Me.TextBoxName On the OnLoad event of the Form B you can use set the value in the text box If not IsNull(Me.OpenArgs) Then Me.TextBox = Me.OpenArgs End If -- ...

Infopath w/ manually entered values in drop-down and qry results
I had originally posted this elsewhere, but was told this forum is the appropariate plase. I have an Infopath form with a drop-down listbox, that is poulated with manually-entered values. I choose a value, submit the updated data, and it does put the correct value in the SQL 2005 database. However, the next time I query the data, the value in the drop-down list box is the default value for the list-box, not the value from the database, which is misleading. I would have expected the drop-down listbox to display the value from the database instead. Thanks. On Tue, 4 Sep 2007, in...

How do I get the data/xml/export option
My export option is disabled under data/xml tab. What is the exact feature I need to install to get this export xml option. I didn't install all the option during excel installation. Which specific install tab are required during excel installation? thanks Nick One way would be to do a web query, first preformat the cells as text, then do the query (data>import external data>new web query), put in the web site address, select the part you want to import, under properties select preserve cell formatting and do the import -- Regards, Peo Sjoblom (No private emails pleas...

Getting to grips with MOOL
Hi, I've bought, downloaded and installed MOOL, only to find it's not quite what I thought (I know some others have been disappointed by the reality vs representation), but more importantly that there's really no guidance on using it. I'm quite surprised how difficult I'm finding it as I've never struggled with any non-specialist applications before, but perhaps I'm just ageing badly! Anyway, can anyone give me a simple guide to getting going? What I would like: Basically I'd like something rather like Exchange. I (now) understand that MOOL won't do ...

Can't get newsgroup messages posted by self
I have a weird issue with Windows Live Mail concerting synchronizing messages from newsgroups. If I post a message to the Microsoft private newsgroups (privatenews.microsoft.com), I don't see the post but I can see new posts by other users. If I use Outlook Express on Windows XP, I see my posts. I don't have problems with WLM synchronizing my hotmail e-mail. Any idea how I can troubleshoot this issue? "Teo Lachev" <TeoLachev@discussions.microsoft.com> wrote in message news:65458221-D220-47D9-95E3-A9B593424714@microsoft.com... > I have a weird issu...

get network login name
I have the following code to get the network login name: Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _ "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long Function fOSUserName() As String ' Returns the network login name Dim lngLen As Long, lngX As Long Dim strUserName As String strUserName = String$(254, 0) lngLen = 255 lngX = apiGetUserName(strUserName, lngLen) If lngX <> 0 Then fOSUserName = Left$(strUserName, lngLen - 1) Else fOSUserName = vbNullString End If E...

Help, how do I get an exe file with outlook 2002.
is there a way to turn off the GD stupid F#@$ing level one interecept "feature" in outlook? I am so angry with this stupid program@! I need to download an exe file and cannot with this software! Maybe if you washed your mouth out with soap, someone would be willing to help you. This is a technical forum -- not a place for ranting fools. "Sean" <sean@pointblankinc.com> wrote in message news:5a8001c42d8f$52c08b20$a401280a@phx.gbl... > is there a way to turn off the GD stupid F#@$ing level > one interecept "feature" in outlook? > > I am so angr...

attachment getting stripped off
I have Outlook 2000 and every time I origionate an email from my home computer with an attachment it gets stripped off before it gets to the addressee. I can foward emails with attachments and they stick. Any tips M are you using RTF format? if so, switch to plain text and see if it helps. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, Visual Quick Start Guide - OneNote 2003 Outlook Tips: http://www.outlook-tips.net/ http://www.poremsky.com - http://www.cdolive.com Expert Zone http://www.microsoft.com/windowsxp/expertzone Search for answe...

Experts Challenge: MAPI is unable to access Public Folder store items
Goodday to all of you, I have crosspost my messages to several newsgroups. Reason for this is that my previous posts to different newsgroups did not solve the problem. Maybe if I crosspost, there will be an expert that has a solution. Also I am not sure to which group I should post my problem. Maybe this post long-winded, do not see at as material for a novel!!! - I am just trying to explain the exact TERRIBLE situation! ---- ---- ---- ---- I am experiencing a problem with our Public Folder Store. All my users are not be able to access e-mail items in the Public Folders using their Outlo...

Trying to get a type of video file format to open and run on my we
I created a video using Windows Movie maker and saved as .wmp file. I saved it to my computer and then inserted on my web page under Front Page 2003. Nothing! I then converted the file to avi format - still nothing! I then tried mpeg4 still nothing. The manual is next to useless on this subject. Anyone tell me what I need to do so that when a user opens up the page the video plays automatically. -- Simon Holloway Save as a .wmv and insert a suitable player into the page. For IE only, Insert->Web Component->Advanced Controls->ActiveX Control Click Next Choose Window...

Logical Filtering based on Value
I've been using this formula to sort some values and return results if( or( and( b9<>"", isnumber( find( Left(b9,1 ,"AWFUY") ) ), isnumber( find("SHORT",g9 ) ), sum( countif(c9,{"BOG","BLM","CMO"}) ) ) ) ) ,i9-0.01,j9 ) --I used ALT-ENTER between each formula so as to see thing clearly Column A values can be "B01" Column B values can be "BRN" Columns I & J are values, one being lowest the other being highest allowed. On another sheet I...

Reading empty query returns
When I open a form that has a bound query as the record source, no data appears (including the control objects of the form) if the query's select statement does not finds records that match the criteria I specify. This causes a problem in my VBA code as the variable I use to check the number of records returned by the query does not seem to be able to read empty query returns, even if I use isnull. So I guess I am asking how can I get Access to read an empty result set in VBA from a SQL select statement that is run??? Here is part of my code for this: DoCmd.OpenForm stDocName2, , ...

Using Jet to read excel file returns blank for last cell
With VB6 I am opening an Excel file to query the contents using ADO. I am then using the contents of the worksheet to create a format file that is used for a bulk insert into SQL Server. We designed the program this way so that users can use a spreadsheet to import data in any format they want and process the data based on values set in the spreadsheet. This keeps us from having to design new tables and format files for new record layouts which come down pretty often. Everything works fine usually. There is just one perculiarity that is happening with the Excel file. Sometimes, th...