(This is the second time I have sent this. For some reason I never did
see my thread on the server. If it appeared for you I apologize for the
duplicate.)

Hi I am struggling with creating an autoincremental key in my table in
my SQL database.

In the insert stored proc I have here I am trying to tell it to raise up
the number for the value in the QUOTEACTIONID field. It is the primary
key.
I am actually using this stored procedure in a vb.net program using
ADO.NET but I am told the incrementing should be done on the SQL side.

In addition I created a table called COUNTER which I thought about
inserting a higher number for each record added which the insert proc
could then use to enter a value for QUOTEACTIONID. But more experienced
folks told me this was a bad idea.

Can anyone suggest something and thank you.

CREATE PROCEDURE databaseexample.QuoteActionInsertCommand
(
@QUOTEACTIONID int,
@QUOTEACTIONDATE datetime,
@QUOTEACTIONLOGINNAME varchar(50),
@QUOTEACTIONIP varchar(50),
@QUOTEACTIONCOMPNAME varchar(50),
@QUOTEACTIONCOMPUSER varchar(50),
@QUOTEACTIONUSERNAME varchar(50),
@QUOTEACTIONQUOTENO int,
@QUOTEACTIONQUOTEREL int,
@QUOTEACTIONITEMNO int,
@QUOTEACTIONITEMDESC varchar(50),
@QUOTEACTIONITEMSTATUS varchar(50),
@QUOTEACTIONITEMCOLOR varchar(50),
@QUOTEACTIONITEMPRICE float,
@QUOTEACTIONORDER varchar(50),
@QUOTEACTIONCOMMENT varchar(250)
)
AS
SET NOCOUNT OFF;
INSERT INTO dbo.QUOTEACTION(QUOTEACTIONID, QUOTEACTIONDATE,
QUOTEACTIONLOGINNAME, QUOTEACTIONIP, QUOTEACTIONCOMPNAME,
QUOTEACTIONCOMPUSER, QUOTEACTIONUSERNAME, QUOTEACTIONQUOTENO,
QUOTEACTIONQUOTEREL, QUOTEACTIONITEMNO, QUOTEACTIONITEMDESC,
QUOTEACTIONITEMSTATUS, QUOTEACTIONITEMCOLOR, QUOTEACTIONITEMPRICE,
QUOTEACTIONORDER, QUOTEACTIONCOMMENT) VALUES (@QUOTEACTIONID,
@QUOTEACTIONDATE, @QUOTEACTIONLOGINNAME, @QUOTEACTIONIP,
@QUOTEACTIONCOMPNAME, @QUOTEACTIONCOMPUSER, @QUOTEACTIONUSERNAME,
@QUOTEACTIONQUOTENO, @QUOTEACTIONQUOTEREL, @QUOTEACTIONITEMNO,
@QUOTEACTIONITEMDESC, @QUOTEACTIONITEMSTATUS, @QUOTEACTIONITEMCOLOR,
@QUOTEACTIONITEMPRICE, @QUOTEACTIONORDER, @QUOTEACTIONCOMMENT);
SELECT QUOTEACTIONID, QUOTEACTIONDATE, QUOTEACTIONLOGINNAME,
QUOTEACTIONIP, QUOTEACTIONCOMPNAME, QUOTEACTIONCOMPUSER,
QUOTEACTIONUSERNAME, QUOTEACTIONQUOTENO, QUOTEACTIONQUOTEREL,
QUOTEACTIONITEMNO, QUOTEACTIONITEMDESC, QUOTEACTIONITEMSTATUS,
QUOTEACTIONITEMCOLOR, QUOTEACTIONITEMPRICE, QUOTEACTIONORDER,
QUOTEACTIONCOMMENT FROM dbo.QUOTEACTION WHERE (QUOTEACTIONID =
@QUOTEACTIONID) GO

Modify QuoteActionID column to have identity and modify the sql stmt as
follows

