Searching a dataset

Is there a faster way to search a dataset that a for loop or new selec

Page 1 of 1

6 Replies - 30375 Views - Last Post: 26 January 2009 - 08:20 AM Rate Topic: -----

#1 Paul Washburn  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 65
  • Joined: 02-October 08

Searching a dataset

Posted 09 December 2008 - 09:40 AM

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.

Is This A Good Question/Topic? 0
  • +

Replies To: Searching a dataset

#2 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5937
  • View blog
  • Posts: 12,862
  • Joined: 16-October 07

Re: Searching a dataset

Posted 09 December 2008 - 09:59 AM

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.
Was This Post Helpful? 0
  • +
  • -

#3 Paul Washburn  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 65
  • Joined: 02-October 08

Re: Searching a dataset

Posted 09 December 2008 - 10:19 AM

View Postbaavgai, 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.
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 9579
  • View blog
  • Posts: 36,293
  • Joined: 12-June 08

Re: Searching a dataset

Posted 09 December 2008 - 10:20 AM

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

Was This Post Helpful? 0
  • +
  • -

#5 Paul Washburn  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 65
  • Joined: 02-October 08

Re: Searching a dataset

Posted 23 January 2009 - 02:53 PM

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


Was This Post Helpful? 0
  • +
  • -

#6 thava  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 180
  • View blog
  • Posts: 1,606
  • Joined: 17-April 07

Re: Searching a dataset

Posted 23 January 2009 - 04:10 PM

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

Was This Post Helpful? 0
  • +
  • -

#7 Paul Washburn  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 65
  • Joined: 02-October 08

Re: Searching a dataset

Posted 26 January 2009 - 08:20 AM

View Postthava, 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>.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1