Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 293
  • Last Modified:

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
0
lesmydad
Asked:
lesmydad
  • 5
  • 3
  • 2
2 Solutions
 
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
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now