Correct c# syntax for adodb.connection

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.

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

Select allOpen in new window

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

Still not sure why you'd want to use outdated ADODB recordsets and such unless there is a business need to do so  : )

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,

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