How to populate arraylist with SQL query?

Question

I am developing a VB.NET ASPX file. This report is currently working but now I want to add a parameter which should be an array list displaying all records from below SQL query:

" select distinct instrument_name AS instrument_name from FRUD.tblXref order by instrument_name "

But this array list displays "System.Data.Common" for all possible values from code:

Sub Main()          
Dim pcSQL As String
Dim ProductList As New ArrayList()

pcSQL = " select distinct instrument_name AS instrument_name from FRUD.tblXref order by instrument_name "
Dim DBConn As SqlConnection
DBConn = New SqlConnection(ConfigurationManager.AppSettings("AMDMetricsConnectionString"))
DBConn.Open()
Dim reader As SqlDataReader
Dim DBCommand As New SqlCommand(pcSQL, DBConn)
reader = DBCommand.ExecuteReader()

dProdCodeSearch.DataSource = reader
dProdCodeSearch.DataBind()
reader.Close()

I am sure I am doing something wrong which is a really simple fix. This SQL Connection works for my data tables in this report. But this is only parameter that I set to SQL output.

Answer 1

You need to create a Collection that is storing the values from the database and then read those values into an array. Something like

Dim instrumentNames As New List(Of String)

While reader.Read()
   instrumentNames.Add(reader.GetString("insturment_name"))
End While

dProdCodeSearch.DataSource = insturmentNames