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
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