473,320 Members | 1,707 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Sql server stored procedure output parameter to ms access vba

374 256MB
Hi all,

I use the following function to execute a stored procedure which inserts records in to my sql database. I am trying to return the primary key value so that I can display it on my page however I keep getting the error that the "item is not found in the current collection".

My function is:
Expand|Select|Wrap|Line Numbers
  1. Function Execute_Stored_Procedure(Proc_Name As String, Proc_Values As String, Optional ByVal Output_Return As Boolean)
  2.  
  3.    On Error GoTo Execute_Stored_Procedure_Error
  4.  
  5.  Dim MyDb As DAO.Database, MyQ As QueryDef
  6.    Dim sql_send As String
  7.  
  8.    Set MyDb = CurrentDb()
  9.  
  10.        Set MyQ = MyDb.QueryDefs("qTemp_Stored_Proc")
  11.  
  12.  
  13.  
  14.     Dim stServer, stDatabase, stUsername, stPassword As String
  15.     Dim stConnect As String
  16.  
  17.     stServer = "xxx.xxx.xxxx.net"
  18.     stDatabase = "xxx"
  19.     stUsername = "xxx"
  20.     stPassword = "4B:mV6hpz_\2q=</Y%?b"
  21.     stConnect = "ODBC;DRIVER={SQL Server};SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
  22.  
  23.  
  24.     sql_send = "exec " & Proc_Name & " " & Proc_Values
  25.  
  26.    ' Set the SQL property and concatenate the variables.
  27.    MyQ.Connect = stConnect
  28.  
  29.    If Output_Return = False Then
  30.    MsgBox "no supply"
  31.    Else
  32.    MsgBox "supply"
  33.    sql_send = "declare @NCC_OUTPUT int " & sql_send
  34.       End If
  35.    Debug.Print sql_send
  36.  
  37.    MyQ.SQL = sql_send
  38.    MyQ.ReturnsRecords = False
  39.  
  40.    MyQ.Execute
  41.  
  42.    Dim X As Integer
  43.    X = MyQ.Parameters("@NCC_OUTPUT")
  44.  
  45.  
  46.   ' Debug.Print MyQ.SQL
  47.  
  48.   Set MyQ = Nothing
  49.  
  50. Done:
  51.    Exit Function
Sorry that the code is a bit rough but I have been testing it over and over.

The stored procedure is as follows and this runs fine:

Expand|Select|Wrap|Line Numbers
  1. ALTER PROCEDURE [dbo].[sp_Submit_NCC] 
  2.     -- Add the parameters for the stored procedure here
  3.     @DteOccur datetime, 
  4.     @Pdetected nvarchar(50),
  5.     @DeptRaisedBy int,
  6.     @DeptResp int,
  7.     @NCDescrip nvarchar(255),
  8.     @NCCause nvarchar(255),
  9.     @NCImmediateAct nvarchar(255),
  10.     @NCLocation nvarchar(100),
  11.     @PNumOrRef nvarchar(30),
  12.     @EventCat int,
  13.     @ReportedEmailAddy nvarchar(100),
  14.     @NCC_Output_ID INT OUTPUT
  15.  
  16. AS
  17. BEGIN
  18.     -- SET NOCOUNT ON added to prevent extra result sets from
  19.     -- interfering with SELECT statements.
  20.     SET NOCOUNT ON;
  21.  
  22. DECLARE @NCC_ID int
  23.  
  24.     -- Insert statements for procedure here
  25.     INSERT INTO tbllog (DteOccur, Pdetected, DeptRaisedBy, DeptResp, NCDescrip, NCCause, NCImmediateAct, NCLocation, PNumOrRef, EventCat, ReportedEmailAddy)
  26.     VALUES (@DteOccur, @Pdetected, @DeptRaisedBy, @DeptResp, @NCDescrip, @NCCause, @NCImmediateAct, @NCLocation, @PNumOrRef, @EventCat, @ReportedEmailAddy)
  27.  
  28. SELECT @NCC_ID=SCOPE_IDENTITY()
  29.  
  30.     INSERT INTO tblStatusTiming (NCIDLINK, StatusType)
  31.     VALUES (@NCC_ID, 1)
  32.  
  33. Set @NCC_Output_ID = SCOPE_IDENTITY()
  34.  
  35. END
