OLEDB Connection open method not functioning

Question

1.00/5 (1 vote)

See more:

I have the following code to bind a GridView from an excel sheet. The file gets saved in the folder, but oconn.open does not get executed and therefore gridview is not populated. I tried with ACE.OLEDB.12.0 too. Can anyone help me to resolve this?

Thanks in advance.

C#

string path = UploadExp.FileName;
UploadExp.SaveAs(Server.MapPath(".\\Uploadfiles\\" + path));
OleDbConnection oconn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(".\\UploadFiles\\" + path) + ";Extended Properties=Excel 8.0");
oconn.Open();
OleDbCommand ocmd = new OleDbCommand("Select * from [Sheet1$]", oconn);
OleDbDataAdapter odap = new OleDbDataAdapter(ocmd);
dl.sqldset = new DataSet();
odap.Fill(dl.sqldset);
GridView1.DataSource = dl.sqldset;
GridView1.DataBind();
oconn.Close();

Posted 1-Jun-14 0:01am

Member 10599847

Updated 1-Jun-14 0:18am

Thomas Daniels

v2

Add a Solution

Comments

[no name] 1-Jun-14 6:24am

What do you mean "does not get executed"? How is it that you know that it does not?

Member 10599847 1-Jun-14 6:30am

In the first place, gridview is not populated. Secondly, i tried to display a message before and after the 'oconn.open'. The message after the command is not displayed. Hence it is evident, for some reason the programs gets terminated at this line. If i run the same code from programming environment (my PC) instead of the server, it gets executed.

[no name] 1-Jun-14 6:56am

In the first place, just because your gridview is not being populated does not mean that line of code is not getting executed. There are 453,346,234 other possible reasons. Secondly, there are no "messages" displayed in your code snippet. Thirdly, where did this other server come from all of a sudden? Fourth, if it works on one computer and not another do you think that it's possible that there is some sort of a difference between the two? The path to the file is probably not what you think it is.

Member 10599847 1-Jun-14 7:56am

I am not sure if I have understood your comments fully. Still I will try to explain the situation, hopefully you can find the difference. The application is developed in my local system, which means all the programs and relevant folders (in this case 'UploadFiles') are located in my local hard disk. But it points to the database in our remote server. When I run the application in a debugging mode or otherwise (i.e. by clicking View in Browser) with the same codes, the gridview is populated with the data in the excel sheet. But after the application is deployed into a dedicated database server when me or any other user logs on to the application from the browser, this particular module fails to work. I may mention here that the application was earlier located in a different server and the same code was working fine. Recently it has been moved to a new server with a different IP address. I don't know if I have made it clear to you

[no name] 1-Jun-14 8:31am

And how on earth would you expect someone to "find the difference" based on the information that you have provided? Obviously, there is some difference between your server and your development PC. What that difference is, no one but you knows. Who can find it? Well it is certainly not us. We don't have access to your development PC or your server. And, now, all of a sudden, there is a database server and a database. What exactly does that have to do with anything? You need to implement some logging and do some debugging. I think you either have a path problem or a permissions issue.

CHill60 1-Jun-14 6:55am

Put a try-catch block around the call to open and display the exception description. It's possibly the location of the database as you've used a relative path

Member 10599847 1-Jun-14 7:01am

Try-catch block is already there, it does not display any error message

DamithSL 1-Jun-14 7:51am

update the question with exact code you tried, may be you are swallowing exception like below
try
{
DoWork();
}
catch (Exception e)
{
// Swallow the exception and continue
// executing.
}

Member 10599847 1-Jun-14 7:58am

Here is the complete code
protected void btnUpload_Click(object sender, EventArgs e)
{
try
{
if (UploadExp.HasFile)
{
string path = UploadExp.FileName;
UploadExp.SaveAs(Server.MapPath(".\\Uploadfiles\\" + path));
OleDbConnection oconn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(".\\UploadFiles\\" + path) + ";Extended Properties=Excel 8.0");
oconn.Open();
OleDbCommand ocmd = new OleDbCommand("Select * from [Sheet1$]", oconn);
OleDbDataAdapter odap = new OleDbDataAdapter(ocmd);
dl.sqldset = new DataSet();
odap.Fill(dl.sqldset);
GridView1.DataSource = dl.sqldset;
GridView1.DataBind();
oconn.Close();

}
else
{
string msg = "<script language='javascript'> alert(' Please Select a file ')</script>";
Page.RegisterStartupScript("popup", msg);
return;
}
}
catch (Exception ex)
{
string Err = "<script language='javascript'> alert(' " + ex.Message + "')</script>";
Page.RegisterStartupScript("popup", Err);
return;
}
}

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Vedat Ozan Oner · Answer 1 · 2014-06-01T02:09:00

you can use the following code to see if there is any problem to connect an excel file on your server. If there is no problem with that, you should look for file existence or permissions etc.
I expect that an exception is thrown in case of such file issues, but you say no exception is caught. I still advice you to log all exceptions into some location (a text file, database etc) to see what is going on, instead of displaying them as error messages on UI.

C#

using System;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace OleDbTest1
{
    class Program
    {
        static void Main()
        {
            try
            {
                // to see any available Jet provider
                Test1();

                // to connect an excel with a Jet provider.
                Test2();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
            Console.ReadKey();
        }

        // connection test
        private static void Test2()
        {
            string connStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}; Extended Properties=""Excel 8.0;HDR=No;IMEX=1""";

            // can be server.map
            connStr = string.Format(connStr, @"D:\tmp\book1.xls");
            using (OleDbConnection conn = new OleDbConnection(connStr))
            {
                conn.Open();
                Console.WriteLine("db='{0}' ds='{1}'", conn.Database, conn.DataSource);
            }
        }

        // to see available ole db providers
        private static void Test1()
        {
            OleDbDataReader reader = OleDbEnumerator.GetRootEnumerator();
            DisplayData(reader);
        }

        static void DisplayData(OleDbDataReader reader)
        {
            // SOURCES_NAME = Microsoft.Jet.OLEDB.4.0
            while (reader.Read())
            {
                if (!reader["SOURCES_NAME"].ToString().Contains("Jet"))
                    continue;
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    Console.WriteLine("{0} = {1}",
                     reader.GetName(i), reader.GetValue(i));
                }
                Console.WriteLine("==================================");
            }
        }
    }
}

DamithSL · Answer 2 · 2014-06-01T02:22:00

try below code

C#

try
{
    if (UploadExp.HasFile)
    {
        string path = UploadExp.FileName;
        UploadExp.SaveAs(Server.MapPath(".\\Uploadfiles\\" + path));
        using (OleDbConnection oconn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(".\\UploadFiles\\" + path) + ";Extended Properties=Excel 8.0"))
        {
            oconn.Open();
            using (OleDbCommand ocmd = new OleDbCommand("Select * from [Sheet1$]", oconn))
            using (OleDbDataAdapter odap = new OleDbDataAdapter(ocmd))
            {
                DataSet ds = new DataSet();
                odap.Fill(ds);
                GridView1.DataSource = ds.Tables[0];
                GridView1.DataBind();
            }
        }

    }
    else
    {
        string msg = "<script language="javascript"> alert(' Please Select a file ')</script>";
        Page.ClientScript.RegisterStartupScript(this.GetType(), "popup", msg);
    }
}
catch (Exception ex)
{
    string Err = "<script language="javascript"> alert(' " + ex.Message + "')</script>";
    Page.ClientScript.RegisterStartupScript(this.GetType(), "popup", Err);
}

OLEDB Connection open method not functioning

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0