connection to sql*plus with visual studio 2010

[olybobo]

dear all I have the following sample code which i am trying to use to connect to sql*plus in visual studio 2010 but i keep getting the error message connection string is not well-formed. see my code below

protected void Button1_Click(object sender, EventArgs e)
    {

        string oradb = "User Id=finance;Password=finacecc;Data Source=XE";
        string sql = "select ID, Create_date from t_mobile";
        OracleDataAdapter adapter = new OracleDataAdapter(oradb, sql);
        OracleCommandBuilder builder = new OracleCommandBuilder(adapter);

        DataSet dataset = new DataSet();

        adapter.Fill(dataset, "t_mobile");

        DataTable mytable = dataset.Tables["t_mobile"];

        GridView1.DataSource = mytable;
        GridView1.DataBind();


    }

[kvprajapati]

Open the Server Explorer, add new oracle database connection, select the newly added connection from server explorer and get the connection string from the property windows.

[kylelendo]

Dude why are you adding ID and password...As far as I know SQL server is integrated in VS2010...Just go to Server explorer and create table....to know more follow the below link

http://www.youtube.com/watch?v=lmWC8AThSVw&feature=related
http://www.youtube.com/watch?v=lePPRXdarOk&feature=related

Hope It helps

[olybobo]

this is the modified code below except that i am getting the error message connection not open unfortunately...i am trying to connect to my sql*plus all

protected void Button1_Click(object sender, EventArgs e)
    {

        DataTable emptable = new DataTable();
        string oradb = "Data Source=(DESCRIPTION ="
    + "(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=olisa-VAIO)(PORT=1521)))"
    + "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XE)));"
    + "User Id = scott; Password=tiger;";
        OracleConnection conn = new OracleConnection(oradb);
        conn.Open();
        string sql = "select ID, Create_date from t_mobile";
        OracleCommand cmd = new OracleCommand(sql, conn);
        cmd.CommandType = CommandType.Text;
        emptable.Load(cmd.ExecuteReader());
        GridView1.DataSource = emptable;
        GridView1.DataBind();

        conn.Close();
        conn.Dispose();

    }

[cocoll]

hello!
if you have VS 2005 so try the same code with it,it should work if you connection string is correct because i didn't check your code or your connection string.
so if in VS 2005 work so don't wast your time for trying to connect to an oracle database from VS 2010 in a 64 bits machine,because i had this problem and i spend over a week to solve it with no success,and i could say it the VS 2010 problem(maybe only in my laptop).

so just try using VS 2005,maybe it would save your time.
Regards!

[olybobo]

hello!
if you have VS 2005 so try the same code with it,it should work if you connection string is correct because i didn't check your code or your connection string.
so if in VS 2005 work so don't wast your time for trying to connect to an oracle database from VS 2010 in a 64 bits machine,because i had this problem and i spend over a week to solve it with no success,and i could say it the VS 2010 problem(maybe only in my laptop).

so just try using VS 2005,maybe it would save your time.
Regards!

i do not have vs 2005 unfortunately, can you please look at my code and verify or test it on a vs 2005 machine since you have one and let me know

[cocoll]

hello!
if i look to your code i don't see anything wrong,so what i want to do is that i will give you a simple code that work 100%,so test it with VS 2010 and you will know from where the problem.
i want to help you ,but VS 2005 isn't installed write now in my laptop,but try this code and tell me if it worked or not:
first create this table in the oracle database:

create table test(FirstName varchar2(50))

the create a new asp.net website with VS 2010 or new empty website then add a new aspx webform and in the code behind add this code:

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.Odbc;
public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
        String con = "DRIVER={Microsoft ODBC for Oracle};DSN=XE;UID=user;PWD=pass;SERVER=localhost;";
        OdbcConnection conx = new OdbcConnection(con);
        conx.Open();
        OdbcCommand command = new OdbcCommand("insert into test values('hello')", conx);
        command.ExecuteNonQuery();

        conx.Close();

       
    }
}

so it will work in VS 2005 and in VS 2010 in a 32 bits machine.
and it's for you to try it and you will certainly know where is the problem.
Regards!

[olybobo]

hello!
if i look to your code i don't see anything wrong,so what i want to do is that i will give you a simple code that work 100%,so test it with VS 2010 and you will know from where the problem.
i want to help you ,but VS 2005 isn't installed write now in my laptop,but try this code and tell me if it worked or not:
first create this table in the oracle database:

create table test(FirstName varchar2(50))

