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
278 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
asp Google Map 2 91
disable data migrations in visual studio 2017 4 68
I need help changing the default value for an @HTML.LabelFor control 4 56
Securing WEBAPI on Azure 2 55
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…
In .NET 2.0, Microsoft introduced the Web Site.  This was the default way to create a web Project in Visual Studio 2005.  In Visual Studio 2008, the Web Application has been restored as the default web Project in Visual Studio/.NET 3.x The Web Si…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

732 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