Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2007-10-17
10
Medium Priority
?
1,119 Views
Last Modified: 2013-11-07
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
0
Comment
Question by:Beish1
  • 5
  • 3
  • 2
10 Comments
 
LVL 9

Expert Comment

by:divinewind80
ID: 20097659
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
 

Author Comment

by:Beish1
ID: 20097697
Problem is the Contribid2 field has unique index so need to adjust its value before the record is inserted for the first time
0
 
LVL 26

Expert Comment

by:Rejojohny
ID: 20098445
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 26

Expert Comment

by:Rejojohny
ID: 20098461
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
 
LVL 26

Expert Comment

by:Rejojohny
ID: 20098473
>>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
 

Author Comment

by:Beish1
ID: 20099323
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
 
LVL 9

Accepted Solution

by:
divinewind80 earned 750 total points
ID: 20100582
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
 
LVL 26

Expert Comment

by:Rejojohny
ID: 20100604
>>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
 

Author Comment

by:Beish1
ID: 20107201
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
 
LVL 26

Assisted Solution

by:Rejojohny
Rejojohny earned 750 total points
ID: 20108551
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
THe viewer will learn how to use NetBeans IDE 8.0 for Windows to perform CRUD operations on a MySql database.
The viewer will learn how to use and create new code templates in NetBeans IDE 8.0 for Windows.
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question