Using ADODB Command to run a SP on msSQL2000 
Author Message
 Using ADODB Command to run a SP on msSQL2000

I am trying to do a relatively simple stored procedure
moving data from one table(new_members) to another
(members), using a unique id in the first to pinpoint the
data, I also need to substract the context of a few cells
to my application(asp / vbscript) for other purposes...

so far my sp looks like this:


AS
SET NOCOUNT ON
Begin Transaction
INSERT [members]
        (members.member_first_name,
        members.member_last_name,
        members.member_login,
        members.member_password,
        members.member_email,
        members.member_city,
        members.member_zip,
        members.member_address1,
        members.member_address2,
        members.member_address3,
        members.member_ph_work,
        members.member_ph_work_ext,
        members.member_fax,
        members.club_status_id,
        members.member_date_added,
        members.security_level_id)
                SELECT  
        new_members.member_first_name,  
        new_members.member_last_name,  
        new_members.member_login,      
        new_members.member_password,    
        new_members.member_email,      
        new_members.member_city,        
        new_members.member_zip,        
        new_members.member_address1,    
        new_members.member_address2,    
        new_members.member_address3,    
        new_members.member_ph_work,    
        new_members.member_ph_work_ext,        
        new_members.member_fax,        
        new_members.club_status_id,    
        new_members.member_date_added,  
        new_members.security_level_id FROM new_members  


COMMIT
GO

How do I access the data in the select statement? Or do I
need to make a seperate select query in the sp? More
precisely I need: first_name, last_name, login, password
and email.

Thx



Fri, 29 Oct 2004 19:38:01 GMT
 Using ADODB Command to run a SP on msSQL2000

Hi,

Add another SELECT statement right after you finished you transferring. So
it will be last statement if you SP and it will select records you need

--
Val Mazur
Microsoft MVP


Quote:
> I am trying to do a relatively simple stored procedure
> moving data from one table(new_members) to another
> (members), using a unique id in the first to pinpoint the
> data, I also need to substract the context of a few cells
> to my application(asp / vbscript) for other purposes...

> so far my sp looks like this:

> CREATE PROCEDURE [sp_transfer_members]

> AS
> SET NOCOUNT ON
> Begin Transaction
> INSERT [members]
> (members.member_first_name,
> members.member_last_name,
> members.member_login,
>   members.member_password,
> members.member_email,
>   members.member_city,
>   members.member_zip,
>   members.member_address1,
> members.member_address2,
> members.member_address3,
> members.member_ph_work,
> members.member_ph_work_ext,
>   members.member_fax,
>   members.club_status_id,
> members.member_date_added,
> members.security_level_id)
> SELECT
> new_members.member_first_name,
> new_members.member_last_name,
> new_members.member_login,
> new_members.member_password,
> new_members.member_email,
> new_members.member_city,
> new_members.member_zip,
> new_members.member_address1,
> new_members.member_address2,
> new_members.member_address3,
> new_members.member_ph_work,
> new_members.member_ph_work_ext,
> new_members.member_fax,
> new_members.club_status_id,
> new_members.member_date_added,
> new_members.security_level_id FROM new_members

> DELETE FROM [new_members] WHERE new_member_id =

> COMMIT
> GO

> How do I access the data in the select statement? Or do I
> need to make a seperate select query in the sp? More
> precisely I need: first_name, last_name, login, password
> and email.

> Thx



Fri, 29 Oct 2004 19:54:18 GMT
 Using ADODB Command to run a SP on msSQL2000

Quote:
>-----Original Message-----
>Hi,

>Add another SELECT statement right after you finished you
transferring. So
>it will be last statement if you SP and it will select

records you need

Yeh thats what I was thinking as well, but I just hoped I
could do it in a "nicer" way, it is probably just my
aesthetics that are causing the problem...

[snip]

Quote:

>> CREATE PROCEDURE [sp_transfer_members]

>> AS
>> SET NOCOUNT ON
>> Begin Transaction
>> INSERT [members]
>> (members.member_first_name,
>> members.member_last_name,
>> members.member_login,
>>   members.member_password,
>> members.member_email,
>>   members.member_city,
>>   members.member_zip,
>>   members.member_address1,
>> members.member_address2,
>> members.member_address3,
>> members.member_ph_work,
>> members.member_ph_work_ext,
>>   members.member_fax,
>>   members.club_status_id,
>> members.member_date_added,
>> members.security_level_id)
>> SELECT
>> new_members.member_first_name,
>> new_members.member_last_name,
>> new_members.member_login,
>> new_members.member_password,
>> new_members.member_email,
>> new_members.member_city,
>> new_members.member_zip,
>> new_members.member_address1,
>> new_members.member_address2,
>> new_members.member_address3,
>> new_members.member_ph_work,
>> new_members.member_ph_work_ext,
>> new_members.member_fax,
>> new_members.club_status_id,
>> new_members.member_date_added,
>> new_members.security_level_id FROM new_members

>> DELETE FROM [new_members] WHERE new_member_id =

>> COMMIT
>> GO



Fri, 29 Oct 2004 20:22:30 GMT
 Using ADODB Command to run a SP on msSQL2000
Hi,

Do not worry. This is not a bad way

--
Val Mazur
Microsoft MVP


Quote:

> >-----Original Message-----
> >Hi,

> >Add another SELECT statement right after you finished you
> transferring. So
> >it will be last statement if you SP and it will select
> records you need

> Yeh thats what I was thinking as well, but I just hoped I
> could do it in a "nicer" way, it is probably just my
> aesthetics that are causing the problem...

> [snip]

> >> CREATE PROCEDURE [sp_transfer_members]

> >> AS
> >> SET NOCOUNT ON
> >> Begin Transaction
> >> INSERT [members]
> >> (members.member_first_name,
> >> members.member_last_name,
> >> members.member_login,
> >>   members.member_password,
> >> members.member_email,
> >>   members.member_city,
> >>   members.member_zip,
> >>   members.member_address1,
> >> members.member_address2,
> >> members.member_address3,
> >> members.member_ph_work,
> >> members.member_ph_work_ext,
> >>   members.member_fax,
> >>   members.club_status_id,
> >> members.member_date_added,
> >> members.security_level_id)
> >> SELECT
> >> new_members.member_first_name,
> >> new_members.member_last_name,
> >> new_members.member_login,
> >> new_members.member_password,
> >> new_members.member_email,
> >> new_members.member_city,
> >> new_members.member_zip,
> >> new_members.member_address1,
> >> new_members.member_address2,
> >> new_members.member_address3,
> >> new_members.member_ph_work,
> >> new_members.member_ph_work_ext,
> >> new_members.member_fax,
> >> new_members.club_status_id,
> >> new_members.member_date_added,
> >> new_members.security_level_id FROM new_members

> >> DELETE FROM [new_members] WHERE new_member_id =

> >> COMMIT
> >> GO



Fri, 29 Oct 2004 20:32:10 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. Sending command to a FoxPro database using ADODB.Command Object

2. Error6115(KILL command cannot be used inside user transactions) in Mssql2000

3. adodb.connection, adodb.command and MTS

4. SP output parameters & ADODB.Recordset using adAsyncExecute

5. ADODB Command Error when using adExecuteStream

6. Using where IN with a ADODB.Command object...

7. Using browser-side ADODB.Command object?

8. Problem using Named Parameters with ADODB.Command

9. Err in using ADODB Command Object in VB.Net

10. Optional StorecProc params using ADODB::Command.

11. Optional StorecProc params using ADODB::Command. [Repost]

12. Install sheild express,run command/run command parameters?


 
Powered by phpBB® Forum Software