Last post Jul 02, 2014 06:16 PM by aspkermit
Jul 01, 2014 06:54 PM|aspkermit|LINK
Using VS2013, .NET framework 4.5, C# and DB2 database.
This is inserting into a DB2 table with a PK field that cannot be defined as a auto-incrementing, identity_seed column with a unique constraint (don't ask me why). So, I will need to select the MAX transaction_id and would like to be able to insert my own
sequential, auto-incrementing number. If someone could help me with this I would greatly appreciate it. I've done some research on this and have come up with some variations of something like this: for (int i = 0; i < result.Count(); i++), but am not sure
how to implement this in my existing code:
query = new StringBuilder();
query.AppendLine("INSERT INTO " + "TEST_TABLE");
query.AppendLine(" (TRANSACTION_ID, TEST_CODE, TEST_CODE_1, TEST_CODE_2, USER_ID, TIME_STAMP) ");
query.AppendLine("" + items.TRANSACTION_ID + ""); //TO DO: self incrementing loop
query.AppendLine("," + items.TEST_CODE + "");
query.AppendLine(", " + items.TEST_CODE_1 + "");
query.AppendLine(", '" + items.TEST_CODE_2 + "'");
query.AppendLine(", '" + items.USER_ID + "'");
query.AppendLine(", '" + items.TIME_STAMP + "'");
catch (Exception ex)
Any help with this or a better solution would be greatly appreciated.
Jul 01, 2014 06:57 PM|MetalAsp.Net|LINK
For that "identity" column, use SELECT MAX(column) +1 FROM table and that will be the value you insert.
Jul 01, 2014 10:33 PM|aspkermit|LINK
Jul 01, 2014 11:09 PM|aspkermit|LINK
May I please ask one more question? Can I do something similar (I'm doing this which works for the MAX id):
MAX(transaction_id) + 1 FROM TEST_TABLE)");
for selecting the most recently inserted record for a particular value? For example, I
have two records that are dups except for a flag indicator:
id | value_1 | value_2 | value_3 | flag
1 | 123 | abc | T1Ad5 | Y
2 | 123 | abc | T1Ad5 | N
3 | 456 | def | T1AdZ | Y
4 | 456 | def | T1AdZ | N
So for 123, abc, T1Ad5 I need to return the record with id 2 and flag = N
and for 456, def, T1AdZ I need to return the record with id 4 and flag = N
Hope this makes sense.
Jul 02, 2014 04:51 AM|MetalAsp.Net|LINK
You can add a where clause, I think. Like: WHERE flag = 'N'
Edit: As long as the 'N' record id will always be greater than the 'Y' record.
Jul 02, 2014 01:50 PM|aspkermit|LINK
No, unfortunately, this will not be the case because they can select Y or N as many times as they want causing an insert each time. Any other ideas?
Jul 02, 2014 02:00 PM|MetalAsp.Net|LINK
One more idea. Add a datetime column to your table. Set it to default to GETDATE(). You can use this field to determine the newer record(s).
Jul 02, 2014 06:16 PM|aspkermit|LINK
Brilliant, thank you again! Much appreciated. --Buster