-
May 22nd, 2018, 07:22 PM
#1
Thread Starter
New Member
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
-
May 22nd, 2018, 08:24 PM
#2
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:
Dim table As New DataTable Dim column1 = table.Columns.Add("Column1") Dim column2 = table.Columns.Add("Column2") Dim column3 = table.Columns.Add("Column3") table.Columns.Add("Column4") table.Columns.Add("Column5") table.PrimaryKey = {column1, column2, column3} '... Dim row = table.Rows.Find({"column1Value", "column2Value", "column3Value"}) If row IsNot Nothing Then '... End If
-
May 23rd, 2018, 06:45 AM
#3
Thread Starter
New Member
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
-
May 23rd, 2018, 08:39 AM
#4
Re: Datatable example with 3 column primary key help
This line doesn't make sense:
vb.net Code:
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:
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|