Solved

Append records using two variables

Posted on 2003-11-01
5
464 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:Doozy
5 Comments
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 250 total points
ID: 9664957
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
 
LVL 18

Expert Comment

by:nigelrivett
ID: 9665128
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
 

Author Comment

by:Doozy
ID: 9667161
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
 

Author Comment

by:Doozy
ID: 9667630
Points to Lowfatspread
I'll resubmit second pert of question
Thanks,
Doozy
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9668018
Doozy,

You will need to install MDAC for ADO to work.

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

HTH

Namasi Navaretnam
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now