Link to home
Start Free TrialLog in
Avatar of Isaac
IsaacFlag for United States of America

asked on

Transfer data from Excel to Oracle

I'm trying to transfer data from Excel to Oracle DB but when I run the code below, I get this error:

Could not find installable ISAM.

and it highlights my connection "oconn.Open()"

What am I doing wrong?
Thanks.
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.OracleClient;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

public partial class addCategory : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        OracleConnection oracleConn = new OracleConnection();
        oracleConn.ConnectionString = ConfigurationManager.ConnectionStrings["oracleConnectionString"].ConnectionString;

        try
        {
            char status;

            if (REC_STAT.Checked)
            {
                status = 'Y';
            }
            else
            {
                status = 'N';
            }

            oracleConn.Open();
            OracleCommand sp_cmd = new OracleCommand("SP_INSERT_CATEGORY");
            sp_cmd.CommandType = CommandType.StoredProcedure;
            sp_cmd.Connection = oracleConn;
            sp_cmd.Parameters.Add("CATEGORY_CD", OracleType.VarChar, 50).Value = CATEGORY_CD.Text;
            sp_cmd.Parameters.Add("CATEGORY_DESC", OracleType.VarChar, 50).Value = CATEGORY_DESC.Text;            
            sp_cmd.Parameters.Add("REC_STAT", OracleType.VarChar, 50).Value = status;

            sp_cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            lblMsg.Text = ex.Message;
        }
        finally
        {
            oracleConn.Close();
            CATEGORY_CD.Text = "";
            CATEGORY_DESC.Text = "";
            REC_STAT.Text = "";
            Response.Redirect("editCategoryList.aspx");
        }
    }
}

Open in new window

Avatar of lucius_the
lucius_the
Flag of Croatia image

Check the "oracleConnectionString" value. Most likely you are trying to connect to a database using a provider that's not installed on your computer.

Try installing the Oracle client libarires and use that provider when connecting to Oracle DB.
Avatar of Isaac

ASKER

My apologies..... I pasted the wrong code.

Oracle client is already installed.

In the code below, you will see that I am trying to connect to Excel and that's where the error is pointing too.
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.OleDb;
using System.Data.OracleClient;


public partial class serverVariables : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
       /* foreach (string x in Request.ServerVariables)
        {
            Label1.Text += x.ToString() + " : ";
            Label1.Text += Request.ServerVariables[x].ToString() + "<br/>";
        }*/

        OracleConnection oracleConn = new OracleConnection();
        oracleConn.ConnectionString = ConfigurationManager.ConnectionStrings["oracleConnectionString"].ConnectionString;


        OleDbConnection oconn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=ogc_Category_lu.xls;Extended Properties=Excel 8.0;HDR=YES;IMEX=2");

        try
        {
            OleDbCommand ocmd = new OleDbCommand("SELECT * FROM [Sheet1$]", oconn);
            oconn.Open();

            OleDbDataReader odr = ocmd.ExecuteReader();
            string categoryCD = "";
            string categoryDesc = "";
            string rec_stat = "";

            while (odr.Read())
            {
                categoryCD = odr[0].ToString();
                categoryDesc = odr[1].ToString();
                rec_stat = odr[2].ToString();

                oracleConn.Open();
                OracleCommand sp_cmd = new OracleCommand("SP_INSERT_CATEGORY");
                sp_cmd.CommandType = CommandType.StoredProcedure;
                sp_cmd.Connection = oracleConn;
                sp_cmd.Parameters.Add("CATEGORY_CD", OracleType.VarChar, 50).Value = categoryCD;
                sp_cmd.Parameters.Add("CATEGORY_DESC", OracleType.VarChar, 50).Value = categoryDesc;
                sp_cmd.Parameters.Add("REC_STAT", OracleType.VarChar, 50).Value = rec_stat;
            }
            
        }
        catch (DataException ee)
        {
            Label2.Text = ee.Message;
        }
        finally
        {
            oconn.Close();
        }
    }
}

Open in new window

Hm... Maybe the file is not in your bin/debug folder...? Did you try to write the full path to the Excel file ?
Avatar of Isaac

ASKER

The file is in the root of my directory (solution explorer)
Is it coped to your output folder, is it in the same folder as EXE ? Maybe that's why it doesn't open. Try putting the Excel file in "C:\temp" and use the full path when opening the file to be sure that's not causing the problem...
Avatar of Isaac

ASKER

I'm running it from visual studio 2008 (debug)
Avatar of Isaac

ASKER

so it's in the root
When you have a file in your project root, you can set properties on it, one of them decides wheather this file will be distributed in the output folder. The output folder is not the root folder on the file system. That's what I was asking you to check. Can you check (not in VStudio, but in your file system) if the excel file is in the same folder as your exe file. If it isn't, that's the most likely reason why your code doesn't work.

And again, just to be sure it's not that, make a folder on C drive, name it "test", put your "ogc_Category_lu.xls" file there and change your conection string to:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\test\ogc_Category_lu.xls;Extended Properties=Excel 8.0;HDR=YES;IMEX=2"

before going with further diagnostics.
Avatar of Isaac

ASKER

I just tried your suggestion and I still get the same error.
See attached file....
ogc.ppt
Maybe the Extended Properties parameter has to be double quoted, try this for a connection string:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=ogc_Category_lu.xls;Extended Properties=""Excel 8.0;HDR=YES;IMEX=2"""
ASKER CERTIFIED SOLUTION
Avatar of lucius_the
lucius_the
Flag of Croatia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Isaac

ASKER

EXCELLENT!!!

That worked.  Also, I put in the full path.
Hopefully when I transfer the files to the test site and just leave the file name, it works.

Thanks again!
Avatar of Isaac

ASKER

lucius_the was very patient and asked the right questions.
Great !
Have a great New Year :)