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: - Function Execute_Stored_Procedure(Proc_Name As String, Proc_Values As String, Optional ByVal Output_Return As Boolean)
-
-
On Error GoTo Execute_Stored_Procedure_Error
-
-
Dim MyDb As DAO.Database, MyQ As QueryDef
-
Dim sql_send As String
-
-
Set MyDb = CurrentDb()
-
-
Set MyQ = MyDb.QueryDefs("qTemp_Stored_Proc")
-
-
-
-
Dim stServer, stDatabase, stUsername, stPassword As String
-
Dim stConnect As String
-
-
stServer = "xxx.xxx.xxxx.net"
-
stDatabase = "xxx"
-
stUsername = "xxx"
-
stPassword = "4B:mV6hpz_\2q=</Y%?b"
-
stConnect = "ODBC;DRIVER={SQL Server};SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
-
-
-
sql_send = "exec " & Proc_Name & " " & Proc_Values
-
-
' Set the SQL property and concatenate the variables.
-
MyQ.Connect = stConnect
-
-
If Output_Return = False Then
-
MsgBox "no supply"
-
Else
-
MsgBox "supply"
-
sql_send = "declare @NCC_OUTPUT int " & sql_send
-
End If
-
Debug.Print sql_send
-
-
MyQ.SQL = sql_send
-
MyQ.ReturnsRecords = False
-
-
MyQ.Execute
-
-
Dim X As Integer
-
X = MyQ.Parameters("@NCC_OUTPUT")
-
-
-
' Debug.Print MyQ.SQL
-
-
Set MyQ = Nothing
-
-
Done:
-
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: - ALTER PROCEDURE [dbo].[sp_Submit_NCC]
-
-- Add the parameters for the stored procedure here
-
@DteOccur datetime,
-
@Pdetected nvarchar(50),
-
@DeptRaisedBy int,
-
@DeptResp int,
-
@NCDescrip nvarchar(255),
-
@NCCause nvarchar(255),
-
@NCImmediateAct nvarchar(255),
-
@NCLocation nvarchar(100),
-
@PNumOrRef nvarchar(30),
-
@EventCat int,
-
@ReportedEmailAddy nvarchar(100),
-
@NCC_Output_ID INT OUTPUT
-
-
AS
-
BEGIN
-
-- SET NOCOUNT ON added to prevent extra result sets from
-
-- interfering with SELECT statements.
-
SET NOCOUNT ON;
-
-
DECLARE @NCC_ID int
-
-
-- Insert statements for procedure here
-
INSERT INTO tbllog (DteOccur, Pdetected, DeptRaisedBy, DeptResp, NCDescrip, NCCause, NCImmediateAct, NCLocation, PNumOrRef, EventCat, ReportedEmailAddy)
-
VALUES (@DteOccur, @Pdetected, @DeptRaisedBy, @DeptResp, @NCDescrip, @NCCause, @NCImmediateAct, @NCLocation, @PNumOrRef, @EventCat, @ReportedEmailAddy)
-
-
SELECT @NCC_ID=SCOPE_IDENTITY()
-
-
INSERT INTO tblStatusTiming (NCIDLINK, StatusType)
-
VALUES (@NCC_ID, 1)
-
-
Set @NCC_Output_ID = SCOPE_IDENTITY()
-
-
END
The execution line for the function is: - ?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)
-
Overall the pass through query being sent is: - 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: - 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
9 9503
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.
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
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).
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: - -- Add the parameters for the stored procedure here
-
@DteOccur datetime,
-
@Pdetected nvarchar(50),
-
@DeptRaisedBy int,
-
@DeptResp int,
-
@NCDescrip nvarchar(255),
-
@NCCause nvarchar(255),
-
@NCImmediateAct nvarchar(255),
-
@NCLocation nvarchar(100),
-
@PNumOrRef nvarchar(30),
-
@EventCat int,
-
@ReportedEmailAddy nvarchar(100)
-
-
-
AS
-
BEGIN
-
-- SET NOCOUNT ON added to prevent extra result sets from
-
-- interfering with SELECT statements.
-
SET NOCOUNT ON;
-
-
DECLARE @NCC_ID int
-
-
-- Insert statements for procedure here
-
INSERT INTO tbllog (DteOccur, Pdetected, DeptRaisedBy, DeptResp, NCDescrip, NCCause, NCImmediateAct, NCLocation, PNumOrRef, EventCat, ReportedEmailAddy)
-
VALUES (@DteOccur, @Pdetected, @DeptRaisedBy, @DeptResp, @NCDescrip, @NCCause, @NCImmediateAct, @NCLocation, @PNumOrRef, @EventCat, @ReportedEmailAddy)
-
-
SET @NCC_ID=SCOPE_IDENTITY()
-
-
INSERT INTO tblStatusTiming (NCIDLINK, StatusType)
-
VALUES (@NCC_ID, 1)
-
-
Select SCOPE_IDENTITY() As Test
-
-
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. - 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" :( - MyQ.SQL = sql_send
-
MyQ.ReturnsRecords = False
-
MyQ.Execute
-
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.
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: - Function ado_test()
-
Dim cnn As New ADODB.Connection
-
Dim rs As New ADODB.Recordset
-
Dim cmd As New ADODB.Command
-
-
Dim stServer, stDatabase, stUsername, stPassword As String
-
Dim stConnect As String
-
-
stServer = "xxx.xxx.xxxx.net"
-
stDatabase = "xxxxx"
-
stUsername = "xxxxx"
-
stPassword = "4B:mV6hpz_\2q=</Y%?b"
-
stConnect = "DRIVER={SQL Server};SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
-
-
cnn.connectionString = stConnect
-
Debug.Print stConnect
-
cnn.Open
-
cnn.CursorLocation = adUseClient
-
-
-
With cmd
-
.ActiveConnection = cnn
-
.CommandText = "sp_Submit_NCC"
-
.CommandType = adCmdStoredProc
-
'.Parameters.Refresh
-
.Parameters("@DteOccur").value = "12/11/2011"
-
.Parameters("@PDetected").value = "'Chris'"
-
.Parameters("@DeptRaisedBy").value = "2"
-
.Parameters("@DeptResp").value = "2"
-
.Parameters("@NcDescrip").value = "'Chris'"
-
.Parameters("@NCCause").value = "'Chris'"
-
.Parameters("@NCImmediateAct").value = "'Chris'"
-
.Parameters("@NCLocation").value = "'Chris'"
-
.Parameters("@PNumOrRef").value = "'Chris'"
-
.Parameters("@EventCat").value = "2"
-
.Parameters("@ReportedEmailAddy").value = "''"
-
Set rs = .Execute()
-
End With
-
-
'Retrieve the ID the new record is stored at
-
Dim id As Integer
-
-
id = Nz(rs!test, 0)
-
MsgBox id
-
End Function
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? - QueryDef SQL:
- SELECT Employees.EmployeeID, Employees.LastName, Employees.FirstName, Employees.MI
-
FROM Employees
-
WHERE (((Employees.LastName)=[Enter Last Name]) AND ((Employees.FirstName)=[Enter First Name]));
- Code to retrieve Parameter Names:
- Dim qdf As DAO.QueryDef
-
Dim prm As DAO.Parameter
-
-
Set qdf = CurrentDb.QueryDefs("qryEmployees")
-
-
For Each prm In qdf.Parameters
-
Debug.Print prm.Name
-
Next
- [Enter Last Name]
-
[Enter First Name]
- OUTPUT:
- [Enter Last Name]
-
[Enter First Name]
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.
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
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
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
|
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...
|
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
|
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...
|
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...
|
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.
...
|
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...
|
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
|
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...
|
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?...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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....
|
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
|
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...
| |