Stored Procedure - Insert & Return Unique ID 
Author Message
 Stored Procedure - Insert & Return Unique ID

Hi everyone

A big thank you to all contributors to this forum. I have learnt a lot over
the past few weeks from reading responses to questions (mine and others),
and I'm finally getting to grips with SQL Server/ADO.

I have managed to get my program working with insertions/deletions/updates
from VB code, and I thought that I ought to have a look at stored
procedures. I'm OK (I think) on deletions & updates, but insertions are
giving me a headache.

The structure of my database is such that the majority of tables have a
unique ID field.

In VB code I would do the following (very simplified recordset structure):

with rsStock
    .addnew
    ![StockCode] = txtCode
    ![StockDesc] = txtDescription
    .Update
    idStock = ![StockID]
end with

rsStock![StockID] is the unique field, and I can now use the idStock to
update other tables where necessary.

To do the same with a Stored Procedure. I'm OK on the Insert as follows:

CREATE PROCEDURE sp_insert_Stock


AS INSERT INTO [StockDb].[dbo].[Stock]
  ([StockCode],
  [StockDesc])

VALUES


and in VB, I'd do the following

dim adoCommand as new adodb.command
dim Params(2) as variant

Params(1)=txtCode
Params(2)=txtDescription

with adocommand
    .ActiveConnection = dbCurrentConnection
    .CommandText = "sp_insert_Stock"
    .CommandType = adCmdStoredProc
    .Execute , Params
end with

I'm stuck on how to get the Stored procedure to return the StockID back to
the VB program. If I can get this cracked, I can use SPs for most, if not
all, of my inserts.

TIA
--
Chris
Kampala, Uganda



Sun, 19 Dec 2004 17:02:16 GMT  
 Stored Procedure - Insert & Return Unique ID
Hi,

It depends on version of SQL Server. In case of SQL Server 7 you would need

Server 2000 you could use SCOPE_IDENTITY() function to get it

Check next KB about that

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q195910

also next link
http://www.able-consulting.com/ADO_Faq.htm#Q9


Stored Procedure"

--
Val Mazur
Microsoft MVP


Quote:
> Hi everyone

> A big thank you to all contributors to this forum. I have learnt a lot
over
> the past few weeks from reading responses to questions (mine and others),
> and I'm finally getting to grips with SQL Server/ADO.

> I have managed to get my program working with insertions/deletions/updates
> from VB code, and I thought that I ought to have a look at stored
> procedures. I'm OK (I think) on deletions & updates, but insertions are
> giving me a headache.

> The structure of my database is such that the majority of tables have a
> unique ID field.

> In VB code I would do the following (very simplified recordset structure):

> with rsStock
>     .addnew
>     ![StockCode] = txtCode
>     ![StockDesc] = txtDescription
>     .Update
>     idStock = ![StockID]
> end with

> rsStock![StockID] is the unique field, and I can now use the idStock to
> update other tables where necessary.

> To do the same with a Stored Procedure. I'm OK on the Insert as follows:

> CREATE PROCEDURE sp_insert_Stock


> AS INSERT INTO [StockDb].[dbo].[Stock]
>   ([StockCode],
>   [StockDesc])

> VALUES


> and in VB, I'd do the following

> dim adoCommand as new adodb.command
> dim Params(2) as variant

> Params(1)=txtCode
> Params(2)=txtDescription

> with adocommand
>     .ActiveConnection = dbCurrentConnection
>     .CommandText = "sp_insert_Stock"
>     .CommandType = adCmdStoredProc
>     .Execute , Params
> end with

> I'm stuck on how to get the Stored procedure to return the StockID back to
> the VB program. If I can get this cracked, I can use SPs for most, if not
> all, of my inserts.

> TIA
> --
> Chris
> Kampala, Uganda



Sun, 19 Dec 2004 19:42:07 GMT  
 Stored Procedure - Insert & Return Unique ID
Hi Val

Excellent info (once again!). The second link has all the info required.

Thanks

Chris

Now to convert my main pricing update to a SP... Hmmmmm :)



Sun, 19 Dec 2004 22:13:01 GMT  
 Stored Procedure - Insert & Return Unique ID
Stored Proc looks like this;

CREATE PROCEDURE sp_insert_Stock



AS INSERT INTO [StockDb].[dbo].[Stock]
  ([StockCode],
  [StockDesc])


