SQL Select statment + best practice

[TrAcER]

hi dudes

im writing a asp.net shopping application, useing a SQL backend basicly this is how i want it to work, i have a drop down box that is bound to a nidex table, in this table are 2 columbs, the name of the table & the SQL name of the table, when a user selects a name from that drop down box it fires the dropdownbox change event off, BUT this is what i dont know what to do

in order to fill my datalist i need 2 sql select statments, the first should select sqlname from db.index where strDisplayName=var

thats ok, problem is it returns a dataset, and the only thig i want to come back is a string, so i can then bind that to a variable and use it in the second sql stament to tell it what tabe to open.

any help asap would be amazing coz im strugling with this now dudes

Cheers

Si

[TrAcER]

<%@ Page Language="VB" %>
<%@ Register TagPrefix="wmx" Namespace="Microsoft.Matrix.Framework.Web.UI" Assembly="Microsoft.Matrix.Framework, Version=0.6.0.0, Culture=neutral, PublicKeyToken=6f763c9966660626" %>
<script runat="server">

    Sub Page_Load(Source as Object, E as EventArgs)
        If Not Page.IsPostBack Then
            DropDownList1.DataTextField = "strSet"
            DropDownList1.DataSource = GetSet()
            DropDownList1.DataBind()
        Else
    
        End If
    End Sub
    
    
        Function GetSet() As System.Data.DataSet
            Dim connectionString As String = "server='(local)'; trusted_connection=true; database='DNScards'"
            Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
    
            Dim queryString As String = "SELECT [MTGindex].[strSet] FROM [MTGindex]"
            Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
            dbCommand.CommandText = queryString
            dbCommand.Connection = dbConnection
    
            Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter
            dataAdapter.SelectCommand = dbCommand
            Dim dataSet As System.Data.DataSet = New System.Data.DataSet
            dataAdapter.Fill(dataSet)
    
            Return dataSet
        End Function
    
        Function GetSet2(ByVal strSet As String)
            Dim connectionString As String = "server='(local)'; trusted_connection=true; database='DNScards'"
            Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
    
            Dim queryString As String = "SELECT [MTGindex].[strTable] FROM [MTGindex] WHERE ([MTGindex].[strSet] = @strSet"& _
    ")"
            Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
            dbCommand.CommandText = queryString
            dbCommand.Connection = dbConnection
    
            Dim dbParam_strSet As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
            dbParam_strSet.ParameterName = "@strSet"
            dbParam_strSet.Value = strSet
            dbParam_strSet.DbType = System.Data.DbType.String
            dbCommand.Parameters.Add(dbParam_strSet)
    
            dbConnection.Open
            Dim dataResult As string = dbCommand.ExecuteScalar().ToString
            getset2=dataResult
            dbConnection.Close
        End Function
    
        Function GetList(byval table as string) As System.Data.DataSet
    
            Dim connectionString As String = "server='(local)'; trusted_connection=true; database='DNScards'"
            Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
    
            Dim queryString As String = "SELECT [" & table & "].* FROM [" & table & "]"
            Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
            dbCommand.CommandText = queryString
            dbCommand.Connection = dbConnection
    
            Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter
            dataAdapter.SelectCommand = dbCommand
            Dim dataSet As System.Data.DataSet = New System.Data.DataSet
            dataAdapter.Fill(dataSet)
    
            Return dataSet
        End Function
    
    Sub DropDownList1_SelectedIndexChanged(sender As Object, e As EventArgs)
       'datalist1.datasource=GetSet2(DropDownList1.SelectedItem.Text)
       dim table
        table=GetSet2(DropDownList1.SelectedItem.Text)
       datalist1.datasource=GetList(table)
       datalist1.DataBind()
    End Sub

</script>
<html>
<head>
</head>
<body>
    <form runat="server">
        <p>
            <asp:DropDownList id="DropDownList1" runat="server" AutoPostBack="True" OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged"></asp:DropDownList>
        </p>
        <p>
            <wmx:SqlDataSourceControl id="SqlDataSourceControl1" runat="server" DeleteCommand="" SelectCommand="SELECT * FROM [MTGindex]" UpdateCommand="" ConnectionString="server='(local)'; trusted_connection=true; database='DNScards'"></wmx:SqlDataSourceControl>
        </p>
        <p>
            <asp:DataList id="DataList1" runat="server">
                <ItemTemplate>
                    <asp:Label id="Label1" runat="server" text='<%# DataBinder.Eval(Container, "DataItem.strTitle") %>'></asp:Label> 
                </ItemTemplate>
            </asp:DataList>
        </p>
        <!-- Insert content here -->
    </form>
</body>
</html>

ok if you dump the above into webmatrix or something, or save it as an aspx, its gonna fail unless u have my database but
what it does do is the following

i have db structure as follows
MTGindex
-strSet
-strTable
crdExample
-No point listing columbs

so whent he web form starts up it adds all the strSet records to a dropdownlist, then when the dropdownlist changes it takes the value of the list box, fires a query off to the MTGindex table again to return the record matching the text in the listbox, it then uses the strTable result that gets returned as a string as the table name

probuibl better ways to do this, or maybe this is futhest from best practices or something dunno, but basicly alows you to index all your tables and give them a nice display name, if you want to keep your SQL db tidy, anyone got n e thing to add let me know

and to all thoese budding programmers out there, dont worry about using other peoples code, it doesnt make you a worse programmer, at the end of the day, aslong as the result is A) produced and B) the best result that can be produced, you have suceeded, the more you code the more you will pick up, i know programmers that have been writing for like 20+ years, that i have put to shame and i have been doing this for only 2 , i have the upmost respect for them, but just because they have been at it for time and a half dosent make then anymore capable of produceing the results
</end rant>