Results 1 to 11 of 11

Thread: Database record search Visual Basic 2010

  1. #1

    Thread Starter
    New Member
    Join Date
    Mar 2017
    Posts
    6

    Database record search Visual Basic 2010

    Hello all...! I'm new to programming and got stuck with an issue and was wondering if you could lend a hand. I have an Access database I'm building a front end for in VB 2010, I was able to successfully connect to the database to retrieve records. I built a search feature and was able to find records with it, but I have searched and searched the web and can't find the right way to accomplish my goal. On the search I need to only search records that are marked "Active" as per the "Active" checkbox, I have tried all possible combinations and I cannot get it to work, here is my code:

    Code:
    Dim searchString As String = txtAssetTagSearch.Text
    Dim dataRow() As DataRow = AMSDataSet.tblITEquipment.Select("AssetTag like '%" & searchString & "%'", "AssetTag")
    If dataRow.GetUpperBound(0) >= 0 Then
    Dim index As Integer
    index = TblITEquipmentBindingSource.Find("AssetTag", dataRow(0).Item("AssetTag").ToString)
    TblITEquipmentBindingSource.Position = index
    
    Else
    
    MsgBox("Record NOT Found!", MsgBoxStyle.Exclamation, "Asset Tag Search")
    txtAssetTagSearch.Text = ""
    
    End If
    I have a textbox, txtAssetTagSearch to put the search string and a search button. He above code works fine, but I need to somehow get it to only searche records whose "Active.checked = True" checkbox and if not found, to return record not found. The above returns both active and in-active records...

    ANY help would be GREATLY appreciated, THANKS in advance!

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Database record search Visual Basic 2010

    So if you want to select the records where the AssetTag is like a string AND the Active field is true....


    read that a couple times... let it sink in...

    It's not complicated.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    New Member
    Join Date
    Mar 2017
    Posts
    6

    Re: Database record search Visual Basic 2010

    Quote Originally Posted by techgnome View Post
    So if you want to select the records where the AssetTag is like a string AND the Active field is true....


    read that a couple times... let it sink in...

    It's not complicated.

    -tg
    Gee thanks, I'm a newbe, not an idiot... I have tried many different combinations of the above using "AND" and "&", among others, and such and will not work, if it were easy, at least for me, I would not be asking for help...

  4. #4
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Database record search Visual Basic 2010

    If I thought you were an idiot, the post would have been a lot different. I've seen even the most experienced miss the obvious sometimes... I try to push people in the right direction rather than out right posting of code if I can help it.

    So... that said, I'm guessing your check box is tied to a field in the datatable that's bound to the grid, right? THAT's what you should be looking at. Active.Checked is meaningless the underlying table doesn't know what that means. But it does know what Active=True means... that's why I underlined that part: AND Active=True ... although in a bit more verbose manner...

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5

    Thread Starter
    New Member
    Join Date
    Mar 2017
    Posts
    6

    Re: Database record search Visual Basic 2010

    Quote Originally Posted by techgnome View Post
    If I thought you were an idiot, the post would have been a lot different. I've seen even the most experienced miss the obvious sometimes... I try to push people in the right direction rather than out right posting of code if I can help it.

    So... that said, I'm guessing your check box is tied to a field in the datatable that's bound to the grid, right? THAT's what you should be looking at. Active.Checked is meaningless the underlying table doesn't know what that means. But it does know what Active=True means... that's why I underlined that part: AND Active=True ... although in a bit more verbose manner...

    -tg
    Ok, maybe I misunderstood your intentions, at this point I have searched the researched the web and cannot find anything similar that would help, that is why I decided to ask for help here...

    Indeed the checkbox is tied to a field in the database, "Active" and yes, I have looked at that as well and figured out that the checkbox itself, Active.checked, is not understood by the table. I'm working with a form not a grid and I have tried different combinations of Active=True and others in both the DataRow line and the index line...

  6. #6
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Database record search Visual Basic 2010

    I saw the BindingSource and thought there was a grid involved... ok... so there isn't a grid... still, as part of the select from the dataset, you should be able to just include the Active = True as part of that:
    Code:
    Dim dataRow() As DataRow = AMSDataSet.tblITEquipment.Select("(Active=True) and (AssetTag like '%" & searchString & "%')", "AssetTag")
    It just clicked... the select tells it what you are selecting... you still need a where...
    try this:
    Code:
    Dim dataRow() As DataRow = AMSDataSet.tblITEquipment.Select("AssetTag like '%" & searchString & "%'", "AssetTag").Where("Active=True")
    Yeah, it's not going to understand what Active.Checked means... that's an object on the form. The data has no knowledge of such things. It's kind of like using your remote to turn on your neighbor's TV... doesn't quite work. Your remote doesn't know anything about the other TV.

    Next question... Ok, this should work for when the checkbox is marked... what about when it isn't? Should ALL rows then be returned, or should it return both active and inactive records?

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  7. #7

    Thread Starter
    New Member
    Join Date
    Mar 2017
    Posts
    6

    Re: Database record search Visual Basic 2010

    Quote Originally Posted by techgnome View Post
    I saw the BindingSource and thought there was a grid involved... ok... so there isn't a grid... still, as part of the select from the dataset, you should be able to just include the Active = True as part of that:

    It just clicked... the select tells it what you are selecting... you still need a where...
    try this:
    Code:
    Dim dataRow() As DataRow = AMSDataSet.tblITEquipment.Select("AssetTag like '%" & searchString & "%'", "AssetTag").Where("Active=True")
    Yeah, it's not going to understand what Active.Checked means... that's an object on the form. The data has no knowledge of such things. It's kind of like using your remote to turn on your neighbor's TV... doesn't quite work. Your remote doesn't know anything about the other TV.

    Next question... Ok, this should work for when the checkbox is marked... what about when it isn't? Should ALL rows then be returned, or should it return both active and inactive records?

    -tg
    Yeah, I had tried that, but as soon as I finish typing the line, it gets underlined blue with error "Overload resolution failed because no accessible 'Where' can be called with these arguments".

    In response to your question, when Active=True, it should only return an item that is set to true, if not it should return record not found, and when set to False it should return any that are false , otherwise you should get the record not found. I'll then need to add a loop or if statement to continue searching if user wants to... I have all of this working on the access database with VBA, just want to create a VB front end, but unfortunately as you probably know the VBA code is not the same... I'm manually converting all parts of the vba to VB...

  8. #8
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Database record search Visual Basic 2010

    Well, to be fair, I don't use typed datasets/tables... so I'm kinda shooting in the dark here. I typically just use DataTables (and the occasional DataSet), just not typed, which I find makes it a whole lot easier to bend it to my will.

    And yeah the jump from VBA to VB.NET is a pretty substantial one.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  9. #9

    Thread Starter
    New Member
    Join Date
    Mar 2017
    Posts
    6

    Re: Database record search Visual Basic 2010

    Quote Originally Posted by techgnome View Post
    Well, to be fair, I don't use typed datasets/tables... so I'm kinda shooting in the dark here. I typically just use DataTables (and the occasional DataSet), just not typed, which I find makes it a whole lot easier to bend it to my will.

    And yeah the jump from VBA to VB.NET is a pretty substantial one.

    -tg
    If you have a better way of doing it, I'm willing to try..., Here is my working VBA code:

    Code:
    If IsNull(txtAssetTagSearch) = True Then
        MsgBox "Please Enter Tag or Partial Tag to Search.", vbOKOnly + vbInformation, "Asset Tag Search"
        Exit Sub
    End If
    
    If Me.chkActiveRecs = True Then
                
           Me.Recordset.FindFirst "AssetTag Like '*" & txtAssetTagSearch & "*' And [Active] = True"
            
        If Me.Recordset.NoMatch Then
            MsgBox "No Record Found", vbOKOnly + vbInformation, "Asset Tag Search"
            
            Me!txtAssetTagSearch = Null
        
            Else
        
                Do While Not Me.Recordset.NoMatch
                
                    If MsgBox("Continue Searching?", vbYesNo + vbQuestion, "AMS - Asset Tag Search") = vbNo Then
                        
                        Me!txtAssetTagSearch = Null
                        Exit Sub
                        
                    End If
                    
                    Me.Recordset.FindNext "AssetTag Like '*" & txtAssetTagSearch & "*' And [Active] = True"
                    
                Loop
                    
            Me!txtAssetTagSearch = Null
            
        End If
            
    End If
    
    If Me.chkActiveRecs = False Then
            
        Me.Recordset.FindFirst "AssetTag Like '*" & txtAssetTagSearch & "*' And [Active] = False"
            
        If Me.Recordset.NoMatch Then
            MsgBox "No Record Found", vbOKOnly + vbInformation, "Name Search"
            
            Me!txtAssetTagSearch = Null
        
            Else
        
                Do While Not Me.Recordset.NoMatch
                
                    If MsgBox("Continue Searching?", vbYesNo + vbQuestion, "Asset Tag Search") = vbNo Then
                        
                        Me!txtAssetTagSearch = Null
                        Exit Sub
                        
                    End If
                    
                    Me.Recordset.FindNext "AssetTag Like '*" & txtAssetTagSearch & "*' And [Active] = False"
                        
                Loop
                
            Me!txtAssetTagSearch = Null
        
        End If
    End If

  10. #10

    Thread Starter
    New Member
    Join Date
    Mar 2017
    Posts
    6

    Re: Database record search Visual Basic 2010

    Anyone have any ideas they can share? Going crazy here, lol!

  11. #11
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Amsterdam...
    Posts
    5,343

    Re: Database record search Visual Basic 2010

    Ive not used .Net.. got it tinstalled but um well ... never got around to using it....

    I think you only need the one loop as you are returning only true (active) or false (not active)

    Code:
     Me.Recordset.FindFirst "AssetTag Like '*" & txtAssetTagSearch & "*' And [Active] = " & Me.chkActiveRecs.Value
            
        If Me.Recordset.NoMatch Then
            MsgBox "No Record Found", vbOKOnly + vbInformation, "Name Search"
            
            Me.txtAssetTagSearch = Null
        
       Else
        
                Do While Not Me.Recordset.NoMatch
                
                    If MsgBox("Continue Searching?", vbYesNo + vbQuestion, "Asset Tag Search") = vbNo Then
                        
    '---- not sure you need the Me. 
    '---- not sure the ! is a good idea... always hated it in Access VBA
                        txtAssetTagSearch = Null
                        Exit Loop
                        
                    End If
                    
                    Me.Recordset.FindNext "AssetTag Like '*" & txtAssetTagSearch & "*' And [Active] = " & Me.chkActiveRecs.Value
                        
                Loop
                
            Me.txtAssetTagSearch = Null
        
        End If
    From what I remember of VB / VBA (Excel) - it can be picky about the exact value you want to return... or text. Probably best to try with .Value

    Also i am unsure of the forms .RecordSet ... This is VB(.Net)? Are you sure there is a recordset to use?

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width