Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Search Database

Posted on 2005-05-05
24
Medium Priority
?
291 Views
Last Modified: 2010-04-23
I am creating a front end for an access database which displays data in text and combo boxes.  When you click a search button on the main form it will open another form which has a combobox on so that you can select to search by name, or box number.  Then when you have entered your data and selected what to search by I would like the application to switch back to the main form and update the text/comboboxes.  

I have tried this code on the search form (form2)
   Private Sub BtnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnSearch.Click

        DirectCast(Me.Owner, Form1).dv.Sort = "BoxNumber"
        Dim x As Integer = DirectCast(Me.Owner, Form1).dv.Find(12)
        DirectCast(Me.Owner, Form1).dv.Sort = "BoxNumber"
        DirectCast(Me.Owner, Form1).cm.Position = x

    End Sub

This returns a result but not to a specific number.

Any ideas?
0
Comment
Question by:matt_swinburne
  • 15
  • 9
24 Comments
 

Author Comment

by:matt_swinburne
ID: 13934110
I would also like the search (form2) to close once the form 1 has been updated.
0
 

Author Comment

by:matt_swinburne
ID: 13934148
When i run program and enter to search boxnumber i enter for example i enter 36 and it jumps to boxnumber 3764

0
 

Author Comment

by:matt_swinburne
ID: 13934156
In fact no matter what you enter it jumps to 3764
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 19

Expert Comment

by:arif_eqbal
ID: 13934330
I would suggest a few changes...
Do not search on the second form, your first form contains the datSet/dataView
Just get the search criteria from there and do the searching locally
Here's How

Search Form (Form2)

Contains TextBoxes to search (I assume just one BoxNo)
Contains a Public Readonly property To return BoxNo value
contains two buttons OK, Cancel


 Dim mBoxNo As String

    Public ReadOnly Property BoxNo() As String
        Get
            BoxNo = mBoxNo
        End Get
    End Property

    Private Sub CmdOK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CmdOK.Click
        mBoxNo = Me.TextBox1.Text
        Me.DialogResult = DialogResult.OK
    End Sub



Now Main Form (Form1)

On the Find Button

        Dim objFrmFind As New FrmFind 'FrmFind is the above Form (Form2)
        Dim No As String
        If objFrmFind.ShowDialog = DialogResult.OK Then
            No = objFrmFind.BoxNo
        End If
        dv.Sort="BoxNumber"
        cm.Position=dv.Find(No)


0
 

Author Comment

by:matt_swinburne
ID: 13934377
For various reasons I have decided not to open a second form for the search, it will still work in the same way but on the same main form
0
 
LVL 19

Expert Comment

by:arif_eqbal
ID: 13934402
Hi matt_swinburne
even that is OK, what I uasually do is
I give a button called "Query" on click of it I clear the TextBoxes and change the BackColor of the particular TextBoxes through which Query criteria can be given.
Then On "Exceute" button I search and Show the Records. All on the same form.

However, two form concept is not very bad. Its pretty starightForward. Ididn't explain it too well perhaps, but it as simple as opening the form in Modal form and then reading the property to get the search criteria and then seraching will be done on the main form only.

Anyway your choice.
0
 

Author Comment

by:matt_swinburne
ID: 13934424
What do you think would be the best/easiest way to go?  My knowledge is basic and therfore I need the way that is easiest to understand.  I am binded using a datatables and not dataview so maybe the code that I was using is not suitable?
0
 
LVL 19

Expert Comment

by:arif_eqbal
ID: 13934474
From your code it appears you are using CurrencyManager (cm.Position looks like it)
I think this is the easiest (and perhaps the best) way to go about it.
Using Currency manager you can easily Navigate.

As for searching if you search on a Key value very fine, beacuse it usually returns one Row and we can easily get the Position and Show (as you are attempting to do)

The problem comes when search returns more than One Row, In such cases DataView comes in very handy. For searching Datatable has a "Select" method but it returns dataRows, so you need to rebind to the DataRows to show the result.
But Dataview has a method called "RowFilter", it takes the same Where clause as Dataset's Select but it does not return anything, rather it filters itself so that now it shows only the Searched Records, that way the existing Binding works fine.

So in case your search criterians are not only on Primary Key and are expected to return more than one row, why not go for dataView, you can set the AllowAdd,AllowDelete etc. to true so that it functions like a DataTable and yet can accomodate multiple search results.

