Isaac
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.
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");
}
}
}
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.
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();
}
}
}
Hm... Maybe the file is not in your bin/debug folder...? Did you try to write the full path to the Excel file ?
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...
ASKER
I'm running it from visual studio 2008 (debug)
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.OL EDB.4.0;Da ta Source=C:\test\ogc_Categor y_lu.xls;E xtended Properties=Excel 8.0;HDR=YES;IMEX=2"
before going with further diagnostics.
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.OL
before going with further diagnostics.
ASKER
Maybe the Extended Properties parameter has to be double quoted, try this for a connection string:
"Provider=Microsoft.Jet.OL EDB.4.0;Da ta Source=ogc_Category_lu.xls ;Extended Properties=""Excel 8.0;HDR=YES;IMEX=2"""
"Provider=Microsoft.Jet.OL
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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!
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!
ASKER
lucius_the was very patient and asked the right questions.
Great !
Have a great New Year :)
Have a great New Year :)
Try installing the Oracle client libarires and use that provider when connecting to Oracle DB.