connection to sql*plus with visual studio 2010

Question

olybobo 0 Newbie Poster

12 Years Ago

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();


    }

asp.net

4 Contributors
8 Replies
702 Views
1 Month Discussion Span
Latest Post 12 Years Ago Latest Post by olybobo

All 8 Replies

kvprajapati 1,826 Posting Genius

12 Years Ago

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.

cocoll 0 Junior Poster in Training

12 Years Ago

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!

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

kylelendo -4 Junior Poster in Training · Answer 1 · 2011-09-30T18:37:58+00:00

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 0 Newbie Poster · Answer 2 · 2011-10-06T06:58:54+00:00

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();

    }

olybobo 0 Newbie Poster · Answer 3 · 2011-10-11T19:05:54+00:00

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 0 Junior Poster in Training · Answer 4 · 2011-10-13T07:06:14+00:00

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 0 Newbie Poster · Answer 5 · 2011-10-14T07:04:41+00:00

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 0 Newbie Poster · Answer 6 · 2011-11-03T00:19:23+00:00

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();
}

connection to sql*plus with visual studio 2010

Recommended Answers Collapse Answers

All 8 Replies

Recommended Answers