Correct c# syntax for adodb.connection

Most venerated pc guru's,
As a newbie to c# program who is conversant in I am getting used to c# syntax.  One problem I have is getting the correct approach for establishing an adodb connection.  

I'm doing a to c# conversion project.

Please see the code below.  

I'm getting error results such as:
Object does not contain a definition for Open
Object does not contain a definition for BeginTrans

I'm stuck here and could use some working examples from creation of the connection to creation of a recordset.



internal object cn = null;
         internal object rs = null;

	//Database Stuff
	cn = Server.CreateObject("ADODB.Connection");
	rs = Server.CreateObject("ADODB.Recordset");

	//On error resume next

Open in new window

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

What is the back end database server? It appears from the tags that it is an MS SQL server. If so, you can use the native client to connect to it. I use a SqlHelper class to wrap all of the calls that i could make to a db server, and here is one of the methods:

#region ExecuteDataSet(string connectionString, string sqlQuery)
        /// <summary>
        /// Executes the query passed in and returns a DataSet with the results
        /// </summary>
        /// <param name="connectionString">The connection string to use to connect to the database</param>
        /// <param name="sqlQuery">The query to execute</param>
        /// <returns>DataSet with the results</returns>
        public static DataSet ExecuteDataSet(string connectionString, string sqlQuery)
            // set up the SqlConnection object
            using (SqlConnection connection = new SqlConnection(connectionString))
                    LogHelper.LogVerboseMessage(CLASS_NAME, "ExecuteDataSet", "Query: " + sqlQuery);

                    // clean the sql statement of invalid characters
                    ////sqlQuery = SqlHelper.CleanSqlStatement(sqlQuery);

                    // Log the clean statement
                    LogHelper.LogVerboseMessage(CLASS_NAME, "ExecuteDataSet", "Cleaned SQL: " + sqlQuery);

                    // open the connection

                    // set up the data adapter to fetch the data
                    SqlDataAdapter da = new SqlDataAdapter(sqlQuery, connection);

                    // set up the dataset to hold the data
                    DataSet ds = new DataSet();

                    // fetch the data into the dataset

                    // return the dataset
                    return ds;
                catch (Exception anyException)
                    // LogHelper.LogException(anyException, CLASS_NAME, "ExecuteDataSet");

                    // return an empty dataset on exception
        } // ExecuteDataSet(string connectionString, string sqlQuery)
        #endregion ExecuteDataSet(string connectionString, string sqlQuery)

Note the use of the SqlConnection object, instead of ADODB. For connecting to other database servers, you can replace SqlConnection with OdbcConnection and accomplish the same thing.
Check out this link:
It might be of some help.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
käµfm³d 👽Commented:
Is there a particular reason you would want to use CreateObject? The .NET framework provides classes for connecting to several databases. Here is a SQL Server example.
using (System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(conn_string))
    SqlTransaction trans = con.BeginTransaction();

    using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(string.Empty, con))
            cmd.CommandText = "INSERT INTO tblName (cust, qty) VALUES('The Man', 14)";
            cmd.CommandText = "SELECT COUNT(1) FROM tblName WHERE qty > 10";
            int total = (int)cmd.ExecuteScalar();
            cmd.CommandText = "UPDATE tblName SET qty = " + (total++).ToString() + " WHERE cust <> 'The Man'";

        catch (SqlException)
}   // "using" should close the connection for you

Open in new window

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

tcalbazAuthor Commented:

You called the precise solution in this case.   Well done.
My thanks to you all for for assisting me with this problem.

Below is the approach that dimaj portrayed.

 private ADODB.Connection cn = new ADODB.Connection();
 private ADODB.Recordset rs = new ADODB.Recordset();
     private string cnStr;
     private string query;
//Connection string.
     cnStr = "Provider=SQLOLEDB;Initial Catalog=Pubs;Data Source=servername;User ID=<username>;Password=;<strong password>";
     query = "Select * From Authors";

     //Connection via Connection open Property.
     cn.Open(cnStr, null, null, 0);

     //Connection via ConnectionString Property.
     cn.ConnectionString = cnStr;
     cn.Open(null, null, null, 0);
käµfm³d 👽Commented:
Still not sure why you'd want to use outdated ADODB recordsets and such unless there is a business need to do so  : )
tcalbazAuthor Commented:

I guess we all have our reasons for doing things.  I'm aware there are better approaches.

My company is performing the rather arduous task of upgrading an enterprise project which starts in asp/vb classic and finishes in c# 4.0 without breaking the code (too much).  

Once we have achieved this we will be incorporating a c# based database framework.  

But for now we just want to make sure we swiftly deliver all of the components working and intact .

Adding refinements will be peformed by the next team.


käµfm³d 👽Commented:
My company is performing the rather arduous task of upgrading an enterprise project which starts in asp/vb classic and finishes in c# 4.0 without breaking the code (too much).

No problem. I was just curious  = )
glad I could be of any help :)
good luck to you
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.