Thread: populate datasource control from DDL

Hi

I need to populate a DataSource control with an id from a ddl, this
DataSource control the populates a ddl.

I have no clue how to pass the id to the DataSource control, I
have tried a few things but no luck yet.

my application is built on a 3 tier design, BL,BO,DA layers.
I'll post the code relevant for the DataSource control i'm trying
to populate.

DA: (in my DA i also have a class that holds my querystrings)
the query:

Code:

Public Class QueryZonesDB
        Public Shared strSQL

        Public Shared Function Zones(ByVal pid As Integer)
            strSQL = "SELECT "
            strSQL = strSQL & "idkey, "
            strSQL = strSQL & "project_id, "
            strSQL = strSQL & "userID, "
            strSQL = strSQL & "edited, "
            strSQL = strSQL & "name, "
            strSQL = strSQL & "description "
            strSQL = strSQL & "FROM tblzones "
            strSQL = strSQL & "WHERE idkey = " & pid & " "
            strSQL = strSQL & "ORDER BY name ASC"
            Return strSQL
        End Function

Code:

Public Class ZonesDB

Public Shared Function GetList(ByVal id As Integer) As ZonesList
            Dim tempList As ZonesList = Nothing
            'Using
            Dim myConnection As MySqlConnection = New MySqlConnection(myConfig.myConnection)
            Try
                Dim myCommand = New MySqlCommand(QueryZonesDB.Zones(id), myConnection)
                myConnection.Open()
                ' Using
                Dim myReader As MySqlDataReader = myCommand.ExecuteReader
                Try
                    If myReader.HasRows Then
                        tempList = New ZonesList
                        While myReader.Read
                            tempList.Add(FillDataRecord(myReader))
                        End While
                    End If
                    myReader.Close()
                Finally
                    CType(myReader, IDisposable).Dispose()
                End Try
            Finally
                CType(myConnection, IDisposable).Dispose()
            End Try
            Return tempList
        End Function

Private Shared Function FillDataRecord(ByVal myDataRecord As IDataRecord) As Zones
            Dim myZones As Zones = New Zones
            myZones.Idkey = myDataRecord.GetInt32(myDataRecord.GetOrdinal("idkey"))
            myZones.ProjectID = myDataRecord.GetInt32(myDataRecord.GetOrdinal("project_id"))
            myZones.UserID = myDataRecord.GetInt32(myDataRecord.GetOrdinal("userID"))
            myZones.Edited = myDataRecord.GetInt32(myDataRecord.GetOrdinal("edited"))
            myZones.Name = myDataRecord.GetString(myDataRecord.GetOrdinal("name"))
            myZones.Description = myDataRecord.GetString(myDataRecord.GetOrdinal("description"))
            Return myZones
        End Function

BO:

Code:

Public Class Zones

        Private _idkey As Nullable(Of Integer)
        Private _project_id As Nullable(Of Integer)
        Private _user_id As Nullable(Of Integer)
        Private _edited_id As Nullable(Of Integer)
        Private _name As String = String.Empty
        Private _description As String = String.Empty

        <DataObjectFieldAttribute(True, True, False)> _
        Public Property Idkey() As Integer
            Get
                Return _idkey
            End Get
            Set(ByVal value As Integer)
                _idkey = value
            End Set
        End Property

        Public Property ProjectID() As Integer
            Get
                Return _project_id
            End Get
            Set(ByVal value As Integer)
                _project_id = value
            End Set
        End Property

        Public Property UserID() As Integer
            Get
                Return _user_id
            End Get
            Set(ByVal value As Integer)
                _user_id = value
            End Set
        End Property

        Public Property Edited() As Integer
            Get
                Return _edited_id
            End Get
            Set(ByVal value As Integer)
                _edited_id = value
            End Set
        End Property

        Public Property Name() As String
            Get
                Return _name
            End Get
            Set(ByVal value As String)
                _name = value
            End Set
        End Property

        Public Property Description() As String
            Get
                Return _description
            End Get
            Set(ByVal value As String)
                _name = value
            End Set
        End Property

    End Class

List collection in my BO:

Code:

    Public Class ZonesList

        Inherits List(Of Zones)

        Public Sub New()
        End Sub

    End Class

BL:

Code:

