Solved

Reference DropDownList.SelectedValue / DataValueField in SQL statement

Posted on 2010-11-11
15
1,081 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
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
 
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

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 was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

863 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

22 Experts available now in Live!

Get 1:1 Help Now