list box primary key vb .net

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.

KerryMoorman
Wed Jan 30 09:33:01 CST 2008

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


"mcotter@frontiernet.net" wrote:

> 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.
>

William
Wed Jan 30 11:37:12 CST 2008

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)
____________________________________________________________________________________________
<mcotter@frontiernet.net> wrote in message
news:62e9a10b-a3cc-4567-a322-4d810a91f2fd@e10g2000prf.googlegroups.com...
> 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
Thu Jan 31 04:18:13 CST 2008

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

------------------------------------------------------------
Procedure spSelectCarType
Select pkCarID, CarType from tblCarType

William
Thu Jan 31 13:07:29 CST 2008

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)
____________________________________________________________________________________________
<mcotter@frontiernet.net> wrote in message
news:bdb8ec63-1dac-42c3-acc3-080a7acf4f73@v17g2000hsa.googlegroups.com...
> 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
>
> ------------------------------------------------------------
> Procedure spSelectCarType
> Select pkCarID, CarType from tblCarType
>

KerryMoorman
Thu Jan 31 13:14:03 CST 2008

mcotter,

Use the listbox's SelectedValue property,

Kerry Moorman


"mcotter@frontiernet.net" wrote:

> 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
>
> ------------------------------------------------------------
> Procedure spSelectCarType
> Select pkCarID, CarType from tblCarType
>
>

Cor
Thu Jan 31 22:57:26 CST 2008

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