INSERT INTO dbo.QUOTEACTION(QUOTEACTIONDATE,
QUOTEACTIONLOGINNAME, QUOTEACTIONIP, QUOTEACTIONCOMPNAME,
QUOTEACTIONCOMPUSER, QUOTEACTIONUSERNAME, QUOTEACTIONQUOTENO,
QUOTEACTIONQUOTEREL, QUOTEACTIONITEMNO, QUOTEACTIONITEMDESC,
QUOTEACTIONITEMSTATUS, QUOTEACTIONITEMCOLOR, QUOTEACTIONITEMPRICE,
QUOTEACTIONORDER, QUOTEACTIONCOMMENT) VALUES (@QUOTEACTIONID,
@QUOTEACTIONDATE, @QUOTEACTIONLOGINNAME, @QUOTEACTIONIP,
@QUOTEACTIONCOMPNAME, @QUOTEACTIONCOMPUSER, @QUOTEACTIONUSERNAME,
@QUOTEACTIONQUOTENO, @QUOTEACTIONQUOTEREL, @QUOTEACTIONITEMNO,
@QUOTEACTIONITEMDESC, @QUOTEACTIONITEMSTATUS, @QUOTEACTIONITEMCOLOR,
@QUOTEACTIONITEMPRICE, @QUOTEACTIONORDER, @QUOTEACTIONCOMMENT);
SELECT QUOTEACTIONDATE, QUOTEACTIONLOGINNAME,
QUOTEACTIONIP, QUOTEACTIONCOMPNAME, QUOTEACTIONCOMPUSER,
QUOTEACTIONUSERNAME, QUOTEACTIONQUOTENO, QUOTEACTIONQUOTEREL,
QUOTEACTIONITEMNO, QUOTEACTIONITEMDESC, QUOTEACTIONITEMSTATUS,
QUOTEACTIONITEMCOLOR, QUOTEACTIONITEMPRICE, QUOTEACTIONORDER,
QUOTEACTIONCOMMENT FROM dbo.QUOTEACTION WHERE (QUOTEACTIONID =
@QUOTEACTIONID)

Try,

CREATE PROCEDURE databaseexample.QuoteActionInsertCommand
(
@QUOTEACTIONDATE datetime,
@QUOTEACTIONLOGINNAME varchar(50),
@QUOTEACTIONIP varchar(50),
@QUOTEACTIONCOMPNAME varchar(50),
@QUOTEACTIONCOMPUSER varchar(50),
@QUOTEACTIONUSERNAME varchar(50),
@QUOTEACTIONQUOTENO int,
@QUOTEACTIONQUOTEREL int,
@QUOTEACTIONITEMNO int,
@QUOTEACTIONITEMDESC varchar(50),
@QUOTEACTIONITEMSTATUS varchar(50),
@QUOTEACTIONITEMCOLOR varchar(50),
@QUOTEACTIONITEMPRICE float,
@QUOTEACTIONORDER varchar(50),
@QUOTEACTIONCOMMENT varchar(250),
@QUOTEACTIONID int output
)
AS
SET NOCOUNT OFF;
INSERT INTO dbo.QUOTEACTION(QUOTEACTIONID, QUOTEACTIONDATE,
QUOTEACTIONLOGINNAME, QUOTEACTIONIP, QUOTEACTIONCOMPNAME,
QUOTEACTIONCOMPUSER, QUOTEACTIONUSERNAME, QUOTEACTIONQUOTENO,
QUOTEACTIONQUOTEREL, QUOTEACTIONITEMNO, QUOTEACTIONITEMDESC,
QUOTEACTIONITEMSTATUS, QUOTEACTIONITEMCOLOR, QUOTEACTIONITEMPRICE,
QUOTEACTIONORDER, QUOTEACTIONCOMMENT) VALUES (@QUOTEACTIONID,
@QUOTEACTIONDATE, @QUOTEACTIONLOGINNAME, @QUOTEACTIONIP,
@QUOTEACTIONCOMPNAME, @QUOTEACTIONCOMPUSER, @QUOTEACTIONUSERNAME,
@QUOTEACTIONQUOTENO, @QUOTEACTIONQUOTEREL, @QUOTEACTIONITEMNO,
@QUOTEACTIONITEMDESC, @QUOTEACTIONITEMSTATUS, @QUOTEACTIONITEMCOLOR,
@QUOTEACTIONITEMPRICE, @QUOTEACTIONORDER, @QUOTEACTIONCOMMENT);

if @@error = 0
begin

set @quoteactionid = scope_identity( )