the create a new asp.net website with VS 2010 or new empty website then add a new aspx webform and in the code behind add this code:

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.Odbc;
public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
        String con = "DRIVER={Microsoft ODBC for Oracle};DSN=XE;UID=user;PWD=pass;SERVER=localhost;";
        OdbcConnection conx = new OdbcConnection(con);
        conx.Open();
        OdbcCommand command = new OdbcCommand("insert into test values('hello')", conx);
        command.ExecuteNonQuery();

        conx.Close();

       
    }
}

so it will work in VS 2005 and in VS 2010 in a 32 bits machine.
and it's for you to try it and you will certainly know where is the problem.
Regards!

I am getting the following error below

ERROR [NA000] [Microsoft][ODBC driver for Oracle][Oracle]ORA-06413: Connection not open.
ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed
ERROR [01000] [Microsoft][ODBC Driver Manager] The driver doesn't support the version of ODBC behavior that the application requested (see SQLSetEnvAttr).

am i doing something wrong, this is my connection path code. should is there a specific path required for my tns location, do i have to install an exe. please guide me

protected void Button1_Click(object sender, EventArgs e)
{
string con = "DRIVER={Microsoft ODBC for Oracle};DSN=XE;UID=finance;PWD=financecc;SERVER=localhost;";
OdbcConnection conx = new OdbcConnection(con);
conx.Open();
DataTable emptable = new DataTable();
OdbcCommand command = new OdbcCommand("select ID, Create_date from t_mobile", conx);

emptable.Load(command.ExecuteReader());
GridView1.DataSource = emptable;
GridView1.DataBind();
conx.Close();
}

[olybobo]

This is the solution below

I finally got it working all...here is the simple solutions. First and foremorst, make sure you are using the right various of sql*plus because I noticed for visual studio 2010 express, it is only compatible with Oracle Database Express Edition 11g Release 2, don't know why but I tired oracle 10g and that didnt work. Oracle Database Express Edition 11g Release 2 can be found at the following link below.

http://www.oracle.com/technetwork/database/express-edition/downloads/index.html

the next step is to download the right drivers to establish the connection which can found at the following link below

http://www.oracle.com/technetwork/developer-tools/visual-studio/downloads/index.html and the name of the driver is ODAC 11.2 Release 3 (11.2.0.2.1) with Oracle Developer Tools for Visual Studio. That is the right driver for oracle 11g for asp.net 4.0.

Once the driver has been downloaded, it is time to write a simple program. An example is shown below

protected void Button1_Click(object sender, EventArgs e)
{
DataTable emptable = new DataTable();
string oradb = "Data Source=(DESCRIPTION ="
+ "(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=olisa-VAIO)(PORT=1521)))"
+ "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XE)));"
+ "User Id = finance; Password=financecc;";
OracleConnection conn = new OracleConnection(oradb);
conn.Open();
string sql = "select ID, Create_date from t_mobile";
OracleCommand cmd = new OracleCommand(sql, conn);
cmd.CommandType = CommandType.Text;
emptable.Load(cmd.ExecuteReader());
GridView1.DataSource = emptable;
GridView1.DataBind();


conn.Close();
conn.Dispose();


}

notice that you will need the right oracle .dll to establish the connection, this can be done by adding a reference, in the reference section click on browser and navigate to the following directory to add the oracle.dataaccess.dll. the path is %ORACLE_HOME%\odp.net\bin\<version>\Oracle.DataAccess.dll and you should be set to go. Notice, you are doing it this way because there is currently a bug with the latest driver.

I am getting the following error below

ERROR [NA000] [Microsoft][ODBC driver for Oracle][Oracle]ORA-06413: Connection not open.
ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed
ERROR [01000] [Microsoft][ODBC Driver Manager] The driver doesn't support the version of ODBC behavior that the application requested (see SQLSetEnvAttr).

am i doing something wrong, this is my connection path code. should is there a specific path required for my tns location, do i have to install an exe. please guide me

protected void Button1_Click(object sender, EventArgs e)
{
string con = "DRIVER={Microsoft ODBC for Oracle};DSN=XE;UID=finance;PWD=financecc;SERVER=localhost;";
OdbcConnection conx = new OdbcConnection(con);
conx.Open();
DataTable emptable = new DataTable();
OdbcCommand command = new OdbcCommand("select ID, Create_date from t_mobile", conx);
emptable.Load(command.ExecuteReader());
GridView1.DataSource = emptable;
GridView1.DataBind();
conx.Close();
}