DataColumn Expression Serial No Generating

yousuf42

Well-known member
Joined
Feb 12, 2006
Messages
101
Programming Experience
1-3
Dear All,

I have datatable with a Column as "Sno".

This DataTable is bound with DataGrid

Now I want to generate Row Numbers for 'Sno' DataColumn through expression


VB.NET:
Public Function Tempinvoice(ByVal DataTableName As String) As DataTable
dt = New DataTable(DataTableName)
 
Dim dcsno As DataColumn
 
dcsno = New DataColumn("Sno", GetType(Integer))
 
dcsno.Expression =  ' Here I need your help to finish
 
dt.Columns.Add(dcsno)
dt.Columns.Add(New DataColumn("ProdID", GetType(String)))
dt.Columns.Add(New DataColumn("Price", GetType(String)))
 
Return dt
End Function

can anyone help me please?
 
Last edited:

yousuf42

Well-known member
Joined
Feb 12, 2006
Messages
101
Programming Experience
1-3
Autoincrement also won't work because if we click DG every time it will give a new number. if we delete a row it won't update 'sno' column bound with DG.

any help pls..

Thanks in advance
 

Arg81

Well-known member
Joined
Mar 11, 2005
Messages
949
Location
Midlands, UK
Programming Experience
1-3
Now I want to generate Row Numbers for 'Sno' DataColumn through expression

I think I understand what you mean.

I have done something similar, although it uses my datagrid to work out the next incremental number. In my app, a WorkNumber can have multiple revisions, so I have my RevisionNumber being an incremental number, so that my SQL table allows

1 - 1
1 - 2
1 - 3
2 - 1
2 - 2
3 - 1
4 - 1
4 - 2

etc etc.

There may be a way of manipulating the code so that it can use something other than the grid.

I've attached the code I use (trimmed down to only necessary stuff), hopefully that may be able to help a bit.

VB.NET:
If Me.grdRevision.VisibleRows = 0 Then
                Dim tblRev As DataTable = Me.DsDevelopment.Tables("DWR_Revision")
                Dim DR As DataRow = tblRev.NewRow()
                DR("DWRNumber") = Me.lblTSDWR.Text
                DR("RevisionNumber") = 1
                tblRev.Rows.Add(DR)
                
            Else
                Me.grdRevision.MoveLast()
                Dim tblRev As DataTable = Me.DsDevelopment.Tables("DWR_Revision")
                Dim DR As DataRow = tblRev.NewRow()
                DR("DWRNumber") = Me.lblTSDWR.Text
                DR("RevisionNumber") = Me.grdRevision.Columns("RevisionNumber").Value + 1
                tblRev.Rows.Add(DR)
                                    
            End If


So, if the grid has no rows already, it will add the RevisionNumber as 1. When the 2nd row is added, it looks at me.grdRevison.Columns("RevisionNumber").Value , and adds 1 to it, thus becoming 2.
When the 3rd row is added, it does the same routine, and adds 1 to 2 so revisionNumber entered in that row is 3.

Notice I use Me.grdRevision.MoveLast() when a row exists - this is so it will go to the last row (the one with the highest rev. no) and use this RevisionNumber to add the 1 to.

Hope that's of some help.
 

yousuf42

Well-known member
Joined
Feb 12, 2006
Messages
101
Programming Experience
1-3
Thank you very much cjard, Arg81,

Sorry for misquoting.

Actually sno is "ItemNo"

DataTable has "ItemNo", "ProdId", "prodName" Columns and it is bound with a DataGrid.

