Solved

auto select database and tables on the fly

Posted on 2009-04-06
4
523 Views
Last Modified: 2013-12-17
I have a working page that I have hard coded database and tables to be selected and display the data. I would like to be able to select a database and then a corresponding table on the fly from the following path C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

<%@ Page Language="C#"%>

<%@ Import Namespace="System.Data" %>

<%@ Import Namespace="System.Data.SqlClient" %>

<script runat="server">

    public DataView Source;

    public DataSet ds;

    public bool getSchema, getData;

    string selectString = "";

        string[][] tables = new string[][]

            {

               new string[] {"authors", "discounts", "employee", "jobs", "publishers", "sales", "stores", "titleauthor", "titles", "categories", "AspNet_SqlCacheTablesForChangeNotification", "Products", "ShoppingCarts", "Orders", "OrderItems"},

               new string[] {"Categories", "Customers", "Employees", "Orders", "Products", "Region", "Shippers", "Suppliers", "Territories"},

               new string[] {"AspNet_CacheTableForChangeNotification", "Categories", "Products", "ShoppingCarts", "Orders", "OrderItems"}, 

            };

        public void Page_Load(object sender, EventArgs e)

        {

            if ( !IsPostBack )

            {

                database_SelectedIndexChanged(database, null);

            }

            Init();

        }

        public void Init()

        {

            selectString = "select * from " + table.SelectedItem.Text;

            query.Text = selectString;

            db.Text = database.SelectedItem.Text;

            String otherString = db.Text;

            String anotherString = table.SelectedItem.Text;

            string valueForTesting = "";

            string valueForTesting1 = "";

            string valueForTesting2 = "";

            string valueForTesting3 = "";

            if ("Pubs" == otherString && "authors" == anotherString)

                {

                    valueForTesting = "defaultselect1";

                    Session["Testing"] = valueForTesting;

                    valueForTesting1 = "defaultselect2";

                    Session["Testing1"] = valueForTesting1;

                    valueForTesting2 = "defaultselect3";

                    Session["Testing2"] = valueForTesting2;

                    valueForTesting3 = "defaultselect4";

                    Session["Testing3"] = valueForTesting3;

                }

        }

        public void database_SelectedIndexChanged(Object sender, EventArgs e)

        {

            table.DataSource = tables[database.SelectedIndex];

            table.DataBind();

            Init();

        }

        public void table_SelectedIndexChanged(Object sender, EventArgs e)

        {

            Init();

        }

        public void Submit_Click(Object sender, EventArgs evt) {

            if (IsPostBack)

            {

                if (table.SelectedIndex > -1 && database.SelectedIndex > -1)

                {

                    /*      

                    

                    */

                    string MyString = System.Net.Dns.GetHostName();

                    string otherString = ('#' + MyString + '#');

                    if ('#' + "fuertemon" + '#' == otherString)

                    {

                        if (database.SelectedIndex == 0)

                        {

                            SqlConnection myConnection = new SqlConnection("server=FUERTEMON;database=pubs;User ID=webusertest;Password=h6web97");

                            SqlDataAdapter myCommand = new SqlDataAdapter(selectString, myConnection);

                            ds = new DataSet();

                            myCommand.Fill(ds, "Table");

                            Source = new DataView(ds.Tables[0]);

                            MyDataGrid.DataSource = Source;

                            MyDataGrid.DataBind();

                        }

                        if (database.SelectedIndex == 1)

                        {

                            SqlConnection myConnection = new SqlConnection("server=FUERTEMON;database=Northwind;User ID=webusertest;Password=h6web97");

                            SqlDataAdapter myCommand = new SqlDataAdapter(selectString, myConnection);

                            ds = new DataSet();

                            myCommand.Fill(ds, "Table");

                            Source = new DataView(ds.Tables[0]);

                            MyDataGrid.DataSource = Source;

                            MyDataGrid.DataBind();

                        }

                        if (database.SelectedIndex == 2)

                        {

                            SqlConnection myConnection = new SqlConnection("server=FUERTEMON;database=StoreDB;User ID=webusertest;Password=h6web97");

                            SqlDataAdapter myCommand = new SqlDataAdapter(selectString, myConnection);

                            ds = new DataSet();

                            myCommand.Fill(ds, "Table");

                            Source = new DataView(ds.Tables[0]);

                            MyDataGrid.DataSource = Source;

                            MyDataGrid.DataBind();

                        }

                    }

                }       

            }

         }

    </script>

<html xmlns="http://www.w3.org/1999/xhtml" >

<head runat="server">

    <title>SchemaCS</title>

</head>

