Check for table

Discussion in 'ASP .Net Web Controls' started by Morris Neuman, Jan 5, 2009.

  1. Hi,
    How do I check if a table exists in my database?
    --
    Thanks
    Morris
     
    Morris Neuman, Jan 5, 2009
    #1
    1. Advertising

  2. Hi Morris,

    If you're using SQL Server you can try:

    SELECT TABLE_NAME, TABLE_TYPE
    FROM INFORMATION_SCHEMA.TABLES

    Check the result you can see all the base tables and views of the database.

    Please let me know if it works. If you have further questions please feel
    free to ask.

    Regards,
    Allen Chen
    Microsoft Online Community Support

    Delighting our customers is our #1 priority. We welcome your comments and
    suggestions about how we can improve the support we provide to you. Please
    feel free to let my manager know what you think of the level of service
    provided. You can send feedback directly to my manager at:
    .

    ==================================================
    Get notification to my posts through email? Please refer to
    http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

    Note: MSDN Managed Newsgroup support offering is for non-urgent issues
    where an initial response from the community or a Microsoft Support
    Engineer within 2 business day is acceptable. Please note that each follow
    up response may take approximately 2 business days as the support
    professional working with you may need further investigation to reach the
    most efficient resolution. The offering is not appropriate for situations
    that require urgent, real-time or phone-based interactions. Issues of this
    nature are best handled working with a dedicated Microsoft Support Engineer
    by contacting Microsoft Customer Support Services (CSS) at
    http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
    ==================================================
    This posting is provided "AS IS" with no warranties, and confers no rights.
     
    Allen Chen [MSFT], Jan 6, 2009
    #2
    1. Advertising

  3. Thanks for the reply.

    I did not make myself very clear. I want to do the following:

    I have a hyperlink field as a column in a gridview. I only want to set this
    hyperlink/column to be visible if a table (mailboxactivitylog) exists in
    either the sql or access database bound to the gridview via datasource.

    How do I check if this table exists? I have a master page and the gridview
    is on the content page.
    --
    Thanks
    Morris


    "Allen Chen [MSFT]" wrote:

    > Hi Morris,
    >
    > If you're using SQL Server you can try:
    >
    > SELECT TABLE_NAME, TABLE_TYPE
    > FROM INFORMATION_SCHEMA.TABLES
    >
    > Check the result you can see all the base tables and views of the database.
    >
    > Please let me know if it works. If you have further questions please feel
    > free to ask.
    >
    > Regards,
    > Allen Chen
    > Microsoft Online Community Support
    >
    > Delighting our customers is our #1 priority. We welcome your comments and
    > suggestions about how we can improve the support we provide to you. Please
    > feel free to let my manager know what you think of the level of service
    > provided. You can send feedback directly to my manager at:
    > .
    >
    > ==================================================
    > Get notification to my posts through email? Please refer to
    > http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.
    >
    > Note: MSDN Managed Newsgroup support offering is for non-urgent issues
    > where an initial response from the community or a Microsoft Support
    > Engineer within 2 business day is acceptable. Please note that each follow
    > up response may take approximately 2 business days as the support
    > professional working with you may need further investigation to reach the
    > most efficient resolution. The offering is not appropriate for situations
    > that require urgent, real-time or phone-based interactions. Issues of this
    > nature are best handled working with a dedicated Microsoft Support Engineer
    > by contacting Microsoft Customer Support Services (CSS) at
    > http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
    > ==================================================
    > This posting is provided "AS IS" with no warranties, and confers no rights.
    >
    >
     
    Morris Neuman, Jan 7, 2009
    #3
  4. Hi Morris,

    Thanks for your clarification. If my understanding is correct you have two
    questions:

    1. How to know if a tables exists in the database.
    2. How to hide a column of a GridView manually.

    To the first question, we can query the database to see all the tables in
    it. Then check if the table exists in the database. Generally database has
    some system tables that can help to do this. For SQL Server, it's
    INFORMATION_SCHEMA.TABLES. So we can use the following query:

    SELECT TABLE_NAME, TABLE_TYPE
    FROM INFORMATION_SCHEMA.TABLES

    For Access database we use MSysObjects. Here's the query for Access:

    Select Name from MSysObjects

    We can check if the table name exists in the result of the query and then
    decide whether to hide the column of the GridView, which is your second
    question.

    To hide the column we can try:

    void GridView1_PreRender(object sender, EventArgs e)
    {
    GridView g = (GridView)sender;
    g.Columns[0].Visible = false;.//Change the index please. Here 0
    is used.
    }

    If it's not what you need please provide your current code. I think it
    would be a great start for us to discuss based on your code.

    Regards,
    Allen Chen
    Microsoft Online Community Support
     
    Allen Chen [MSFT], Jan 7, 2009
    #4
  5. Hi,

    Thanks for the response. I tried your method but could not get it to work.
    I checked the MSDN help and tried examples of using the DataTableCollection
    through the Tables property, however could not get that to work either.

    1) Can you show in the code below how I would use your mehtod as well as the
    one using DataTableCollection?
    2) Which is the better way to check for a table?

    My code is:

    protected void Page_Load(object sender, EventArgs e)
    {
    //check web.config if system app setting set for sql or access
    //MyDataSource will always be either SqlDataSource1 or
    AccessDataSource1
    string id = ConfigurationManager.AppSettings["MyDataSource"];
    Control datasourcecontrol = this.FindControl(id);


    // Get the DataSet of a DataGrid.
    //AS I DON'T HAVE a GRID NOT SURE HOW TO GET THE DATASET
    //dSet = (DataSet)DataGrid1.DataSource;

    // Get the DataTableCollection through the Tables property.
    DataTableCollection tablesCol = dSet.Tables;

    // Check if the named table exists.
    if (tablesCol.Contains("MailboxActivityLog"))
    {
    HyperLink11.Visible = "true";
    }
    else
    {
    HyperLink11.Visible = "false";
    }
    }


    3) I also found an example using the following in the page load but could
    not get that to work either.
    if Exists (Select MailboxActivityLog From INFORMATION_SCHEMA.TABLES)
    {
    HyperLink11.Visible = "true";
    }
    else
    {
    HyperLink11.Visible = "false";
    }

    Once again, I am looking to you for help and to point me in the right
    direction.
    --
    Thanks
    Morris


    "Allen Chen [MSFT]" wrote:

    > Hi Morris,
    >
    > Thanks for your clarification. If my understanding is correct you have two
    > questions:
    >
    > 1. How to know if a tables exists in the database.
    > 2. How to hide a column of a GridView manually.
    >
    > To the first question, we can query the database to see all the tables in
    > it. Then check if the table exists in the database. Generally database has
    > some system tables that can help to do this. For SQL Server, it's
    > INFORMATION_SCHEMA.TABLES. So we can use the following query:
    >
    > SELECT TABLE_NAME, TABLE_TYPE
    > FROM INFORMATION_SCHEMA.TABLES
    >
    > For Access database we use MSysObjects. Here's the query for Access:
    >
    > Select Name from MSysObjects
    >
    > We can check if the table name exists in the result of the query and then
    > decide whether to hide the column of the GridView, which is your second
    > question.
    >
    > To hide the column we can try:
    >
    > void GridView1_PreRender(object sender, EventArgs e)
    > {
    > GridView g = (GridView)sender;
    > g.Columns[0].Visible = false;.//Change the index please. Here 0
    > is used.
    > }
    >
    > If it's not what you need please provide your current code. I think it
    > would be a great start for us to discuss based on your code.
    >
    > Regards,
    > Allen Chen
    > Microsoft Online Community Support
    >
    >
     
    Morris Neuman, Jan 8, 2009
    #5
  6. Hi Morris,

    Thanks for your update. I've made a sample that demonstrates how to do
    this. The database I used is the Northwind database. If you have no that
    database you can change the NorthwindConnectionString1 setting in the
    web.config:


    <connectionStrings>
    <add name="NorthwindConnectionString1" connectionString="Your Connection
    String" providerName="System.Data.SqlClient"/>
    </connectionStrings>


    Here's the code.

    Aspx:

    <asp:TextBox ID="TextBox1" runat="server" Text="Orders"></asp:TextBox>
    <asp:Label ID="Label1" runat="server" Text=""></asp:Label>
    <asp:Button ID="Button1"
    runat="server" Text="Check" onclick="Button1_Click" />
    <asp:GridView ID="GridView1" runat="server">
    <Columns>
    <asp:TemplateField>
    <ItemTemplate>
    <asp:LinkButton ID="LinkButton1" runat="server">Yes the table
    is in the database!</asp:LinkButton>
    </ItemTemplate></asp:TemplateField></Columns>
    </asp:GridView>

    Aspx.cs:

    protected void Button1_Click(object sender, EventArgs e)
    {
    using (SqlConnection sc = new
    SqlConnection(ConfigurationManager.ConnectionStrings["NorthwindConnectionStr
    ing1"].ToString()))
    {

    SqlCommand command = new SqlCommand(@"SELECT
    TABLE_NAME, TABLE_TYPE
    FROM INFORMATION_SCHEMA.TABLES", sc);
    SqlDataAdapter sa = new SqlDataAdapter(command);
    DataTable dt = new DataTable();
    sa.Fill(dt);

    //The following code just shows the table on the page,
    which can provide a direct vision of the data retrieved.
    this.GridView1.DataSource = dt;
    this.GridView1.DataBind();
    ///////////////////////////

    //The key here, to see if the table exists in the database
    if you also want to check the views, please check dr[0] only.
    string tablename = this.TextBox1.Text;
    bool hasfound = false;
    foreach (DataRow dr in dt.Rows)
    {
    if (dr[1].ToString() == "BASE TABLE" &&
    dr[0].ToString() == tablename)
    {

    Label1.Text = "The table " + tablename + " is in
    the database";
    this.GridView1.Columns[0].Visible = true;
    hasfound = true;
    break;
    }

    }
    if (!hasfound)
    {
    Label1.Text = "The table " + tablename + " is NOT in
    the database";
    this.GridView1.Columns[0].Visible = false;
    }
    }
    }

    To test, you can enter an arbitary table name in the TextBox and click the
    "Check" button. You'll see the Lable shows if the table exists in the
    database and a hyperlink column in the GridView will also be
    visible/invisible according to the result.
    As to Access database we don't use:

    SELECT TABLE_NAME, TABLE_TYPE
    FROM INFORMATION_SCHEMA.TABLES

    We use this query instead:

    Select Name from MSysObjects

    If there's anything unclear please feel free to let me know. I'll do my
    best to provide a clearer explanation.

    Regards,
    Allen Chen
    Microsoft Online Support
     
    Allen Chen [MSFT], Jan 9, 2009
    #6
  7. Hi Morris,

    I'm contacting you to see if you have tested my code and what the test
    result is. I want to see if the information provided was helpful. Please
    keep me posted on your progress and let us know if you have any additional
    questions or concerns.

    I'm looking forward to your response.


    Regards,
    Allen Chen
    Microsoft Online Community Support
     
    Allen Chen [MSFT], Jan 13, 2009
    #7
  8. Hi Morris,

    I'm contacting you to see if you have tested my code and what the test
    result is. I want to see if the information provided was helpful. Please
    keep me posted on your progress and let us know if you have any additional
    questions or concerns.

    I'm looking forward to your response.


    Regards,
    Allen Chen
    Microsoft Online Community Support
     
    Allen Chen [MSFT], Jan 13, 2009
    #8
  9. Hi Allen,

    Had to make some changes to a section of the website and am running into
    some problems, so have not had a chance to try your code. Hopefully once I
    can get that resolved then I will get back to try your solution.

    A quick follow-up, why doen's the code below work?

    if Exists (Select MailboxActivityLog From INFORMATION_SCHEMA.TABLES)
    {
    HyperLink11.Visible = "true";
    }
    else
    {
    HyperLink11.Visible = "false";
    }

    --
    Thanks
    Morris


    "Allen Chen [MSFT]" wrote:

    > Hi Morris,
    >
    > I'm contacting you to see if you have tested my code and what the test
    > result is. I want to see if the information provided was helpful. Please
    > keep me posted on your progress and let us know if you have any additional
    > questions or concerns.
    >
    > I'm looking forward to your response.
    >
    >
    > Regards,
    > Allen Chen
    > Microsoft Online Community Support
    >
    >
     
    Morris Neuman, Jan 14, 2009
    #9
  10. HI Morris,

    Thanks for your update.

    Quote from Morris==================================================

    A quick follow-up, why doen's the code below work?

    if Exists (Select MailboxActivityLog From INFORMATION_SCHEMA.TABLES)
    {
    HyperLink11.Visible = "true";
    }
    else
    {
    HyperLink11.Visible = "false";
    }
    ==================================================

    Do you get any exception when compiling the above code? If the Exist is a
    method written by you that has one string parameter and return bool you can
    try:

    if(Exists ("SELECT * FROM INFORMATION_SCHEMA.TABLES") )

    Another problem is, the MailboxActivityLog is not a column of the returned
    table. You can check the returned table of the query:

    SELECT *
    FROM INFORMATION_SCHEMA.TABLES

    The * means to select all the column. So the returned table contains all
    the column of the table INFORMATION_SCHEMA.TABLES. You can see
    MailboxActivityLog is not there. Could you let me know what this column is?

    Regards,
    Allen Chen
    Microsoft Online Support
     
    Allen Chen [MSFT], Jan 14, 2009
    #10
  11. Hi Allen,

    I used your solution from 1/9 and it works fine with SQL. Thanks again.

    I could not get it to work with Access.

    Since my users can have either an SQL db or Access db I have to check this
    before checking the database for the table, so I modified the code to check
    for this appsetting.

    The type of database is in my web.config as appsetting and the connection is
    also in the web.config. Below are the definitions in the web.config:
    <connectionStrings>
    <add name="CallMasterSQLConnectionString" connectionString="Data
    Source=4600cJer;Initial Catalog=CALLMasterSQL;Integrated Security=SSPI;"
    providerName="System.Data.SqlClient" />
    <add name="ASPNETDB_ConnectionString" connectionString="Integrated
    Security=SSPI;Persist Security Info=False;Initial Catalog=aspnetdb;Data
    Source=4600cJer;Initial File
    Name=C:\Inetpub\wwwroot\CMWebManager\App_Data\ASPNETDB.MDF" />
    <add name="CALLMasterMDB" connectionString="C:\Program
    Files\CallMaster\Data\Callmaster.mdb"
    providerName="System.Data.OleDB" />
    </connectionStrings>
    <appSettings>
    <add key="MyDataSource" value="SqlDataSource1"/>
    </appSettings>


    Since I was could not see how to define the SQLConnection and command
    outside the using statement, I tried creating variables for the actual
    connection string and select statement and used that variable. However I
    cannot use SQLConnection for Access. I tried the following but get error
    with access. I am using a Master page. Hope you can show me how to change
    the code so I can make it work with either database.

    <%@ Page Language="C#" MasterPageFile="~/MasterPage1.master"
    Title="Admin-Manage CALLMaster" %>

    <script runat="server">

    protected void Page_Load(object sender, EventArgs e)
    {
    //check web.config if system app setting set for sql or access
    //MyDataSource will always be either SqlDataSource1 or
    AccessDataSource1
    string id = ConfigurationManager.AppSettings["MyDataSource"];
    Control datasourcecontrol = this.FindControl(id);

    //SqlConnection sc = new SqlConnection();
    //SqlCommand command;
    string dbconnection = "";
    string dbselect = "";

    //for testing only
    id = "AccessDataSource1";

    if (id.Equals("SqlDataSource1"))
    {
    dbconnection = "CallMasterSQLConnectionString";
    dbselect = "SELECT TABLE_NAME, TABLE_TYPE FROM
    INFORMATION_SCHEMA.TABLES ORDER BY TABLE_NAME";
    }
    else
    {
    dbconnection = "CALLMasterMDB";
    dbselect = "Select Name from MSysObjects";
    }


    using (SqlConnection sc = new
    SqlConnection(ConfigurationManager.ConnectionStrings[dbconnection].ToString()))
    {
    SqlCommand command = new SqlCommand(@dbselect, sc);

    SqlDataAdapter sa = new SqlDataAdapter(command);
    DataTable dt = new DataTable();
    sa.Fill(dt);

    //The following code just shows the table on the page, which can
    provide a direct vision of the data retrieved.
    this.GridView2.DataSource = dt;
    this.GridView2.DataBind();
    ///////////////////////////

    //The key here, to see if the table exists in the database if
    you also want to check the views, please check dr[0] only.
    //string tablename = this.TextBox1.Text;
    string tablename1 = "AttendantActivityLogX";
    bool hasfound_tb1 = false;
    string tablename2 = "MailboxActivityLogX";
    bool hasfound_tb2 = false;

    foreach (DataRow dr in dt.Rows)
    {
    if (dr[1].ToString() == "BASE TABLE" && dr[0].ToString() ==
    tablename1)
    {
    hasfound_tb1 = true;
    }
    if (dr[1].ToString() == "BASE TABLE" && dr[0].ToString() ==
    tablename2)
    {
    hasfound_tb2 = true;
    }
    if ((hasfound_tb1) && (hasfound_tb2))
    {
    break;
    }
    }

    if ((!hasfound_tb1) && (!hasfound_tb2))
    {
    Label3.Text = "Tables " + tablename1 + " and " + tablename2
    + " are NOT in the database";
    HyperLink10.Visible = false;
    HyperLink11.Visible = false;
    //this.GridView1.Columns[0].Visible = false;
    }
    else
    {
    if ((hasfound_tb1) && (!hasfound_tb2))
    {
    Label3.Text = "Table " + tablename2 + " is NOT in the
    database";
    HyperLink11.Visible = false;
    //this.GridView1.Columns[0].Visible = true;
    }
    else
    {
    Label3.Text = "Table " + tablename1 + " is NOT in the
    database";
    HyperLink10.Visible = false;
    //this.GridView1.Columns[0].Visible = true;
    }
    }
    }
    }
    </script>

    Look forward to your help.

    --
    Thanks
    Morris


    "Allen Chen [MSFT]" wrote:

    > HI Morris,
    >
    > Thanks for your update.
    >
    > Quote from Morris==================================================
    >
    > A quick follow-up, why doen's the code below work?
    >
    > if Exists (Select MailboxActivityLog From INFORMATION_SCHEMA.TABLES)
    > {
    > HyperLink11.Visible = "true";
    > }
    > else
    > {
    > HyperLink11.Visible = "false";
    > }
    > ==================================================
    >
    > Do you get any exception when compiling the above code? If the Exist is a
    > method written by you that has one string parameter and return bool you can
    > try:
    >
    > if(Exists ("SELECT * FROM INFORMATION_SCHEMA.TABLES") )
    >
    > Another problem is, the MailboxActivityLog is not a column of the returned
    > table. You can check the returned table of the query:
    >
    > SELECT *
    > FROM INFORMATION_SCHEMA.TABLES
    >
    > The * means to select all the column. So the returned table contains all
    > the column of the table INFORMATION_SCHEMA.TABLES. You can see
    > MailboxActivityLog is not there. Could you let me know what this column is?
    >
    > Regards,
    > Allen Chen
    > Microsoft Online Support
    >
    >
     
    Morris Neuman, Jan 14, 2009
    #11
  12. Hi Morris,

    Here's the code for Access Database:

    Aspx:

    <asp:TextBox ID="TextBox1" runat="server" Text="Orders"></asp:TextBox>
    <asp:Label ID="Label1" runat="server" Text=""></asp:Label>
    <asp:Button ID="Button1"
    runat="server" Text="Check" onclick="Button1_Click" />
    <asp:GridView ID="GridView1" runat="server">
    <Columns>
    <asp:TemplateField>
    <ItemTemplate>
    <asp:LinkButton ID="LinkButton1" runat="server">Yes the table
    is in the database!</asp:LinkButton>
    </ItemTemplate></asp:TemplateField></Columns>
    </asp:GridView>

    Aspx.cs:

    protected void Button1_Click(object sender, EventArgs e)
    {
    using (OleDbConnection oc = new
    OleDbConnection(ConfigurationManager.ConnectionStrings["Database1ConnectionS
    tring"].ToString()))
    {

    oc.Open();
    DataTable dt =oc.GetSchema("tables");

    //The following code just shows the table on the page,
    which can provide a direct vision of the data retrieved.
    this.GridView1.DataSource = dt;
    this.GridView1.DataBind();
    ///////////////////////////

    //The key here, to see if the table exists in the database
    if you also want to check the views, please check dr[0] only.
    string tablename = this.TextBox1.Text;
    bool hasfound = false;
    foreach (DataRow dr in dt.Rows)
    {
    if (dr[3].ToString() == "TABLE" &&
    dr[2].ToString() == tablename)
    {

    Label1.Text = "The table " + tablename + " is in
    the database";
    this.GridView1.Columns[0].Visible = true;
    hasfound = true;
    break;
    }

    }
    if (!hasfound)
    {
    Label1.Text = "The table " + tablename + " is NOT in
    the database";
    this.GridView1.Columns[0].Visible = false;
    }
    }
    }

    You can compare the above code with the code I provided in my previous
    post, that is for Sql database. The main difference is to use the classes
    under the System.Data.OleDb namespace instead of the classes under the
    System.Data.SqlClient namespace. For example to use OleDbConnection instead
    of SqlConnection. Another difference is the query. Though the query I
    provided before can work for Access database it needs additional security
    settings. To make it easier for you to test I used another way here, that
    is to use OleDbConnection.GetSchema() method to get all the tables
    information from the Access database. You can merge the above code into
    your existing code. If you have any questions about the code please feel
    free to ask.

    Regards,
    Allen Chen
    Microsoft Online Support
     
    Allen Chen [MSFT], Jan 15, 2009
    #12
  13. Hi Allen,

    I tried your code for access but get errors for the connection string.

    1) I get error -
    Format of the initialization string does not conform to specification
    starting at index 0.
    with Code -
    using (OleDbConnection oc = new
    OleDbConnection(ConfigurationManager.ConnectionStrings["CALLMasterMDB"].ToString()))
    {
    oc.Open();
    DataTable dt =oc.GetSchema("tables");

    2) I also tried
    Code -
    using (OleDbConnection oc = new
    OleDbConnection(ConfigurationManager.ConnectionStrings[2].ToString()))
    {
    oc.Open();
    DataTable dt =oc.GetSchema("tables");
    Get Error -
    An OLE DB Provider was not specified in the ConnectionString. An example
    would be, 'Provider=SQLOLEDB;'.

    3) I then tried
    Code -
    using (OleDbConnection oc = new
    OleDbConnection(ConfigurationManager.ConnectionStrings[2].ProviderName="SqlOleDB"))
    {
    oc.Open();
    DataTable dt =oc.GetSchema("tables");

    get Error -
    The configuration is read only.

    My web.config is
    <connectionStrings>
    <add name="CallMasterSQLConnectionString" connectionString="Data
    Source=4600cJer;Initial Catalog=CALLMasterSQL;Integrated Security=SSPI;"
    providerName="System.Data.SqlClient" />
    <add name="ASPNETDB_ConnectionString" connectionString="Integrated
    Security=SSPI;Persist Security Info=False;Initial Catalog=aspnetdb;Data
    Source=4600cJer;Initial File
    Name=C:\Inetpub\wwwroot\CMWebManager\App_Data\ASPNETDB.MDF" />
    <add name="CALLMasterMDB" connectionString="C:\Program
    Files\CallMaster\Data\Callmaster.mdb"
    providerName="System.Data.OleDB" />
    <add name="ASPNETDBConnectionString" connectionString="Integrated
    Security=SSPI;Persist Security Info=False;Initial Catalog=aspnetdb;Data
    Source=4600cJer;Initial File
    Name=C:\Inetpub\wwwroot\CMWebManager\App_Data\ASPNETDB.MDF"
    providerName="System.Data.SqlClient" />
    <add name="ASPNETDBConnectionString2" connectionString="Integrated
    Security=SSPI;Persist Security Info=False;Initial Catalog=aspnetdb;Data
    Source=4600cJer;Initial File
    Name=C:\Inetpub\wwwroot\CMWebManager\App_Data\ASPNETDB.MDF"
    providerName="System.Data.SqlClient" />
    </connectionStrings>

    In my code I check if the appsetting is for SQL or Access and then have an
    if else to process.
    if (id.Equals("SqlDataSource1"))
    {
    using (SqlConnection sc = new
    SqlConnection(ConfigurationManager.ConnectionStrings["CallMasterSQLConnectionString"].ToString()))
    {
    ......
    }
    }
    else
    {
    using (OleDbConnection oc = new
    OleDbConnection(ConfigurationManager.ConnectionStrings[2].ProviderName="SqlOleDB"))
    {
    oc.Open();
    DataTable dt =oc.GetSchema("tables");
    .......
    }
    }


    Look forward to your reply.

    --
    Thanks for your help.
    Morris


    "Allen Chen [MSFT]" wrote:

    > Hi Morris,
    >
    > Here's the code for Access Database:
    >
    > Aspx:
    >
    > <asp:TextBox ID="TextBox1" runat="server" Text="Orders"></asp:TextBox>
    > <asp:Label ID="Label1" runat="server" Text=""></asp:Label>
    > <asp:Button ID="Button1"
    > runat="server" Text="Check" onclick="Button1_Click" />
    > <asp:GridView ID="GridView1" runat="server">
    > <Columns>
    > <asp:TemplateField>
    > <ItemTemplate>
    > <asp:LinkButton ID="LinkButton1" runat="server">Yes the table
    > is in the database!</asp:LinkButton>
    > </ItemTemplate></asp:TemplateField></Columns>
    > </asp:GridView>
    >
    > Aspx.cs:
    >
    > protected void Button1_Click(object sender, EventArgs e)
    > {
    > using (OleDbConnection oc = new
    > OleDbConnection(ConfigurationManager.ConnectionStrings["Database1ConnectionS
    > tring"].ToString()))
    > {
    >
    > oc.Open();
    > DataTable dt =oc.GetSchema("tables");
    >
    > //The following code just shows the table on the page,
    > which can provide a direct vision of the data retrieved.
    > this.GridView1.DataSource = dt;
    > this.GridView1.DataBind();
    > ///////////////////////////
    >
    > //The key here, to see if the table exists in the database
    > if you also want to check the views, please check dr[0] only.
    > string tablename = this.TextBox1.Text;
    > bool hasfound = false;
    > foreach (DataRow dr in dt.Rows)
    > {
    > if (dr[3].ToString() == "TABLE" &&
    > dr[2].ToString() == tablename)
    > {
    >
    > Label1.Text = "The table " + tablename + " is in
    > the database";
    > this.GridView1.Columns[0].Visible = true;
    > hasfound = true;
    > break;
    > }
    >
    > }
    > if (!hasfound)
    > {
    > Label1.Text = "The table " + tablename + " is NOT in
    > the database";
    > this.GridView1.Columns[0].Visible = false;
    > }
    > }
    > }
    >
    > You can compare the above code with the code I provided in my previous
    > post, that is for Sql database. The main difference is to use the classes
    > under the System.Data.OleDb namespace instead of the classes under the
    > System.Data.SqlClient namespace. For example to use OleDbConnection instead
    > of SqlConnection. Another difference is the query. Though the query I
    > provided before can work for Access database it needs additional security
    > settings. To make it easier for you to test I used another way here, that
    > is to use OleDbConnection.GetSchema() method to get all the tables
    > information from the Access database. You can merge the above code into
    > your existing code. If you have any questions about the code please feel
    > free to ask.
    >
    > Regards,
    > Allen Chen
    > Microsoft Online Support
    >
    >
     
    Morris Neuman, Jan 15, 2009
    #13
  14. Hi Morris,

    Please try the following connection string for your Access Database.

    <add name="Database1ConnectionString"
    connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program
    Files\CallMaster\Data\Callmaster.mdb" providerName="System.Data.OleDb"/>

    To get the correct connection strings for different databases you can refer
    to this site.

    http://connectionstrings.com/

    Can it work if you try the above connection string?

    Regards,
    Allen Chen
    Microsoft Online Community Support
     
    Allen Chen [MSFT], Jan 16, 2009
    #14
  15. Hi Allen,

    1) I tried the connection string with Provider as per your suggestion using
    my MS Access 2000 database and get the following error just opening the first
    default.aspx login page:
    'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program
    Files\CallMaster\Data\CallMaster.mdb' is not a valid virtual path.

    I checked my registry setting and I do have Jet 4.0.


    2) I then converted my Access 2000 file to Access 2007 and tried with the
    following setting,
    <add name="CALLMasterMDB"
    connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Program
    Files\CallMaster\Data\CallMaster2007.accdb"
    providerName="System.Data.OleDB" />
    and get same error:
    'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Program
    Files\CallMaster\Data\CallMaster2007.accdb' is not a valid virtual path.

    Look forward to your reply.
    --
    Thanks
    Morris


    "Allen Chen [MSFT]" wrote:

    > Hi Morris,
    >
    > Please try the following connection string for your Access Database.
    >
    > <add name="Database1ConnectionString"
    > connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program
    > Files\CallMaster\Data\Callmaster.mdb" providerName="System.Data.OleDb"/>
    >
    > To get the correct connection strings for different databases you can refer
    > to this site.
    >
    > http://connectionstrings.com/
    >
    > Can it work if you try the above connection string?
    >
    > Regards,
    > Allen Chen
    > Microsoft Online Community Support
    >
    >
     
    Morris Neuman, Jan 19, 2009
    #15
  16. Hi Morris,

    It's really strange. Is your code the same as mine? Do you use
    Server.MapPath() method in your code? Could you provide the call stack of
    this exception and send me a demo that can reproduce this issue (please
    send the mdb file as well)? I'll debug it on my side to see what the
    problem is.

    My email is (please notice that my email has been
    changed). Please update here after sending the project in case I missed
    that email.

    Regards,
    Allen Chen
    Microsoft Online Support
     
    Allen Chen [MSFT], Jan 20, 2009
    #16
  17. Hi Allen,

    I don't use Server.MapPath() in this page's code but use it in other pages.

    The web manager shares the CallMaster.mdb database with a telephony
    application. As such, the database resides in the
    C:\Program Files\CallMaster\Data\ folder.

    For the test, I set my web.config connection as:
    <connectionStrings>
    <add name="CALLMasterMDB"
    connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program
    Files\CallMaster\Data\Callmaster.mdb"
    providerName="System.Data.OleDB" />
    </connectionStrings>

    My page code is:
    using (OleDbConnection oc = new
    OleDbConnection(ConfigurationManager.ConnectionStrings["CALLMasterMDB"].ToString()))
    {
    oc.Open();
    DataTable dt =oc.GetSchema("tables");

    //The following code just shows the table on the page, which
    can provide a direct vision of the data retrieved.
    // this.GridView2.DataSource = dt;
    // this.GridView2.DataBind();

    //The key here, to see if the table exists in the database
    if you also want to check the views, please check dr[0] only.
    foreach (DataRow dr in dt.Rows)
    {
    if (dr[3].ToString() == "BASE TABLE" && dr[2].ToString()
    == tablename1)
    {
    hasfound_tb1 = true;
    }
    if (dr[3].ToString() == "BASE TABLE" && dr[2].ToString()
    == tablename2)
    {
    hasfound_tb2 = true;
    }
    if ((hasfound_tb1) && (hasfound_tb2))
    {
    break;
    }
    }


    I have the following namespaces imported for this page:
    <%@ Import Namespace="System.IO" %>
    <%@ Import Namespace="System.Data" %>
    <%@ Import Namespace="System.Data.OleDb" %>
    <%@ Import Namespace="System.Data.SqlClient" %>
    <%@ Import Namespace="System.Data.Odbc" %>
    <%@ Import Namespace="System" %>
    <%@ Import Namespace="System.Collections" %>
    <%@ Import Namespace="System.Configuration" %>
    <%@ Import Namespace="System.Web" %>
    <%@ Import Namespace="System.Web.Security" %>
    <%@ Import Namespace="System.Web.UI" %>
    <%@ Import Namespace="System.Web.UI.WebControls" %>
    <%@ Import Namespace="System.Web.UI.WebControls.WebParts" %>
    <%@ Import Namespace="System.Web.UI.HtmlControls" %>
    <%@ Import Namespace="System.Text" %>

    Let me know if the above sheds any more light on my problem. If not then I
    will email a demo to you.

    --
    Thanks for your help.
    Morris


    "Allen Chen [MSFT]" wrote:

    > Hi Morris,
    >
    > It's really strange. Is your code the same as mine? Do you use
    > Server.MapPath() method in your code? Could you provide the call stack of
    > this exception and send me a demo that can reproduce this issue (please
    > send the mdb file as well)? I'll debug it on my side to see what the
    > problem is.
    >
    > My email is (please notice that my email has been
    > changed). Please update here after sending the project in case I missed
    > that email.
    >
    > Regards,
    > Allen Chen
    > Microsoft Online Support
    >
    >
     
    Morris Neuman, Jan 20, 2009
    #17
  18. Hi Morris,

    Please send me a demo along with the mdb file (if it contains sensitive
    information please clear the records). In addition, please provide the
    detailed information of the exception so that I can see if the repro on my
    side is the same as the one on your side. You can get the information in
    this way:

    1. Make sure there's NO following tag in the web.config. If there is,
    comment it or set mode="Off".
    <customErrors mode="On">
    </customErrors>

    2. Don't debug the project. Instead, in the Visual Studio, right click the
    aspx page in the solution explorer window, select "View in Browser" to open
    the page. Then test it to reproduce the exception. You'll see detailed
    information listed on the page as soon as the unhandled exception is thrown.


    My email is (please notice that my email has been
    changed). Please update here after sending the project in case I missed
    that email.

    Regards,
    Allen Chen
    Microsoft Online Support
     
    Allen Chen [MSFT], Jan 21, 2009
    #18
  19. Hi Allen,

    I have emailed you the details. The file was too big to email so I have
    uploaded to our ftp site and emailed you the link. The email has been sent
    by my collegue Jer Mehta ()
    --
    Thanks
    Morris


    "Allen Chen [MSFT]" wrote:

    > Hi Morris,
    >
    > Please send me a demo along with the mdb file (if it contains sensitive
    > information please clear the records). In addition, please provide the
    > detailed information of the exception so that I can see if the repro on my
    > side is the same as the one on your side. You can get the information in
    > this way:
    >
    > 1. Make sure there's NO following tag in the web.config. If there is,
    > comment it or set mode="Off".
    > <customErrors mode="On">
    > </customErrors>
    >
    > 2. Don't debug the project. Instead, in the Visual Studio, right click the
    > aspx page in the solution explorer window, select "View in Browser" to open
    > the page. Then test it to reproduce the exception. You'll see detailed
    > information listed on the page as soon as the unhandled exception is thrown.
    >
    >
    > My email is (please notice that my email has been
    > changed). Please update here after sending the project in case I missed
    > that email.
    >
    > Regards,
    > Allen Chen
    > Microsoft Online Support
    >
    >
     
    Morris Neuman, Jan 22, 2009
    #19
  20. Hi Morris,

    Thanks for the project. I've reproduced it and found the root cause. It's
    due to the DataFile property of the AccessDataSource control. From your
    code we can see you used many AccessDataSource controls and specify the
    DataFile in this way:

    <asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="<%$
    ConnectionStrings:CALLMasterMDB %>"

    The expression "<%$ ConnectionStrings:CALLMasterMDB %> will retrieve the
    string "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\THE PHYSICAL
    PATH\Callmaster.mdb", which is defined in the following tag of the
    web.config file.

    <add name="CALLMasterMDB"
    connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\THE
    PHYSICAL PATH\Callmaster.mdb"
    providerName="System.Data.OleDB" />

    The problem is, the DataFile needs a virtual path. The string provided is
    not even a path, it's a connectionstring, which is
    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Physical path of the mdb.
    AccessDataSource internally uses HttpRequest.MapPath() method to convert
    the virtual path to physical path. If it sees the string is not a virtual
    path it will throw this exception.

    So a quick solution is to add one entry in appSettings of the web.config.
    <appSettings>

    <!--If using MS Access CALLMaster.mdb then
    set value="AccessDataSource{X)"/>-->
    <!--If using MS SQL CALLMasterSQL.mdf then
    set value="SqlDataSource(X)"/>-->
    <add key="CALLMasterMDB" value="~/Callmaster.mdb"></add>

    Then change all "<%$ ConnectionStrings:CALLMasterMDB %>" to '<%$
    AppSettings:MyDataSource1 %>'

    This should fix the exception.

    Please have a try and let me know the result.

    Regards,
    Allen Chen
    Microsoft Online Community Support
     
    Allen Chen [MSFT], Jan 22, 2009
    #20
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Davisro
    Replies:
    1
    Views:
    701
    Michael D. Ober
    Jun 14, 2004
  2. mit
    Replies:
    1
    Views:
    835
    Ramu Pulipati
    Jan 25, 2006
  3. David Williams
    Replies:
    2
    Views:
    1,154
    Jacob Yang [MSFT]
    Aug 12, 2003
  4. kris
    Replies:
    0
    Views:
    531
  5. Rio
    Replies:
    4
    Views:
    1,227
Loading...

Share This Page