Visual Basic 2005

Find a Record

This is my code for finding a record. My data set is called ItemsDataSet. I'm looking for a match in the first column of the data set.

Private Sub btnFind_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFind.Click

Dim strFound As String

strFound = "n"

inc = -1

'Look for it

Do

inc = inc + 1

If txtID.Text = (ds.Tables("ItemsDataSet").Rows(inc).Item(0)) Then

'found

DisplayRecord()

strFound = "y"

End If

Loop While strFound = "n" And inc < MaxRows - 1

If strFound = "y" Then

'found

DisplayRecord()

Else

'not found

MsgBox("Record was not found")

End If

End Sub

Display a Record

The form tools are assigned data from the data set. I use a global variable called inc to send the record number to the sub routine.

I have one field that may be blank. It holds the date when the punch list item is closed. It will not have a date until that happens.

There is no problem writing the data set with a blank field from the data base, but Visual Basic 2005 has a problem working with Null values in data sets. If I attempt to do anything with a field containing a Null value, I will get a run-time error. So I have another field that indicates whether the item is closed or not. It is 1 if the item is closed and there is a closed date; a zero if not. I check to see if the item is closed before I attempt to work with the date field. This way I do not get a run-time error.

Private Sub DisplayRecord()

txtID.Text = Str((ds.Tables("ItemsDataSet").Rows(inc).Item(0)))

txtUnit.Text = (ds.Tables("ItemsDataSet").Rows(inc).Item(1))

cboxContract.Text = (ds.Tables("ItemsDataSet").Rows(inc).Item(2))

cboxAreaSystem.Text = (ds.Tables("ItemsDataSet").Rows(inc).Item(3))

txtProblem.Text = (ds.Tables("ItemsDataSet").Rows(inc).Item(4))

DateReported.Text = (ds.Tables("ItemsDataSet").Rows(inc).Item(5))

txtReportedBy.Text = (ds.Tables("ItemsDataSet").Rows(inc).Item(6))

If (ds.Tables("ItemsDataSet").Rows(inc).Item(7)) = 1 Then

DateClosed.Text = (ds.Tables("ItemsDataSet").Rows(inc).Item(8))

Else

DateClosed.Visible = False

End If

txtNotes.Text = (ds.Tables("ItemsDataSet").Rows(inc).Item(9))

End Sub

Add Button Click Event Statements

'check for a valid row number

If Inc > -1 Then

'create a command builder variable

Dim cb As New OleDb.OleDbCommandBuilder(da)

'create a variable to hold the new record in a row

Dim dsNewRow As DataRow

'create the new row

dsNewRow = ds.Tables("datasetname").NewRow()

'put the new data in the row

dsNewRow.Item(1) = txtName1.Text

dsNewRow.Item(2) = txtName2.Text

'add the row to the data set

ds.Tables("datasetname").Rows.Add(dsNewRow)

'tell the data adaptor to update the data base

da.Update(ds, "TableName")

End If

Another page developed by

vicky@vickywoodard.com

Visual Basic 2005

Find a Record

Display a Record

Another page developed by

Copyright © 2006 Vicky K. Woodard

All rights reserved

Last updated: 8/17/2006