-- The quick and dirty VB code is this, but you can speed it up by creating
the parameters in code...

    Set cmd = New ADODB.Command
    Set cmd.ActiveConnection = ADOConnection
    cmd.CommandText = "sp_insert_Stock"
    cmd.CommandType = adCmdStoredProc
    cmd.Parameters.Refresh


    cmd.Execute


--
regards,

Paul Durdin
Taradata Ltd


Quote:
> Hi everyone

> A big thank you to all contributors to this forum. I have learnt a lot
over
> the past few weeks from reading responses to questions (mine and others),
> and I'm finally getting to grips with SQL Server/ADO.

> I have managed to get my program working with insertions/deletions/updates
> from VB code, and I thought that I ought to have a look at stored
> procedures. I'm OK (I think) on deletions & updates, but insertions are
> giving me a headache.

> The structure of my database is such that the majority of tables have a
> unique ID field.

> In VB code I would do the following (very simplified recordset structure):

> with rsStock
>     .addnew
>     ![StockCode] = txtCode
>     ![StockDesc] = txtDescription
>     .Update
>     idStock = ![StockID]
> end with

> rsStock![StockID] is the unique field, and I can now use the idStock to
> update other tables where necessary.

> To do the same with a Stored Procedure. I'm OK on the Insert as follows:

> CREATE PROCEDURE sp_insert_Stock


> AS INSERT INTO [StockDb].[dbo].[Stock]
>   ([StockCode],
>   [StockDesc])

> VALUES


> and in VB, I'd do the following

> dim adoCommand as new adodb.command
> dim Params(2) as variant

> Params(1)=txtCode
> Params(2)=txtDescription

> with adocommand
>     .ActiveConnection = dbCurrentConnection
>     .CommandText = "sp_insert_Stock"
>     .CommandType = adCmdStoredProc
>     .Execute , Params
> end with

> I'm stuck on how to get the Stored procedure to return the StockID back to
> the VB program. If I can get this cracked, I can use SPs for most, if not
> all, of my inserts.

> TIA
> --
> Chris
> Kampala, Uganda



Mon, 20 Dec 2004 02:37:54 GMT  
 Stored Procedure - Insert & Return Unique ID
Thanks Paul

I notice that you don't have a "Go" at the end of your SP, whereas the
example on the Able Consulting page does.

I had copied the AC page and amended it, when checking the syntax everything
was OK, when saving it the error "Incorrect Syntax near Go" was displayed. I
solved it by adding "FROM [StockDb].[dbo].[Stock]" after the last select.

What does the Go do - if anything?
--
Chris
Kampala, Uganda


Quote:
> Stored Proc looks like this;

> CREATE PROCEDURE sp_insert_Stock



> AS INSERT INTO [StockDb].[dbo].[Stock]
>   ([StockCode],
>   [StockDesc])


> -- The quick and dirty VB code is this, but you can speed it up by
creating
> the parameters in code...

>     Set cmd = New ADODB.Command
>     Set cmd.ActiveConnection = ADOConnection
>     cmd.CommandText = "sp_insert_Stock"
>     cmd.CommandType = adCmdStoredProc
>     cmd.Parameters.Refresh


>     cmd.Execute


> --
> regards,

> Paul Durdin
> Taradata Ltd



> > Hi everyone

> > A big thank you to all contributors to this forum. I have learnt a lot
> over
> > the past few weeks from reading responses to questions (mine and
others),
> > and I'm finally getting to grips with SQL Server/ADO.

> > I have managed to get my program working with

insertions/deletions/updates

- Show quoted text -

Quote:
> > from VB code, and I thought that I ought to have a look at stored
> > procedures. I'm OK (I think) on deletions & updates, but insertions are
> > giving me a headache.

> > The structure of my database is such that the majority of tables have a
> > unique ID field.

> > In VB code I would do the following (very simplified recordset
structure):

> > with rsStock
> >     .addnew
> >     ![StockCode] = txtCode
> >     ![StockDesc] = txtDescription
> >     .Update
> >     idStock = ![StockID]
> > end with

> > rsStock![StockID] is the unique field, and I can now use the idStock to
> > update other tables where necessary.

> > To do the same with a Stored Procedure. I'm OK on the Insert as follows:

> > CREATE PROCEDURE sp_insert_Stock


> > AS INSERT INTO [StockDb].[dbo].[Stock]
> >   ([StockCode],
> >   [StockDesc])

> > VALUES


> > and in VB, I'd do the following

> > dim adoCommand as new adodb.command
> > dim Params(2) as variant

