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.

Amr Bekhit
  • 4,613
  • 8
  • 34
  • 56

2 Answers2

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
  • 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!) – Colin Mackay Dec 15 '10 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? –  Dec 15 '10 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. – Colin Mackay Dec 15 '10 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. – Amr Bekhit Dec 15 '10 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? – Amr Bekhit Dec 15 '10 at 21:25
  • After doing some more testing and searching, I'm coming to the conclusion that I cannot use parameters to change the table name of a query and will have to do the data binding in code behind. – Amr Bekhit Dec 20 '10 at 11:14
-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?

Rashmi
  • 1