<body>

    <form id="form1" runat="server">

    <div>

   <table border="0" cellpadding="5" style="font:10pt verdana">

        <tr>

          <td>

            <b>Table Properties</b><br />

            Database:<br/>

            Table:

          </td>

          <td style="width: 94px">

            <br />

            <asp:DropDownList

             runat="server"

             id="database"

             AutoPostBack="true"

             OnSelectedIndexChanged="database_SelectedIndexChanged">

              <asp:ListItem>Pubs</asp:ListItem>

              <asp:ListItem>Northwind</asp:ListItem> 

              <asp:ListItem>StoreDB</asp:ListItem> 

            </asp:DropDownList><br/>

            <asp:DropDownList

             runat="server"

             id="table"

             AutoPostBack="true"

             OnSelectedIndexChanged="table_SelectedIndexChanged" />

          </td>

        </tr>

        <tr>

          <td colspan="2">

            <b>Query:</b>

            <asp:Label runat="server" id="query" />

            <asp:Label runat="server" id="db" />

            <% string valueOfTesting = (Session["Testing"] != null ? Session["Testing"].ToString() : ""); %>

            <a href="./dataGrid/<% =(Session["Testing"]) %>.aspx">Add</a>

            <a href="./dataGrid/<% =(Session["Testing1"])%>.aspx">Edit</a>

            <a href="./dataGrid/<% =(Session["Testing2"])%>.aspx">Delete</a>

            <a href="./dataGrid/<% =(Session["Testing3"])%>.aspx">Sort</a>

          </td>

          </tr>

        <tr>

          <td valign="top">

            <input id="Submit1" type="submit" runat="server"  onserverclick = "Submit_Click"/>

          </td>

        </tr>

        <tr>

          <td colspan="2">

              <%  if (Page.IsPostBack && ds != null) {  %>

                  <b>Data:</b><br/>

              <%  }  %>

              <ASP:DataGrid id="MyDataGrid"

                BackColor="#EDBE7B"

                BorderColor="black"

                ShowFooter="false"

                CellPadding="3"

                cellspacing="0"

                Font-Names="Verdana"

                Font-Size="8pt"

                HeaderStyle-BackColor="#DC6035"

                EnableViewState="false"

                runat="server"/>

          </td>

        </tr>

        </table>	

    </div>

    </form>

</body>

</html>

Open in new window

0
Comment
Question by:homeshopper
  • 2
  • 2
4 Comments
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 24084858
Question not very clear. Do you want the user to be able to select the database and the table they want to see? If so, Merge the db name with the connection string and then replace the tablename with the name supplied by user. For example

dim dbcon as new oledbconnection(connectionstring & txtdb.text)
dbcon.open
dim dbcmd as new oledbcommand("Select * from " & txtTableName.text)
0
 

Author Comment

by:homeshopper
ID: 24085229
sorry about the clarity. I have about 20 sql databases residing at C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data and do not really want to hard code all the table info from the dbases.
I would first like to be able to sectect a database.
Once selected, fill a dropdown list of available tables in that database.
The query would then display the table data.
I tried to work out a function using schema, but not sure of the prammeters available.
Is there a simpler way of achieving this?
0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 500 total points
ID: 24085261
0
 

Author Comment

by:homeshopper
ID: 24180527
Dear CodeCruiser,
I have converted all my code to vb.net.
The table at the moment are hard coded to tables()
Private tables()() As String = { New String() {"authors", "discounts", "employee", "jobs", "publishers", "sales", "stores", "titleauthor", "titles", "categories", "AspNet_SqlCacheTablesForChangeNotification", "Products", "ShoppingCarts", "Orders", "OrderItems"}, New String() {"Categories", "Customers", "Employees", "Orders", "Products", "Region", "Shippers", "Suppliers", "Territories"}, New String() {"AspNet_CacheTableForChangeNotification", "Categories", "Products", "ShoppingCarts", "Orders", "OrderItems"} }
I wanted to pull out the tables from a given selected database.
My initial code follows at the end of this comment.
To the initial code I made the following alterations:
I do not think I have dimensioned some values correctly and am getting 7 errors:
------------------------------------------------------------------------------------------------
Dim TablesSchema As DataTable = objConn.OpenSchema()
'OpenSchema' is not a member of 'String' C:\Projects\Web\ServerOnLine\dbaseCMS\DefaultPopulateVB.aspx
------------------------------------------------------------------------------------------------
Do While Not TablesSchema.EOF
'EOF' is not a member of 'System.Data.DataTableC:\Projects\Web\ServerOnLine\dbaseCMS\DefaultPopulateVB.aspx
------------------------------------------------------------------------------------------------
If Left(TablesSchema("TABLE_NAME"), 4) = "xml_" Then ready = True
Class 'System.Data.DataTable' cannot be indexed because it has no default property.C:\Projects\Web\ServerOnLine\dbaseCMS\DefaultPopulateVB.aspx
------------------------------------------------------------------------------------------------
 If ready And Left(TablesSchema("TABLE_NAME"), 4) <> "xml_" Then started = True
