Hi,

I'm fairly new to ASPX and I've spent the last week trying to figure out how to do this and this is my last resort. My selectcommand statement reads (in part) as;


"SELECT Tbl_2.Word AS Word2, Tbl_1.Word AS Word1..."


My aim is that instead of "Select Tbl_2.Word", I want to use the results within a dropdown list to pick up which table to access therefore something like this (as it works in VBA);


"Select" & Me.DropDownList1.SelectedValue & ".Word AS Word2, " & Me.DropDownList2.SelectedValue & ".Word AS Word1..."


I've tried assigning control parameters, or rewriting the sql at page_load(), however it simply won't work for me. Does anyone have any ideas as to how I could do this?

Ouch, this sounds like a nasty idea!

What's your justification for this design?
Dynamic SQL is a big can of security holed worms.

Can you post what you've tried so far please?

Sorry for the delay.

I actually don't have anything written down on my pages. I keep deleting a re-writing just to get something. My code from my original database was as it is below (although its a lot more complex than what I posted below) I've just made it as simple as possible. All I'm trying to do at the moment is to establish how to do it so I'm using the most simple of statements to generate it. The results are generate within a gridview.

<aspqlDataSource ID="SqlDataSource1"
runat="server"
ConnectionString="<%$ ConnectionStrings:CustomerConnectionString %>"
ProviderName="<%$ ConnectionStrings:CustomConnectionString.ProviderN ame %>"
Selectcommand="SELECT Tbl_Table1.* FROM Tbl_Table1"
EnableViewState="False">

<SelectParameters>

<asp:ControlParameter
ControlID="Table1"
DefaultValue="*"
Name="Param1"
PropertyName="Text"
Type="String" />

</SelectParameters>

</aspqlDataSource>


My VBA in my original database was along the lines of:
"SELECT" & Me.DropDown & ".* FROM "& Me.DropDown

Where the selected value of the dropdown list was the table name.

Actually I've done it now...

I've set the initial selectcommand to run a statement that doesn't display any results, and I've written the SQL string that I need within a page load event, so on page load the select command is re-written. It seemed to do the job.

There isn't anything wrong in doing it this way is there?

I would set the command source to be a commandtable and change the text in page load.

You can cancel select for the sqldatasource by trapping it's onselect event (or whatever it's called) and setting cancel = -1