0
 

Author Comment

by:matt_swinburne
ID: 13934553
Will using dataview effect any of this coding?

  Public Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        dv = DataSet11.Tables("main").DefaultView
        cm = CType(Me.BindingContext(DataSet11, "Main"), CurrencyManager)

        'Fill Dataset
        DbMain.Fill(DataSet11, "Main")
        DbLocations.Fill(DataSet11, "Locations")
        DbFeeEarner.Fill(DataSet11, "FeeEarner")
        DbMatter.Fill(DataSet11, "Matter")
        DbStatus.Fill(DataSet11, "Status")
        DbOffices.Fill(DataSet11, "Offices")
        cm = CType(Me.BindingContext(DataSet11, "Main"), CurrencyManager)

        'Load Originating Location Combo
        CmbLocation.DataSource = DataSet11.Tables("Locations")
        CmbLocation.DisplayMember = "LocationName"
        CmbLocation.ValueMember = "LocationID"
        CmbLocation.DataBindings.Add("selectedvalue", DataSet11, "Main.LocationID")


        'Load FeeEarner ComboBox
        CmbFeeEarner.DataSource = DataSet11.Tables("FeeEarner")
        CmbFeeEarner.DisplayMember = "FeeEarnerName"
        CmbFeeEarner.ValueMember = "FeeEarnerID"
        CmbFeeEarner.DataBindings.Add("selectedvalue", DataSet11, "Main.FeeEarnerID")

        'Load MatterType Combobox
        CmbMatter.DataSource = DataSet11.Tables("Matter")
        CmbMatter.DisplayMember = "MatterTypeName"
        CmbMatter.ValueMember = "MatterTypeID"
        CmbMatter.DataBindings.Add("selectedvalue", DataSet11, "Main.MatterTypeID")

        'Load Status Combobox
        CmbStatus.DataSource = DataSet11.Tables("Status")
        CmbStatus.DisplayMember = "StorageName"
        CmbStatus.ValueMember = "StorageID"
        CmbStatus.DataBindings.Add("selectedvalue", DataSet11, "Main.StatusID")

        'Load Office Combobox
        CmbOffice.DataSource = DataSet11.Tables("Offices")
        CmbOffice.DisplayMember = "OfficeName"
        CmbOffice.ValueMember = "OfficeID"
        CmbOffice.DataBindings.Add("selectedvalue", DataSet11, "Main.OfficeName")

    End Sub

    Private Sub NavigationButtons_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnFirst.Click, BtnLast.Click, BtnNext.Click, BtnPrevious.Click
        Select Case sender.Name
            Case "BtnFirst"

                cm.Position = 0

            Case "BtnPrevious"

                cm.Position -= 1

            Case "BtnNext"

                cm.Position += 1

            Case "BtnLast"

                cm.Position = DataSet11.Tables("Main").Rows.Count - 1

        End Select

    End Sub


This is what I am using so far in the main form to bind and navigate the database
0
 

Author Comment

by:matt_swinburne
ID: 13934649
I tried your 1st suggestion but on;

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnMainSearch.Click
        Dim objFrmFind As New Search 'FrmFind is the above Form (Form2)
        Dim No As String
        If objFrmFind.ShowDialog = DialogResult.OK Then
            No = objFrmFind.BoxNo
        End If
>>        dv.Sort = "BoxNo" ' Error
        cm.Position = dv.Find(No)

        Dim frmSearch As New Search
        frmSearch.Owner = Me
        frmSearch.ShowDialog()
     

    End Sub

I get;

Object reference not set to an instance of an object.

Thanks
0
 
LVL 19

Expert Comment

by:arif_eqbal
ID: 13934996
Your First question first
Your code appears to be just fine, changing from DataTable to Dataview would not affect anything

As for the second problem DV is your dataview is it set or not ???
Another thing I notice is that your dataset has too many tables and they are used to fill the ComboBoxes, However your Currency Manger is bound to the table MAIN, so probably that is the table of our interest, I mean the DataView is to be of that table or in other words the searching is to be done on the Main table ???

I am trying to glean up the code you have posted and get you a complete code....

Main Form
--------------
Dim dv as DataView
Dim cm As CurrencyManager

