Link to home
Start Free TrialLog in
Avatar of lesmydad
lesmydad

asked on

retriving data from database to dropdown list

Hi Guys,
I have two database table tblTelephoneSuppliers and tblTelephoneCallPlans.  tblTelephoneCallPlans which has fileds TelephoneCallPlansID, TelephoneSuppliersID and TelephoneCallPlanName. tblTelephoneSuppliers has fields tblTelephoneSuppliersID and TelephoneSupplierName.

I have a dropdown list which retrive all the telephone suppliers from tblTelephoneSuppliers on that dropdown list. I also have another dropdown box. What i want is when a selected item is selected from the dropdown list, the call plan for that supplier to be shown on dropdown list 2.

I've done the store procedure like this:
CREATE Procedure sp_getTelephoneCallPlans
      @TelephoneSupplierID int
AS
Select Distinct
TelephoneCallPlanName
From tblTelephoneCallPlans TS
Where
Enabled <> 0
      AND
      TS.TelephoneSupplierID = @TelephoneSupplierID

the drop down supplier list store procedure is done like this:
CREATE Procedure sp_getSecondSupplier
      
AS

SELECT
      TelephoneSupplierName
FROM
      tblTelephoneSuppliers
WHERE
      Enabled <> 0;

Now how would i do the backend and front end coding so when a value from dropdown list 1 is selected its correspondent plans to be shown on dropdown list 2.

Thanks,
Les
ASKER CERTIFIED SOLUTION
Avatar of RacinRan
RacinRan
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of lesmydad
lesmydad

ASKER

With the above code error is thrown: 'SelectedValue' is not a member of 'System.Web.UI.WebControls.DropDownList'.

Also what i would need to do on the web form. It construcfted like this:
Drop Down List one:
<asp:DropDownList id="secondsupplier" DataValueField="TelephoneSupplierName" DataTextField="TelephoneSupplierName" AutoPostBack="True" Runat="server"></asp:DropDownList></asp:Label></div>

This is retriving values from the second procedure on the option list.


Drop Down List Two
<asp:DropDownList id="secondplanname" AutoPostBack="True" Runat="server"></asp:DropDownList>

by the way its not combobox its dropdown list

Avatar of lesmydad
lesmydad

ASKER

Thi sis how i did it:

 Private Sub secondsupplier_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles secondsupplier.SelectedIndexChanged
        Dim TelephoneSupplierID As Integer
        TelephoneSupplierID = secondplanname.SelectedIndex

        'Code to execute sp_getTelephonePlans and bind to combo 2
    End Sub

but still the list of plans are not shown on dropdownl list2 when a option from dropdown list is executed
Avatar of RacinRan
RacinRan
Oh ... this is a VB forum ... but ASP is ok.

You would retrieve it from the DataValueField which should equal "TelephoneSupplierID"

Racin
Avatar of lesmydad
lesmydad

ASKER

Sorry did not recognise as VB forum

In combobox 2 i inserted these DataValueField="TelephoneCallPlanName" DataTextField="TelephoneCallPlanName" to the webform but still have no luck. I also tested with "TelephoneSupplierID" but does not functions.

Any other idea.
thanks
les
SOLUTION
Avatar of platinumbay
platinumbay
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of platinumbay
platinumbay
Sorry, that should be

MyList.DataSource = Ctype(SQLData.CreateDate("sp_getTelephonePlans", objconn, SQLType.BindingData), DataView)
MyList.DataBind
Avatar of lesmydad
lesmydad

ASKER

Hi platinumbay,
I dont need the sql connection and retrival stuffs as i already have done them separately on the dataprovider.vb through the store procedure. I'm using VS.Studio.
Connection and store procedure works perfectly.

The dorpdown list two needs to show the values accordinarly to dropdown list 1. At the moment its like this:

<asp:DropDownList id="secondsupplier" DataValueField="TelephoneSupplierID" DataTextField="TelephoneSupplierName" AutoPostBack="True" Runat="server"></asp:DropDownList>


<asp:DropDownList id="secondplanname" AutoPostBack="True" Runat="server" DataValueField="TelephoneSupplierID" DataTextField="TelephoneCallPlanName"></asp:DropDownList>

what do i need to change there or the store procedure to make this function - do i need to add exetra bvits of cod eon my back end script
Thanks

Avatar of platinumbay
platinumbay
Use the postback event handler for dropdown 1 to populate dropdown 2.  Double-click on dropdown list 1 to create the method in VS.NET.  Then add your code to populate List 2 based on the selection in list 1.
Avatar of lesmydad
lesmydad

ASKER

After hours of test and trial i manage to write the script:

VB.NET part:
   Private Sub secondsupplier_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles secondsupplier.SelectedIndexChanged
        Dim TelephoneSupplierID As Integer
        Dim DataSource

        TelephoneSupplierID = secondsupplier.SelectedItem.Value

        dtSecondPlan = FRI.SOMB.Visitor.DataProvider.getSecondPlan(TelephoneSupplierID)

        secondplanname.DataSource = dtSecondPlan
        secondplanname.DataBind()

 End Sub

ASP.NET form script:
<asp:DropDownList id="secondsupplier" DataValueField="TelephoneSupplierID" DataTextField="TelephoneSupplierName" AutoPostBack="True" Runat="server" ><asp:listitem Text="Please Select..."/></asp:DropDownList></asp:Label></div>

<div><asp:Label ID="callplan" Runat="server" Text="What is the name of the call plan you are on?" >
<asp:DropDownList id="secondplanname"  Runat="server" DataValueField="TelephoneCallPlanName" DataTextField="TelephoneCallPlanName">
</asp:DropDownList>
</asp:Label></div>

Thanks to all these who tried to help
Les