Solved

How to get field name alias to display in drop down while passing the field name as a value

Posted on 2006-11-16
9
274 Views
Last Modified: 2008-02-07
I have the following code to populate a dropdownlist and is passing the parameter to a select statement  but i'd like the alias such as "First Name" to be displayed in the dropdown list but  pass 'fname' as the value of that selectedvalue.   How do i modify the code to make that happen

        If Not Page.IsPostBack Then
            Dim strCon As String = ConfigurationManager.ConnectionStrings("CMConnectionString").ConnectionString
            Dim con As New SqlConnection(strCon)
            Dim sSQL As String = "Select top 1 FName as [First Name], LName as [Last Name], Company, State, Zip, HomePhone as [Home Phone], WorkPhone as [Work Phone], SerialNumber as [Serial#], ProductName as [Model],  HomeEmail as [Email Address] from tblCM"

            Dim cmd As New SqlCommand(sSQL, con)
            Dim oEmptyOption As New ListItem()
            Dim Array1 As New ArrayList
            Try
                con.Open()
                Dim reader As SqlDataReader = cmd.ExecuteReader()
                For intColumn As Integer = 0 To reader.FieldCount - 1
                    Array1.Add(reader.GetName(intColumn))
                Next

                ddSearchField.DataSource = Array1
                ddSearchField.DataBind()
                ddSearchField.Items.Insert(0, oEmptyOption)

                ddSearchField1.DataSource = Array1
                ddSearchField1.DataBind()
                ddSearchField1.Items.Insert(0, oEmptyOption)

                ddSearchField2.DataSource = Array1
                ddSearchField2.DataBind()
                ddSearchField2.Items.Insert(0, oEmptyOption)
            Catch
            Finally
                con.Close()
            End Try
        End If
End sub

-------here is where i get  the value of the dropdownlist selectedvalue --- In this section I aslo wants to return CMId and SourceID but not show them in the gridview ---

    Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSearch.Click
        Dim criteria1 As String = ddSearchField.SelectedValue & ddCriteria.SelectedValue & "'" & TextBox1.Text & "'"
        Dim criteria12 As String = ddCond.SelectedValue & ddSearchField1.SelectedValue & ddCriteria1.SelectedValue & TextBox2.Text
        Dim criteria3 As String = ddCond1.SelectedValue & ddSearchField2.SelectedValue & ddCriteria2.SelectedValue & TextBox3.Text

        Dim strCon As String = ConfigurationManager.ConnectionStrings("CMConnectionString").ConnectionString
        Dim con As New SqlConnection(strCon)
        Dim sSQL As String = "Select CMID, SourceID , SourceName as [Source], FName as [First Name], LName as [Last Name], HomePhone as [Telephone], SerialNumber as [Serial#], Address1 as [Address], City, State, Zip, ProductName as [Product Name]  From vCM where " & criteria1
        Dim cmd As New SqlCommand(sSQL, con)
        Dim myDataSet As New DataSet

        'Create a SqlDataAdapter instance
        Dim myAdapter As New SqlDataAdapter(cmd)
        myAdapter.Fill(myDataSet)

        'Bind the DataSet to the GridView
        GridView1.DataSource = myDataSet
        GridView1.DataBind()

        'Close the connection
        con.Close()

    End Sub
0
Comment
Question by:krbnldy
  • 5
  • 4
9 Comments
 
LVL 96

Expert Comment

by:Bob Learned
ID: 17955601
You need to create an ArrayList with objects like DictionaryEntry that has two properties--Key and Value that you can bind the DataTextField and DataValueField to.

Bob
0
 

Author Comment

by:krbnldy
ID: 17955613
COuld you show me how that is done.  I am still new at this
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 17955749
Examples:

1) Add a DictionaryEntry to the ArrayList
    Array1.Add(New DictionaryEntry("Name", "Value"))

2) Bind the DropDownList to the ArrayList

3) Set the DataTextField and DataValueField:

   ddSearchField.DataTextField = "Key"
   ddSearchField.DataValueField = "Value"
 
   The DictionaryEntry class has two properties--Key and Value.  When you create a new DictionaryEntry, the constructors takes two arguments--Key and Value.  

Bob
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:krbnldy
ID: 17955833
I modified my code to look like below and it is not displaying the list,  
I think I am not getting it clearly

Dim reader As SqlDataReader = cmd.ExecuteReader()
                For intColumn As Integer = 0 To reader.FieldCount - 1
                   Array1.Add(reader.GetName(intColumn))
                    Array1.Add(New DictionaryEntry("name", "first name"))
                Next

                ddSearchField.DataSource = Array1
                ddSearchField.DataBind()
                ddSearchField.DataTextField = "Key"
                ddSearchField.DataValueField = "Value"
                ddSearchField.Items.Insert(0, oEmptyOption)
0
 
LVL 96

Accepted Solution

by:
Bob Learned earned 75 total points
ID: 17955855
Try this:

                Dim reader As SqlDataReader = cmd.ExecuteReader()
                For intColumn As Integer = 0 To reader.FieldCount - 1
                    Array1.Add(New DictionaryEntry("name", "first name"))
                Next

                ddSearchField.DataSource = Array1
                ddSearchField.DataTextField = "Key"
                ddSearchField.DataValueField = "Value"
                ddSearchField.Items.Insert(0, oEmptyOption)
                ddSearchField.DataBind()

The DataBind call needs to be the last thing.

Bob
0
 

Author Comment

by:krbnldy
ID: 17955928
This works with one problem.  My dropdown list has only one value that is first name.

I added the following but it still has only one value,   How do i make it show all the values in the sql statement

                    Array1.Add(New DictionaryEntry("First Name", "fname"))
                    Array1.Add(New DictionaryEntry("Last Name", "lname"))
                    Array1.Add(New DictionaryEntry("Company", "Company"))
                    Array1.Add(New DictionaryEntry("State", "State"))
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 17955972
Are you trying to add those properties from the data reader?

Bob
0
 

Author Comment

by:krbnldy
ID: 17956008
Yes i am

I tried cahnging the array to

                    Array1.Add(New DictionaryEntry(reader.GetName(intColumn), value))
 but got the list but not the value of each fiield
0
 

Author Comment

by:krbnldy
ID: 17956064
Think I got it

                    Array1.Add(New DictionaryEntry(reader.GetName(0), "fname"))
                    Array1.Add(New DictionaryEntry(reader.GetName(1), "lname"))
                    Array1.Add(New DictionaryEntry(reader.GetName(2), "Company"))
                    Array1.Add(New DictionaryEntry(reader.GetName(3), "State"))
                    Array1.Add(New DictionaryEntry(reader.GetName(4), "Zip"))
                    Array1.Add(New DictionaryEntry(reader.GetName(5), "HomePhone"))
                    Array1.Add(New DictionaryEntry(reader.GetName(6), "WorkPhone"))
Thank you for your help
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I recently went through the process of creating a Calendar Control of events with the basis of using a database to keep track of the dates that are selectable, one requirement was to have the selected date pop-up in a simple lightbox.  At first this…
A quick way to get a menu to work on our website, is using the Menu control and assign it to a web.sitemap using SiteMapDataSource. Example of web.sitemap file: (CODE) Sample code to add to the page menu: (CODE) Running the application, we wi…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question