Angelflower
Have a nice day.
- Local time
- Yesterday, 19:44
- Joined
- Nov 8, 2006
- Messages
- 51
I have a stored procedure that inserts a new line to a table when pressed from a button on the form. The code behind the form is grabbing the Utility_read_ID as a variable and passing that to the stored procedure via a Pass-Through query.
The code below seems to work with the exception that I keep getting a Run-time error ‘3219’ invalid operation yet the new line is added to the table as expected. The error is on the Set rstAddRead = qdf.OpenRecordset(). If I take this line out of the code no new line is added to the table but if I keep it then I get the desired results. What can I do to make this line happy and not throw the error? Tks!
Code behind the button:
Private Sub cmd_AddRead_Click()
'Declare needed variables and collections
Dim db As DAO.Database
Dim rstAddRead As DAO.Recordset
Dim strUtility_read_ID As Integer
Dim strAddRead As String
Dim qdf As QueryDef
'Assign values to variables based on parameter selections on the main menu.
strUtility_read_ID = [Forms]![electricity_reading]![utility_read_ID]
'Build SQL string to pass into procAddReading based on parameters.
strAddRead = "EXEC procAddReading " & "'" & strUtility_read_ID & "'"
' Set database variable to current database.
Set db = CurrentDb
'Open QueryDef object.
Set qdf = db.QueryDefs("cmd_procAddReading")
'Assign query def sql command to the sql string built form user selections.
qdf.SQL = strAddRead
'Allow the stored procedure to return a result set to be bound.
qdf.ReturnsRecords = False
Debug.Print strAddRead
'Open Recordset object as a query def.
Set rstAddRead = qdf.OpenRecordset()
This is what the Debug window says for the debug.PrintstrAddRead
EXEC procAddReading '66'
This is the Stored Procedure:
USE [Utilities]
GO
/****** Object: StoredProcedure [dbo].[procAddReading] Script Date: 08/19/2013 07:48:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date: 08/15/2013
-- Description: Add new line to the utility_reading
-- =============================================
ALTER PROCEDURE [dbo].[procAddReading]
-- Add the parameters for the stored procedure here
@utility_read_ID int = 0
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
INSERT INTO [Utilities].[dbo].[utility_reading]
([utility_read_ID]
,[transaction_date]
,[from_reading_date]
,[to_reading_date]
,[current_reading]
,[prior_reading]
,[usage_provided]
,[usage_calculated]
,[reading_provided]
,[divisor]
,[multiplier]
,[reading_to_standard]
,[calc_type]
,[notes])
SELECT utility_reading.utility_read_ID
, GETDATE () as transaciton_date
, dateadd(M,+1,max(from_reading_date)) as from_reading_date
, dateadd(day,-1,dateadd(M,+2,max(from_reading_date))) as to_reading_date
, '' as current_reading
, '' as prior_reading
, '' as usage_provided
, '' as usage_calculated
, '' as reading_provided
, utility_reading.divisor
, utility_reading.multiplier
, '' as reading_to_standard
, utility_reading.calc_type
, '' as notes
FROM utility_reading
WHERE utility_reading.utility_read_ID = @utility_read_ID
group by utility_read_ID, divisor, multiplier, calc_type
order by utility_read_ID asc
END
The code below seems to work with the exception that I keep getting a Run-time error ‘3219’ invalid operation yet the new line is added to the table as expected. The error is on the Set rstAddRead = qdf.OpenRecordset(). If I take this line out of the code no new line is added to the table but if I keep it then I get the desired results. What can I do to make this line happy and not throw the error? Tks!
Code behind the button:
Private Sub cmd_AddRead_Click()
'Declare needed variables and collections
Dim db As DAO.Database
Dim rstAddRead As DAO.Recordset
Dim strUtility_read_ID As Integer
Dim strAddRead As String
Dim qdf As QueryDef
'Assign values to variables based on parameter selections on the main menu.
strUtility_read_ID = [Forms]![electricity_reading]![utility_read_ID]
'Build SQL string to pass into procAddReading based on parameters.
strAddRead = "EXEC procAddReading " & "'" & strUtility_read_ID & "'"
' Set database variable to current database.
Set db = CurrentDb
'Open QueryDef object.
Set qdf = db.QueryDefs("cmd_procAddReading")
'Assign query def sql command to the sql string built form user selections.
qdf.SQL = strAddRead
'Allow the stored procedure to return a result set to be bound.
qdf.ReturnsRecords = False
Debug.Print strAddRead
'Open Recordset object as a query def.
Set rstAddRead = qdf.OpenRecordset()
This is what the Debug window says for the debug.PrintstrAddRead
EXEC procAddReading '66'
This is the Stored Procedure:
USE [Utilities]
GO
/****** Object: StoredProcedure [dbo].[procAddReading] Script Date: 08/19/2013 07:48:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date: 08/15/2013
-- Description: Add new line to the utility_reading
-- =============================================
ALTER PROCEDURE [dbo].[procAddReading]
-- Add the parameters for the stored procedure here
@utility_read_ID int = 0
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
INSERT INTO [Utilities].[dbo].[utility_reading]
([utility_read_ID]
,[transaction_date]
,[from_reading_date]
,[to_reading_date]
,[current_reading]
,[prior_reading]
,[usage_provided]
,[usage_calculated]
,[reading_provided]
,[divisor]
,[multiplier]
,[reading_to_standard]
,[calc_type]
,[notes])
SELECT utility_reading.utility_read_ID
, GETDATE () as transaciton_date
, dateadd(M,+1,max(from_reading_date)) as from_reading_date
, dateadd(day,-1,dateadd(M,+2,max(from_reading_date))) as to_reading_date
, '' as current_reading
, '' as prior_reading
, '' as usage_provided
, '' as usage_calculated
, '' as reading_provided
, utility_reading.divisor
, utility_reading.multiplier
, '' as reading_to_standard
, utility_reading.calc_type
, '' as notes
FROM utility_reading
WHERE utility_reading.utility_read_ID = @utility_read_ID
group by utility_read_ID, divisor, multiplier, calc_type
order by utility_read_ID asc
END