Public Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        dv = DataSet11.Tables("main").DefaultView
        cm = CType(Me.BindingContext(dv), CurrencyManager) '--> Changed here

        'Fill Dataset
        DbMain.Fill(DataSet11, "Main")
        DbLocations.Fill(DataSet11, "Locations")
        DbFeeEarner.Fill(DataSet11, "FeeEarner")
        DbMatter.Fill(DataSet11, "Matter")
        DbStatus.Fill(DataSet11, "Status")
        DbOffices.Fill(DataSet11, "Offices")
        'cm = CType(Me.BindingContext(DataSet11, "Main"), CurrencyManager) '-->removed this line seems to be redundant

        'Load Originating Location Combo
        CmbLocation.DataSource = DataSet11.Tables("Locations")
        CmbLocation.DisplayMember = "LocationName"
        CmbLocation.ValueMember = "LocationID"
        CmbLocation.DataBindings.Add("selectedvalue", DataSet11, "Main.LocationID")


        'Load FeeEarner ComboBox
        CmbFeeEarner.DataSource = DataSet11.Tables("FeeEarner")
        CmbFeeEarner.DisplayMember = "FeeEarnerName"
        CmbFeeEarner.ValueMember = "FeeEarnerID"
        CmbFeeEarner.DataBindings.Add("selectedvalue", DataSet11, "Main.FeeEarnerID")

        'Load MatterType Combobox
        CmbMatter.DataSource = DataSet11.Tables("Matter")
        CmbMatter.DisplayMember = "MatterTypeName"
        CmbMatter.ValueMember = "MatterTypeID"
        CmbMatter.DataBindings.Add("selectedvalue", DataSet11, "Main.MatterTypeID")

        'Load Status Combobox
        CmbStatus.DataSource = DataSet11.Tables("Status")
        CmbStatus.DisplayMember = "StorageName"
        CmbStatus.ValueMember = "StorageID"
        CmbStatus.DataBindings.Add("selectedvalue", DataSet11, "Main.StatusID")

        'Load Office Combobox
        CmbOffice.DataSource = DataSet11.Tables("Offices")
        CmbOffice.DisplayMember = "OfficeName"
        CmbOffice.ValueMember = "OfficeID"
        CmbOffice.DataBindings.Add("selectedvalue", DataSet11, "Main.OfficeName")

    End Sub

    Private Sub NavigationButtons_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnFirst.Click, BtnLast.Click, BtnNext.Click, BtnPrevious.Click
        Select Case sender.Name
            Case "BtnFirst"
                cm.Position = 0
            Case "BtnPrevious"
                cm.Position -= 1
            Case "BtnNext"
                cm.Position += 1
            Case "BtnLast"
                cm.Position = DataSet11.Tables("Main").Rows.Count - 1
        End Select

    End Sub


Now the Find button in thid form

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnMainSearch.Click
        Dim objFrmFind As New Search 'FrmFind is the above Form (Form2)
        Dim No As String
        If objFrmFind.ShowDialog = DialogResult.OK Then
            No = objFrmFind.BoxNo
        End If
       dv.Sort = "BoxNo" '--> Change BoxNo to the column name on which you are searching
        cm.Position = dv.Find(No)
    End Sub

That's it....

though none of the ComboBox values would change after your search that's becaus none of the combo boxes are bound to the table Main, I assume you are having other TextBoxes etc. which are bound to the table Main on which you are searching and on which you are using the CurrencyManager.
0
 

Author Comment

by:matt_swinburne
ID: 13935438
So this goes into the 2nd form on the search button.

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnMainSearch.Click
        Dim objFrmFind As New Search 'FrmFind is the above Form (Form2)
        Dim No As String
        If objFrmFind.ShowDialog = DialogResult.OK Then
            No = objFrmFind.BoxNo
        End If
       dv.Sort = "BoxNo" '--> Change BoxNo to the column name on which you are searching
        cm.Position = dv.Find(No)
    End Sub

To answer your question, yes i am searching on the table Main, I only bound the other tables to the same dataset as I needed data off them to populate the combo-boxes
0
 

Author Comment

by:matt_swinburne
ID: 13935626
I tried the coding you gave me and got

 Value cannot be null.

on cm = CType(Me.BindingContext(dv), CurrencyManager)
0
 

Author Comment

by:matt_swinburne
ID: 13935940
Im guessing this error means that my dv is not set?  as the current statement doesn't return this error
0
 
LVL 19

Expert Comment

by:arif_eqbal
ID: 13942706
Hi matt_swinburne

