PrimaryKey consisting of two PKs with autoincrement?

I want to create a table where I can make a history of a file, while it's
processed by my application. The file will change and even get a new file, so
I created a Document table and a Journal table. The Journal table contains
the time, the event that did occure and a FK to the Document table. One line
represents a specific document (one unique file).
I added two primary keys to this table, one for the transaction and one for
the document itself. So always when a file changes (gets a new file) a new
line will be inserted with a new document key, but the same transaction key.
When a completly new file gets processed a new transaction should start and
the document key will again begin by zero.
I just would like to new if it is possible to make the document key
autoincremental, so that I don't have to worry about it in the application. I
don't really think that the transaction key can be made autoincremental, as
the database does not know, if it is a completly new file or just a new
version of an older file.
But could there be any problems with making the document key
autoincremental? I just want it to be zero again, every time a new
transaction key starts, but I think the database will just count on.
Hi
Take a look at an INDETITY property that is a good choice for artificial
keys.
"Stampede" <Stampede@.discussions.microsoft.com> wrote in message
news:F835C787-5BC8-4835-A31D-D38C4EA581A1@.microsoft.com...
> I want to create a table where I can make a history of a file, while it's
> processed by my application. The file will change and even get a new file,
so
> I created a Document table and a Journal table. The Journal table contains
> the time, the event that did occure and a FK to the Document table. One
line
> represents a specific document (one unique file).
> I added two primary keys to this table, one for the transaction and one
for
> the document itself. So always when a file changes (gets a new file) a new
> line will be inserted with a new document key, but the same transaction
key.
> When a completly new file gets processed a new transaction should start
and
> the document key will again begin by zero.
> I just would like to new if it is possible to make the document key
> autoincremental, so that I don't have to worry about it in the
application. I
> don't really think that the transaction key can be made autoincremental,
as
> the database does not know, if it is a completly new file or just a new
> version of an older file.
> But could there be any problems with making the document key
> autoincremental? I just want it to be zero again, every time a new
> transaction key starts, but I think the database will just count on.

PrimaryKey consisting of two PKs with autoincrement?

I want to create a table where I can make a history of a file, while it's
processed by my application. The file will change and even get a new file, so
I created a Document table and a Journal table. The Journal table contains
the time, the event that did occure and a FK to the Document table. One line
represents a specific document (one unique file).
I added two primary keys to this table, one for the transaction and one for
the document itself. So always when a file changes (gets a new file) a new
line will be inserted with a new document key, but the same transaction key.
When a completly new file gets processed a new transaction should start and
the document key will again begin by zero.
I just would like to new if it is possible to make the document key
autoincremental, so that I don't have to worry about it in the application. I
don't really think that the transaction key can be made autoincremental, as
the database does not know, if it is a completly new file or just a new
version of an older file.
But could there be any problems with making the document key
autoincremental? I just want it to be zero again, every time a new
transaction key starts, but I think the database will just count on.Hi
Take a look at an INDETITY property that is a good choice for artificial
keys.
"Stampede" <Stampede@.discussions.microsoft.com> wrote in message
news:F835C787-5BC8-4835-A31D-D38C4EA581A1@.microsoft.com...
> I want to create a table where I can make a history of a file, while it's
> processed by my application. The file will change and even get a new file,
so
> I created a Document table and a Journal table. The Journal table contains
> the time, the event that did occure and a FK to the Document table. One
line
> represents a specific document (one unique file).
> I added two primary keys to this table, one for the transaction and one
for
> the document itself. So always when a file changes (gets a new file) a new
> line will be inserted with a new document key, but the same transaction
key.
> When a completly new file gets processed a new transaction should start
and
> the document key will again begin by zero.
> I just would like to new if it is possible to make the document key
> autoincremental, so that I don't have to worry about it in the
application. I
> don't really think that the transaction key can be made autoincremental,
as
> the database does not know, if it is a completly new file or just a new
> version of an older file.
> But could there be any problems with making the document key
> autoincremental? I just want it to be zero again, every time a new
> transaction key starts, but I think the database will just count on.

PrimaryKey consisting of two PKs with autoincrement?

