Solved

Append records using two variables

Posted on 2003-11-01
5
466 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

785 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