I just made a small sample for you to understand how things ought to be.
I have used the sample on the EMP table. The Emp Table contains 3 fields EmpID, EmpName and Dept.

I have a form(FrmEmp) that displays these in Textboxes and ComboBoxes.
I have Next/Previous buttons for Navigation then I have a Find button.
This Find button opens a New Form (FrmFind), On this new form user will give the Department Name (I have chosen to search on Department name as one Department will have many Employees)
Now when he gives the Dept and says OK the main form (FrmEmp) shows the selected records, Now the Next/Previous buttons only navigate to records so selected. I have another Button called ShowAll (typically people call it Refresh), clicking on this button will again De-Filter i.e all records will be shown.

I am posting the complete Form codes (Two Forms) paste in on empty Forms
Also you need to create a table (i used Access) and name it Emp this will have 3 fields Empid - Text, EmpName - Text and Dept - Text.

Run it and Go through the code this will make things a bit clear


here is Main Form
--------------------

Imports System.Data.OleDb
Public Class FrmEmp
    Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    Friend WithEvents Label1 As System.Windows.Forms.Label
    Friend WithEvents Label2 As System.Windows.Forms.Label
    Friend WithEvents Label3 As System.Windows.Forms.Label
    Friend WithEvents TxtID As System.Windows.Forms.TextBox
    Friend WithEvents CmbDept As System.Windows.Forms.ComboBox
    Friend WithEvents txtName As System.Windows.Forms.TextBox
    Friend WithEvents CmdNext As System.Windows.Forms.Button
    Friend WithEvents CmdPrev As System.Windows.Forms.Button
    Friend WithEvents CmdFind As System.Windows.Forms.Button
    Friend WithEvents CmdShowAll As System.Windows.Forms.Button
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.Label1 = New System.Windows.Forms.Label
        Me.Label2 = New System.Windows.Forms.Label
        Me.Label3 = New System.Windows.Forms.Label
        Me.TxtID = New System.Windows.Forms.TextBox
        Me.CmbDept = New System.Windows.Forms.ComboBox
        Me.txtName = New System.Windows.Forms.TextBox
        Me.CmdNext = New System.Windows.Forms.Button
        Me.CmdPrev = New System.Windows.Forms.Button
        Me.CmdFind = New System.Windows.Forms.Button
        Me.CmdShowAll = New System.Windows.Forms.Button
        Me.SuspendLayout()
        '
        'Label1
        '
        Me.Label1.Location = New System.Drawing.Point(32, 24)
        Me.Label1.Name = "Label1"
        Me.Label1.Size = New System.Drawing.Size(64, 16)
        Me.Label1.TabIndex = 0
        Me.Label1.Text = "ID"
        '
        'Label2
        '
        Me.Label2.Location = New System.Drawing.Point(32, 48)
        Me.Label2.Name = "Label2"
        Me.Label2.Size = New System.Drawing.Size(64, 16)
        Me.Label2.TabIndex = 1
        Me.Label2.Text = "Name"
        '
        'Label3
        '
        Me.Label3.Location = New System.Drawing.Point(32, 72)
        Me.Label3.Name = "Label3"
        Me.Label3.Size = New System.Drawing.Size(64, 16)
        Me.Label3.TabIndex = 2
        Me.Label3.Text = "Dept"
        '
        'TxtID
        '
        Me.TxtID.Location = New System.Drawing.Point(104, 16)
        Me.TxtID.Name = "TxtID"
        Me.TxtID.Size = New System.Drawing.Size(104, 20)
        Me.TxtID.TabIndex = 3
        Me.TxtID.Text = ""
        '
        'CmbDept
        '
        Me.CmbDept.Location = New System.Drawing.Point(104, 64)
        Me.CmbDept.Name = "CmbDept"
        Me.CmbDept.Size = New System.Drawing.Size(104, 21)
        Me.CmbDept.TabIndex = 4
        '
        'txtName
        '
        Me.txtName.Location = New System.Drawing.Point(104, 40)
        Me.txtName.Name = "txtName"
        Me.txtName.Size = New System.Drawing.Size(104, 20)
        Me.txtName.TabIndex = 5
        Me.txtName.Text = ""
        '
        'CmdNext
        '
        Me.CmdNext.Location = New System.Drawing.Point(136, 112)
        Me.CmdNext.Name = "CmdNext"
        Me.CmdNext.Size = New System.Drawing.Size(32, 24)
        Me.CmdNext.TabIndex = 6
        Me.CmdNext.Text = ">"
        '
        'CmdPrev
        '
        Me.CmdPrev.Location = New System.Drawing.Point(96, 112)
        Me.CmdPrev.Name = "CmdPrev"
        Me.CmdPrev.Size = New System.Drawing.Size(32, 24)
        Me.CmdPrev.TabIndex = 7
        Me.CmdPrev.Text = "<"
        '
        'CmdFind
        '
        Me.CmdFind.Location = New System.Drawing.Point(96, 144)
        Me.CmdFind.Name = "CmdFind"
        Me.CmdFind.Size = New System.Drawing.Size(72, 24)
        Me.CmdFind.TabIndex = 8
        Me.CmdFind.Text = "Find"
        '
        'CmdShowAll
        '
        Me.CmdShowAll.Location = New System.Drawing.Point(96, 176)
        Me.CmdShowAll.Name = "CmdShowAll"
        Me.CmdShowAll.Size = New System.Drawing.Size(72, 24)
        Me.CmdShowAll.TabIndex = 9
        Me.CmdShowAll.Text = "Show All"
        '
        'FrmEmp
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(272, 273)
        Me.Controls.Add(Me.CmdShowAll)
        Me.Controls.Add(Me.CmdFind)
        Me.Controls.Add(Me.CmdPrev)
        Me.Controls.Add(Me.CmdNext)
        Me.Controls.Add(Me.txtName)
        Me.Controls.Add(Me.CmbDept)
        Me.Controls.Add(Me.TxtID)
        Me.Controls.Add(Me.Label3)
        Me.Controls.Add(Me.Label2)
        Me.Controls.Add(Me.Label1)
        Me.Name = "FrmEmp"
        Me.Text = "FrmEmp"
        Me.ResumeLayout(False)

    End Sub

