Solved

Reference DropDownList.SelectedValue / DataValueField in SQL statement

Posted on 2010-11-11
15
1,096 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
What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

 
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 50 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

734 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