Results 1 to 4 of 4

Thread: Datatable example with 3 column primary key help

  1. #1

    Thread Starter
    New Member
    Join Date
    Apr 2018
    Posts
    11

    Datatable example with 3 column primary key help

    Can someone give me an example code of a VB datatable, with three of the columns(my table has five columns total) as the primary key, then do a find on the table? I have been racking my brains to do this, but i'm missing something. I'm simply building a table and checking if i need to update a row or if it already exists and my "find" is not working. A basic functioning example would be GREATLY appreciated!!!
    table: Col1,col2,Col3,Col4,Col5
    primary key: col1,col2,col3

    I need to check if the row exists already(then update, Col4 & Col5) or not(then add new row)....
    Thanks,
    Brian

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Datatable example with 3 column primary key help

    For future reference, please provide your code first. Asking us to write code from scratch when you already have code that, for all you know, is already 99% correct is a bit inconsiderate.

    Anyway, finding a row by primary key is the same for a compound PK as it is for a singular PK except that need to pass the multiple values as an array. Setting the PK is exactly the same because DataTable.PrimaryKey is a DataColumn array, even if that array only contains one column.
    vb.net Code:
    1. Dim table As New DataTable
    2. Dim column1 = table.Columns.Add("Column1")
    3. Dim column2 = table.Columns.Add("Column2")
    4. Dim column3 = table.Columns.Add("Column3")
    5.  
    6. table.Columns.Add("Column4")
    7. table.Columns.Add("Column5")
    8. table.PrimaryKey = {column1, column2, column3}
    9.  
    10. '...
    11.  
    12. Dim row = table.Rows.Find({"column1Value", "column2Value", "column3Value"})
    13.  
    14. If row IsNot Nothing Then
    15.     '...
    16. End If

  3. #3

    Thread Starter
    New Member
    Join Date
    Apr 2018
    Posts
    11

    Re: Datatable example with 3 column primary key help

    Sorry about not adding code!!!!
    Code:
            Dim Table1 As New DataTable("Customers")
            ' Declare colums, cust, year, service, amt, order count
            Table1.Columns.Add("COLcust", GetType(System.String))
            Table1.Columns.Add("COLyear", GetType(System.Int32))T
            Table1.Columns.Add("COLservice", GetType(System.String))
            Table1.Columns.Add("COLamt", GetType(System.Decimal))
            Table1.Columns.Add("COLordcnt", GetType(System.Int32))
            Dim primarykey(2) As DataColumn
            primarykey(0) = Table1.Columns("Colcust")
            primarykey(1) = Table1.Columns("COLyear")
            primarykey(2) = Table1.Columns("COLservice")
            Table1.PrimaryKey = primarykey
            Dim foundRow As DataRow = Table1.Rows.Find(primarykey)
            Dim Table1Row As DataRow = Table1.NewRow()
    		
    		
    		    If Not (foundRow Is Nothing) Then       'update existing row for new order
                            'update
                            If Not IsDBNull(Table1Row("COLamt")) Then
                                Table1Row("COLamt") = Table1Row("COLamt") + Varordamt
                                Table1Row("COLcnt") = Table1Row("COLcnt") + 1
                            Else
                                Table1Row("COLamt") = Varordamt
                                Table1Row("COLcnt") = Table1Row("COLcnt") + 1
                            End If
                        Else
                            'add new row
                            Table1Row("COLcust") = Varcustomer
                            Table1Row("COLyear") = Varyearord2
                            Table1Row("COLservice") = VARservice
                            If Not IsDBNull(Table1Row("COLamt")) Then
                                Table1Row("COLamt") = Table1Row("COLamt") + Varordamt
                            Else
                                Table1Row("COLamt") = Varordamt
                            End If
                            Table1.Rows.Add(Table1Row)
                        End If

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Datatable example with 3 column primary key help

    This line doesn't make sense:
    vb.net Code:
    1. Dim foundRow As DataRow = Table1.Rows.Find(primarykey)
    'primaryKey' is the array of DataColumns that comprise the primary key for the table. That 'Find' method is expecting an array of values that could be contained in those columns, not the columns themselves. The values you appear to be trying to find are 'Varcustomer', 'Varyearord2' and 'VARservice', so you need to put them into an array and pass that to 'Find'. See what a difference it can make fi you supply your code?

    By the way, don't be calling NewRow before you've even tested whether 'foundRow' is Nothing. You don't need to call it at all if you found a matching row so that call should be inside the 'Else' block. Also, it's considered better practice to use 'IsNot' rather than 'Not Is', i.e. use:
    vb.net Code:
    1. If foundRow IsNot Nothing Then

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width