Public Class ZonesManager

        <DataObjectMethod(DataObjectMethodType.Select, True)> _
        Public Shared Function GetList(ByVal id As Integer) As ZonesList
            Return ZonesDB.GetList(id)
        End Function

my page with the DDL and DS controls:

Code:

<form id="form1" runat="server">
    <div>
        <p>
            <asp:Label ID="lblWelcome" runat="server"></asp:Label>&nbsp;|&nbsp;
            <asp:HyperLink ID="logout" runat="server" NavigateUrl="~/Default.aspx">LogOut</asp:HyperLink>
        </p>
    </div>
    <div>
        <p>
            <asp:DropDownList ID="ProjectDDL" runat="server" AutoPostBack="True" DataSourceID="ProjectDataSource"
                DataTextField="Name" DataValueField="Idkey">
            </asp:DropDownList>
            <asp:ObjectDataSource 
                ID="ProjectDataSource" 
                runat="server" 
                DataObjectTypeName="Keith.ProjectManager.BO.Projects"
                DeleteMethod="Delete" 
                InsertMethod="Save" 
                SelectMethod="GetList" 
                UpdateMethod="Update" 
                TypeName="Keith.ProjectManager.Bll.ProjectsManager">
                <SelectParameters>
                    <asp:Parameter Name="id" Type="Int32" />
                </SelectParameters>
            </asp:ObjectDataSource>
        </p>
    </div>
    <div>
        <p>
            <asp:Label ID="textmsg" runat="server"></asp:Label>&nbsp;
            <asp:DropDownList ID="ZonesDDL" runat="server" AutoPostBack="True" DataSourceID="ZonesDataSource"
                DataTextField="Name" DataValueField="Idkey" Visible="False">
                <asp:ListItem>Select Here</asp:ListItem>
            </asp:DropDownList>
            <asp:ObjectDataSource 
                ID="ZonesDataSource" 
                runat="server" 
                DataObjectTypeName="Keith.ProjectManager.BO.Zones"
                DeleteMethod="Delete" 
                InsertMethod="Save" 
                SelectMethod="GetList" 
                TypeName="Keith.ProjectManager.Bll.ZonesManager" 
                UpdateMethod="Update">
                <SelectParameters>
                    <asp:Parameter Name="id" />
                </SelectParameters>
            </asp:ObjectDataSource>
        </p>
    </div>
    </form>

any help would be great

thanks
Shem

So basically you want to pass the selected value from ProjectDDL to ZonesDataSource which will populate ZonesDDL.

Is that about right?

exacto

Try changing this:-

Code:

                <SelectParameters>
                    <asp:Parameter Name="id" />
                </SelectParameters>

to:-

Code:

                <SelectParameters>
                    <asp:ControlParameter Name="id" ControlID="ProjectDDL" />
                </SelectParameters>

tried that already no luck

is there a way that we can check if the ProjectDDL.SelectedValue is being
passed to ZonesDataSource?

Shem

What actually happens when you try the change I posted?

Do you get any errors? Does the DDL just not get populated?

As far as I know that is the correct way to pass an id to datasource control. I don't use datasource controls so am guessing a little bit here.

did a response.write on:

Code:

Public Shared Function Zones(ByVal pid As Integer)
            strSQL = "SELECT "
            strSQL = strSQL & "idkey, "
            strSQL = strSQL & "project_id, "
            strSQL = strSQL & "userID, "
            strSQL = strSQL & "edited, "
            strSQL = strSQL & "name, "
            strSQL = strSQL & "description "
            strSQL = strSQL & "FROM tblzones "
            strSQL = strSQL & "WHERE idkey = " & pid & " "
            strSQL = strSQL & "ORDER BY name ASC"
            HttpContext.Current.Response.Write(strSQL)
            Return strSQL
        End Function

and it is getting a value..
so how come my ZonesDDL is blank

Shem

ok it's getting values but as you can see below, the option name is blank?

Code:

<select name="ZonesDDL" onchange="javascript:setTimeout('__doPostBack(\'ZonesDDL\',\'\')', 0)" id="ZonesDDL">
	<option value="4"></option>
	<option value="5"></option>
	<option value="6"></option>

</select>

any ideas?
@RR: what do you use if ya don't use datasource control?