Improve company productivity with a Business Account.Sign Up

x
?
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
Medium Priority
?
322 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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

 

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

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Sometimes in DotNetNuke module development you want to swap controls within the same module definition.  In doing this DNN (somewhat annoyingly) swaps the Skin and Container definitions to the default admin selections.  To get around this you need t…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
If you are looking for an automated solution for backup single or multiple Office 365 user mailboxes to Outlook data file, then you can use Kernel Office 365 Backup & Restore tool. Go through the video to check out the steps to backup single or mult…

595 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