Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 211
  • Last Modified:

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

0
Isaac
Asked:
Isaac
  • 7
  • 7
1 Solution
 
lucius_theCommented:
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.
0
 
IsaacSharePoint Client Side DeveloperAuthor Commented:
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

0
 
lucius_theCommented:
Hm... Maybe the file is not in your bin/debug folder...? Did you try to write the full path to the Excel file ?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
IsaacSharePoint Client Side DeveloperAuthor Commented:
The file is in the root of my directory (solution explorer)
0
 
lucius_theCommented:
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...
0
 
IsaacSharePoint Client Side DeveloperAuthor Commented:
I'm running it from visual studio 2008 (debug)
0
 
IsaacSharePoint Client Side DeveloperAuthor Commented:
so it's in the root
0
 
lucius_theCommented:
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.
0
 
IsaacSharePoint Client Side DeveloperAuthor Commented:
I just tried your suggestion and I still get the same error.
See attached file....
ogc.ppt
0
 
lucius_theCommented:
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"""
0
 
lucius_theCommented:
Or, for C# maybe this:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=ogc_Category_lu.xls;Extended Properties=\"Excel 8.0;HDR=YES;IMEX=2\";"
0
 
IsaacSharePoint Client Side DeveloperAuthor Commented:
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!
0
 
IsaacSharePoint Client Side DeveloperAuthor Commented:
lucius_the was very patient and asked the right questions.
0
 
lucius_theCommented:
Great !
Have a great New Year :)
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now