Retrieving a row from a dataset based on the Key

[mushin]

From an array of primary keys, what is the best method to
retrieve that row and it's subsequent elements ?

This code populates a datagrid and then the array of keys.

=============================================================
Private Sub PopulateImageBrowser()
Dim DApics As New SqlDataAdapter
Dim selectCmd As SqlCommand = New SqlCommand("Select * from Picture", cnSQL)
selectCmd.CommandTimeout = 15
DApics.SelectCommand = selectCmd
cnSQL.Open()
Try
DApics.Fill(DSpics, "Picture")
Catch ex As Exception
MsgBox("Picture Not Found for this Search")
End Try
cnSQL.Close()
' DTpics = DSpics.Tables(0)
DV = New DataView(DSpics.Tables("Picture"))
dgImages.DataSource = DV
DV.Sort = "PictureId"
End Sub

Private Sub populatePictureArray()
' read thru datagrid, store picture table keys
Dim myRow As DataRow
iMax = 0
For Each myRow In DSpics.Tables("Picture").Rows
arrPictureKeys(iMax) = CInt(myRow("pictureid"))
iMax = iMax + 1
Next
tb_max.Text = iMax.ToString
End Sub
===========================================================

If I can read thru the dataset to get the keys, what do I have to do to use each of those keys, in turn,
to access the desired row and retieve a specific data element within it ?

I have tried the following:
(1)
If iCount < iMax Then
iCount = iCount + 1
searchKey = arrPictureKeys(iCount)
' myFoundRow = DSpics.Tables("Picture").Rows.Find(searchKey)

This fails with an error the the table has no primary-key,
which is untrue, it does (PictureId).

(2)
Dim i As Integer
Dim k As Integer
k = dv.Table.Rows.Count
i = dv.Find(searchKey)

if i < 0 Or i > k Then
MsgBox("Nothing Found, crap !")
Else
MsgBox("Found it")
End If
This works but I have no idea how to then retrieve the
fields from the dataView, nor do I understand why I cannot search directly on the dataset since I can access it.

Obviously I am missing something basic here, but what ?

 

[ThatRickGuy]

the datatable object has a .Select(string) method that returns an arraow of datarows. you can pass in a where clause to like:

dim drPics() as data.datarows

DTpics = DSpics.Tables(0)
drPics = DTpics.Select("field = value AND field = Value")
if drPics.Length > 0 then 
  'Atleast one record was found
  MyVariable = drPics(0).item(0)
else
  'no records found
end if

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[chrissie1]

what's an arraow? ;-)

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting

 

[ThatRickGuy]

Sorry, my son was trying to help with the typing

should be Array

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[chrissie1]

at least he got datarows right. Smart little kid, must have the brains of his father (who ever that may be)

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting

 

[mushin]

Solved:

By adding this to the datatable definition:
Dim myColArray(1) As DataColumn
myColArray(0) = DTpics.Columns("PictureID")
DTpics.PrimaryKey = myColArray

I was able to code the search as:

Dim myFoundRow As DataRow = DTpics.Rows.Find(searchKey)