Solved

Reference DropDownList.SelectedValue / DataValueField in SQL statement

Posted on 2010-11-11
15
1,075 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
  • 8
  • 4
  • 3
15 Comments
 
LVL 13

Expert Comment

by:AngryBinary
Comment Utility
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
Comment Utility
Could not find control 'DropDownList1' in ControlParameter 'contactid'.

Is the error I'm getting with that InsertParameter
0
 

Author Comment

by:alright
Comment Utility
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
 
LVL 4

Expert Comment

by:MSSystems
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 4

Expert Comment

by:MSSystems
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
       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
Comment Utility
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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now