Auto Sequence

[Arg81]

Hi guys,

I am developing a VB.Net frontend to an SQL 2000 backend database.

I have had no problems with data binding etc, but in my 2 related tables, I have a field called Sequence. In SQL I have set this field to int, and not to auto-increment.

Table1 has a field DWRNo. Table2 also has DWRNo.
DWR001 in table1 could have 5 records matching in table2.

I need the program to auto-sequence the second field in table2, DWR_Sequence.
I.E. when a user adds a new record to the datagrid (master-child setup), it looks at what the DWRNo is, then at what the highest value of DWR_Sequence is matching that DWRNo, and adds 1 to it for the new value, and inserts this value.

I've got a book (Murachs VB.Net database programming) that discusses it, but gives no example on how to do it. Therefore I know it can be done, but I haven't found a clear consise way of doing it.

If anyone can give me some pointers I would be very, very grateful.

Kind Regards,
Luke Argent

 

[DavidT_macktool]

Go to the last record of table2:

Me.BindingContext(DataSet2, "Table2").Position = Me.BindingContext(DataSet2, "Table2").Count - 1

set the new drawing number to the last records drawing number + 1.

I use ComponentOne TrueDBGrids so the code may be a little different.

Dim Table2 As DataTable
Table2 = DataSet2.Tables("Table2")
Dim drNewRow As DataRow
drNewRow = Table2.NewRow()
'set the drawing number to the current drawing number + 1
drNewRow("DWRNo") = Grid2.Columns("DWRNo").Value + 1 'This line could be different
'set the other fields also
Table2.Rows.Add(drNewRow)


hope this helps,

 

[Arg81]

Hi David,

Thanks for your reply.

Now I've got a few other bits out of the way I can sit down to work this out.

I had a look at ComponentOne but that's not the way I would like to do it.

I understand what you are saying, but where would I put this code?

Basically on the form is the DWRSequence datagrid. this displays only a few items, but the user must click on a button to open a new form with all the information to be inserted into the DWRSequence table.
At the point the user clicks add, that is where I need the program to generate the next sequence number, so I assume I add this code to the add button on the form?

Thanks for your help,
Regards
Luke