#End Region

    Dim DV As DataView
    Dim CM As CurrencyManager
'YOU NEED TO CHANGE THIS TO POINT TO YOUR DATABASE
    Dim Constr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\.Net\test.mdb;Persist Security Info=False"

    Private Sub FrmEmp_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim DA As New OleDbDataAdapter("Select * From Emp", Constr)
        Dim DT As New DataTable
        DA.Fill(DT)
        DV = DT.DefaultView
        CM = CType(Me.BindingContext(DV), CurrencyManager)
        TxtID.DataBindings.Add("Text", DV, "EmpID")
        txtName.DataBindings.Add("Text", DV, "EmpName")

        CmbDept.DataSource = DV
        CmbDept.DisplayMember = "Dept"
        CmbDept.ValueMember = "Dept"
        CmbDept.DataBindings.Add("selectedvalue", DV, "Dept")

    End Sub

    Private Sub CmdPrev_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CmdPrev.Click
        CM.Position -= 1
    End Sub

    Private Sub CmdNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CmdNext.Click
        CM.Position += 1
    End Sub

    Private Sub CmdFind_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CmdFind.Click
        Dim objFind As New FrmFind
        Dim Dpt As String
        If objFind.ShowDialog = DialogResult.OK Then
            Dpt = objFind.Dept
            DV.Sort = "Dept"
            DV.RowFilter = "Dept='" & Dpt & "'"
        End If
    End Sub

    Private Sub CmdShowAll_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CmdShowAll.Click
        DV.RowFilter = ""
    End Sub
End Class

----------------------------------------------

The search Form (FrmFind)


Public Class FrmFind
    Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    Friend WithEvents CmdOK As System.Windows.Forms.Button
    Friend WithEvents TxtDept As System.Windows.Forms.TextBox
    Friend WithEvents Label1 As System.Windows.Forms.Label
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.CmdOK = New System.Windows.Forms.Button
        Me.TxtDept = New System.Windows.Forms.TextBox
        Me.Label1 = New System.Windows.Forms.Label
        Me.SuspendLayout()
        '
        'CmdOK
        '
        Me.CmdOK.Location = New System.Drawing.Point(48, 72)
        Me.CmdOK.Name = "CmdOK"
        Me.CmdOK.Size = New System.Drawing.Size(72, 24)
        Me.CmdOK.TabIndex = 0
        Me.CmdOK.Text = "OK"
        '
        'TxtDept
        '
        Me.TxtDept.Location = New System.Drawing.Point(40, 32)
        Me.TxtDept.Name = "TxtDept"
        Me.TxtDept.Size = New System.Drawing.Size(88, 20)
        Me.TxtDept.TabIndex = 1
        Me.TxtDept.Text = ""
        '
        'Label1
        '
        Me.Label1.Location = New System.Drawing.Point(32, 8)
        Me.Label1.Name = "Label1"
        Me.Label1.Size = New System.Drawing.Size(112, 16)
        Me.Label1.TabIndex = 2
        Me.Label1.Text = "Give Dept to Search"
        '
        'FrmFind
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(160, 125)
        Me.Controls.Add(Me.Label1)
        Me.Controls.Add(Me.TxtDept)
        Me.Controls.Add(Me.CmdOK)
        Me.Name = "FrmFind"
        Me.Text = "FrmFind"
        Me.ResumeLayout(False)

    End Sub

