0

I have an ASP.NET 3.5 web form with a DropDownList bound to a table of company names in a database. I also have a data bound GridView which I would like to update with data from the database depending on the company name selected in the DropDownList, so that the SelectCommand for the GridView's SqlDataSource is:

SELECT Registration, Telephone, Profile FROM {CompanyName}_VehicleData

Where {CompanyName} is whatever is selected in the DropDownList. I've used the Command and Parameter Editor to create a ControlParameter pointing to the SelectedValue of the DropDownList, but I don't know how to write the SelectCommand query to concatenate the parameter to '_VehicleData'. Any thoughts.

Improve this question
2
  • Please post your code. How are you binding your controls? Commented Dec 15, 2010 at 17:02
  • They have been bound using the Visual Studio 2010 IDE wizard that allows you to bind a control to an SqlDataSource. Commented Dec 22, 2010 at 16:48

2 Answers 2

Reset to default
0

If you are using a sqldatasource you could set the select command in the code behind.

<sqldatasource>.SelectCommand = "select registration, telephone, profile " & _
                                "from " & <dropdown>.selectedvalue & "_VehicleData"
<sqldatasource>.SelectType = SqlDataSourceCommandType.Text
Improve this answer
6
  • Dumping values from user controls is a very bad idea. Even if you think you know what you are likely to get there are ways to post back malicious data to the server and have it work with that, rather than with what you expected. Always validate incoming information and never inject control data directly into SQL statements (that's the fastest way to get a SQL Injection Attack!) Commented Dec 15, 2010 at 17:23
  • @Colin, good to know. What would you propose the solution be? Checking the selected value to the dropdown datasource before dropping it in the select command? Commented Dec 15, 2010 at 17:31
  • 1
    Use parameterised queries, and in this case a stored proc that evaluates whether the concatenated value (DropDown.SelectedValue + "_VehicleData") maps to a real table using the INFORMATION_SCHEMA.TABLES view before injecting it into a SQL command. Also, ensure that QUOTENAME(xxx) is used to properly escape the table name just in case it genuinely contains any weird characters. Commented Dec 15, 2010 at 17:34
  • The only issue I have with modifying the SelectCommand is that SqlDataSource objects do not save their properties in the ViewState and so revert back to their design time property values on every postback, which makes working with in my situation is a real pain. I'm sure that this can be done entirely using code behind and without using the SqlDataSources at all, but when I found out about Parameters (learning as I go along), it sounded like a potential solution to my problem without having to write the code myself. Commented Dec 15, 2010 at 20:44
  • @Colin: Surely in this situation this is not an issue since the DropDownList is databound and non-editable by the user so I control exactly what options the user can choose? Commented Dec 15, 2010 at 21:25
-1

@Colin: You said that the solution is writing a stored procedure that evaluates whether the concatenated value (DropDown.SelectedValue + "_VehicleData") maps to a real table using the INFORMATION_SCHEMA.TABLES view before injecting it into a SQL command. Can you please give a code snippet for the stored proc?

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.