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
LVL 1
lesmydadAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RacinRanCommented:
In the SelectedIndexChanged event of combo box 1 take the ID and run the stored procedure passing it the ID.  Then bind the results to combo box 2.

Private Sub cbo1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cbo1.SelectedIndexChanged

dim SupplierID as integer

SupplierID = cbo1.SelectedValue

'Code to execute sp_getTelephonePlans and bind to combo 2

End Sub

btw... I is not good practice to use sp_ as a prefix for your sp's.  The reason is it assumes this is a system stored procedure and will search through the master database looking for it first.

Racin
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lesmydadAuthor Commented:
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

0
lesmydadAuthor Commented:
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
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

RacinRanCommented:
Oh ... this is a VB forum ... but ASP is ok.

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

Racin
0
lesmydadAuthor Commented:
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
0
platinumbayCommented:
Here is a class I use:

Imports System.Data.SqlClient

Public Class SQLData
    Enum ReturnType
        BindingData
        DataTable
        DataSet
    End Enum

    Public Shared Function CreateData(ByVal strSQL As String, ByVal objConn As SqlConnection, ByVal DataType As ReturnType) As Object
        Dim objDA As SqlDataAdapter
        Dim objDS As DataSet
        OpenConn(objConn)
        objDA = New SqlDataAdapter(strSQL, objConn)
        objDS = New DataSet

        objDA.Fill(objDS, "myTable")
        CloseConn(objConn)
        Select Case DataType
            Case ReturnType.BindingData
                Return objDS.Tables(0).DefaultView
            Case ReturnType.DataSet
                Return objDS
            Case ReturnType.DataTable
                Return objDS.Tables(0)
            Case Else
                Return Nothing
        End Select
    End Function

    Public Shared Function ExecuteScalar(ByVal strSQL As String, ByVal objConn As SqlConnection) As String
        Dim strResult As String
        Dim objCmd As New SqlCommand
        OpenConn(objConn)
        objCmd = New SqlCommand(strSQL, objConn)
        strResult = objCmd.ExecuteScalar.ToString()
        Return strResult
        CloseConn(objConn)
    End Function

    Public Shared Function ExecuteNonQuery(ByVal strSQL As String, ByVal objConn As SqlConnection) As Boolean
        OpenConn(objConn)
        Dim objCmd As New SqlCommand(strSQL, objConn)
        ExecuteNonQuery = Convert.ToBoolean(objCmd.ExecuteNonQuery)
        CloseConn(objConn)
    End Function

    Private Shared Sub CloseOpenConn(ByVal connection As SqlConnection)
        If connection.State <> ConnectionState.Closed Then connection.Close()
        If connection.State <> ConnectionState.Open Then connection.Open()
    End Sub

    Private Shared Sub CloseConn(ByVal connection As SqlConnection)
        If connection.State <> ConnectionState.Closed Then connection.Close()
    End Sub

    Private Shared Sub OpenConn(ByVal connection As SqlConnection)
        If connection.State <> ConnectionState.Open Then connection.Open()
    End Sub
End Class



MyList.DataSource = SQLData.CreateDate("sp_getTelephonePlans", objconn, SQLType.BindingData)
MyList.DataBind

and make sure you have the text and value properties set for the dropdown.
0
platinumbayCommented:
Sorry, that should be

MyList.DataSource = Ctype(SQLData.CreateDate("sp_getTelephonePlans", objconn, SQLType.BindingData), DataView)
MyList.DataBind
0
lesmydadAuthor Commented:
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

0
platinumbayCommented:
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.
0
lesmydadAuthor Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

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.