Check for table

[Morris Neuman]

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

 

[Allen Chen [MSFT]]

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:
(e-mail address removed).

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

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.

 

[Allen Chen [MSFT]]

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]

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.

 

[Allen Chen [MSFT]]

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]]

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]]

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]

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

 

[Allen Chen [MSFT]]

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]

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.

 

[Allen Chen [MSFT]]

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]

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

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]]

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]

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.

 

[Allen Chen [MSFT]]

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 (e-mail address removed) (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]

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.

 

[Allen Chen [MSFT]]

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 (e-mail address removed) (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]

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 ([email protected])

 

[Allen Chen [MSFT]]

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