> > Params(1)=txtCode
> > Params(2)=txtDescription

> > with adocommand
> >     .ActiveConnection = dbCurrentConnection
> >     .CommandText = "sp_insert_Stock"
> >     .CommandType = adCmdStoredProc
> >     .Execute , Params
> > end with

> > I'm stuck on how to get the Stored procedure to return the StockID back
to
> > the VB program. If I can get this cracked, I can use SPs for most, if
not
> > all, of my inserts.

> > TIA
> > --
> > Chris
> > Kampala, Uganda



Mon, 20 Dec 2004 15:07:04 GMT  
 Stored Procedure - Insert & Return Unique ID
Hi Chris,

Here is exact description about GO from T-SQL reference

"Signals the end of a batch of Transact-SQL statements to the Microsoft? SQL
ServerT utilities"

It means that GO just *says*  "Execute previous statement". You cannot use
GO from VB or other code and it recognizable only from inside utilities like
Query Analyzer. You just need to remove GOs from your code, if you want to
execute it from VB

--
Val Mazur
Microsoft MVP


Quote:
> Thanks Paul

> I notice that you don't have a "Go" at the end of your SP, whereas the
> example on the Able Consulting page does.

> I had copied the AC page and amended it, when checking the syntax
everything
> was OK, when saving it the error "Incorrect Syntax near Go" was displayed.
I
> solved it by adding "FROM [StockDb].[dbo].[Stock]" after the last select.

> What does the Go do - if anything?
> --
> Chris
> Kampala, Uganda



> > Stored Proc looks like this;

> > CREATE PROCEDURE sp_insert_Stock



> > AS INSERT INTO [StockDb].[dbo].[Stock]
> >   ([StockCode],
> >   [StockDesc])


> > -- The quick and dirty VB code is this, but you can speed it up by
> creating
> > the parameters in code...

> >     Set cmd = New ADODB.Command
> >     Set cmd.ActiveConnection = ADOConnection
> >     cmd.CommandText = "sp_insert_Stock"
> >     cmd.CommandType = adCmdStoredProc
> >     cmd.Parameters.Refresh


> >     cmd.Execute


> > --
> > regards,

> > Paul Durdin
> > Taradata Ltd



> > > Hi everyone

> > > A big thank you to all contributors to this forum. I have learnt a lot
> > over
> > > the past few weeks from reading responses to questions (mine and
> others),
> > > and I'm finally getting to grips with SQL Server/ADO.

> > > I have managed to get my program working with
> insertions/deletions/updates
> > > from VB code, and I thought that I ought to have a look at stored
> > > procedures. I'm OK (I think) on deletions & updates, but insertions
are
> > > giving me a headache.

> > > The structure of my database is such that the majority of tables have
a
> > > unique ID field.

> > > In VB code I would do the following (very simplified recordset
> structure):

> > > with rsStock
> > >     .addnew
> > >     ![StockCode] = txtCode
> > >     ![StockDesc] = txtDescription
> > >     .Update
> > >     idStock = ![StockID]
> > > end with

> > > rsStock![StockID] is the unique field, and I can now use the idStock
to
> > > update other tables where necessary.

> > > To do the same with a Stored Procedure. I'm OK on the Insert as
follows:

> > > CREATE PROCEDURE sp_insert_Stock


> > > AS INSERT INTO [StockDb].[dbo].[Stock]
> > >   ([StockCode],
> > >   [StockDesc])

> > > VALUES


> > > and in VB, I'd do the following

> > > dim adoCommand as new adodb.command
> > > dim Params(2) as variant

> > > Params(1)=txtCode
> > > Params(2)=txtDescription

> > > with adocommand
> > >     .ActiveConnection = dbCurrentConnection
> > >     .CommandText = "sp_insert_Stock"
> > >     .CommandType = adCmdStoredProc
> > >     .Execute , Params
> > > end with

> > > I'm stuck on how to get the Stored procedure to return the StockID
back
> to
> > > the VB program. If I can get this cracked, I can use SPs for most, if
> not
> > > all, of my inserts.

> > > TIA
> > > --
> > > Chris
> > > Kampala, Uganda



Mon, 20 Dec 2004 19:41:04 GMT  
 Stored Procedure - Insert & Return Unique ID
Thanks Val

The Go was in the Stored Procedure, this is where the error was occuring.

I've left it out, and the function seems to be working fine.

Chris

--
Chris
Kampala, Uganda

Quote:
> Hi Chris,

