Correct c# syntax for adodb.connection

Most venerated pc guru's,
As a newbie to c# program who is conversant in vb.net 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 vb.net 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.

thanks,

Ted

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

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

	//On error resume next
	cn.BeginTrans();

Open in new window

LVL 1
tcalbazAsked:
Who is Participating?
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.

TheMozzCommented:
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))
            {
                try
                {
                    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
                    connection.Open();

                    // 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
                    da.Fill(ds);

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

                    // return an empty dataset on exception
                    throw;
                }
                finally
                {
                    connection.Close();
                }
            }
        } // 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.
0
dimajCommented:
Check out this link: http://support.microsoft.com/kb/308611
It might be of some help.
0

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))
    {
        try
        {
            cmd.CommandText = "INSERT INTO tblName (cust, qty) VALUES('The Man', 14)";
            cmd.ExecuteNonQuery();
            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'";
            cmd.ExecuteNonQuery();

            trans.Commit();
        }
        catch (SqlException)
        {
            trans.RollBack();
        }
    }
}   // "using" should close the connection for you

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

tcalbazAuthor Commented:
dimaj

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
     query = "Select * From Authors";

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

     //Connection via ConnectionString Property.
     cn.ConnectionString = cnStr;
     cn.Open(null, null, null, 0);
     cn.Close();
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  : )
0
tcalbazAuthor Commented:
kaufmed:

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.

regards,

0
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  = )
0
dimajCommented:
glad I could be of any help :)
good luck to you
0
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
C#

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.