?
Solved

auto select database and tables on the fly

Posted on 2009-04-06
4
Medium Priority
?
538 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 1500 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

718 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