Solved

Dataset convert into array

Posted on 2009-05-05
4
707 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

710 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