While I've been impressed with the ease of building SQL statements with parameters in ASP.NET 2.0, I haven't yet figured out how to take the parameters from a multiple-select listbox control where the number of parameters will vary.

In VBScript and ASP.NET 1.1 I did it by builing an SQL statement with a FOR/NEXT loop and concatenation.

Does ASP.NET have a formal, menu-based approach to handling a varying range of parameters?

Also, if I have to use a FOR/Next loop on the forms collection, could someone provide an example? I haven't yet figured out how to manipulate an actual SQL querystring directly in 2.0

Much appreciated.

Joe

Joe G

If the number of parameters will vary, then building a SQL statement in code is still an acceptable solution, using techniques you've used previously. One thing you must make sure you do is use parameters though, as this protects against SQL Injection attacks, which can be a big security risk. Don't concatenate the values entered by users directly into the SQL - do a search for "SQL Injection" and you'll find lots of details on it.

ASP.NET doesn't have a formal way of handling multiple parameters if the number of parameters us unknown. How you implement this does depend on what exactly you are trying to do, and what controls you want to use. For example, do you want to have a SqlDataSource wth a variable number of parameters, depending upon what the user selects from listboxes? It sounds like you are letting users select a number of columns and you want a SqlDataSource and GridView. If this is the case, then you can do this in code - add parameter objects to the SelectParameters of the SqlDataSource.

Dave

In addition the PAG group has an outline of best-practice on protection from SQL injects. I have worked a little with the PAG group and can testify to their excellent documentation. The article in question can be found here http://msdn.microsoft.com/library/de...aght000002.asp

Chris

Chris Ullman
Programmer/Technical Author
http://www.cuasp.co.uk

Thanks Dave and Chris. I'm on it.

The reason I buy the Wrox books is because of people like you.

Joe


Joe G

Well, guys, I've been researching the web and reviewing your examples for hours and I just can't make the final step to move the results of my For/Next concatenation on my .VB page into AccessDataSource2 on my .aspx page. I've tried all the parameter options -- querystring,control,session variable and just don't get it.

Here's what I have:

Button2 fires sub Button2_Click

Button2_Click receives all the selections and concatenates them into a portion of the SelectCommand property, e.g., "(dbo_NamesView.customer)='Air Canada ' OR (dbo_NamesView.customer)='Airlines ')"

The sub just ends with the variable being created. What do I have to do here to send it back to AccessDataSource2?


Then what do I do in the datasource? Here's one of my tries using queryString.

<asp:AccessDataSource ID="AccessDataSource2" runat="server" DataFile="~/App_Data/InfoTrack.mdb" SelectCommand="SELECT [name], [location], [site], [SA], [SDM], [customer] FROM [dbo_NamesView] WHERE ([customer] = ?)">
            <SelectParameters>
                <asp:QueryStringParameter DefaultValue="UHG" Name="customer" QueryStringField="f_customer"
                    Type="String" />
            </SelectParameters>

</asp:AccessDataSource>

Hoping your patience persists.

Joe




Joe G

Hi Joe,
             We haven't forgotten you or lost patience. It's a bit trickier when the error's in your own code, rather than the book. I was hoping Dave would jump in on this one (it's more his area). I'm wondering if it's databinding, and you just need to call the DataBind() method of the DataSource (this would be my 1.1 centric suggestion). Have you had any luck solving it yourself?

Chris

Chris Ullman
Programmer/Technical Author
http://www.cuasp.co.uk

I'm on the case on this one Chris; I should have posted something. Joe & I have taken it out of the forums, as it's technically not directly related to the book. We'll post more details once we have a solution, then everyone can benefit.

Dave