The execution line for the function is:

Expand|Select|Wrap|Line Numbers
  1. ?Execute_Stored_Procedure("sp_Submit_NCC","@DteOccur='12/11/2011', @PDetected='Chris', @DeptRaisedBy=2, @DeptResp=2, @NCDescrip='test', @NCCause='test', @NCImmediateAct='test', @NCLocation='test', @PNumOrRef='test', @EventCat=2, @ReportedEmailAddy='', @NCC_Output_ID=@NCC_Output OUTPUT",True)
  2.  
Overall the pass through query being sent is:
Expand|Select|Wrap|Line Numbers
  1. declare @NCC_OUTPUT int exec sp_Submit_NCC @DteOccur='12/11/2011', @PDetected='Chris', @DeptRaisedBy=2, @DeptResp=2, @NCDescrip='test', @NCCause='test', @NCImmediateAct='test', @NCLocation='test', @PNumOrRef='test', @EventCat=2, @ReportedEmailAddy='', @NCC_Output_ID=@NCC_Output OUTPUT
The error is on the line:
Expand|Select|Wrap|Line Numbers
  1.   X = MyQ.Parameters("@NCC_OUTPUT")
In my function.. I have also tried adding .value on to the end of the .parameters with no luck.

Thank you for any help.

Chris
Oct 26 '11 #1
9 9503
ADezii
8,834 Expert 8TB
This isn't exactly my area of expertise, but I was under the impression that Pass Through Queries provide no mechanism for dealing with Output Parameters or the Return Value of a Stored Procedure.
Oct 26 '11 #2
jimatqsi
1,271 Expert 1GB
Adezii may be right, I'm not sure, I recall being stumped when I tried to run a stored procedure from VBA. But I suppose a clever programmer would just change the stored procedure to drop the result in a table instead of returning it.

What line of code does the error occur on? What happens if you edit query qTemp_Stored_Proc? Does it have a spelling problem?

Jim
Oct 26 '11 #3
NeoPa
32,556 Expert Mod 16PB
ADezii:
I was under the impression that Pass Through Queries provide no mechanism for dealing with Output Parameters or the Return Value of a Stored Procedure.
Pass-Through queries have properties Returns Records and Log Messages. The former indicates there is a cursor to display and the latter creates a log table on the fly for messages displayed (which can include returned values I believe).
Oct 26 '11 #4
munkee
374 256MB
I have moved on a small amount with this I have altered my stored procedure and when running this within Sql server query analyser I am returning the desired value.

The trouble now is getting this in to ms access via my function which I cant seem to get my head around.

The new stored procedure:

Expand|Select|Wrap|Line Numbers
  1.     -- Add the parameters for the stored procedure here
  2.     @DteOccur datetime, 
  3.     @Pdetected nvarchar(50),
  4.     @DeptRaisedBy int,
  5.     @DeptResp int,
  6.     @NCDescrip nvarchar(255),
  7.     @NCCause nvarchar(255),
  8.     @NCImmediateAct nvarchar(255),
  9.     @NCLocation nvarchar(100),
  10.     @PNumOrRef nvarchar(30),
  11.     @EventCat int,
  12.     @ReportedEmailAddy nvarchar(100)
  13.  
  14.  
  15. AS
  16. BEGIN
  17.     -- SET NOCOUNT ON added to prevent extra result sets from
  18.     -- interfering with SELECT statements.
  19.     SET NOCOUNT ON;
  20.  
  21. DECLARE @NCC_ID int
  22.  
  23.     -- Insert statements for procedure here
  24.     INSERT INTO tbllog (DteOccur, Pdetected, DeptRaisedBy, DeptResp, NCDescrip, NCCause, NCImmediateAct, NCLocation, PNumOrRef, EventCat, ReportedEmailAddy)
  25.     VALUES (@DteOccur, @Pdetected, @DeptRaisedBy, @DeptResp, @NCDescrip, @NCCause, @NCImmediateAct, @NCLocation, @PNumOrRef, @EventCat, @ReportedEmailAddy)
  26.  
  27. SET @NCC_ID=SCOPE_IDENTITY()
  28.  
  29.     INSERT INTO tblStatusTiming (NCIDLINK, StatusType)
  30.     VALUES (@NCC_ID, 1)
  31.  
  32. Select SCOPE_IDENTITY() As Test
  33.  
  34. END
