Solved

Dataset convert into array

Posted on 2009-05-05
4
708 Views
Last Modified: 2013-11-08
I have a dataset that the user has changed in a Datagrid.  I want to take one column of data, i.e. sales order # and get an array so I can use the list in another SQL lookup for more details on the order number.

Sample
ord_no    Cust_no    Address
1              3334           1 Test Dr.
2               3336           5 Test Dr.
6              3339            9 Test Dr.

Now I need to get the ord_no from the dataset as the following
(1, 2, 6)
Reason is so I can place into SQL command Select * from Order_details where ord_no in (1, 2, 6)
0
Comment
Question by:kwitcom
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 15

Expert Comment

by:oobayly
ID: 24305599
Just create an array of the correct size (and type), and then just loop through each row of the table in the dataset & populate the array
Dim table As DataTable = ds.Tables("TableName")
Dim values As New Integer(table.Rows.Count -1)
For i As Integer = 0 to values.Length - 1
  values(i) = CInt(table.Rows(i)("ord_no"))
Next

Open in new window

0
 
LVL 15

Expert Comment

by:mohan_sekar
ID: 24305602
You need to loop through the dataset to create an array
0
 
LVL 11

Accepted Solution

by:
srikanthreddyn143 earned 500 total points
ID: 24305623
Dim sString As String = String.Empty
For Each row As DataRow In Ds.Table(0).Rows

sString = sString & row("Order#") & ","

Next

sString = sString.Remove(sString.Length-1)

Dim sSQL As String = "Select * from Order_details where ord_no in (" & sString & ")"
0
 
LVL 15

Expert Comment

by:oobayly
ID: 24305773
If you're going to concatenate a string in a loop of an unknown (at design time) number of items, I'd highly recommend using either StringBuilder or creating a String array, and using Join.

The reason being that a string is immutable, ie. a new copy is created every time you modify it.

Of course, don't go overboard with StringBuilder, if you know the loop will be only 3 items there's no need for it. If there could be 100+ items, it's probably worth using.
Dim values As New String(table.Rows.Count - 1)
'' Populate String array
 
Dim query As String = "SELECT ... IN (' & String.Join(",", values) & ")"

Open in new window

0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month7 days, 18 hours left to enroll

617 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