multiple tables

hi,

I have 3 tables: Unix, Windows & Sybase

there are 3 dropdownlists in .asp page,

dropdown1=<displays the lists of table> (Unix, Windows & Sybase) variable as @.table

dropdown2=<displays the list of Columns from the dropdown1 selected table> variable as @.server

dropdown3=<displays the list of Row Header from the dropdown1 selected table> variable as @.user

how shal i define the select statement for this:

it should be something like,

Select * from @.table WHERE Server= @.server AND Row-Header = @.user

I appreciate if you could kindly help me in getting the correct syntax, thanx in advance.

Hi,

the tablename cannot be defined at runtime without using dynamic Sql, compose your SQLString first (e.g. in a stored procedure) then execute it via sp_executesql:

DECLARE @.SQLString VARCHAR(4000)

SET @.SQLString ='Select * from ' + @.table + ' WHERE Server= ''' + @.server + ''' AND Row-Header = ''' + @.user + ''''

But keep in mind that dynamic sql can be dangerous, read the aarticle of Erland first: http://www.sommarskog.se/dynamic_sql.html

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
--

|||

hi,

Thanks a million Jens,

I have done the same way as you explained ( First line: DECLARE @.SQLString VARCHAR(4000) Second line: SET @.SQLString ='Select * from ' + @.table + ' WHERE Server= ''' + @.server + ''' AND Row-Header = ''' + @.user + '''').executed the .asp page: it shows only the dropdown & no table. Appreciate your help, many thanks again for this:

-

execution script part:
-
<script runat="server">
Sub OSClick1(ByVal sender As Object, ByVal e As EventArgs)
'EXEC "@.SQLString"'
End Sub
</script>
-
processing script part:
-
<asp:Button ID="btnOSClick1" runat="server" Text="Get" Width="90px" OnClick="OSClick1" /><br />
<br />
<asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource4">
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource4" runat="server" ConnectionString="<%$ ConnectionStrings:Unix.Primary %>"
SelectCommand="CREATE PROCEDURE general_select @.table nvarchar(127), @.server nvarchar(127), @.user nvarchar(127),&#13;&#10;DECLARE @.SQLString VARCHAR(4000)&#13;&#10;SET @.SQLString ='Select * from ' + @.table + ' WHERE Server= ''' + @.server + ''' AND Row-Header = ''' + @.user + ''''&#13;&#10;'">
<SelectParameters>
<asp:ControlParameter ControlID="txtOS" Name="table" PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="txtSrvUnix" Name="server" PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="txtUnixDes" Name="user" PropertyName="SelectedValue" />
<asp:Parameter Name="SQLString" />
</SelectParameters>
</asp:SqlDataSource>
-

|||Hi,

I am not very familiar with those new SQLDataSource mechanism, but you do not need to create the procedure as a Select Command. Create the procedure before and do just execute the procedure as the Select Command. In plain C# code you will have to assign the Commandtype = StoredProcedure first to the Select command (You will probably have to do this here also) and then just write something like SelectCommand="general_Select"

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de