Class 'System.Data.DataTable' cannot be indexed because it has no default property.C:\Projects\Web\ServerOnLine\dbaseCMS\DefaultPopulateVB.aspx
------------------------------------------------------------------------------------------------
tables(i) = TablesSchema("TABLE_NAME")
Class 'System.Data.DataTable' cannot be indexed because it has no default property.C:\Projects\Web\ServerOnLine\dbaseCMS\DefaultPopulateVB.aspx
------------------------------------------------------------------------------------------------
TablesSchema.MoveNext()
'MoveNext' is not a member of 'System.Data.DataTable'.C:\Projects\Web\ServerOnLine\dbaseCMS\DefaultPopulateVB.aspx
------------------------------------------------------------------------------------------------
<%Dim valueOfTesting As String = (If(Session("Testing") IsNot Nothing, Session("Testing").ToString(), "")) %>
Expression expected. C:\Projects\Web\ServerOnLine\dbaseCMS\DefaultPopulateVB.aspx
------------------------------------------------------------------------------------------------

<%@ Page Language="VB"%>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<script runat="server">
    Public Source As DataView
      Public ds As DataSet
      Public getSchema, getData As Boolean
      Private selectString As String = ""
    'Private tables()() As String = { New String() {"authors", "discounts", "employee", "jobs", "publishers", "sales", "stores", "titleauthor", "titles", "categories", "AspNet_SqlCacheTablesForChangeNotification", "Products", "ShoppingCarts", "Orders", "OrderItems"}, New String() {"Categories", "Customers", "Employees", "Orders", "Products", "Region", "Shippers", "Suppliers", "Territories"}, New String() {"AspNet_CacheTableForChangeNotification", "Categories", "Products", "ShoppingCarts", "Orders", "OrderItems"} }
   
    Private tables()() As String
    Dim objConn As String
    Dim OpenSchema As DataTable
    Dim ready As Boolean
    Dim started As Boolean
    Dim TablesSchema As DataTable = objConn.OpenSchema()
   
    Public Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
        If (Not IsPostBack) Then
            database_SelectedIndexChanged(database, Nothing)
            Dim i As Integer = 0
            Do While Not TablesSchema.EOF
                If Left(TablesSchema("TABLE_NAME"), 4) = "xml_" Then ready = True
                If ready And Left(TablesSchema("TABLE_NAME"), 4) <> "xml_" Then started = True
                If Started Then
                    i = i + 1
                    tables(i) = TablesSchema("TABLE_NAME")
                End If
                TablesSchema.MoveNext()
            Loop
               
                End If
                Init()
    End Sub

The original initial working code is as attached code snippet
<%@ Page Language="VB"%>

<%@ Import Namespace="System.Data" %>

<%@ Import Namespace="System.Data.SqlClient" %>

