?
Solved

Append records using two variables

Posted on 2003-11-01
5
Medium Priority
?
470 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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 insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

770 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