Formview Insert - set second field equal to the auto-generated primary key field value

I'm using Formview to create a SQL Server database record. There is a primary key field called ContribID which is an autoincrement field - ie identity specification=yes. There is a second field called ContribID2 which I would like to set = the automatically generated value of the ContribID, when I click the Submit button to insert the new record.
Is this possible? I code in VB.net.

Ken Laing
Beish1Asked:
Who is Participating?
 
divinewind80Commented:
If you are wanting to set the value of the second column equal to the unique ID of the first, either my or Rejojohny's solution should work.

My solution operates under the assumption that you do not have many records being inserted in a small span of time.  If you do, Rejojohny's solution is better as it tracks the ID specifically to the record you JUST inserted.  

Is this what you are trying to do?
0
 
divinewind80Commented:
Yes, it is possible.  One way to do it, assuming there are not lots of records being inserted at once, is to go ahead with your usual insert statement then run a select statement on the table as follows:

"SELECT TOP 1 CONTRIBID FROM TABLE ORDER BY CONTRIBID DESC"

This gets your most recent entry's ID.  You can then run an Update statement on the record like so:

"UPDATE TABLE SET CONTIBID2 = " & your variable & " WHERE CONTRIBID = " & you variable & ""

Does that help?
0
 
Beish1Author Commented:
Problem is the Contribid2 field has unique index so need to adjust its value before the record is inserted for the first time
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
RejojohnyCommented:
just set the contribid2 also to autoincrement .. identity column .. i do not think there is a restriction in SQL server which restricts 2 indetity column .. or is there such a restriction .. i never had a reason to try that  ..

Rejo
0
 
RejojohnyCommented:
or write a trigger on your table .. insert trigger .. so after insert copy the value from the first column and update contribid2 ...

try this ..

CREATE TRIGGER tr_TriggerName
ON TableName
FOR INSERT
AS
update a
from TableName a, inserted b
set contribid2 = b.contribid1
where b.contribid1 = a.contribid1

go
0
 
RejojohnyCommented:
>>Problem is the Contribid2 field has unique index so need to adjust its value before the record is inserted for the first time
I am not sure I understand what you meant here .. if Contribid2 = Contribid1, then how can it not be unique since Contribid1 is the primary field and a identity column ..

Anyway, does setting a trigger help you?

Rejo
0
 
Beish1Author Commented:
Rejo - I need the Contribid2 code to be a string and unique when created. It will be changed later to an all alpha string value

Ken
0
 
RejojohnyCommented:
>>There is a second field called ContribID2 which I would like to set = the automatically generated value of the ContribID
This is what you asked and now you say
>>I need the Contribid2 code to be a string and unique when created
So I am still not sure whether I understand your problem .. you have a id which is identity and unique .. i thought you wanted to copy that value into another field in the same table .. if yes, will that not be unique too? its a string you want, just "cast" it .. with SQL server it will work without casting too ..

or is it that there is a specific format in wchich you want the value of Contribid2 must be and it must also contain the Contribid1 .. if yes, do mention what the format is ..
0
 
Beish1Author Commented:
I just want Contrib2 to be set to the same value which is automatically generated for Contrib1 before the new record is inserted. Contrib2 is a varchar field.
0
 
RejojohnyCommented:
Then please try the couple of suggestions I have made above and one of them will work for you .. I would recommend using the trigger ..

Rejo
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.