I want to create a table where I can make a history of a file, while it's
processed by my application. The file will change and even get a new file, s
o
I created a Document table and a Journal table. The Journal table contains
the time, the event that did occure and a FK to the Document table. One line
represents a specific document (one unique file).
I added two primary keys to this table, one for the transaction and one for
the document itself. So always when a file changes (gets a new file) a new
line will be inserted with a new document key, but the same transaction key.
When a completly new file gets processed a new transaction should start and
the document key will again begin by zero.
I just would like to new if it is possible to make the document key
autoincremental, so that I don't have to worry about it in the application.
I
don't really think that the transaction key can be made autoincremental, as
the database does not know, if it is a completly new file or just a new
version of an older file.
But could there be any problems with making the document key
autoincremental? I just want it to be zero again, every time a new
transaction key starts, but I think the database will just count on.Hi
Take a look at an INDETITY property that is a good choice for artificial
keys.
"Stampede" <Stampede@.discussions.microsoft.com> wrote in message
news:F835C787-5BC8-4835-A31D-D38C4EA581A1@.microsoft.com...
> I want to create a table where I can make a history of a file, while it's
> processed by my application. The file will change and even get a new file,
so
> I created a Document table and a Journal table. The Journal table contains
> the time, the event that did occure and a FK to the Document table. One
line
> represents a specific document (one unique file).
> I added two primary keys to this table, one for the transaction and one
for
> the document itself. So always when a file changes (gets a new file) a new
> line will be inserted with a new document key, but the same transaction
key.
> When a completly new file gets processed a new transaction should start
and
> the document key will again begin by zero.
> I just would like to new if it is possible to make the document key
> autoincremental, so that I don't have to worry about it in the
application. I
> don't really think that the transaction key can be made autoincremental,
as
> the database does not know, if it is a completly new file or just a new
> version of an older file.
> But could there be any problems with making the document key
> autoincremental? I just want it to be zero again, every time a new
> transaction key starts, but I think the database will just count on.

Primary key constraint issues with merge replication

I have merge replication set up with a publisher and two subscribers. All of my primary key fields are set for autoincrement (not for replication). The articles in the publication are set to force the subscriber to use a range for generating unique prim
ary keys. Things seem to go well for a while then my application starts throwing primary key constraint errors on both clients. Any suggestions?
Yep. Disable the auto-identity range and do it yourself manually.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com

Primary Key Constraint errors when replicating

I have merge replication set up with a publisher and two subscribers. All of my primary key fields are set for autoincrement (not for replication). The articles in the publication are set to force the subscriber to use a range for generating unique prim
ary keys. Things seem to go well for a while then my application starts throwing primary key constraint errors on both clients. I was under the impression that the range option was designed to solve this type of issue. If you have a suggestion, please
be as specific as possible. THANKS!
Aaron,
the check constraints should preclude duplicate primary key values being
entered. When you say Primary Key constraint errors, are these from
duplicates, or from the range having been used up? Could you check the
publisher and each subscriber's check constraints and post up what you find,
as well as the text of the error message.
TIA,
Paul Ibison
|||Turn off the auto identity management and manage the ranges yourself.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||How does one go about managing auto identity ranges without using sql
server?
Thanks!
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||The errors are from duplicates. The errors occur on the clients with
subscriptions. It seems that it runs fine for a while then the
duplicate key errors start popping up. It seems like the ranges quit
getting assigned. I'm really not sure. Several people have mentioned
that I should assign the ranges myself, How would I go about doing that?
Aaron R. Davis
Development Manager
MDTablet, LLC
www.mdtablet.com
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||http://www.mssqlserver.com/replication
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||Mike,
Thanks for the article. I read the article and you mention that with
SQL Server 2000 you can set these ranges when setting up replication,
which I am doing but still seem to run into problems. How,
specifically, do I manually set a range for each subscriber.
Thanks,
Aaron R. Davis
Development Manager
MDTablet, LLC
www.mdtablet.com
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Go back to that section. There is a specific article in there which details
one approach to manually managing ranges.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||Mike,
I understand the method in which you recommend assigning ranges. In
that article, you mention that if you are using sql server 2000 this can
be done automatically through the article properties, which I am doing.
My problem is that we periodically have updates to our database which
forces us to drop replication, make the changes, and setup replication
again. In those instances we start experiencing primary key
errors(duplicates being assigned) and I believe it stems from the auto
identity range assignment being reseeded. What can I do to solve this.
Thanks,
Aaron R. Davis
Development Manager
MDTablet, LLC
www.mdtablet.com
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

primary key autoincrement question.

I have a table in my sqlserver 2000 that has a field IDNO. i want this
field to be my primary key. however i don't want this field to use the
autoincrement feature. when i access this table from vb.net and try to
add a record this field is autoincrementing. how can i disable the
autoincrement of this field yet serves this as my primary key?

