Go Premium for a chance to win a PS4. Enter to Win

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

Reference DropDownList.SelectedValue / DataValueField in SQL statement

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
alright
Asked:
alright
  • 8
  • 4
  • 3
1 Solution
 
AngryBinaryCommented:
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
 
alrightAuthor Commented:
Could not find control 'DropDownList1' in ControlParameter 'contactid'.

Is the error I'm getting with that InsertParameter
0
 
alrightAuthor Commented:
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
MSSystemsCommented:
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
 
MSSystemsCommented:
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
 
alrightAuthor Commented:
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
 
AngryBinaryCommented:
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
 
MSSystemsCommented:
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
 
alrightAuthor Commented:
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
 
alrightAuthor Commented:
MSSystems: Do you have that in C#?

Befuddling how difficult this is turning out to be to perform such a seemingly simple procedure :(
0
 
AngryBinaryCommented:
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
 
alrightAuthor Commented:
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
 
alrightAuthor Commented:
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
 
alrightAuthor Commented:
       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
 
AngryBinaryCommented:
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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

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