<script runat="server">

    Public Source As DataView

	Public ds As DataSet

	Public getSchema, getData As Boolean

	Private selectString As String = ""

	Private tables()() As String = { New String() {"authors", "discounts", "employee", "jobs", "publishers", "sales", "stores", "titleauthor", "titles", "categories", "AspNet_SqlCacheTablesForChangeNotification", "Products", "ShoppingCarts", "Orders", "OrderItems"}, New String() {"Categories", "Customers", "Employees", "Orders", "Products", "Region", "Shippers", "Suppliers", "Territories"}, New String() {"AspNet_CacheTableForChangeNotification", "Categories", "Products", "ShoppingCarts", "Orders", "OrderItems"} }

	

    Public Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)

        If (Not IsPostBack) Then

            database_SelectedIndexChanged(database, Nothing)

        End If

        Init()

    End Sub

		

		Public Sub Init()

			selectString = "select * from " & table.SelectedItem.Text

			query.Text = selectString

			db.Text = database.SelectedItem.Text

			Dim otherString As String = db.Text

			Dim anotherString As String = table.SelectedItem.Text

			Dim valueForTesting As String = ""

			Dim valueForTesting1 As String = ""

			Dim valueForTesting2 As String = ""

			Dim valueForTesting3 As String = ""

			

			If "Pubs" = otherString AndAlso "authors" = anotherString Then

					valueForTesting = "defaultselect1"

					Session("Testing") = valueForTesting

					valueForTesting1 = "defaultselect2"

					Session("Testing1") = valueForTesting1

					valueForTesting2 = "defaultselect3"

					Session("Testing2") = valueForTesting2

					valueForTesting3 = "defaultselect4"

					Session("Testing3") = valueForTesting3

			End If

		End Sub

		

		Public Sub database_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)

			table.DataSource = tables(database.SelectedIndex)

			table.DataBind()

			Init()

		End Sub

		

		Public Sub table_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)

			Init()

		End Sub

		

		Public Sub Submit_Click(ByVal sender As Object, ByVal evt As EventArgs)

			If IsPostBack Then

				If table.SelectedIndex > -1 AndAlso database.SelectedIndex > -1 Then

					Dim MyString As String = System.Net.Dns.GetHostName()

                Dim otherString As String = ("#" & MyString & "#")

                If "#" & "fuertemon" & "#" = otherString Then

                    If database.SelectedIndex = 0 Then

                        Dim myConnection As New SqlConnection("server=FUERTEMON;database=pubs;User ID=sa;Password=h6web97")

                        Dim myCommand As New SqlDataAdapter(selectString, myConnection)

                        ds = New DataSet()

                        myCommand.Fill(ds, "Table")

                        Source = New DataView(ds.Tables(0))

                        MyDataGrid.DataSource = Source

                        MyDataGrid.DataBind()

                    End If

                    If database.SelectedIndex = 1 Then

                        Dim myConnection As New SqlConnection("server=FUERTEMON;database=Northwind;User ID=sa;Password=h6web97")

                        Dim myCommand As New SqlDataAdapter(selectString, myConnection)

                        ds = New DataSet()

                        myCommand.Fill(ds, "Table")

                        Source = New DataView(ds.Tables(0))

                        MyDataGrid.DataSource = Source

                        MyDataGrid.DataBind()

                    End If

                    If database.SelectedIndex = 2 Then

                        Dim myConnection As New SqlConnection("server=FUERTEMON;database=StoreDB;User ID=sa;Password=h6web97")

                        Dim myCommand As New SqlDataAdapter(selectString, myConnection)

                        ds = New DataSet()

                        myCommand.Fill(ds, "Table")

                        Source = New DataView(ds.Tables(0))

                        MyDataGrid.DataSource = Source

                        MyDataGrid.DataBind()

                    End If

                End If

				End If

			End If

		End Sub

	</script>

<html xmlns="http://www.w3.org/1999/xhtml" >

<head id="Head1" runat="server">

    <title>SchemaCS</title>

</head>

<body>

    <form id="form1" runat="server">

    <div>

   <table border="0" cellpadding="5" style="font:10pt verdana">

        <tr>

          <td>

            <b>Table Properties</b><br />

            Database:<br/>

            Table:

          </td>

          <td style="width: 94px">

            <br />

            <asp:DropDownList

             runat="server"

             id="database"

             AutoPostBack="true"

             OnSelectedIndexChanged="database_SelectedIndexChanged">

              <asp:ListItem>Pubs</asp:ListItem>

              <asp:ListItem>Northwind</asp:ListItem> 

              <asp:ListItem>StoreDB</asp:ListItem> 

            </asp:DropDownList><br/>

            <asp:DropDownList

             runat="server"

             id="table"

             AutoPostBack="true"

             OnSelectedIndexChanged="table_SelectedIndexChanged" />

          </td>

        </tr>

        <tr>

          <td colspan="2">

            <b>Query:</b>

            <asp:Label runat="server" id="query" />

            <asp:Label runat="server" id="db" />

            <%Dim valueOfTesting As String = (If(Session("Testing") IsNot Nothing, Session("Testing").ToString(), "")) %>

            <a href="./<%=(Session("Testing")) %>.asp">Add</a>

            <a href="./<%=(Session("Testing1")) %>.asp">Edit</a>

            <a href="./<%=(Session("Testing2")) %>.asp">Delete</a>

            <a href="./<%=(Session("Testing3")) %>.asp">Sort</a>

          </td>

          </tr>

        <tr>

          <td valign="top">

            <input id="Submit1" type="submit" runat="server"  onserverclick = "Submit_Click"/>

          </td>

        </tr>

        <tr>

          <td colspan="2">

              <%  If (Page.IsPostBack AndAlso ds IsNot Nothing) Then%>

                  <b>Data:</b><br/>

              <%  end if  %>

              <ASP:DataGrid id="MyDataGrid"

                BackColor="#EDBE7B"

                BorderColor="black"

                ShowFooter="false"

                CellPadding="3"

                cellspacing="0"

                Font-Names="Verdana"

                Font-Size="8pt"

                HeaderStyle-BackColor="#DC6035"

                EnableViewState="false"

                runat="server"/>

          </td>

        </tr>

        </table>	

    </div>

    </form>

</body>

</html>

Open in new window

0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
WPF - Tooltips for ComboBox items 5 28
Name space syntax error 12 39
Replace &lt; with < 14 54
Error on link 14 36
This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now