User input query parameters in Visual Studio

Question

I'm new to both Visual Studio and ASP.NET.

I'm creating this project for my Data Base course in the university and I want to make it web-based with Visual Studio. So I've created my site, imported the DB, etc. and now I want to create a query with user input parameter. Using the query builder I've created the following SQL statement:

SELECT [Column1], [Column2], Column3 
FROM [DataBaseTable]
WHERE ([Column1] = ?)

The next step in the query builder is where I need to define the parameter source. I figured out that I need a control source, using a DropDownList. I've created the list items and assigned the list to the parameter via the control option.

So, now when I launch my site the query is working, getting the value from the first list item. The problem is that I want to change the query value dynamically by choosing a different list item. What do I need to do to make it so?

EDIT: This is the code:

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="AccessDataSource1" Height="269px" Width="923px">
    <Columns>
        <asp:BoundField DataField="Column1" HeaderText="Column1" SortExpression="Column1" />
        <asp:BoundField DataField="Column2" HeaderText="Column2" SortExpression="Column2" />
        <asp:BoundField DataField="Column3" HeaderText="Column3" SortExpression="Column3" />
    </Columns>
</asp:GridView>
<asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="~/Road.accdb" SelectCommand="SELECT [Column1], [Column2], [Column3] FROM [Второкласни пътища] WHERE ([Column1] = ?)">
    <SelectParameters>
        <asp:ControlParameter ControlID="DropDownList1" DefaultValue="" Name="?" PropertyName="SelectedValue" />
    </SelectParameters>
</asp:AccessDataSource>
<asp:DropDownList ID="DropDownList1" runat="server">
    <asp:ListItem>1</asp:ListItem>
    <asp:ListItem>2</asp:ListItem>
    <asp:ListItem>3</asp:ListItem>
</asp:DropDownList>

Michael Richardson · Accepted Answer · 2013-01-05T16:40:47.280

0

Based on your code, I think you may only need one small change. Add "AutoPostBack="true" to your DDL. I would also give your ListItems explicit values, to be more clear. This way you can set the ListItem text to something useful (whatever the number is supposed to represent in your DB).

<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="true">
    <asp:ListItem Value="1">1</asp:ListItem>
    <asp:ListItem Value="2">2</asp:ListItem>
    <asp:ListItem Value="3">3</asp:ListItem>
</asp:DropDownList>

edited Jan 05 '13 at 16:40

answered Jan 05 '13 at 02:38

Michael Richardson

4,213
2
31
48

Ok, I've done some research based on your answer and I think I understand it. Only thing I don't know is where exactly to write this "code behind"? When I click on "View Code" and post it `namespace RoadsProject { public partial class query1 : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } } }` It gives a compilation error. Can you explain in more detail please. – bbahov Jan 05 '13 at 15:36
The code behind may not be necessary since you're doing a full postback. If you remove "OnSelectedIndexChanged="DoSomething"", and change the selection in the dropdown does your query work as expected? – Michael Richardson Jan 05 '13 at 16:10
No. On page load it gets the value from the first list element and if I change the list element from the DropDownList nothing happens. – bbahov Jan 05 '13 at 16:19
"Add "AutoPostBack="true" to your DDL." Thank you dude, this saved my life!:) – bbahov Jan 05 '13 at 16:33
Hey, can I ask you one more question? What about when the control is a textbox/input(text) or something where the user has to type the value? What do I need to do to take the writen value for the parameter of the query? – bbahov Jan 05 '13 at 20:27
I would bind to the control like you did before and use a submit button. – Michael Richardson Jan 05 '13 at 21:07

User input query parameters in Visual Studio

1 Answers1