#End Region

    Dim mDept As String

    Public ReadOnly Property Dept() As String
        Get
            Dept = mDept
        End Get
    End Property

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CmdOK.Click
        mDept = TxtDept.Text
        Me.DialogResult = DialogResult.OK
    End Sub

End Class




0
 

Author Comment

by:matt_swinburne
ID: 13943463
I now use this code:

Main Form

Imports System.Data.OleDb
Public Class Form1
    Inherits System.Windows.Forms.Form
    Dim dv As DataView
    Dim cm As CurrencyManager

    Dim Constr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\matthewm\Desktop\File Tracking.mdb;Persist Security Info=False"

 Public Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim DA As New OleDbDataAdapter("Select * From Emp", Constr)
        Dim DT As New DataTable
>>   DA.Fill(DT)  << Error
        dv = DT.DefaultView
        cm = CType(Me.BindingContext(dv), CurrencyManager)

        TxtClient.DataBindings.Add("Text", dv, "ClientName")
        TxtMatterNumber.DataBindings.Add("Text", dv, "MatterNo")
        TxtCloseDate.DataBindings.Add("Text", dv, "CloseDate")
        TxtNotes.DataBindings.Add("Text", dv, "Notes")
        TxtBox.DataBindings.Add("Text", dv, "BoxNumber")
        TxtDateIn.DataBindings.Add("Text", dv, "DateIn")
        TxtStoreDate.DataBindings.Add("Text", dv, "StoreDate")
        TxtDateRemoved.DataBindings.Add("Text", dv, "RemoveDate")
        TxtDestroyDate.DataBindings.Add("Text", dv, "DestroyDate")

        'Load Originating Location Combo
        CmbLocation.DataSource = dv
        CmbLocation.DisplayMember = "LocationName"
        CmbLocation.ValueMember = "LocationID"
        CmbLocation.DataBindings.Add("selectedvalue", dv, "LocationID")


        'Load FeeEarner ComboBox
        CmbFeeEarner.DataSource = dv
        CmbFeeEarner.DisplayMember = "FeeEarnerName"
        CmbFeeEarner.ValueMember = "FeeEarnerID"
        CmbFeeEarner.DataBindings.Add("selectedvalue", dv, "FeeEarnerID")

        'Load MatterType Combobox
        CmbMatter.DataSource = dv
        CmbMatter.DisplayMember = "MatterTypeName"
        CmbMatter.ValueMember = "MatterTypeID"
        CmbMatter.DataBindings.Add("selectedvalue", dv, "MatterTypeID")

        'Load Status Combobox
        CmbStatus.DataSource = dv
        CmbStatus.DisplayMember = "StorageName"
        CmbStatus.ValueMember = "StorageID"
        CmbStatus.DataBindings.Add("selectedvalue", dv, "StatusID")

        'Load Office Combobox
        CmbOffice.DataSource = dv
        CmbOffice.DisplayMember = "OfficeName"
        CmbOffice.ValueMember = "OfficeID"
        CmbOffice.DataBindings.Add("selectedvalue", dv, "OfficeName")

    End Sub

    Private Sub NavigationButtons_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnFirst.Click, BtnLast.Click, BtnNext.Click, BtnPrevious.Click
        Select Case sender.Name
            Case "BtnFirst"

                cm.Position = 0

            Case "BtnPrevious"

                cm.Position -= 1

            Case "BtnNext"

                cm.Position += 1

            Case "BtnLast"

                cm.Position = DataSet11.Tables("Main").Rows.Count - 1

        End Select

    End Sub




    Private Sub BtnMainSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnMainSearch.Click
        Dim objFind As New Search
        Dim Name As String
        If objFind.ShowDialog = DialogResult.OK Then
            Name = objFind.Name
            dv.Sort = "ClientName"
            dv.RowFilter = "ClientName='" & Name & "'"
        End If
    End Sub