thanks in advancego to design, select your id make shure that there is a key symbol next to
it (if not right click and set primary key)
this makes it the primary key the identity is something else -->
autoincrementing set it to false and there you go :)

hope it helps

eric

"jaYPee" <hijaypee@.yahoo.com> wrote in message
news:8vl470phkea0fueivehemm3h882ousjg8f@.4ax.com...
> I have a table in my sqlserver 2000 that has a field IDNO. i want this
> field to be my primary key. however i don't want this field to use the
> autoincrement feature. when i access this table from vb.net and try to
> add a record this field is autoincrementing. how can i disable the
> autoincrement of this field yet serves this as my primary key?
> thanks in advance|||Thank you for the reply. however i can't find an autoincrementing
properties under column properties in order to set it to false.

under column properties of IDNO field i have i only see this
properties:

Description
Default Value
Precision
Scale
Identity
Identity Seed
Identity Increment
Is RowGuid
Formula
Collation

i presume before and until now that i have to set the identity to "no"
but still in my vb.net app when i add record the IDNO field still
increment to the last value + 1.

don't know where can i set the autoincrement to "false"

thanks again

On Tue, 6 Apr 2004 09:41:21 +0200, "EricJ"
<ericReMoVe@.ThiSbitconsult.be.RE> wrote:

>go to design, select your id make shure that there is a key symbol next to
>it (if not right click and set primary key)
>this makes it the primary key the identity is something else -->
>autoincrementing set it to false and there you go :)
>hope it helps
>eric
>"jaYPee" <hijaypee@.yahoo.com> wrote in message
>news:8vl470phkea0fueivehemm3h882ousjg8f@.4ax.com...
>> I have a table in my sqlserver 2000 that has a field IDNO. i want this
>> field to be my primary key. however i don't want this field to use the
>> autoincrement feature. when i access this table from vb.net and try to
>> add a record this field is autoincrementing. how can i disable the
>> autoincrement of this field yet serves this as my primary key?
>>
>> thanks in advance|||these are the ones you are after

> Identity
> Identity Seed
> Identity Increment

yust set the identity to false the rest will follow :)

eric

"jaYPee" <hijaypee@.yahoo.com> wrote in message
news:aos470d3p2gup57707rmeib2907453sn69@.4ax.com...
> Thank you for the reply. however i can't find an autoincrementing
> properties under column properties in order to set it to false.
> under column properties of IDNO field i have i only see this
> properties:
> Description
> Default Value
> Precision
> Scale
> Identity
> Identity Seed
> Identity Increment
> Is RowGuid
> Formula
> Collation
> i presume before and until now that i have to set the identity to "no"
> but still in my vb.net app when i add record the IDNO field still
> increment to the last value + 1.
> don't know where can i set the autoincrement to "false"
> thanks again
> On Tue, 6 Apr 2004 09:41:21 +0200, "EricJ"
> <ericReMoVe@.ThiSbitconsult.be.RE> wrote:
> >go to design, select your id make shure that there is a key symbol next
to
> >it (if not right click and set primary key)
> >this makes it the primary key the identity is something else -->
> >autoincrementing set it to false and there you go :)
> >hope it helps
> >eric
> >"jaYPee" <hijaypee@.yahoo.com> wrote in message
> >news:8vl470phkea0fueivehemm3h882ousjg8f@.4ax.com...
> >> I have a table in my sqlserver 2000 that has a field IDNO. i want this
> >> field to be my primary key. however i don't want this field to use the
> >> autoincrement feature. when i access this table from vb.net and try to
> >> add a record this field is autoincrementing. how can i disable the
> >> autoincrement of this field yet serves this as my primary key?
> >>
> >> thanks in advance|||>> I have a table in my SQL Server 2000 that has a field [sic] IDNO. I
want this field [sic] to be my primary key. <<

You need to read a book on SQL and RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access
or ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless.

What does this table model in the real world? Look at the real world
and ask what the key is. It cannot ever be the internal state of the
hardware in which your model resides. You are still thinking that
there are rcord numbers, like a sequential file system in an RDBMS --
you even use the terminology of a sequential file system.

This is totally wrong. There is no "Magic, Universal
one-size-fits-all" way to get a key. Building a data model is work.

Primary Key AutoIncrement

(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) GOModify 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:

> (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
>|||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:
> 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:
>|||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@.discussions.microsoft.com> wrote in
message news:AlejandroMesa@.discussions.microsoft.com:
> 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:
>