הערה
הגישה לדף זה מחייבת הרשאה. באפשרותך לנסות להיכנס או לשנות מדריכי כתובות.
הגישה לדף זה מחייבת הרשאה. באפשרותך לנסות לשנות מדריכי כתובות.
Question
Friday, November 16, 2007 5:54 AM
hi,
I am a beginner in VB.Net. I have a table tblName; which consist of columns 'Name' and 'ID'.
I want to populate the comboBox, where displaymember = 'Name' and ValueMember = 'ID'.
In short, when a user selects a 'Name', return value should be the 'ID'.
I can do it using DataAdapter. Another way of doing is;
using a Listbox in parralel with the comboBox, which will be hidden, to save the 'ID' .
dr = cmd.ExecuteReader()
While dr.Read
ComboBox1.Items.Add(dr(0).ToString)
ListBox1.Items.Add(dr(1).ToString) ' Hidden
End While
However, Is it possible to do it using SqlDataReader and ComboBox only?
Please reply soon.
All replies (9)
Friday, November 16, 2007 9:38 AM ✅Answered
First, I would suggest not using the reader. Create a dataset and dataadapter and set the datasource of the combobox = dataset. From there you can set the display member and value member. Here is the code:
Code Block
VB.Net 2005
Dim dataset As New DataSet
Using connection As New SqlConnection(connectionString)
Dim adapter As New SqlDataAdapter()
adapter.SelectCommand = New SqlCommand("select Name,ID from tblName", connection)
adapter.Fill(dataset)
End Using
VB.Net 2003
Dim dataset As New DataSet
Dim connection As New SqlConnection(connectionString)
Try
Dim adapter As New SqlDataAdapter()
adapter.SelectCommand = New SqlCommand(storedProcedureName, connection )
adapter.Fill(dataset)
dataset.Tables(0).TableName = TableName
Return dataset
Catch ex As Exception
Finally
connection .Close()
End Try
Both
ComboBox1.DataSource = dataset.Tables(0)
ComboBox1.DisplayMember = "Name"
ComboBox1.ValueMember = "ID"
Let me know if this works for you.
Wade
Friday, November 23, 2007 5:32 AM ✅Answered
Yes Wade, its finnaly over now.
Am grateful to you.
Regards
Nongban
Wednesday, November 21, 2007 10:06 AM
Thanks for your reply, Wade.
As i had mentioned;
I can do it using DataAdapter. Another way of doing is;
using a Listbox in parralel with the comboBox, which will be hidden, to save the 'ID' .
dr = cmd.ExecuteReader()
While dr.Read
ComboBox1.Items.Add(dr(0).ToString)
ListBox1.Items.Add(dr(1).ToString) ' Hidden
End While
My query was: Is it possible to do it using SqlDataReader and ComboBox only?
For which, using listbox is one way. The other way of acheiving the same functionality is using a class and class data.
Here is the code:
'Class definition
Public Class clsGetDetail
Public iNameId As Integer
Public sName As String
#Region "Constructor "
Public Sub New(ByVal someword As String)
sName = someword
End Sub
Public Overrides Function ToString() As String
Return sName
End Function
#End Region
End Class
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
cnn = New SqlConnection(sDbConnectionStr)
cnn.Open()
cmd = New SqlCommand("Select * from tblName ", cnn)
dr = cmd.ExecuteReader()
While dr.Read
Dim ocls As New clsGetDetail(dr(1).ToString)
ocls.iNameId = dr(0).ToString
ComboBox1.Items.Add(ocls) 'Populates the ComboBox with the Names
End While
End Sub
'Button click event
Private Sub btnShowDetail_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnShowDetail.Click
Try
Dim ocls As clsGetDetail
ocls = ComboBox1.Items(ComboBox1.SelectedIndex)
MsgBox(ocls.iNameId) 'Displays the Id of the selected Name
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
End Class
Do let me know if there is any other way. Thanks for ur effort, Wade.
Regards
Nongban
Wednesday, November 21, 2007 1:14 PM
If you insist on using the datareader, it is better to just put the information into an ArrayList, then you can use databing to simplify the problem. Also, you should use "select ID,Name from tblName" to only return the data you need. With this you should use dr.GetInt32("ID"), dr.GetString("Name"))to ensure you select the correct columns to use when creating the class as well as get the correct type.
Code Block
Custom Class for your information
Public Class ComboBoxItem
Private mID As Integer
Private mName As String
Public Sub New(ByVal id As Integer, ByVal name As String)
mID = id
mName = Name
End Sub
Public Property ID() As Integer
Get
Return mID
End Get
Set(ByVal value As Integer)
mID = value
End Set
End Property
Public Property Name() As String
Get
Return mName
End Get
Set(ByVal value As String)
mName = value
End Set
End Property
End Class
Code Block
Form Events
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim MyItems As New ArrayList
cnn = New SqlConnection(sDbConnectionStr)
cnn.Open()
cmd = New SqlCommand("Select ID,Name from tblName", cnn)
dr = cmd.ExecuteReader()
While dr.Read
MyItems.Add(New ComboBoxItem(dr.GetInt32("ID"), dr.GetString("Name")) 'Populates the ArrayList with the Names and IDs
End While
ComboBox1.DataSource = MyItems
ComboBox1.DisplayMember = "Name"
ComboBox1.ValueMember = "ID"
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
MsgBox(ComboBox1.SelectedValue.ToString)
End Sub
Hopefully this will work better for you.
Wade
Thursday, November 22, 2007 10:31 AM
Thanks again wade.
It works well after adding the below in your class definition.
Public
Overrides Function ToString() As String
Return Name
End Function
However, i have a doubt here. The below lines throws an error:
MyItems.Add(New ComboBoxItem(dr.GetInt32("ID"), dr.GetString("Name"))
It says: "Conversion from string "ID" to type 'Integer' is not valid.
But it works fine if i use the below code instead:
MyItems.Add(New ComboBoxItem (dr(0).ToString, dr(1).ToString))
Why is it so?
regards,
Nongban
Thursday, November 22, 2007 10:40 AM
Nongban,
The ToString override should only work if you use it in reference to the class object (e.g. ComboBoxItem.ToString). In my example I used the ToString of the ComboBox itself. The error is caused by figuring the wrong data type of ID. You will need to change the dr.GetInt32("ID") to dr.GetString("ID"). In the class you will also need to change the data type of mID to string as well as the property that returns mID. Hopefully, we have finally got to a workable solution for you.
Wade
Friday, November 23, 2007 9:31 AM
Nongban,
Excellent, glad to help.
Wade
Friday, November 23, 2007 12:57 PM
Hi wade,
This is out of curiosity.
Is it possible to assign the valuemember of the combobox from the frontend; by writting the code.
Like, for populating the combobox:
ComboBox1.Items.Add("happy")
Is there anything viz,
ComboBox1.ValueMember.Add("happy")
Hope i am making sense to you.
regards,
Nongban
Tuesday, November 8, 2011 4:51 PM
Very good solution! It was very useful for me! thanks Wade73!