Help with creating string variable for records found


Ho do you build string variable with values of data element ID when a record is found using VB.NET?, for example if my search finds 4 records, how do i include their IDs in a string? ('2','4','5','6')

I am using a databale to hold the results.


Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jacques Bourgeois (James Burger)PresidentCommented:
I do not have time to build a test procedure, but this should be close to what you are looking for:

		Dim yourString As String = "("

		For Each row As DataRow In yourtable.Rows
			yourString &= "'" & CStr(row.Item("yourField")) & "',"
		yourString = yourString.Substring(0, yourString.Length - 1) & ")"

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Fernando SotoRetiredCommented:
Hi Victor;

Here is another solution using linq. In the code below the data is in a DataTable object called dt and the column name called ID.

Dim yourIDs As String = (From id In dt.AsEnumerable() _
                         Select id).Aggregate("(", _
                           Function(final, nextId) _
                           final & "'" & nextId.Field(Of String)("ID") & "', "). _
                           TrimEnd(New Char() {",", " "}) & ")"

Open in new window

vcharlesAuthor Commented:

Thank you for the codes.

How do I  integrate your code with the code in Part A to create string variable yx1 from FilteredDTA?

Afterwards I can use the code in Part B where yx1 includes the string values of the IDs.

Part A:

Dim vc12 As Integer
        vc1 = 0
        vc12 = 0
        If FormGlobalMain.ComboBox1.SelectedIndex = 4 Then
            For Each Srow In FormGlobalMain.C1Data.SelectedRows
                If vc12 > 0 Then
                    yx = yx & "," & "'" & FormGlobalMain.C1Data.Columns("NSN").CellText(Srow) & "'"
                    yx = "'" & FormGlobalMain.C1Data.Columns("NSN").CellText(Srow) & "'"
                End If

                vc12 = vc12 + 1
        End If

  fsLinkAOP5 = New System.IO.FileStream(Application.StartupPath + "\aop5.xml", IO.FileMode.Open)

If yx <> "" Then
            Dim SearchCriteria As String = "NSN IN (" & yx & " )"
            Dim FilteredDTA As DataTable
            Dim DVA As New DataView(dtsetLinkAOP5.Tables(0), SearchCriteria, Nothing, DataViewRowState.CurrentRows)
            FilteredDTA = DVA.ToTable
End If

'Code needed to extract unique ID values to create yx1


Part B:

Dim SearchCriteria1 As String = "ID IN (" & yx1 & " )"
            Dim FilteredDTA1 As DataTable
            Dim DVA As New DataView(dtsetLinkAOP5.Tables(0), SearchCriteria1, Nothing, DataViewRowState.CurrentRows)
            FilteredDTA1 = DVA.ToTable
            If FilteredDTA1.Rows.Count > 0 Then
            Me.C1AOP5.DataSource = FilteredDTA1

Open in new window

Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

vcharlesAuthor Commented:

I was able to integrate the code with my application. The p
vcharlesAuthor Commented:
Hi again,

The first solution works but the second solution returns error message " Aggregate is not a member of system.Data.EnumerableRowCollection (Of Sydtem.Data.Row)

How do I fix this error?
Also, How do I remove duplicates in a string and reorder values?

For example if I have S = '5','7','5','4'  How do I obtain S = '4','5','7'?


Jacques Bourgeois (James Burger)PresidentCommented:
If the first solution works, why don't you simply use it?

The second one does not work it you case because it uses an aggregate. An aggregate groups together all the rows that have the same value for the requested field(s), and is usually used to compute sums, counts and the likes on each group.

Fernando probably proposed it because you where working on an ID, and IDs are often unique in a table. Because you have duplicates, something that was not evident from your original question, it cannot be applied to your case.
vcharlesAuthor Commented:
Thanks for your comment.
vcharlesAuthor Commented:
Thank You.
Fernando SotoRetiredCommented:
Please post your code to show how you implemented the code. The code was tested before posting but of course it all depends on how I interpreted the question and how you implemented it.
Jacques Bourgeois (James Burger)PresidentCommented:

vcharles did not specify that there were duplicates in his IDs, so you aggregate filtered them out.
vcharlesAuthor Commented:

The code was used as follows:

Dim S As String = (From SN In FilterData.AsEnumerable() _ Select SN).Aggregate("(", _ Function(final, nextId) _ final & "'" & nextId.Field(Of String)("SN") & "', "). _ TrimEnd(New Char() {",", " "}) & ")"
Fernando SotoRetiredCommented:
@James, To your statement, "An aggregate groups together all the rows that have the same value for the requested field(s), and is usually used to compute sums, counts and the likes on each group.", From Microsoft Documentation simplified, "The Aggregate(Of TSource)(IEnumerable(Of TSource), Func(Of TSource, TSource, TSource)) method makes it simple to perform a calculation over a sequence of values. This method works by calling func one time for each element in source. Each time func is called, passes both the element from the sequence and an aggregated value (as the first argument to func). The first element of source is used as the initial aggregate value. The result of func replaces the previous aggregated value. Returns the final result of func.", they are not Grouped.
Fernando SotoRetiredCommented:
@Victor, It sounds like from the error message that you are missing a reference to some dll maybe. Does your project have a reference to System.Data.DataSetExtensions.dll? If it does and you still have an error just use the first solution.
Fernando SotoRetiredCommented:

If you got my version working then this version will remove duplicates from the results.

Dim S As String = (From id In dt.AsEnumerable() _
                   Select id.Field(Of String)("ID")). _
                     Distinct().Aggregate("(", _
                     Function(final, nextId) _
                     final & "'" & nextId & "', "). _
                     TrimEnd(New Char() {",", " "}) & ")"

Open in new window

Jacques Bourgeois (James Burger)PresidentCommented:
A little editorial about LINQ then ;-)

Things are different in other fields. The verb aggregate does not implicitely means grouping.

But in computing, I have always encountered aggregate functions as a way of grouping elements of a set on a given common value or condition, and perform operations on each group thus formed. No matter in which reference you look, this is always what is implied by aggregate in relation to computing and more specifically in data processing, which is what LINQ is supposed to be about. I thus automatically inferred that this was the problem with your suggestion, that would probably work by the way if the IDs where unique.

... a function where the values of multiple rows are grouped together as input on certain criteria to form a single value of more significant meaning or measurement such as a set, a bag or a list. (Wikipedia)

... a function that performs a computation on a set of values rather than on a single value. For example, finding the average or mean of a list of numbers is an aggregate function. (Webopedia)

Data aggregation is any process in which information is gathered and expressed in a summary form, for purposes such as statistical analysis. A common aggregation purpose is to get more information about particular groups based on specific variables such as age, profession, or income. (TechTarget)

From VB6VB6, to SQL Server, to
the list of Aggregate Functions in Transact-SQL or in Microsoft Access, aggregation has always, in my experience, been a way of grouping things together.

Even the official Aggregation Operations page from the LINQ documentation goes that way. They have a Contanetation Operations page however, that makes more sense than their example where they use Aggregate to perform a concatenation.

So, now they would have an aggregate function that does not group. One more reason to dislike LINQ, where they take standard paradigms and decide uniterally to change them for some reason.
vcharlesAuthor Commented:
Hi Fernando,

I imported the dll file but it did not work, will use the first solution.

Thank you for all your inputs
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.