Hi All
I am trying to get my head around using stored procedures in SQL Server
On my form, I have a button that SHOULD add students from a specified formid(which is a combobox)
The following is the underlying code.
My 'SPI_ACT_ACTIVITYDTL' Stored Procedure on the sql server is defined as :
As you can see, the SP is trying to INSERT data that is sourced from a SELECT query.
If I can only make calls to an ACTON query that does not contain a SELECT statement in it, then how can i make the SP work and then as a consequence of not being able to put SELECTS in my SP's, is there any point to using an sp?
Thanks
I am trying to get my head around using stored procedures in SQL Server
On my form, I have a button that SHOULD add students from a specified formid(which is a combobox)
The following is the underlying code.
Code:
Private Sub Btn_Add_Studs_By_Form_Click()
Dim qdef As DAO.QueryDef
Dim err_msg As String
Dim err As DAO.Error
On Error GoTo err_lbl
'In the front end, I have a linked Pass Though Query called "PTQ" which is modified as follows.
Set qdef = CurrentDb.QueryDefs("PTQ")
qdef.SQL = "EXEC SPI_ACT_ACTIVITYDTL " & Me.ID_ACT_ActivityHdr & ", '" & Trim(Me.Add_Form) & "'"
'Although the line above returns what seems to be the correct call with correct parameter info,
'the following line returns a 3065 error (Cannot execute select query)
qdef.Execute dbFailOnError
end sub
My 'SPI_ACT_ACTIVITYDTL' Stored Procedure on the sql server is defined as :
Code:
USE [Demo]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SPI_ACT_ACTIVITYDTL] @ID_ACT_ActivityHdr integer, @FMName nvarchar(15)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
SET NOCOUNT ON;
INSERT INTO dbo.ACT_ActivityDtl ( ID_Student, FmName, ID_ACT_ActivityHdr, ID_staff, ID_form )
SELECT ID_Student, FORM_Name, @ID_ACT_ActivityHdr, 0 AS Staff, ID_FORMID
FROM dbo.STU_Student
WHERE (((dbo.STU_Student.FORM_Name)=@FMName));
END
GO
As you can see, the SP is trying to INSERT data that is sourced from a SELECT query.
If I can only make calls to an ACTON query that does not contain a SELECT statement in it, then how can i make the SP work and then as a consequence of not being able to put SELECTS in my SP's, is there any point to using an sp?
Thanks