[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Append records using two variables

Posted on 2003-11-01
5
Medium Priority
?
471 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 1000 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

649 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