Solved

auto select database and tables on the fly

Posted on 2009-04-06
4
536 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 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
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…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

624 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