SQL Server and Primary Key

[scorpion53061]

I am attemtping to add a record to SQL Server.

I am trying to find an example of how to increment the primary key value
like we do in Access.

Can anyone point me to an example?

I tried the code from Access and it writes the first record as -1 and then
complains about distinct errors.

Thank You!!

Dscustaction1.Tables(0).Columns(0).AutoIncrement = True
'.IDColumn.AutoIncrement = True
Dscustaction1.Tables(0).Columns(0).AutoIncrementSeed = -1
Dscustaction1.Tables(0).Columns(0).AutoIncrementStep = -1

 

[William Ryan eMVP]

Scorp, set the Autoincrement property to true, then set the seed to 0 (or
wherever you want). You can set the AutoincrementValue to -1 for instance
or +1 or 2 or whatever.

It's a good idea if this is multiuser to set it to -1, (the Autoincremement
Value), that way you'll always have successive negative numbers. When you
call update, Access will see the numbers aren't valid and assign the next
available valid number to each field. If you use the Refresh dataset option
(if you are using the COnfiguration Wizard) or if you add a select
statement, then you'll get the new value back and if you have datarelations
defined, the child records will be updated accordingly.

If you use positive numbers, then you'll have to devise a scheme to make
sure you don't try submitting something that someone else is using, and if
we both start at 0 for instance, we are both probably going to have a 1 or 2
or 3. If we use negative numbers, the db will take care of this for us.

Let me know if you have any problem.s

Bill

 

[Cor Ligthert]

Hi Bill,

Are you now lucky

))

Cor

 

[scorpion53061]

?

Bill what does by mean by you getting lucky?

 

[scorpion53061]

The code as I had it worked fine for Access. However in updating a SQL
table is where the problem is. I am trying to add records and increment the
primary key on a SQL table...

 

[Cor Ligthert]

He has cryed two days in the language.vb group that you where not responding
anymore some days. You should see those long threads.

just for fun scorpion not serious

Cor

 

[William Ryan eMVP]

??

 

[William Ryan eMVP]

Are the schemas the same, in access the field was defined as Autoincrement ?
If so, then is the Sql Server field set up as an identity?

 

[scorpion53061]

No I created this table in SQL.

I am trying to, when adding rows, make the row take the next highest number
for the primary key value of the row (the field is called recordID)

 

[scorpion53061]

What thread.....this I got to see........

 

[William Ryan eMVP]

Cor:

Cmon, I think that's a bit of a misstatement. I responded to IAmIronMan.
Herfried asked me about Scorp and all I said was he's still around. My
whole post was directed to IAmIronMan b/c he showed back up and what he did
to Scorp was really uncool.

Crying isn't exactly the word I'd use to describe it ;-).

 

[Cor Ligthert]

Sometimes you have to overdo it Bill, otherwise there is no reaction

Cor

 

[scorpion53061]

I finally found the thread you were referring to.

Cor are you saying you like to stir up trouble in order to get people's
reactions?

 

[William Ryan eMVP]

If they'll increase incrementally, then the Autoincrement will work, set it
to positive one then. Otherwise, you can use
DataTable.Comput("Max(recordID)", "recordID>0") 'the filter is just some
bull that you konw wiill always be true. Increment the number that compute
returns and use it. You could incorporate this in RowChanging

 

[scorpion53061]

Okay this code does increment it.

However, on the first time it is attempted an exception is generated saying
the column does not allow nulls.

How would you change this to avoid that problem?

Dscustaction1.Tables(0).Columns(0).AutoIncrement = True
'.IDColumn.AutoIncrement = True
Dscustaction1.Tables(0).Columns(0).AutoIncrementSeed = +1
Dscustaction1.Tables(0).Columns(0).AutoIncrementStep = +1

 

[scorpion53061]

I spoke too soon.

After I shut the program down and started again it reported it was trying to
insert row (1) again giving a "unique error"

 

[William Ryan eMVP]

Set the defaultValue of the column to something nonsensical...then just
reset it afterward.

 

[William Ryan eMVP]

My last post was before I read this. Anyway, can you trap the value onClose
and see what it is? BTW, are you in a Autoincrement scenario or one where
you need to determine the max b/c it could be anything?

 

[scorpion53061]

Additional information: Cannot set AutoIncrement property for a column with
DefaultValue set.

is what happened when I set the default value.
I need to determine the max I think prior to adding the row. My reasoning is
that it cannot know what to "seed" if it does not know how many rows there
are.
But I hope you don't have to do a fill of the entire SQL table in order to
find that out...

 

[Cor Ligthert]

stir up trouble

Never, just prickling however this time Herfried had me very good.

And that I said and Bill did not understand why and than I started telling
why, without trying to stir up the trouble of course and than Armin did
again not understand etc etc.

Cor