Matrix1000
asked on
Filling a List from SQL query - what am I doing wrong?
I'm trying to fill a listbox with an sql query and it works great...except I want to have multiple things/columns per line.
This is what Ive got so far and it returns just the stock#'s to the listbox...how can add a few more things to each line like ,Year,Make,Model,Color
cmd.Connection = conn
cmd.CommandType = CommandType.Text
cmd.CommandText = "SELECT StockNumber,Year,Make,Mode l,Color FROM dbo.VehicleMaster ORDER BY StockNumber"
Try
cmd.Connection.Open()
dr = cmd.ExecuteReader
While dr.Read
lstStocknumberList.Items.A dd(dr.GetV alue(dr.Ge tOrdinal(" StockNumbe r")).ToStr ing)
End While
Catch ex As SqlException
MessageBox.Show(ex.Message )
Finally
cmd.Connection.Close()
End Try
This is what Ive got so far and it returns just the stock#'s to the listbox...how can add a few more things to each line like ,Year,Make,Model,Color
cmd.Connection = conn
cmd.CommandType = CommandType.Text
cmd.CommandText = "SELECT StockNumber,Year,Make,Mode
Try
cmd.Connection.Open()
dr = cmd.ExecuteReader
While dr.Read
lstStocknumberList.Items.A
End While
Catch ex As SqlException
MessageBox.Show(ex.Message
Finally
cmd.Connection.Close()
End Try
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Assuming WinForms application, not ASP.NET.
ASKER
Yes WinForms...
Ok did that but now how do I fill the list with the items....say record #1 contained |Stock#|Year|Make|Model|Co lor
how do I modify my listitems.add code to add the data?
Ok did that but now how do I fill the list with the items....say record #1 contained |Stock#|Year|Make|Model|Co
how do I modify my listitems.add code to add the data?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks Chaosian but VB.net is giving me an error on
" DataAdapter is not defined" ...is that referring to...
Dim conn As New SqlConnection("My connection string ;")
Dim cmd As New SqlCommand
Dim dr As SqlDataReader
also " DataBind is not a member of System.Windows.Forms.Listb ox"
Thanks for your help!
" DataAdapter is not defined" ...is that referring to...
Dim conn As New SqlConnection("My connection string ;")
Dim cmd As New SqlCommand
Dim dr As SqlDataReader
also " DataBind is not a member of System.Windows.Forms.Listb
Thanks for your help!
ASKER
oops figured out that 'DataAdapter' should be "SqlDataAdapter" sorry :) but still stumped on 'DataBind'
Doh! I'm in ASPX mode... apparently you don't need to call BindData at all.
ASKER
hmm now in the list it shows 5 columns of "System.Data.DataRowView"
current code is...
Dim conn As New SqlConnection("Connection string...;")
Dim cmd As New SqlCommand
Dim dr As SqlDataReader
cmd.Connection = conn
cmd.CommandType = CommandType.Text
cmd.CommandText = "SELECT StockNumber,Year,Make,Mode l,Color FROM dbo.VehicleMaster ORDER BY StockNumber"
Try
cmd.Connection.Open()
Dim adapt As SqlDataAdapter = New SqlDataAdapter(cmd)
Dim dt As New DataTable
adapt.Fill(dt)
With lstStocknumberList
.DataSource = dt
.MultiColumn = True
.ColumnWidth = 5
End With
Catch ex As SqlException
MessageBox.Show(ex.Message )
Finally
cmd.Connection.Close()
End Try
current code is...
Dim conn As New SqlConnection("Connection string...;")
Dim cmd As New SqlCommand
Dim dr As SqlDataReader
cmd.Connection = conn
cmd.CommandType = CommandType.Text
cmd.CommandText = "SELECT StockNumber,Year,Make,Mode
Try
cmd.Connection.Open()
Dim adapt As SqlDataAdapter = New SqlDataAdapter(cmd)
Dim dt As New DataTable
adapt.Fill(dt)
With lstStocknumberList
.DataSource = dt
.MultiColumn = True
.ColumnWidth = 5
End With
Catch ex As SqlException
MessageBox.Show(ex.Message
Finally
cmd.Connection.Close()
End Try
Try setting the DisplayMember for the list box.
lstStockNumberlst.DisplayM ember = "StockNumber"
Then try setting it to show multiple columns (this is NOT documented):
lstStockNumberlst.DisplayM ember = "StockNumber,Year,Make,Mod el,Color"
lstStockNumberlst.DisplayM
Then try setting it to show multiple columns (this is NOT documented):
lstStockNumberlst.DisplayM
You're probably going to discover that you can't have different fields in your columns -- you'll need to go to a datagrid to do that...