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_CostType, 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_New
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.cboTenderOld.Column(0)
tender_ID_New = Forms!frmRepeatSchedule.txtTenderNew

vSQL = " procNewSchedule ??????? "

DoCmd.RunSQL vSQL

End Sub

Thanks in advance
Doozy
DoozyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LowfatspreadCommented:
Hi Doozy,
not sure for the vba code...

but try removing the set nocount lines
that should then return the number of rows affected  which
would qualify as a resultset...

also its good practice to end all procedures with
RETURN

good luck


Cheers!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
nigelrivettCommented:
It's doing an insert so doesn't return a resultset so you haven't done anything wrong.

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.Command")
objCmd.Parameters.append objcmd.createparameter("",adInteger, adParamInput,,tender_ID_Original)
objCmd.Parameters.append objcmd.createparameter("",adInteger, adParamInput,,tender_ID_New)
objCmd.ActiveConnection = objConn    ' need connection here
objCmd.CommandType = adCmdStoredProc
objCmd.CommandText = "procNewSchedule"
objCmd.Execute rowsaffected
set objCmd.ActiveConnection = nothing
0
DoozyAuthor Commented:
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
0
DoozyAuthor Commented:
Points to Lowfatspread
I'll resubmit second pert of question
Thanks,
Doozy
0
namasi_navaretnamCommented:
Doozy,

You will need to install MDAC for ADO to work.

http://www.hillsoft.com/hs_installMDAC.htm

HTH

Namasi Navaretnam
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.