Used the scope identity to select from the table and assign to a column name of "Test".

Executing the following returns Test with the row value of the last Autonumber.

Expand|Select|Wrap|Line Numbers
  1. exec sp_Submit_NCC @DteOccur='12/11/2011', @PDetected='Chris', @DeptRaisedBy=2, @DeptResp=2, @NCDescrip='test', @NCCause='test', @NCImmediateAct='test', @NCLocation='test', @PNumOrRef='test', @EventCat=2, @ReportedEmailAddy=''
Output: Test 55

Once again though same error within my vba function of "item not found in current collection" :(

Expand|Select|Wrap|Line Numbers
  1. MyQ.SQL = sql_send
  2.    MyQ.ReturnsRecords = False
  3.  MyQ.Execute
  4.  MsgBox MyQ.Parameters("Test").value
If I try to set the .returnsrecords = true I get an error regarding "cannot execute a select query" which is fine because you can only "execute" an insert/update/delete/append and a simple select does not get executed, which part of my stored procedure is doing.
Oct 26 '11 #5
munkee
374 256MB
OK managed to get this working by switching away from DAO and using ADO as I found a pretty decent tutorial regarding its usage. I have pretty much 0 knowledge of ADO so it would be nice to have found out the solution using DAO but the structure seems quite easy to understand in the new method so for now this will have to do.

My completed code:
Expand|Select|Wrap|Line Numbers
  1. Function ado_test()
  2. Dim cnn As New ADODB.Connection
  3.     Dim rs As New ADODB.Recordset
  4.     Dim cmd As New ADODB.Command
  5.  
  6.     Dim stServer, stDatabase, stUsername, stPassword As String
  7.     Dim stConnect As String
  8.  
  9.     stServer = "xxx.xxx.xxxx.net"
  10.     stDatabase = "xxxxx"
  11.     stUsername = "xxxxx"
  12.     stPassword = "4B:mV6hpz_\2q=</Y%?b"
  13.     stConnect = "DRIVER={SQL Server};SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
  14.  
  15.    cnn.connectionString = stConnect
  16.    Debug.Print stConnect
  17.    cnn.Open
  18.    cnn.CursorLocation = adUseClient
  19.  
  20.  
  21.   With cmd
  22.     .ActiveConnection = cnn
  23.     .CommandText = "sp_Submit_NCC"
  24.     .CommandType = adCmdStoredProc
  25.     '.Parameters.Refresh
  26.     .Parameters("@DteOccur").value = "12/11/2011"
  27.     .Parameters("@PDetected").value = "'Chris'"
  28.     .Parameters("@DeptRaisedBy").value = "2"
  29.     .Parameters("@DeptResp").value = "2"
  30.     .Parameters("@NcDescrip").value = "'Chris'"
  31.     .Parameters("@NCCause").value = "'Chris'"
  32.     .Parameters("@NCImmediateAct").value = "'Chris'"
  33.     .Parameters("@NCLocation").value = "'Chris'"
  34.     .Parameters("@PNumOrRef").value = "'Chris'"
  35.     .Parameters("@EventCat").value = "2"
  36.     .Parameters("@ReportedEmailAddy").value = "''"
  37.     Set rs = .Execute()
  38.   End With
  39.  
  40.   'Retrieve the ID the new record is stored at
  41.   Dim id As Integer
  42.  
  43.   id = Nz(rs!test, 0)
  44.   MsgBox id
  45. End Function
Oct 26 '11 #6
ADezii
8,834 Expert 8TB
I realize that this is a long shot, and I'm not even sure if it is possible. For Validation purposes, within your context, can you integrate Code such as the following that will display the Name of each Parameter in the Parameters Collection of the QueryDef Object?
  1. QueryDef SQL:
    Expand|Select|Wrap|Line Numbers
    1. SELECT Employees.EmployeeID, Employees.LastName, Employees.FirstName, Employees.MI
    2. FROM Employees
    3. WHERE (((Employees.LastName)=[Enter Last Name]) AND ((Employees.FirstName)=[Enter First Name]));
  2. Code to retrieve Parameter Names:
    Expand|Select|Wrap|Line Numbers
    1. Dim qdf As DAO.QueryDef
    2. Dim prm As DAO.Parameter
    3.  
    4. Set qdf = CurrentDb.QueryDefs("qryEmployees")
    5.  
    6. For Each prm In qdf.Parameters
    7.   Debug.Print prm.Name
    8. Next
    Expand|Select|Wrap|Line Numbers
    1. [Enter Last Name]
    2. [Enter First Name]
  3. OUTPUT:
    Expand|Select|Wrap|Line Numbers
    1. [Enter Last Name]
    2. [Enter First Name]
Oct 26 '11 #7
NeoPa
32,556 Expert Mod 16PB
I would strongly recommend using DAO for all those case where Access, and only Access (Jet SQL), is used but for all other situations use ADODB.
Oct 26 '11 #8
munkee
374 256MB
Thank you for the input NeoPa. I assume this means Jet runs perfectly fine and is adept at working with the client side object manipulation etc but ADODB is the one for working with the server
Oct 26 '11 #9
NeoPa
32,556 Expert Mod 16PB
I wouldn't phrase it quite that way, but if it helps :-
DAO works best in an exclusively Jet environment. That includes linked tables to outside sources.
ADODB works better when working directly with outside sources.

It would all be client-side essentially, as that's where the code executes.
Oct 26 '11 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: crisp99 | last post by:
Hi, I have a SQL Stored Procedure : CREATE PROCEDURE spFilterOne @city varchar(25) AS SELECT * FROM tblCities WHERE tblCities.strCity = @city ORDER BY tblCities.strName
6
by: fumanchu | last post by:
I've got to let end users (really just one person) load billing batch files into a third party app table. They need to specify the billing cycle name, the batch name, and the input file name and...
1
by: Craig Buchanan | last post by:
Has any one been able to get MS DataAccess Block code to populate a return parameter in the ExecuteReader method? If so, how? Thanks, Craig Buchanan
2
by: honcho | last post by:
Hello, Does anyone have an example of an SQL Server stored procedure that updates a record, where one of its field is of type "text"? My procedure is /* ** Update the client note and...
4
by: Mr Not So Know It All | last post by:
im new to SQL Server and ASP.Net. Here's my problem. I have this SQL Server stored procedure with an input parameter and output parameter CREATE PROCEDURE . @in_rc varchar(8) @out_eList...
0
by: rockdale | last post by:
Hi, All How to get the output parameter's value when you use the SQLHelper (Microsoft Data Access Block)? When I try to access my ourput parm I got the following error. ...
5
by: Alan T | last post by:
I have a string passed from another function, eg list_employee 4 This will call the stored procedure list_employee to get details of employee of id 4. Is there a way to just use this...
9
by: VK | last post by:
Hello, Can some one suggest me a way of putting Stored procedure output into a file, the file should be available on my Computer, I don't want to use FTP process. Thanks vk
1
by: David Gillen | last post by:
Hello. I'm having difficulty getting the value of output parameters from stored procedures on a SQL server. I understand that this just doesn't work with odbc so I've tried both the mssql...
1
by: leec | last post by:
I need to populate an access table with the results from a SELECT stored procedure in SQL Server. 1. Is this possible? 2. If it is possible, can it all be part of the modified Stored Procedure?...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.