Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Reference DropDownList.SelectedValue / DataValueField in SQL statement

Posted on 2010-11-11
15
Medium Priority
?
1,102 Views
Last Modified: 2012-05-10
Quick & easy: I have a DetailsView control with a TemplateField. In the TemplateField I have a DropDownList control. The DropDownList control is as follows:

                                                    <asp:DropDownList ID="DropDownList1" runat="server"
                                                        DataSourceID="SDSExisting" DataTextField="fullname"
                                                        DataValueField="contactid">
                                                    </asp:DropDownList>

I want to pass the contactid of the selected DDL item into my InsertCommand for the DataSource, but using simply INSERT INTO [tblTest] ([contactid]) VALUES (@contactid) returns NULL
0
Comment
Question by:alright
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 4
  • 3
15 Comments
 
LVL 13

Expert Comment

by:AngryBinary
ID: 34114325
What are the InsertParameters you've defined for the insert command? It should look something like this:

      <asp:sqldatasource
        ...
        insertcommand="INSERT INTO [tblTest] ([contactid]) VALUES (@contactid)">
          <insertparameters>
            <asp:controlparameter name="contactid" controlid="DropDownList1" propertyname="SelectedValue"/>
          </insertparameters>
      </asp:sqldatasource>
0
 

Author Comment

by:alright
ID: 34114426
Could not find control 'DropDownList1' in ControlParameter 'contactid'.

Is the error I'm getting with that InsertParameter
0
 

Author Comment

by:alright
ID: 34114498
Can the DataSource not find the control because the DDL is nested within a TemplateField of a DetailsView control within the EmptyDataTemplate of a GridView? Or am I overlooking something easier
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!

 
LVL 4

Expert Comment

by:MSSystems
ID: 34114588
Maybe try and specify the data type on the parameter if you are using a data source object. Else you have to catch it with the control command and the assign the default parameter value.
 <InsertParameters>	
	<asp:ControlParameter ControlID="DropDownList1" Name="contactid" PropertyName="SelectedValue" Type="Int" />
</InsertParameters>

Open in new window

0
 
LVL 4

Expert Comment

by:MSSystems
ID: 34114616
I think with item templates you have to catch the control command and the assigns the default parameter value on the execution of the insert command.
0
 

Author Comment

by:alright
ID: 34114742
Example?