End Class


Search Form:

Public Class Search
    Inherits System.Windows.Forms.Form
    Dim mName As String

Public ReadOnly Property Client() As String
        Get
            Name = mName
        End Get
    End Property


    Private Sub BtnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnSearch.Click
        mName = TxtSearch.Text
        Me.DialogResult = DialogResult.OK

    End Sub
End Class

However I get an error on the line:

DA.Fill(DT) -- On the main Form

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll
0
 

Author Comment

by:matt_swinburne
ID: 13943537
Sorry blatent error:      Dim DA As New OleDbDataAdapter("Select * From Emp", Constr)

    Dim DA As New OleDbDataAdapter("Select * From Main", Constr)

Now i get  Could not bind to the new display member.

on

  CmbStatus.ValueMember = "StorageID"
0
 
LVL 19

Expert Comment

by:arif_eqbal
ID: 13943544
does your database "File Tracking.mdb" have a table called Emp ??
because we are trying to fill the datatable DT with Emp table.

Also put the Fill command inside a Try Block, that way you'll get a meaningful Error, right now the error message is generic, you can't tell waht the problem is, so put a Try block
like this

Dim DA As New OleDbDataAdapter("Select * From Emp", Constr)
        Dim DT As New DataTable
       
       Try
              DA.Fill(DT)  
       Catch Ex as Exception
            Msgbox Ex.Message  '<--- Here you'll get a more meaningful error message
       End Try

        dv = DT.DefaultView
        cm = CType(Me.BindingContext(dv), CurrencyManager)
        .......
 
0
 
LVL 19

Expert Comment

by:arif_eqbal
ID: 13943554
Ok
does the table "Main" have these two fields
"StorageID" and "StorageName"

there's some problem here

'Load Status Combobox
        CmbStatus.DataSource = dv
        CmbStatus.DisplayMember = "StorageName"
        CmbStatus.ValueMember = "StorageID"  '<---- StorageID  ??
        CmbStatus.DataBindings.Add("selectedvalue", dv, "StatusID")  '<---- StatusID ??

should it be StorageID or StatusID ??
It can be only one of them....
0
 

Author Comment

by:matt_swinburne
ID: 13943588
The table Main has Status ID and the table Status has two columns;

StorageID and StorageName

0
 
LVL 19

Expert Comment

by:arif_eqbal
ID: 13943651
You can not bind like that.
We are telling the comboBox to bind to column StatusID from source dv
--->   CmbStatus.DataBindings.Add("selectedvalue", dv, "StatusID")  
Now the Combo will look for the ValueMember & DisplayMember in the same source DV
but dv does not have StorageID and StorageName, so the error

0
 

Author Comment

by:matt_swinburne
ID: 13943762
Hi Arif,  This isnt my database which is why it isnt very well constructed.  Went into the database and changed the field names to match.  Now works.  

1st Problem:
Database access seems reeaally slow.

2nd Problem:
The search doesnt return any results just leaves the fields blank

3rd Problem:
I would like the user to select from a dropdown menu on the search form to pick whether they would like to search by Boxno, matter no or name

Is all this possible do you know?
0
 
LVL 19

Accepted Solution

by:
arif_eqbal earned 500 total points
ID: 13944212
Ok so it works
As for the slow thing, it might be taking time to show the forms (accessing data) but once fully shown it should work fine.

Yes you can have a choice what field to search on, you'll need more than one property now, one for each field. Then check which one he has entered data into and search based on that.

And it is not showing any value after search??
that means the condition did not search anything...
debug and check the value for the search condition i.e. whether the Name variable is set properly and if we have that value in the database...

Private Sub BtnMainSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnMainSearch.Click
        Dim objFind As New Search
        Dim Name As String
        If objFind.ShowDialog = DialogResult.OK Then
            Name = objFind.Name '<--- Check if Name is fine
            dv.Sort = "ClientName"
            dv.RowFilter = "ClientName='" & Name & "'"
        End If
    End Sub
End Class
0
 

Author Comment

by:matt_swinburne
ID: 13956144
I got it working thanks after a  few adjustments
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses
Course of the Month20 days, 23 hours left to enroll

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question