[Paul Washburn]

If have a program that connects to an access 2008 table, the table contains a list of valid entries for the various text boxes and each textbox needs to be checked against that table.

Is there a simpler/faster way to search the dataset after it has been filled via sql select statement, without doing a new select statement or a for loop for each textbox.

i tried the following but it could not detect the primary key of the table.

		If userDetailsTable.Rows.Contains(txtCasesReport.Text) Or txtCasesReport.Text = "" Then
			blnValidEntry = True
		Else
			MsgBox("Invalid Entry", MsgBoxStyle.OkOnly, "Invalid Entry")
		End If

Thanks in advance.

[baavgai]

I'm not even sure if that will work.

Look into the Select Method of the DataTable. It may be just what you're looking for.

[Paul Washburn]

baavgai, on 9 Dec, 2008 - 08:59 AM, said:

I'm not even sure if that will work.

Look into the Select Method of the DataTable. It may be just what you're looking for.

Thanks, dont think thats what im looking for though, the For Loop seems like less code than the select method.

I was really hoping for a simple boolean statement something along the lines of if column A contains "AB" return true.

[modi123_1]

Baav's right.

MyDataTable is a table in DataSet2.
It contains the following columns:
lValue (int32) (key)
sValue (string)
dtDate (datetime)
sNote (string)

	Private Sub foobar()
		Dim foo As New DataSet2
		Dim tempValue As Int32 = 0
		Dim returnRows() As System.Data.DataRow = Nothing

'-- this returns any rows form MyDataTable (which is part of the dataset variable foo) where lValue equals 0.
'-- if nothing is returned then reteurnRows is nothing
'-- the Select() on the table usually takes in a filter expression simliar to a where clause in SQL.
		returnRows = foo.MyDataTable.Select("lValue = " & tempValue.ToString)
	End Sub

This post has been edited by modi123_1: 09 December 2008 - 10:21 AM

[Paul Washburn]

Thanks for the help, been playing with the code and have not been able to get it to work. When I try the code below i get a nullreferenceexception was unhandled: Object reference not set to an instance of an object.

Do i need to specify in someway which column Signatures is? or does this function require a loop to move through the table?

Thanks again for the help.

	Private Sub ValidEntry(ByRef blnValidEntry, ByRef strInvalidEntry)
		Dim conn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source= \\Bkesl\VOL2\Common\updates\CaseAdmin\CaseAdmin.accdb")
		conn.Open()

		'Dim userDetailsAdapter As New OleDbDataAdapter("SELECT * FROM tblSignatures", conn)

		Dim userDetailsDataSet As New DataSet
		'userDetailsAdapter.Fill(userDetailsDataSet)
		'userDetailsAdapter.Dispose()
		Dim userDetailsTable As DataTable = userDetailsDataSet.Tables("tblSignatures")
		Dim returnRows() As System.Data.DataRow = Nothing

		returnRows = userDetailsTable.Select("Signature = " & txtCasesReport.Text)

		If returnRows Is Nothing Then
			MsgBox("User initials do not exist", MsgBoxStyle.OkCancel, "Invalid Entry")
			txtCasesReport.Focus()
		Else
			blnValidEntry = True
		End If
	End Sub

[thava]

you miss two things i note it for you

    Private Sub ValidEntry(ByRef blnValidEntry, ByRef strInvalidEntry)
        Dim conn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source= \\Bkesl\VOL2\Common\updates\CaseAdmin\CaseAdmin.accdb")
        conn.Open()

        Dim userDetailsAdapter As New OleDbDataAdapter("SELECT * FROM tblSignatures", conn)
Dim userDetailsTable As new DataTable       ' you miss the new operator
        'Dim userDetailsDataSet As New DataSet
        userDetailsAdapter.Fill(userDetailsTable ) ' you miss to Fill the datatable
        'userDetailsAdapter.Dispose()
        Dim returnRows() As System.Data.DataRow = Nothing
        returnRows = userDetailsTable.Select("Signature = " & txtCasesReport.Text)

        If returnRows Is Nothing Then
            MsgBox("User initials do not exist", MsgBoxStyle.OkCancel, "Invalid Entry")
            txtCasesReport.Focus()
        Else
            blnValidEntry = True
        End If
    End Sub

This post has been edited by thava: 23 January 2009 - 04:13 PM

[Paul Washburn]

thava, on 23 Jan, 2009 - 03:10 PM, said:

you miss two things i note it for you

    Private Sub ValidEntry(ByRef blnValidEntry, ByRef strInvalidEntry)
        Dim conn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source= \\Bkesl\VOL2\Common\updates\CaseAdmin\CaseAdmin.accdb")
        conn.Open()

        Dim userDetailsAdapter As New OleDbDataAdapter("SELECT * FROM tblSignatures", conn)
Dim userDetailsTable As new DataTable       ' you miss the new operator
        'Dim userDetailsDataSet As New DataSet
        userDetailsAdapter.Fill(userDetailsTable ) ' you miss to Fill the datatable
        'userDetailsAdapter.Dispose()
        Dim returnRows() As System.Data.DataRow = Nothing
        returnRows = userDetailsTable.Select("Signature = " & txtCasesReport.Text)

        If returnRows Is Nothing Then
            MsgBox("User initials do not exist", MsgBoxStyle.OkCancel, "Invalid Entry")
            txtCasesReport.Focus()
        Else
            blnValidEntry = True
        End If
    End Sub

Thanks,

When i try this code i get an unhandled OLEDBException Error: Cannot find column <value of txtCasesReport.txt>.