Now the "ItemNo" is Row Number ( Rowindex 0 is ItemNo 1, Rowindex 1 isItemNo 2, and so on.

If we set "ItemNo" column to Autoincrement, every time you click a new row then exit from the row then click on new row it increases number without keeping its sequence. (for example click newrow 2 exit then click row 2 again it will increase 3,4 and on). Also another shortcoming of the Autoincrement is if I delete a row in middle of Datagrid it won't update "ItemNo" column of all rows in DG(I mean DataTable) with sequence.

So I think best thing to overcome this problem is to define "ItemNo" column with Expression. (Datatable.Rowindex +1 or somehow. Sorry I can't overcome this)

If you have any other idea to overcome this or through expression with code is most appreciated.

Thanks in advance
 

Arg81

Well-known member
Joined
Mar 11, 2005
Messages
949
Location
Midlands, UK
Programming Experience
1-3
If we set "ItemNo" column to Autoincrement, every time you click a new row then exit from the row then click on new row it increases number without keeping its sequence.

That's correct. It's the problem I was facing and I found the solution as I put in my last post, using the value in the grid.

However, if you're not using a grid to display your data then I'm not sure the best way to try and help.

Maybe someone else will, sorry for not being able to help much further.
 

yousuf42

Well-known member
Joined
Feb 12, 2006
Messages
101
Programming Experience
1-3
Thanks Arg81

I'm also using DataGrid only

You are .Net 3.0. I'm using VS2003 and .Net 1.1 only. So some of commands (like "Me.grdRevision.MoveLast()" and "grdRevision.VisibleRows" ) in your syntax unavailable to me and I'm unable to use your code.

I think DataColumn.Expression can do this But, sofar I could not do this.

Any help is most appreciated.
 

Arg81

Well-known member
Joined
Mar 11, 2005
Messages
949
Location
Midlands, UK
Programming Experience
1-3
I'm actually using VS2005 .net 2 - not sure why it's suddenly saying .net 3

The code worked perfectly in my old application, which was in 1.1

Instead of using IF grdRevision.VisibleRows = 0 THEN you can use IF dataSet.dataTable.rows.count = 0 THEN instead.

The moveLast is an important part though, it might of been slightly different in VS2003, what this does is allows the user to select any row in the grid, but when they add a new row, it goes to the last one before taking the value of RevisionNumber and adding 1 to it.
If you lock the grid, so that the last row is always the one selected and the user can't select any other row, then you don't need to worry about .moveLast :)
 

yousuf42

Well-known member
Joined
Feb 12, 2006
Messages
101
Programming Experience
1-3
Thank you very much Arg81.

Many of the methods in your code unavailable to me. I have tried all as here. but so far I have not succeeded.

Can any one help me please???:confused:


VB.NET:
Private Sub dataGrid1_Navigate(ByVal sender As Object, ByVal ne As System.Windows.Forms.NavigateEventArgs) Handles dataGrid1.Navigate
        If tblTempInvoice.Rows.Count = 0 Then
            Dim tblRev As DataTable = tblTempInvoice
            Dim DR As DataRow = tblRev.NewRow()
            DR(0) = Me.lblTSDWR.Text
            DR(0) = 1
            tblRev.Rows.Add(DR)

        Else
            'tblTempInvoice()
            'Me.grdRevision.MoveLast()
            Dim tblRev As DataTable = tblTempInvoice
            Dim DR As DataRow = tblRev.NewRow()
            DR(0) = Me.lblTSDWR.Text
            DR(0) = Convert.ToInt32(tblTempInvoice.Columns(0).ToString) + 1 '             
            tblRev.Rows.Add(DR)

        End If

Attached is a simple application in which we should implement this.:confused:

Any help is most appreciated.

Thanks in advance
 

Attachments

  • Test.zip
    9.2 KB · Views: 19
Last edited:

yousuf42

Well-known member
Joined
Feb 12, 2006
Messages
101
Programming Experience
1-3
For Next Error.

Dear All,

I have following error in For Next statement.

An unhandled exception of type 'System.ArgumentOutOfRangeException' occurred in system.windows.forms.dll

Additional information: Specified argument was out of the range of valid values.


VB.NET:
Private Sub Button3_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button3.Click

        Dim nrows As Integer = tblTempInvoice.Rows.Count
        Dim AutoNum(nrows) As Integer
        Dim i As Integer
 
        For i = 0 To UBound(AutoNum)
                     dataGrid1.Item(i, 0) = i + 1

        Next

 End Sub

can anyone figureout the problem please?

Thanks in advance
 

JohnH

VB.NET Forum Moderator
Staff member
Joined
Dec 17, 2005
Messages
15,617
Location
Norway
Programming Experience
10+
VB.NET:
Dim nrows As Integer = tblTempInvoice.Rows.Count
Dim AutoNum(nrows) As Integer
can anyone figureout the problem please?
The problem is that you have declared the array one item too long, Count is the number of items, arrays are zero-based so you have to declare it one item less than Count:
VB.NET:
Dim AutoNum(nrows - 1) As Integer
 

yousuf42

Well-known member
Joined
Feb 12, 2006
Messages
101
Programming Experience
1-3
Thank you very much everybody.

Now it is solved. Here I provide the code in order to help others who have similar problem.

VB.NET:
Private Sub Button3_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button3.Click

Dim nrows As Integer = tblTempInvoice.Rows.Count

Dim AutoNum(nrows - 1) As Integer

Dim i As Integer


For i = 0 To UBound(AutoNum)

Dim dr As DataRow = tblTempInvoice.NewRow

dataGrid1.Item(i, 0) = i + 1

Next
End Sub
 

cjard

Well-known member
Joined
Apr 25, 2006
Messages
7,059
Programming Experience
10+
The problem is that you have declared the array one item too long, Count is the number of items, arrays are zero-based so you have to declare it one item less than Count:
VB.NET:
Dim AutoNum(nrows - 1) As Integer

OMG, I'd forgotten completely about that.. The number to use in a VB.NET array initializer is the index of the last element, not the total number of elements.. Ugh! Thanks for the reminder John.. On the rare occasions I use arrays I probably forget this.

yousuf, I have to ask.. Why do you dim an array solely for using its length as a loop limiter?

VB.NET:
Private Sub Button3_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button3.Click
        For i as Integer = 0 To tblTempInvoice.Rows.Count -1
                     dataGrid1.Item(i, 0) = i + 1
        Next
 End Sub
PPS, you really probably should put this in the datatable itself, not the grid that is showing the table
 

yousuf42

Well-known member
Joined
Feb 12, 2006
Messages
101
Programming Experience
1-3
Thanks cjard,

I have tried all options those I know to generate itemNo column to autoincrement. then I come up with this. Also I tried to put this into DataTable instead of putting into form directly as below but it does not update values as I add rows to the datatable.

If you can figureout the problem, then definitely I would use it in DataTable.:p


Here is the failed code:-

VB.NET:
Public Function Tempinvoice(ByVal DataTableName As String) As DataTable

         dt = New DataTable(DataTableName)
         

        Dim dcSno As DataColumn
        dcSno = New DataColumn("Sno", GetType(Integer))

        Dim nrows As Integer = dt.Rows.Count
        Dim AutoNum(nrows - 1) As Integer
        Dim i As Integer

        For i = 0 To UBound(AutoNum)
            'Dim dr As DataRow = dt.NewRow
            dcSno.Expression = "i + 1"


        Next
        dt.Columns.Add(dcSno)

        dt.Columns.Add(New DataColumn("ProdID", GetType(String)))
        Return dt

    End Function

Thanks in advance
 

JohnH

VB.NET Forum Moderator
Staff member
Joined
Dec 17, 2005
Messages
15,617
Location
Norway
Programming Experience
10+
Merged threads, it doesn't look as the array was the problem after all, cjard probably have a clever solution for this mess ;)
 

cjard

Well-known member
Joined
Apr 25, 2006
Messages
7,059
Programming Experience
10+
To be perfectly honest, we dont do this in the client at all. At the time of insertion into the database, the next serial number is worked out and when the insert has finished the new number is sent back to the client.

Trying to do this in the client is usually a dumb idea for multi user systems, because clients arent centrally intelligent enough to hand out unique numbers
 
Top Bottom