Hi,
How do I check if a table exists in my database?

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:
msd***@microsoft.com.

==================================================
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.

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.
Show quoteHide quote

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

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.
Show quoteHide quote

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

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

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

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";
        }

Show quoteHide quote

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

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.

Show quoteHide quote

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

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.

Show quoteHide quote

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

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.
Show quoteHide quote

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 all***@microsoft.com (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

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.

Show quoteHide quote

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 all***@microsoft.com (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

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 (jme***@speechsoft.com)
Show quoteHide quote

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

Thanks for the solution Allen.  The problem is that the CallMaster.mdb
database is owned by another and seperate telephony service and is placed in
a seperate directory.  I cannot put the CallMaster.mdb in the web project's
root directory.

As mentioned your solution works for CallMaster Sql.  I now need it to also
work for the CallMaster access version.

Is there another workaround to checking the database to see if certain
tables exist? 
Show quoteHide quote

Hi Morris,

If the mdb file cannot be moved I suggest you use SqlDataSource instead of
AccessDataSource to connect to the Access database. You can refer to the
following documentation to learn how to do so.

http://msdn.microsoft.com/en-us/library/ms247233(VS.80).aspx

The exception is not caused by the code to check the existing tables in
Access database, but by the invalid path specified for the AccessDataSource
control. When SqlDataSource is used we can use the connectionstring instead
of the virtual path to specify the location of the database file.

After the exception is eliminated we can still use the code I provided
before to check the existing of the table in Access database. I'd like to
repaste the code here.

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;
                }
            }
        }

web.config:

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

You can see no AccessDataSource control is used here so it's not related to
the exception "....is not a valid virtual path".

Regards,
Allen Chen
Microsoft Online Support

Hi Allen,

As usual, your suggestion helped solve the problem.

We really appreciate your thorough, deatiled and quick response.
Show quoteHide quote

Glad to know you've solved this issue, Morris. Thank you for using our
Newsgroup Support Service!

Regards,
Allen Chen
Microsoft Online Community Support

Adapting ReorderList WebControl for rendering dynamic html
SelectCommand with SelectParameters.Add
UpdateProgress animated gif freezes in ajax postback
RE: Gridview templaye button control
Re: FTP download
RE: SelectCommand with SelectParameters.Add
Re: Databinding Syntax Not Working in ListView LayoutTemplate
Test for table in database
Master/Detail Page.
using formview control