SELECT
QUOTEACTIONID, QUOTEACTIONDATE, QUOTEACTIONLOGINNAME,
QUOTEACTIONIP, QUOTEACTIONCOMPNAME, QUOTEACTIONCOMPUSER,
QUOTEACTIONUSERNAME, QUOTEACTIONQUOTENO, QUOTEACTIONQUOTEREL,
QUOTEACTIONITEMNO, QUOTEACTIONITEMDESC, QUOTEACTIONITEMSTATUS,
QUOTEACTIONITEMCOLOR, QUOTEACTIONITEMPRICE, QUOTEACTIONORDER,
QUOTEACTIONCOMMENT
FROM dbo.QUOTEACTION
WHERE (QUOTEACTIONID = @QUOTEACTIONID)

return @@error
end
else
return 1
GO

Let sql server to generate the identity value and add an output parameter to
the command parameters collection to get the value of the identity.


AMB

"scorpion53061" wrote:

Correction,

I forgot to take off column QUOTEACTIONID from the insert.

CREATE PROCEDURE databaseexample.QuoteActionInsertCommand
(
@QUOTEACTIONDATE datetime,
@QUOTEACTIONLOGINNAME varchar(50),
@QUOTEACTIONIP varchar(50),
@QUOTEACTIONCOMPNAME varchar(50),
@QUOTEACTIONCOMPUSER varchar(50),
@QUOTEACTIONUSERNAME varchar(50),
@QUOTEACTIONQUOTENO int,
@QUOTEACTIONQUOTEREL int,
@QUOTEACTIONITEMNO int,
@QUOTEACTIONITEMDESC varchar(50),
@QUOTEACTIONITEMSTATUS varchar(50),
@QUOTEACTIONITEMCOLOR varchar(50),
@QUOTEACTIONITEMPRICE float,
@QUOTEACTIONORDER varchar(50),
@QUOTEACTIONCOMMENT varchar(250),
@QUOTEACTIONID int output
)
AS
SET NOCOUNT OFF;
INSERT INTO dbo.QUOTEACTION(QUOTEACTIONDATE,
QUOTEACTIONLOGINNAME, QUOTEACTIONIP, QUOTEACTIONCOMPNAME,
QUOTEACTIONCOMPUSER, QUOTEACTIONUSERNAME, QUOTEACTIONQUOTENO,
QUOTEACTIONQUOTEREL, QUOTEACTIONITEMNO, QUOTEACTIONITEMDESC,
QUOTEACTIONITEMSTATUS, QUOTEACTIONITEMCOLOR, QUOTEACTIONITEMPRICE,
QUOTEACTIONORDER, QUOTEACTIONCOMMENT) VALUES (@QUOTEACTIONDATE,
@QUOTEACTIONLOGINNAME, @QUOTEACTIONIP,
@QUOTEACTIONCOMPNAME, @QUOTEACTIONCOMPUSER, @QUOTEACTIONUSERNAME,
@QUOTEACTIONQUOTENO, @QUOTEACTIONQUOTEREL, @QUOTEACTIONITEMNO,
@QUOTEACTIONITEMDESC, @QUOTEACTIONITEMSTATUS, @QUOTEACTIONITEMCOLOR,
@QUOTEACTIONITEMPRICE, @QUOTEACTIONORDER, @QUOTEACTIONCOMMENT);

if @@error = 0
begin

set @quoteactionid = scope_identity( )

SELECT
QUOTEACTIONID, QUOTEACTIONDATE, QUOTEACTIONLOGINNAME,
QUOTEACTIONIP, QUOTEACTIONCOMPNAME, QUOTEACTIONCOMPUSER,
QUOTEACTIONUSERNAME, QUOTEACTIONQUOTENO, QUOTEACTIONQUOTEREL,
QUOTEACTIONITEMNO, QUOTEACTIONITEMDESC, QUOTEACTIONITEMSTATUS,
QUOTEACTIONITEMCOLOR, QUOTEACTIONITEMPRICE, QUOTEACTIONORDER,
QUOTEACTIONCOMMENT
FROM dbo.QUOTEACTION
WHERE (QUOTEACTIONID = @QUOTEACTIONID)

return @@error
end
else
return 1
GO


