SQL Server and Primary Key

  • Thread starter Thread starter scorpion53061
  • Start date Start date
Ok, I wasn't sure if you were using AutoIncrement. On new value, it should
not have a null, that's weird. Did you try to DataTable.Compute?
 
I am sorry.

I am not sure what how you mean me to do that.

Does not the compute statement assume you actually have rows available to be
computed?
at this point you are creating a row to be added.
 
Right, use compute to find out what the highest number in the series is, and
then add it as the value.

So before I add my new row, I might have a top number 200, I check this, use
201 as the value for the new row.
scorpion53061 said:
I am sorry.

I am not sure what how you mean me to do that.

Does not the compute statement assume you actually have rows available to be
computed?
at this point you are creating a row to be added.

William Ryan eMVP said:
Ok, I wasn't sure if you were using AutoIncrement. On new value, it should
not have a null, that's weird. Did you try to DataTable.Compute?
reasoning
Click to expand...
order
to
find that out...

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?
Set the defaultValue of the column to something nonsensical...then just
reset it afterward.
message
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
Click to expand...
=
+1
Dscustaction1.Tables(0).Columns(0).AutoIncrementStep
Click to expand...
=
+1
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
message
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)

message
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?
in
message
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
Click to expand...
records
and
Click to expand...
Click to expand...
 
Bill,

This problem appears to be solved using this method.

I am a little concerned that there may be concurrency issues if users bump
into each other.

But I think this will work. Thank you much.

William Ryan eMVP said:
Right, use compute to find out what the highest number in the series is, and
then add it as the value.

So before I add my new row, I might have a top number 200, I check this, use
201 as the value for the new row.
scorpion53061 said:
I am sorry.

I am not sure what how you mean me to do that.

Does not the compute statement assume you actually have rows available
Click to expand...
to
be
computed?
at this point you are creating a row to be added.

order
Click to expand...
Dscustaction1.Tables(0).Columns(0).AutoIncrementSeed
=
Click to expand...
Dscustaction1.Tables(0).Columns(0).AutoIncrementStep
= wrote
in
Click to expand...
defined
Click to expand...
 
Trapping concurrency exceptions is actually fun. (I know I just exposed my
true nerdiness) but seriously, you can do alot with them. When one crops
up, drop me a line, I've handled it every way under the son.

Otherwise though the Compute solved it?
scorpion53061 said:
Bill,

This problem appears to be solved using this method.

I am a little concerned that there may be concurrency issues if users bump
into each other.

But I think this will work. Thank you much.

William Ryan eMVP said:
Right, use compute to find out what the highest number in the series is, and
then add it as the value.

So before I add my new row, I might have a top number 200, I check this, use
201 as the value for the new row.
to Dscustaction1.Tables(0).Columns(0).AutoIncrementSeed
Dscustaction1.Tables(0).Columns(0).AutoIncrementStep
Click to expand...
filter
is
Click to expand...
Click to expand...
 
Yes.

As long as no other records are written between the time you checked the
value and the time you write the record life is good.

However, I am looking forward to your true nerdiness being exposed and
learning this topic....... :)

William Ryan eMVP said:
Trapping concurrency exceptions is actually fun. (I know I just exposed my
true nerdiness) but seriously, you can do alot with them. When one crops
up, drop me a line, I've handled it every way under the son.

Otherwise though the Compute solved it?
Click to expand...
 
Back
Top