Same error with specifying the Type property. This is silly as I have Text Boxes in TemplateFields that pass their values as variables just fine :(
0
 
LVL 13

Accepted Solution

by:
AngryBinary earned 200 total points
ID: 34114876
Yes, the datasource won't be able to find a control in a template field from the Page scope. A quick fix would be to put the datasource in the Template, if it's practical. Otherwise, you have to do something like this (bound to the "OnInserting" event of the SDSExisting datasource, substitute "templateControlReference" with the parent control of the DropDownList):

      <asp:sqldatasource
        ...
        insertcommand="INSERT INTO [tblTest] ([contactid]) VALUES (@contactid)">
          <insertparameters>
            <asp:parameter name="contactid" type="String"/>
          </insertparameters>
      </asp:sqldatasource>

 protected void SDSExisting_Inserting(Object sender, SqlDataSourceCommandEventArgs e) {
    e.Command.Parameters["@contactid"].Value = (templateControlReference.FindControl("DropDownList1") as DropDownList).SelectedValue;
 }
0
 
LVL 4

Expert Comment

by:MSSystems
ID: 34114896
I am also adding some example on how to catch a checkbox, I hope it is relevant.
    Private Sub Example(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
        SqlDataSourceCR.InsertParameters("Region").DefaultValue = Var1
        SqlDataSourceCR.InsertParameters("SystemRegion").DefaultValue = Var2
        SqlDataSourceCR.Insert()
    End Sub

------------- 

       Private Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
        For Each row As GridViewRow In GridView1.Rows
            Dim cb As CheckBox = row.FindControl("CheckBox")
            If cb IsNot Nothing AndAlso cb.Checked Then

                SqlDataSource1.InsertParameters("Var1").DefaultValue = row.Cells(0).Text
                SqlDataSource1.InsertParameters("Var2").DefaultValue = row.Cells(1).Text
                SqlDataSource1.Insert()
            End If
        Next
        GridView1.DataBind()

    End Sub

Open in new window

0
 

Author Comment

by:alright
ID: 34115175
Angry: I've tried placing the DataSource as close within the nest to the DDL as I can, but I can't practically place it within the ItemTemplate field. I am not having luck getting the FindControl function to locate the DropDownList Control. Replacing templateControlReference with DVInsertExisting (the parent DetailsView of the DDL) yields no results. Generally my method would be similar to this:

        protected void SDSExistingOfficer_Inserting(object sender, SqlDataSourceCommandEventArgs e)
        {
            SqlDataSource sds = (SqlDataSource)sender;
            DetailsView dv = (DetailsView)(sds.NamingContainer.FindControl("DVInsertExisting"));
            DropDownList ddl = (DropDownList)(dv.NamingContainer.FindControl("DropDownList1"));
        }

However, this is throwing the following exception:

Unable to cast object of type 'System.Web.UI.WebControls.SqlDataSourceView' to type 'System.Web.UI.WebControls.SqlDataSource'.

I don't know how else to properly locate the nested control
0
 

Author Comment

by:alright
ID: 34115319
MSSystems: Do you have that in C#?

Befuddling how difficult this is turning out to be to perform such a seemingly simple procedure :(
0
 
LVL 13

Expert Comment

by:AngryBinary
ID: 34116839
Maybe provide you're ASPX markup. I'm not sure why you'd need to use FindControl to locate the DetailsView control, unless it lived in another template as well.

Anyway, I think you can still use the SqlDataSourceView class if you prefer:

        protected void SDSExistingOfficer_Inserting(object sender, SqlDataSourceCommandEventArgs e)
        {
            SqlDataSourceView sds = (SqlDataSourceView)sender;
            DetailsView dv = (DetailsView)(sds.NamingContainer.FindControl("DVInsertExisting"));
            DropDownList ddl = (DropDownList)(dv.NamingContainer.FindControl("DropDownList1"));

            sds.InsertParameters["@contactid"].Value = ddl.SelectedValue;

        }
0
 

Author Comment

by:alright
ID: 34117264
The SqlDataSourceView control doesn't have the .NamingContainer property unfortunately so that code isn't working. Here is an equivalent of my markup; the actual markup is much longer and "polluted" :) but this shows the same structure that I'm working with. Thanks!


<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
        DataKeyNames="contactid" DataSourceID="SDSTest1"
        onrowcommand="GridView1_RowCommand">
        <Columns>
            <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
            <asp:BoundField DataField="firstname" HeaderText="firstname"
                SortExpression="firstname" />
            <asp:BoundField DataField="middlename" HeaderText="middlename"
                SortExpression="middlename" />
            <asp:BoundField DataField="lastname" HeaderText="lastname"
                SortExpression="lastname" />
            <asp:BoundField DataField="contactid" HeaderText="contactid"
                InsertVisible="False" ReadOnly="True" SortExpression="contactid" />
            <asp:ButtonField CommandName="Insert" Text="New" />
        </Columns>
        <EmptyDataTemplate>
            <asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False"
                DataKeyNames="contactid" DataSourceID="SDSExistingOfficer" DefaultMode="Insert" Height="50px"
                Width="125px">
                <Fields>
                    <asp:BoundField DataField="id" HeaderText="id" InsertVisible="False"
                        ReadOnly="True" SortExpression="id" />
                    <asp:TemplateField HeaderText="title" SortExpression="title">
                        <EditItemTemplate>
                            <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("title") %>'></asp:TextBox>
                        </EditItemTemplate>
                        <InsertItemTemplate>
                            <asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SDSExistingOfficer"
                                DataTextField="contactid" DataValueField="contactid">
                            </asp:DropDownList>
                        </InsertItemTemplate>
                        <ItemTemplate>
                            <asp:Label ID="Label1" runat="server" Text='<%# Bind("title") %>'></asp:Label>
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:CommandField ShowInsertButton="True" />
                </Fields>
            </asp:DetailsView>
            <asp:SqlDataSource ID="SDSExistingOfficer" runat="server"
                ConnectionString="<%$ ConnectionStrings:sdsConnectionString1 %>"
                DeleteCommand="DELETE FROM [tblTest] WHERE [id] = @id"
                InsertCommand="INSERT INTO [tblTest] ([contactid], [title]) VALUES (@contactid, @title)"
                SelectCommand="SELECT [id], [contactid], [title] FROM [tblTest]"
                UpdateCommand="UPDATE [tblTest] SET [contactid] = @contactid, [title] = @title WHERE [id] = @id">
                <DeleteParameters>
                    <asp:Parameter Name="id" Type="Int32" />
                </DeleteParameters>
                <InsertParameters>
                    <asp:Parameter Name="contactid" Type="Int32" />
                    <asp:Parameter Name="title" Type="String" />
                </InsertParameters>
                <UpdateParameters>
                    <asp:Parameter Name="contactid" Type="Int32" />
                    <asp:Parameter Name="title" Type="String" />
                    <asp:Parameter Name="id" Type="Int32" />
                </UpdateParameters>
            </asp:SqlDataSource>
        </EmptyDataTemplate>
    </asp:GridView>
0
 

Author Comment

by:alright
ID: 34117457
I'm fairly certain that if I can just get the control found properly, declaring the @contactid parameter within the SDSExistingOfficer_Inserting command will solve all of the world's problems! Using Stringbuilder and a cute script I was able to find the following in regards to where this control is actually living, unfortunately I'm not yet versed enough in the FindControl function to do much with this output... is anyone? How would I get to this DropDownList1 using FindControl


DropDownList1 (System.Web.UI.WebControls.DropDownList) ->
(System.Web.UI.WebControls.DataControlFieldCell) ->
(System.Web.UI.WebControls.DetailsViewRow) ->
(System.Web.UI.WebControls.ChildTable) ->
DVInsertExistingOfficer (System.Web.UI.WebControls.DetailsView) ->
(System.Web.UI.WebControls.TableCell) ->
(System.Web.UI.WebControls.GridViewRow) ->
(System.Web.UI.WebControls.ChildTable) ->
GVOfficer (System.Web.UI.WebControls.GridView) ->
MainContent (System.Web.UI.WebControls.ContentPlaceHolder) ->
(System.Web.UI.HtmlControls.HtmlForm) ->
(ASP.site_master) ->
__Page (ASP.account_userportal_aspx)
0
 

Author Comment

by:alright
ID: 34124208
       protected void SDSExistingOfficer_Inserting(object sender, SqlDataSourceCommandEventArgs e)
        {
            DetailsView dtv = (DetailsView)GVOfficer.Controls[0].Controls[0].Controls[0].FindControl("DVInsertExistingOfficer");

            e.Command.Parameters["@contactid"].Value = (dtv.FindControl("DropDownList1") as DropDownList).SelectedValue;
        }

Was the final solution. Thanks so much for all the input!
0
 
LVL 13

Expert Comment

by:AngryBinary
ID: 34124242
Ok, so the template nesting makes this a little trickier. This may require a bit of trial and error, but I think you can locate your DetailsView control using the empty data row from the GridView control:

        protected void SDSExistingOfficer_Inserting(object sender, SqlDataSourceCommandEventArgs e)
        {
            SqlDataSourceView sds = (SqlDataSourceView)sender;

            Table tbl = (Table)GridView1.Controls[0];
            GridViewRow row = (GridViewRow)tbl.Controls[0];
            TableCell cell = row.Cells[0];

            DetailsView dv = (DetailsView)(cell.FindControl("DVInsertExisting"));
            DropDownList ddl = (DropDownList)(dv.FindControl("DropDownList1"));

            sds.InsertParameters["@contactid"].Value = ddl.SelectedValue;

        }
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

661 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question