> Here is exact description about GO from T-SQL reference

> "Signals the end of a batch of Transact-SQL statements to the Microsoft?
SQL
> ServerT utilities"

> It means that GO just *says*  "Execute previous statement". You cannot use
> GO from VB or other code and it recognizable only from inside utilities
like
> Query Analyzer. You just need to remove GOs from your code, if you want to
> execute it from VB

> --
> Val Mazur
> Microsoft MVP



> > Thanks Paul

> > I notice that you don't have a "Go" at the end of your SP, whereas the
> > example on the Able Consulting page does.

> > I had copied the AC page and amended it, when checking the syntax
> everything
> > was OK, when saving it the error "Incorrect Syntax near Go" was
displayed.
> I
> > solved it by adding "FROM [StockDb].[dbo].[Stock]" after the last
select.

> > What does the Go do - if anything?
> > --
> > Chris
> > Kampala, Uganda



> > > Stored Proc looks like this;

> > > CREATE PROCEDURE sp_insert_Stock



> > > AS INSERT INTO [StockDb].[dbo].[Stock]
> > >   ([StockCode],
> > >   [StockDesc])


> > > -- The quick and dirty VB code is this, but you can speed it up by
> > creating
> > > the parameters in code...

> > >     Set cmd = New ADODB.Command
> > >     Set cmd.ActiveConnection = ADOConnection
> > >     cmd.CommandText = "sp_insert_Stock"
> > >     cmd.CommandType = adCmdStoredProc
> > >     cmd.Parameters.Refresh


> > >     cmd.Execute


> > > --
> > > regards,

> > > Paul Durdin
> > > Taradata Ltd



> > > > Hi everyone

> > > > A big thank you to all contributors to this forum. I have learnt a
lot
> > > over
> > > > the past few weeks from reading responses to questions (mine and
> > others),
> > > > and I'm finally getting to grips with SQL Server/ADO.

> > > > I have managed to get my program working with
> > insertions/deletions/updates
> > > > from VB code, and I thought that I ought to have a look at stored
> > > > procedures. I'm OK (I think) on deletions & updates, but insertions
> are
> > > > giving me a headache.

> > > > The structure of my database is such that the majority of tables
have
> a
> > > > unique ID field.

> > > > In VB code I would do the following (very simplified recordset
> > structure):

> > > > with rsStock
> > > >     .addnew
> > > >     ![StockCode] = txtCode
> > > >     ![StockDesc] = txtDescription
> > > >     .Update
> > > >     idStock = ![StockID]
> > > > end with

> > > > rsStock![StockID] is the unique field, and I can now use the idStock
> to
> > > > update other tables where necessary.

> > > > To do the same with a Stored Procedure. I'm OK on the Insert as
> follows:

> > > > CREATE PROCEDURE sp_insert_Stock


> > > > AS INSERT INTO [StockDb].[dbo].[Stock]
> > > >   ([StockCode],
> > > >   [StockDesc])

> > > > VALUES


> > > > and in VB, I'd do the following

> > > > dim adoCommand as new adodb.command
> > > > dim Params(2) as variant

> > > > Params(1)=txtCode
> > > > Params(2)=txtDescription

> > > > with adocommand
> > > >     .ActiveConnection = dbCurrentConnection
> > > >     .CommandText = "sp_insert_Stock"
> > > >     .CommandType = adCmdStoredProc
> > > >     .Execute , Params
> > > > end with

> > > > I'm stuck on how to get the Stored procedure to return the StockID
> back
> > to
> > > > the VB program. If I can get this cracked, I can use SPs for most,
if
> > not
> > > > all, of my inserts.

> > > > TIA
> > > > --
> > > > Chris
> > > > Kampala, Uganda



Mon, 20 Dec 2004 21:56:42 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. Return Value & ID from INSERT Stored Proc

2. Returned recordset is closed when executing stored procedure with Insert Into statement

3. stored procedures: return codes and return status with rdo

4. Returning Informix serial id to VB after insert

5. VB & Access: duplicate inserts on unique key

6. To Stored Procedure or not to stored procedure

7. To stored procedure or not to stored procedure

8. sub report changes its source stored procedure to main report's stored procedure

9. To stored procedure or not to stored procedure

10. ado recordsets from stored procedures, stored procedures have input parameters

11. Error when running Insert Stored Procedure from Visual Basic

12. Group Insert with a Stored Procedure

 

 
Powered by phpBB® Forum Software