Doozy
asked on
Append records using two variables
I have a table: tblSchedule in SQL Server with fields:
Schedule_ID (int PRIMARYKEY Autoincrementing), Schedule_CostType (char 16), Schedule_Section (char 16) , Schedule_No (char 16), Schedule_Order (float 8), Schedule_Desc (char 32), Schedule_Unit (char 16), Schedule_Qty (real 4) ,tender_ID (int FK)
I want to select all records where Tender_ID = a variable tender_ID_Original
and append them in the same table but with Tender_ID = a variable tender_ID_New
(Note: there are no integrity problems as The FK tender_ID_New already exists)
I have written the sp :
ALTER PROCEDURE procNewSchedule
(@tender_ID_Original int,
@tender_ID_New int)
AS
SET NOCOUNT ON
INSERT INTO
dbo.tblSchedule(Schedule_C ostType, Schedule_Section, Schedule_No, Schedule_Order, Schedule_Desc, Schedule_Unit, Schedule_Qty,tender_ID)
SELECT Schedule_CostType,Schedule _No, Schedule_Section, Schedule_Order, Schedule_Desc, Schedule_Unit, Schedule_Qty,@tender_ID_Ne w
FROM dbo.tblSchedule
WHERE Tender_ID = @tender_ID_Original
SET NOCOUNT OFF
When I run it asks for the variables OK and seems to run OK but I get the message 'The stored procedure executed successfully but did not return records"
What have i done wrong?
also I want to run thus sp in Access using VBA code but I'm not sure how to pass the variables
Private Sub btnAppendSchedule_Click()
Dim tender_ID_Original, tender_ID_New As Integer
Dim vSQL As String
tender_ID_Original = Forms!frmRepeatSchedule.cb oTenderOld .Column(0)
tender_ID_New = Forms!frmRepeatSchedule.tx tTenderNew
vSQL = " procNewSchedule ??????? "
DoCmd.RunSQL vSQL
End Sub
Thanks in advance
Doozy
Schedule_ID (int PRIMARYKEY Autoincrementing), Schedule_CostType (char 16), Schedule_Section (char 16) , Schedule_No (char 16), Schedule_Order (float 8), Schedule_Desc (char 32), Schedule_Unit (char 16), Schedule_Qty (real 4) ,tender_ID (int FK)
I want to select all records where Tender_ID = a variable tender_ID_Original
and append them in the same table but with Tender_ID = a variable tender_ID_New
(Note: there are no integrity problems as The FK tender_ID_New already exists)
I have written the sp :
ALTER PROCEDURE procNewSchedule
(@tender_ID_Original int,
@tender_ID_New int)
AS
SET NOCOUNT ON
INSERT INTO
dbo.tblSchedule(Schedule_C
SELECT Schedule_CostType,Schedule
FROM dbo.tblSchedule
WHERE Tender_ID = @tender_ID_Original
SET NOCOUNT OFF
When I run it asks for the variables OK and seems to run OK but I get the message 'The stored procedure executed successfully but did not return records"
What have i done wrong?
also I want to run thus sp in Access using VBA code but I'm not sure how to pass the variables
Private Sub btnAppendSchedule_Click()
Dim tender_ID_Original, tender_ID_New As Integer
Dim vSQL As String
tender_ID_Original = Forms!frmRepeatSchedule.cb
tender_ID_New = Forms!frmRepeatSchedule.tx
vSQL = " procNewSchedule ??????? "
DoCmd.RunSQL vSQL
End Sub
Thanks in advance
Doozy
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Lowfatspread, your answer got that part working - I'll give you half points (OK?)
nigelrivett - your code won't compile in Access VBA - it highlights 'Server' and says variable not defined
So 125 points still for anyone who can solve how to pass the variables
nigelrivett - your code won't compile in Access VBA - it highlights 'Server' and says variable not defined
So 125 points still for anyone who can solve how to pass the variables
ASKER
Points to Lowfatspread
I'll resubmit second pert of question
Thanks,
Doozy
I'll resubmit second pert of question
Thanks,
Doozy
Doozy,
You will need to install MDAC for ADO to work.
http://www.hillsoft.com/hs_installMDAC.htm
HTH
Namasi Navaretnam
You will need to install MDAC for ADO to work.
http://www.hillsoft.com/hs_installMDAC.htm
HTH
Namasi Navaretnam
For executing an sp in vba see
http://www.nigelrivett.net/DBAccess.inc.html
in can be simpler as
dim objcmd, rowsaffected
set objcmd = Server.CreateObject("ADODB
objCmd.Parameters.append objcmd.createparameter("",
objCmd.Parameters.append objcmd.createparameter("",
objCmd.ActiveConnection = objConn ' need connection here
objCmd.CommandType = adCmdStoredProc
objCmd.CommandText = "procNewSchedule"
objCmd.Execute rowsaffected
set objCmd.ActiveConnection = nothing