search dataset?
-
-
-
normally id just create a loop and go through all of data row by row... You could function that as well, to allow you to use the same code for different sets of columns and values... If anyone else knows something easier id like to know as well...
-
'Make a fake DS and Table Dim DS As New DataSet With DS.Tables.Add("Account Numbers") .Columns.Add("FName") .Columns.Add("LName") .Columns.Add("ACCTNO") End With 'FName |LName |ACCTNO | '------------------------ ' | | | ' | | | For Each r As DataRow In DS.Tables("Account Numbers").Rows() If r.Item("FName") = "John" And r.Item("LName") = "Doe" Then 'DO SOMETHING End If 'Modify a specific value Select Case r.Item("FName") Case "John" r.Item("ACCTNO") = "J" & CType(r.Item("ACCTNO"), String) Case "Dan" r.Item("ACCTNO") = "D" & CType(r.Item("ACCTNO"), String) End Select Next 'to return a row number Dim cnt As Int32 = 0 Dim rownum As Int32 While DS.Tables("Account Numbers").Rows.Count() > cnt With DS.Tables("Account Numbers") If CType(.Rows(cnt).Item("FName"), String) = "Joe" Then 'if in a function return cnt 'otherwise change a var out of scope rownum = cnt End If End With cnt += 1 End While you can function these out... i hope it helps... also you can return full rows instead of just the offset number if youd like... if you cant read the code, cutting and pasting it in to vb will reset it to something you actually can follow.
-
Hi Brit, search through your dataset/datatable using the datatables "Select" method. It takes a string parameter in the form of a T-Sql like select statement. ex: 'create your dataset and datatable etc... Dim ds as New Dataset("myDataset") Dim dt as New DataTable("myDataTable") dt.Columns.Add(New Datacolumn("columnOne", sqldbtype.varchar) dt.Columns.Add(New DataColumn("columnTwo", sqldbtype.varchar) 'some code to populate the table here... 'search column one for 'Foo'... dim drResultsColOne() as Datarow = ds.Tables("myDataTable").Select("columnOne = 'Foo'") 'search column two for 'Foo'... dim drResultsColTwo() as Datarow = ds.Tables("myDataTable").Select("columnTwo = 'Foo'") 'or search both columns for 'Foo'... dim drResultsBoth() as Datarow = ds.Tables("myDataTable").Select("columnTwo = 'Foo' OR columnTwo='Foo'") the result is a datarow array that contains the datarows that match your search criteria. forgive any spelling errors, this was typed off the top of my head.
-jim
-
Hi Brit, search through your dataset/datatable using the datatables "Select" method. It takes a string parameter in the form of a T-Sql like select statement. ex: 'create your dataset and datatable etc... Dim ds as New Dataset("myDataset") Dim dt as New DataTable("myDataTable") dt.Columns.Add(New Datacolumn("columnOne", sqldbtype.varchar) dt.Columns.Add(New DataColumn("columnTwo", sqldbtype.varchar) 'some code to populate the table here... 'search column one for 'Foo'... dim drResultsColOne() as Datarow = ds.Tables("myDataTable").Select("columnOne = 'Foo'") 'search column two for 'Foo'... dim drResultsColTwo() as Datarow = ds.Tables("myDataTable").Select("columnTwo = 'Foo'") 'or search both columns for 'Foo'... dim drResultsBoth() as Datarow = ds.Tables("myDataTable").Select("columnTwo = 'Foo' OR columnTwo='Foo'") the result is a datarow array that contains the datarows that match your search criteria. forgive any spelling errors, this was typed off the top of my head.
-jim
-
no, if you were to search for 'Foo' it would not include any rows with 'Food' in column1 or column2, as Foo and Food are not equal. However the select statement does support the use of wildcards, so a search for 'Foo%' would include any rows with 'Food', 'Foos' or 'Fool' as well as, but not include rows with 'Ofoo' or 'Afoo' in columns 1 or 2. You can also use '%Foo%' to search for 'Afool', but note that if your datasource is large enough, you could see a performance hit when using wildcards on both sides of your search value. as for getting the data from the array, you would work with the datarwow array as you would with any other array. from my previous example: drResultsBoth(0) would give you the datarow at position 0 in the drResultsBoth array. typically you would enumerate over the array to perform some sort of change on a field in the datarow. ex: if (drResultsBoth.Length > 0) Then For Each currentRow as Datarow in drResults if (cstr(currentRow("ColumnOne")).trim = "Foo" Then currentRow("ColumnTwo") = "Bar" End If Next Else messagebox.show("No results were found for your search criteria!") End IF hope this helps
-jim
-
no, if you were to search for 'Foo' it would not include any rows with 'Food' in column1 or column2, as Foo and Food are not equal. However the select statement does support the use of wildcards, so a search for 'Foo%' would include any rows with 'Food', 'Foos' or 'Fool' as well as, but not include rows with 'Ofoo' or 'Afoo' in columns 1 or 2. You can also use '%Foo%' to search for 'Afool', but note that if your datasource is large enough, you could see a performance hit when using wildcards on both sides of your search value. as for getting the data from the array, you would work with the datarwow array as you would with any other array. from my previous example: drResultsBoth(0) would give you the datarow at position 0 in the drResultsBoth array. typically you would enumerate over the array to perform some sort of change on a field in the datarow. ex: if (drResultsBoth.Length > 0) Then For Each currentRow as Datarow in drResults if (cstr(currentRow("ColumnOne")).trim = "Foo" Then currentRow("ColumnTwo") = "Bar" End If Next Else messagebox.show("No results were found for your search criteria!") End IF hope this helps
-jim
Is it possible to search through the dataset using a select statement? If so how? That is really what I need. So if the user does search for 'foo' then I want to capture the first match. If there is a value of food and foot in the database, then which ever one is first in the database I will display. I really dont need an array. I just want to search for the value they enter and find the first closest match and display it. The only thing with using a select statement. I don't think I will be able to find out which row it found the closest match on. Will it? Thanks, Beginner in ASP.Net and VB.Net
-
Is it possible to search through the dataset using a select statement? If so how? That is really what I need. So if the user does search for 'foo' then I want to capture the first match. If there is a value of food and foot in the database, then which ever one is first in the database I will display. I really dont need an array. I just want to search for the value they enter and find the first closest match and display it. The only thing with using a select statement. I don't think I will be able to find out which row it found the closest match on. Will it? Thanks, Beginner in ASP.Net and VB.Net
>Is it possible to search through the dataset using a select statement? well, yeah. just keep in mind that the data is stored in the tables within your dataset. so if you want to search accross different tables you would have to run the select statement on each individual table separately. but it seems to me that if your database/dataset design is done correctly you should only have to worry about searching a single table; ideally, only a single field. in regards to displaying the first match... i would still recommend using the select method. i haven't seen any of your code or db design, so i'm not sure exactly what that looks like, but providing you have a primary key defined and you are carrying that over to your dataset when you load the data, (this should happen automatically if you are using ms sql) the select method of the datatable should use the primary key when searching your datatable and place the items into the array based on the primary key values. so when you do your select, even though you get back an array you can just take the first item iun the array, index 0, and display the value from the appropriate field. usually when implementing a search, you want to display more than just a single field value, for instance in the case of a product search, you could take all attributes of the row at array index 0 and place them into textboxes for display/manipulation or insert them into html for display on a webpage.
-jim
-
>Is it possible to search through the dataset using a select statement? well, yeah. just keep in mind that the data is stored in the tables within your dataset. so if you want to search accross different tables you would have to run the select statement on each individual table separately. but it seems to me that if your database/dataset design is done correctly you should only have to worry about searching a single table; ideally, only a single field. in regards to displaying the first match... i would still recommend using the select method. i haven't seen any of your code or db design, so i'm not sure exactly what that looks like, but providing you have a primary key defined and you are carrying that over to your dataset when you load the data, (this should happen automatically if you are using ms sql) the select method of the datatable should use the primary key when searching your datatable and place the items into the array based on the primary key values. so when you do your select, even though you get back an array you can just take the first item iun the array, index 0, and display the value from the appropriate field. usually when implementing a search, you want to display more than just a single field value, for instance in the case of a product search, you could take all attributes of the row at array index 0 and place them into textboxes for display/manipulation or insert them into html for display on a webpage.
-jim
-
'Make a fake DS and Table Dim DS As New DataSet With DS.Tables.Add("Account Numbers") .Columns.Add("FName") .Columns.Add("LName") .Columns.Add("ACCTNO") End With 'FName |LName |ACCTNO | '------------------------ ' | | | ' | | | For Each r As DataRow In DS.Tables("Account Numbers").Rows() If r.Item("FName") = "John" And r.Item("LName") = "Doe" Then 'DO SOMETHING End If 'Modify a specific value Select Case r.Item("FName") Case "John" r.Item("ACCTNO") = "J" & CType(r.Item("ACCTNO"), String) Case "Dan" r.Item("ACCTNO") = "D" & CType(r.Item("ACCTNO"), String) End Select Next 'to return a row number Dim cnt As Int32 = 0 Dim rownum As Int32 While DS.Tables("Account Numbers").Rows.Count() > cnt With DS.Tables("Account Numbers") If CType(.Rows(cnt).Item("FName"), String) = "Joe" Then 'if in a function return cnt 'otherwise change a var out of scope rownum = cnt End If End With cnt += 1 End While you can function these out... i hope it helps... also you can return full rows instead of just the offset number if youd like... if you cant read the code, cutting and pasting it in to vb will reset it to something you actually can follow.
-
'Make a fake DS and Table Dim DS As New DataSet With DS.Tables.Add("Account Numbers") .Columns.Add("FName") .Columns.Add("LName") .Columns.Add("ACCTNO") End With 'FName |LName |ACCTNO | '------------------------ ' | | | ' | | | For Each r As DataRow In DS.Tables("Account Numbers").Rows() If r.Item("FName") = "John" And r.Item("LName") = "Doe" Then 'DO SOMETHING End If 'Modify a specific value Select Case r.Item("FName") Case "John" r.Item("ACCTNO") = "J" & CType(r.Item("ACCTNO"), String) Case "Dan" r.Item("ACCTNO") = "D" & CType(r.Item("ACCTNO"), String) End Select Next 'to return a row number Dim cnt As Int32 = 0 Dim rownum As Int32 While DS.Tables("Account Numbers").Rows.Count() > cnt With DS.Tables("Account Numbers") If CType(.Rows(cnt).Item("FName"), String) = "Joe" Then 'if in a function return cnt 'otherwise change a var out of scope rownum = cnt End If End With cnt += 1 End While you can function these out... i hope it helps... also you can return full rows instead of just the offset number if youd like... if you cant read the code, cutting and pasting it in to vb will reset it to something you actually can follow.
I created a dataset through access database. I just need the search, or find engine to locate the files faster, if the files are too long. I would do the same thing as you suggested. Mine is an address book, and I also have a password dialog. How would I also can make the password dialog, if i want to change my password code? My addressbook consist of First Name, Last Name, Address, City, State, Zip Code, Email. I would like to have a search button with code. Then I use a select statement for every field, then when the dataset populates, I can use the search and input box to enter the name of the person I am looking for? Or do I have to enter in a case select every persons name within a dataset? bravo659