list box primary key vb .net

  • Thread starter Thread starter mcotter
  • Start date Start date
M

mcotter

Does anyone know how to populate a list box using SqlDataReader. I am
using VB .NET 2005. Along with the list box value that is visible I
need it's primary key value that is not visible.
 
mcotter,

Probably the easiest way is to load a datatable with the datareader (the
datatable has a Load method that accepts a datareader) and then bind the
datatable to the listbox, using the listbox's DisplayMember, ValueMember and
DataSource properties.

Kerry Moorman
 
The Listbox control can be bound to a single column via a DataReader in an
ASP.NET program, but not directly to the ListBox control in a Windows forms
application. That would take using code (something) like this

Private Sub BuildCommand()
Try
cmd = New SqlCommand("SELECT Au_ID, Author, Year_Born FROM
Authors " _
& " WHERE Year_Born = @YearWanted", cn)
cmd.Parameters.Add("@YearWanted", SqlDbType.Int).Value = 1947
cn.Open()
Dim dr As SqlDataReader
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
Dim tb As New DataTable
tb.Load(dr)
ListBox1.DisplayMember = "Author"
ListBox1.DataSource = tb
Catch exsql As SqlException
MessageBox.Show(exsql.ToString)
Catch ex As Exception
Debug.Assert(False, ex.ToString)
Finally
cn.Close()
End Try
End Sub
--

FMI see my 7th Edition.
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________
 
Thanks. Your code works, however, how do I return the Primary Key,
pkCarID, from the list box when I select an item in the list box.
Here is the code I am using

Dim dr As SqlDataReader
Dim dt As New DataTable

cmd = conn.CreateCommand
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "Health.spSelectCarType"

dr = cmd.ExecuteReader
dt.Load(dr)
dr.Close()

lstStandard.Items.Clear()
lstStandard.ValueMember = "pkCarID"
lstStandard.DisplayMember = "CarType"
lstStandard.DataSource = dt
 
The items displayed in a Listbox are in a collection. I would use the
current row index to index the Rows collection of the bound DataTable.

Private Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged
Dim strPK As String = ""
Dim intIndex As Integer
intIndex = ListBox1.SelectedIndex
strPK = tb.Rows(intIndex).Item("AU_ID").ToString
textbox1.text = strPK

End Sub


--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________
 
Hi,

Just a question, why do you want to use the in this case insufficient
SqlDataReader, while this can be your code.

\\\
Sub X
Dim connection as New SqlConnection(TheConnectionString)
Dim dt as New DataTable
dim da = New SqlDataAdapter(SqlString,connection)
da.Fill(dt)
ListBox1.DataSource = dt
ListBox1.DisplayMember = "TheMember"
ListBox1.ValueMember = "TheValue"
End Sub
///

Void X
{
DataTable dt = new DataTable;
SqlDataAdapterdim da = new
SqlDataAdapterDataAdapter(SqlString,connection);
da.Fill(dt);
ListBox1.DataSource = dt;
ListBox1.DisplayMember = "TheMember";
ListBox1.ValueMember = "TheValue";
}

Cor
 
Back
Top