AMB

"Alejandro Mesa" wrote:

Thank you Alejandro for your help.

I tried your proc and it looks like I put the cart before the horse at
least it seems so what I was reading.

I added a "identity column" with this statement and made it the primary
key...


alter table QUOTEACTION
add record_id numeric(5,0) identity not null

Then I adjusted the proc as follows. When I run this however with this
query analyzer statement it tells me that "Explicit value must be
specified for identity column in table 'QUOTEACTION' when
IDENTITY_INSERT is set to ON"

Here is the query analyzer for the insert followed by the stored proc
that was generated. By the way do you know by chance how .NET handles
this or even needs to handle this? It balked on the delete statement and
the update statement. I wonder if it has to do with the insert.

QuoteActionInsert '1/1/2000',
'loginname',
'ipaddress',
'computer name',
'computer user',
'login name',
1,
1,
1,
'ITEM DESCRIPTION',
'ITEM STATUS',
'ITEM COLOR',
'1.21',
'ORDER',
'COMMENT'


CREATE PROCEDURE kjmsolutions.QuoteActionInsert
(
@QUOTEACTIONDATE datetime,
@QUOTEACTIONLOGINNAME varchar(50),
@QUOTEACTIONIP varchar(50),
@QUOTEACTIONCOMPNAME varchar(50),
@QUOTEACTIONCOMPUSER varchar(50),
@QUOTEACTIONUSERNAME varchar(50),
@QUOTEACTIONQUOTENO int,
@QUOTEACTIONQUOTEREL int,
@QUOTEACTIONITEMNO int,
@QUOTEACTIONITEMDESC varchar(50),
@QUOTEACTIONITEMSTATUS varchar(50),
@QUOTEACTIONITEMCOLOR varchar(50),
@QUOTEACTIONITEMPRICE float,
@QUOTEACTIONORDER varchar(50),
@QUOTEACTIONCOMMENT varchar(250)
)
AS
SET NOCOUNT OFF;
INSERT INTO dbo.QUOTEACTION(QUOTEACTIONDATE, QUOTEACTIONLOGINNAME,
QUOTEACTIONIP, QUOTEACTIONCOMPNAME, QUOTEACTIONCOMPUSER,
QUOTEACTIONUSERNAME, QUOTEACTIONQUOTENO, QUOTEACTIONQUOTEREL,
QUOTEACTIONITEMNO, QUOTEACTIONITEMDESC, QUOTEACTIONITEMSTATUS,
QUOTEACTIONITEMCOLOR, QUOTEACTIONITEMPRICE, QUOTEACTIONORDER,
QUOTEACTIONCOMMENT) VALUES (@QUOTEACTIONDATE, @QUOTEACTIONLOGINNAME,
@QUOTEACTIONIP, @QUOTEACTIONCOMPNAME, @QUOTEACTIONCOMPUSER,
@QUOTEACTIONUSERNAME, @QUOTEACTIONQUOTENO, @QUOTEACTIONQUOTEREL,
@QUOTEACTIONITEMNO, @QUOTEACTIONITEMDESC, @QUOTEACTIONITEMSTATUS,
@QUOTEACTIONITEMCOLOR, @QUOTEACTIONITEMPRICE, @QUOTEACTIONORDER,
@QUOTEACTIONCOMMENT);
SELECT record_id, QUOTEACTIONDATE, QUOTEACTIONLOGINNAME, QUOTEACTIONIP,
QUOTEACTIONCOMPNAME, QUOTEACTIONCOMPUSER, QUOTEACTIONUSERNAME,
QUOTEACTIONQUOTENO, QUOTEACTIONQUOTEREL, QUOTEACTIONITEMNO,
QUOTEACTIONITEMDESC, QUOTEACTIONITEMSTATUS, QUOTEACTIONITEMCOLOR,
QUOTEACTIONITEMPRICE, QUOTEACTIONORDER, QUOTEACTIONCOMMENT FROM
dbo.QUOTEACTION WHERE (record_id = @@IDENTITY)
GO



"Alejandro Mesa" <AlejandroMesa (AT) discussions (DOT) microsoft.com> wrote in
message news:AlejandroMesa (AT) discussions (DOT) microsoft.com: