• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 296
  • Last Modified:

How to build a select list from a Database field

I need to build a select list from an sql/server2008 db.
the field is 'strTitle' the contents are a string of event names.

I will use it to parse out a single event and thier register users
0
westdh
Asked:
westdh
1 Solution
 
TMarkham1Commented:
There are actually a few ways you can do this... have a look at this. I believe what you're looking for is approximately in the middle of the page under the title: "Binding to data table, data set and data view":

http://www.aspfree.com/c/a/ASP.NET/ASPNET-Dropdown-List-Control-Eight-Ways-to-Bind-Data/2/
0
 
westdhAuthor Commented:
This is what I have: I am not filling the dropdown with Event Titles.
Event Titles:
<asp:DropDownList ID="DroplistData" runat="server" Width="200px" >
</asp:DropDownList>
======================================================

 Protected Sub DroplistData_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)

        Dim dtVolumeOrder As New DataTable()
        Dim strSelectCommand As String = "SELECT e.dtmEvent,e.strTitle,r.strName,r.strEmail,u.strRegistrationUserName,u.strBadgeName,r.fltOrderAmount,r.blnEmailSent,r.dtmCreated FROM i2Integration_EventRegv45_Event e,i2Integration_EventRegv45_Registration r, i2Integration_EventRegv45_RegistrationUser u where e.intEventID = r.intEventID and r.intRegistrationID = u.intRegistrationID and r.blnEmailSent=1 and(YEAR(e.dtmEvent) = '" + ddlYear.SelectedValue + "' AND MONTH(e.dtmEvent) = '" + ddlMth.SelectedValue + "') Order by e.dtmEvent"
        Using sqlConn As New SqlConnection(ConfigurationManager.ConnectionStrings("SiteSqlServer").ConnectionString)
            Using adapPatientBills As New SqlDataAdapter(strSelectCommand, sqlConn)
                adapPatientBills.Fill(dtVolumeOrder)
            End Using
        End Using
        iRowsCount = dtVolumeOrder.Rows.Count - 1
        DroplistData.DataSource = dtVolumeOrder
        DroplistData.DataTextField = "strTitle"
        DroplistData.DataBind()

    End Sub

0
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!

 
Rajar AhmedConsultantCommented:
hi...

wats d problem in this code ..??

Not able to get ur problem completely ....

does Ur need is this ??
Are you filling any gridview or such that using the selection change of droplistdata ..????

Confusing part??
if this is "DroplistData_SelectedIndexChanged" ur DroplistData  selection changed event then..
.why u have not used d value of DroplistData dropdown in that selection changed event of ur query .........???

""
SELECT e.dtmEvent,e.strTitle,r.strName,....and(YEAR(e.dtmEvent) = '" + ddlYear.SelectedValue + "' AND MONTH(e.dtmEvent) = '" + ddlMth.SelectedValue + "') Order by e.dtmEvent


I tested ur code with different simple query of my table...which works fine ..to fill data of that dropdown....but dint understand wat ur needs ...??

Please Answer this :
1.do u ve query problem ... ??
2.Does ur dropdown list is filling according to ur query?
3.Receive any errors ??
4.What u need to do in selection change event of DroplistData??





<asp:DropDownList ID="DroplistData" runat="server" AutoPostBack="true" OnSelectedIndexChanged="DroplistData_SelectedIndexChanged">
        <asp:ListItem>Hi</asp:ListItem>
        <asp:ListItem>Hey</asp:ListItem>
        </asp:DropDownList>


Protected Sub DroplistData_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim dtVolumeOrder As New DataTable()
        Dim iRowsCount As String
        Dim strSelectCommand As String = "You query" ' Are you having problem with query ..??
        Using sqlConn As New SqlConnection(ConfigurationManager.AppSettings("myDSN"))'<--Small change initially i got obect reference error.
            Using adapPatientBills As New SqlDataAdapter(strSelectCommand, sqlConn)
                adapPatientBills.Fill(dtVolumeOrder)
            End Using
        End Using
        iRowsCount = dtVolumeOrder.Rows.Count - 1
        DroplistData.DataSource = dtVolumeOrder
        DroplistData.DataTextField = "cvid"
        DroplistData.DataBind()
    End Sub

Open in new window

0
 
ROMA CHAUHANProject LeadCommented:
Why r u doing this in DroplistData_SelectedIndexChanged() event.
Keep it in page load event.
Try this....
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not Page.IsPostBack Then
            Dim dtVolumeOrder As New DataTable()
            Dim strSelectCommand As String = "SELECT e.dtmEvent,e.strTitle,r.strName,r.strEmail,u.strRegistrationUserName,u.strBadgeName,r.fltOrderAmount,r.blnEmailSent,r.dtmCreated FROM i2Integration_EventRegv45_Event e,i2Integration_EventRegv45_Registration r, i2Integration_EventRegv45_RegistrationUser u where e.intEventID = r.intEventID and r.intRegistrationID = u.intRegistrationID and r.blnEmailSent=1 and(YEAR(e.dtmEvent) = '" + ddlYear.SelectedValue + "' AND MONTH(e.dtmEvent) = '" + ddlMth.SelectedValue + "') Order by e.dtmEvent"
            Using sqlConn As New SqlConnection(ConfigurationManager.ConnectionStrings("SiteSqlServer").ConnectionString)
                Using adapPatientBills As New SqlDataAdapter(strSelectCommand, sqlConn)
                    adapPatientBills.Fill(dtVolumeOrder)
                End Using
            End Using
            DroplistData.DataSource = dtVolumeOrder
            DroplistData.DataTextField = "strTitle"
            DroplistData.DataBind()
        End If
    End Sub
0
 
ROMA CHAUHANProject LeadCommented:
and remove the code from selected index changed event.
0
 
westdhAuthor Commented:
Ok... I have it now somewhat workings:
-------------------------------------------------------------------------
The dropdown now returns the value of "strTitle", But I get 3 of them (how do I filter it to one unigue value), one for each of the three records I currently have.

Select Event Name:  Strategies for Cultivating Respect for Diversity
                                 Strategies for Cultivating Respect for Diversity
                                 Strategies for Cultivating Respect for Diversity

as I add more events to my db I would like to have only one "strTitle"  for each group of different Titles.

====================================================
Select Event Name:
<asp:DropDownList ID="DroplistData" runat="server" Width="200px" AutoPostBack="true" OnSelectedIndexChanged="DroplistData_SelectedIndexChanged">
<asp:ListItem>Hi</asp:ListItem>
        <asp:ListItem>Hey</asp:ListItem>
</asp:DropDownList>
==================================================
 Protected Sub DroplistData_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim dtVolumeOrder As New DataTable()
        Dim strSelectCommand As String = "SELECT e.dtmEvent,e.strTitle,r.strName,r.strEmail,u.strRegistrationUserName,u.strBadgeName,r.fltOrderAmount,r.blnEmailSent,r.dtmCreated FROM i2Integration_EventRegv45_Event e,i2Integration_EventRegv45_Registration r, i2Integration_EventRegv45_RegistrationUser u where e.intEventID = r.intEventID and r.intRegistrationID = u.intRegistrationID and r.blnEmailSent=1 and(YEAR(e.dtmEvent) = '" + ddlYear.SelectedValue + "' AND MONTH(e.dtmEvent) = '" + ddlMth.SelectedValue + "' AND e.strTitle = '" + DroplistData.SelectedValue + "') Order by e.dtmEvent"
        Using sqlConn As New SqlConnection(ConfigurationManager.ConnectionStrings("SiteSqlServer").ConnectionString)
            Using adapPatientBills As New SqlDataAdapter(strSelectCommand, sqlConn)
                adapPatientBills.Fill(dtVolumeOrder)
            End Using
        End Using
        DroplistData.DataSource = dtVolumeOrder
        DroplistData.DataTextField = "strTitle"
        DroplistData.DataBind()
    End Sub
0
 
ROMA CHAUHANProject LeadCommented:
Then change your code to this one....
  Dim dtVolumeOrder As New DataTable("TableName")
        Dim strSelectCommand As String = "SELECT e.dtmEvent,e.strTitle,r.strName,r.strEmail,u.strRegistrationUserName,u.strBadgeName,r.fltOrderAmount,r.blnEmailSent,r.dtmCreated FROM i2Integration_EventRegv45_Event e,i2Integration_EventRegv45_Registration r, i2Integration_EventRegv45_RegistrationUser u where e.intEventID = r.intEventID and r.intRegistrationID = u.intRegistrationID and r.blnEmailSent=1 and(YEAR(e.dtmEvent) = '" + ddlYear.SelectedValue + "' AND MONTH(e.dtmEvent) = '" + ddlMth.SelectedValue + "') Order by e.dtmEvent"
        Using sqlConn As New SqlConnection(ConfigurationManager.ConnectionStrings("SiteSqlServer").ConnectionString)
            Using adapPatientBills As New SqlDataAdapter(strSelectCommand, sqlConn)
                adapPatientBills.Fill(dtVolumeOrder)
            End Using
        End Using

        Dim dtView As New DataView
        dtView.Table = dtVolumeOrder
        DroplistData.DataSource = dtView.ToTable(True, "strTitle")
        DroplistData.DataTextField = "strTitle"
        DroplistData.DataBind()
0
 
westdhAuthor Commented:
Then change your code to this one....????????
This did not help I am still listing all three titles   'Strategies for Cultivating Respect for Diversity'
for each record .. where as I only one to list one.. unigue title for each group

db records 'title' field
-----------------------------
Event Title One
Event Title One  
Event Title One  
Event Title Two
Event Title Two  
Event Title Two  
 
what I am currently listing in the dropdownlist is
------------------------------
Event Title One
Event Title One  
Event Title One  
Event Title Two
Event Title Two  
Event Title Two    

what I need  to list in the dropdownlist is
--------------------------------------------
Event Title One  
Event Title Two
0
 
ROMA CHAUHANProject LeadCommented:
In
DroplistData.DataSource = dtView.ToTable(True, "strTitle")
You have to specified the columns name which you want as distinct.

Its working fine at my place.

You can also get the distinct title from the sql query itself.
in sql query specify "Select distinct title from tablename"... You'll get the distinct value from database directly and don't have to do any process in code behind
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now