Sample DataSet SQL Select

'******************* OPEN A DATABASE **************************************************************

Dim objConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & "c:\db1.mdb")

Dim objCommand As New OleDbCommand()

Dim objDa As New OleDbDataAdapter()

Dim objDs As New DataSet()

Dim objCommandBuilder As New OleDbCommandBuilder()

' **************************************************************************************************

        objCommand.CommandText = "Select * From Candidate"

        objCommand.Connection = objConn

        objDa.SelectCommand = objCommand

        objDa.Fill(objDs, "Candidate")

'This Select Function Will Return ROWS, Hence the DataRow() dimension.

Dim Rows As DataRow()

        Rows = objDs.Tables("Candidate").Select("Lname like '%s%'")

'Notice the ROW is singular here....

Dim Row As DataRow

For Each Row In Rows

            MsgBox(Row.Item("Lname"))

Next

    CREATE A PRIMARY KEY IN A DATASET

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

'******************* OPEN A DATABASE ****************************************************

Dim objConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" &             "C:\inetpub\wwwroot\cp\cp.mdb")

Dim objCommand As New OleDbCommand()

Dim objDa As New OleDbDataAdapter()

Dim objDs As New DataSet()

Dim objCommandBuilder As New OleDbCommandBuilder()

' **********************************************

        objCommand.CommandText = "SELECT DISTINCTROW                                                                          candidate.ssntypelev,candidate.ssn,candidate.examtype,candidate.examlev, candidate.lname, demographic.demo8,             demographic.demo9 FROM CANDIDATE INNER JOIN demographic ON CANDIDATE.SSNTYPELEV = demographic.ssntypelev"

        objCommand.Connection = objConn

        objDa.SelectCommand = objCommand

        objDa.Fill(objDs, "CanDem")

        objConn.Close()

        objDa.FillSchema(objDs, SchemaType.Source, "CanDem")

        objCommandBuilder = New OleDbCommandBuilder(objDa)

'***********************************************************************************************

'We have declared an array of DataColumn object.

'Our CanDem table has 3 fields that acts as primary field i.e. Ssn-ExamType-ExamLev hence

'we declared array to hold 3 elements. In case you have 1 field simply make size of the array = 1.

'Next, we assigned which columns to be treated as primary key. Finally, we set PrimaryKey property of

'the DataTable object.

Dim pk(3) As DataColumn

        pk(0) = objDs.Tables("CanDem").Columns("ssn")

        pk(1) = objDs.Tables("CanDem").Columns("examtype")

        pk(2) = objDs.Tables("CanDem").Columns("examlev")

        objDs.Tables("CanDem").PrimaryKey = pk

'Configure our Primary Key array....

'These 3 elements makeup the primary key

Dim a(2) As String

        a(0) = "888888888"

        a(1) = "ISC2"

        a(2) = "CERT"

'***********************************

Dim r As DataRow

        r = objDs.Tables("CanDem").Rows.Find(a)

If r Is Nothing Then

            MessageBox.Show("Not Found")

Else

            MsgBox(r.Item("Lname"))

End If

End Sub

Sample DataSet Relations

      Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

'**************************************************

' We have a database, c:\DB1.MDB, with 2 tables:  *

'   Candidate  -- Fields(Lname, Id)               *

'   Scores     -- Fields(Score, Id)               *

'**************************************************

'******************* OPEN A DATABASE **************************************************************

Dim objConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & "c:\db1.mdb")

Dim objCommand As New OleDbCommand()

Dim objDa As New OleDbDataAdapter()

Dim objDs As New DataSet()

Dim objCommandBuilder As New OleDbCommandBuilder()

' **************************************************************************************************

        objCommand.CommandText = "Select * From Candidate"

        objCommand.Connection = objConn

        objDa.SelectCommand = objCommand

        objDa.Fill(objDs, "Candidate")

        objCommand.CommandText = "SELECT * From Scores"

        objCommand.Connection = objConn

        objDa.SelectCommand = objCommand

        objDa.Fill(objDs, "Registry")

        objConn.Close()

'***********************************************************************************************

'Setup our relations... **********************************************************************************

Dim CanRegRel As DataRelation = objDs.Relations.Add("CanReg", objDs.Tables("Candidate").Columns("Id"), _

                                        objDs.Tables("Registry").Columns("Id"))

'**********************************************************************************************************

'Example 1:

'This shows how to loop through each relation in the table..

Dim CanRow As DataRow

Dim RegRow As DataRow

For Each CanRow In objDs.Tables("Candidate").Rows

For Each RegRow In CanRow.GetChildRows(CanRegRel)

                MsgBox("Last Name=" & CanRow("Lname") & "  Score=" & RegRow("Score"))

Next

Next

'Example 2:

'Define A Primary Key ********************************

Dim pk(1) As DataColumn

        pk(0) = objDs.Tables("Candidate").Columns("Id")

        objDs.Tables("Candidate").PrimaryKey = pk

'*****************************************************

'Find the record with an Id of '2'  ******************

        CanRow = objDs.Tables("Candidate").Rows.Find(2)

If CanRow Is Nothing Then

            MessageBox.Show("Record Not Found")

Exit Sub

Else

'Show The Scores for id found

For Each RegRow In CanRow.GetChildRows(CanRegRel)

                MsgBox(CanRow("Lname") & " " & RegRow("score"))

Next

End If

'*****************************************************

End Sub