-
Mar 28th, 2017, 07:12 AM
#1
Thread Starter
New Member
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!
-
Mar 28th, 2017, 08:32 AM
#2
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
-
Mar 28th, 2017, 08:56 AM
#3
Thread Starter
New Member
Re: Database record search Visual Basic 2010
Originally Posted by techgnome
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...
-
Mar 28th, 2017, 10:03 AM
#4
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
-
Mar 28th, 2017, 01:33 PM
#5
Thread Starter
New Member
Re: Database record search Visual Basic 2010
Originally Posted by techgnome
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...
-
Mar 28th, 2017, 02:07 PM
#6
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
-
Mar 28th, 2017, 02:26 PM
#7
Thread Starter
New Member
Re: Database record search Visual Basic 2010
Originally Posted by techgnome
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...
-
Mar 28th, 2017, 02:33 PM
#8
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
-
Mar 28th, 2017, 02:49 PM
#9
Thread Starter
New Member
Re: Database record search Visual Basic 2010
Originally Posted by techgnome
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
-
Mar 30th, 2017, 08:31 AM
#10
Thread Starter
New Member
Re: Database record search Visual Basic 2010
Anyone have any ideas they can share? Going crazy here, lol!
-
Mar 30th, 2017, 03:29 PM
#11
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?
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|