//method below are codes for connecting the database
        private static OleDbCommand DbConnect(OleDbCommand mDB)
        {
            mDB.ConnectionString = "Provider = Microsoft.Jet.OLEDB.4.0";
            + "Data source= C:\Database\winbase.mdb";
            return mDB;
        }

When i debug, i have a error saying unrecognized escape sequence. I tried adding @ and \\ but i have more errors.

Edited 6 Years Ago by __avd: Added [code] tags.

Write the string as follows:

private static OleDbCommand DbConnect(OleDbCommand mDB)
        {
            mDB.ConnectionString = @"Provider = Microsoft.Jet.OLEDB.4.0; Data source= C:\Database\winbase.mdb";
            return mDB;
        }
This should work!

or if you would like to have the string written in two (2) rows:

private static OleDbCommand DbConnect(OleDbCommand mDB)
        {
            mDB.ConnectionString = @"Provider = Microsoft.Jet.OLEDB.4.0; " +
                                   @"Data source= C:\Database\winbase.mdb";
            return mDB;
        }

You get the error because C# considers the backslash character (\) and escape character to use to get a special characters (line return or linefeed). There are two ways to solve this problem, the first being the one Mitja posted, by prefixing the string with the "this is a literal string with no escape sequences" character (@) in front of the string. The second is to escape the escape character (use double \\). So these are equivalent:

mDB.ConnectionString = @"Provider = Microsoft.Jet.OLEDB.4.0; Data source=C:\Database\winbase.mdb";
mDB.ConnectionString = "Provider = Microsoft.Jet.OLEDB.4.0; Data source=C:\\Database\\winbase.mdb";

Hi. Thanks for replying.
I tried both ways and I got more errors.

this is my coding.
this is the one edited with the second way.

using System;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;

namespace WineForUs
{
    public class Stocks
    {
        OleDbCommand mDB = new OleDbCommand();
        OleDbCommand cmd;
        public int intQty;

        //method below are codes for connecting the database
        private static OleDbCommand DbConnect(OleDbCommand mDB)
        {
            mDB.ConnectionString = @"Provider = Microsoft.Jet.OLEDB.4.0; Data source=C:\Database\winbase.mdb";
            return mDB;
        }
        //method below are codes to retrieve Product's quantity on hand
        public Stocks(string strPName)
        {
            DbConnect(mDB);//this command calls DbConnect to activate the connection string
            string strSQL = "Select QtyOnHand From Products Where ProductName = @Name";
            cmd = new OleDbCommand(strSQL, mDB);
            cmd.Parameters.AddWithValue("@Name", strPName);
            // a try catch is used here to catch database errors
            try
            {
                mDB.Open();
                // the line below stores the QtyOnHand read by ExecuteScalar to oQty object
                object oQty = cmd.ExecuteScalar();
                intQty = (int)oQty;
                mDB.Close();
                return;
            }
            catch (Exception ex)
            {
                mDB.Close(); MessageBox.Show(ex.Message);
            }
        } // closing brace for Stocks(string strPName) method
        // method below are codes to update Product's new quantity after a sale
        public Stocks(string strPName, int intQty)
        {
            DbConnect(mDB);
            String strSQL = "UPDATE Products SET QtyOnHand = @Qty WHERE ProductName = @Name";
            cmd = new OleDbCommand(strSQL, mDB);
            cmd.Parameters.AddWithValue("@Qty", intQty);
            cmd.Parameters.AddWithValue("@Name", strPName);
            try
            {
                mDB.Open();
                cmd.ExecuteNonQuery();
                mDB.Close();
                MessageBox.Show("Stock quatity updated");
                return;
            }
            catch (OleDbException e)
            {
                mDB.Close(); MessageBox.Show(e.Message);
            }
        } 
    } 

}

Edited 6 Years Ago by __avd: Added [code] tags.

Hi. Thanks for replying.
I tried both ways and I got more errors.

this is my coding.
this is the one edited with the second way.

using System;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;

namespace WineForUs
{
    public class Stocks
    {
        OleDbCommand mDB = new OleDbCommand();
        OleDbCommand cmd;
        public int intQty;

        //method below are codes for connecting the database
        private static OleDbCommand DbConnect(OleDbCommand mDB)
        {
            mDB.ConnectionString = @"Provider = Microsoft.Jet.OLEDB.4.0; Data source=C:\Database\winbase.mdb";
            return mDB;
        }
        //method below are codes to retrieve Product's quantity on hand
        public Stocks(string strPName)
        {
            DbConnect(mDB);//this command calls DbConnect to activate the connection string
            string strSQL = "Select QtyOnHand From Products Where ProductName = @Name";
            cmd = new OleDbCommand(strSQL, mDB);
            cmd.Parameters.AddWithValue("@Name", strPName);
            // a try catch is used here to catch database errors
            try
            {
                mDB.Open();
                // the line below stores the QtyOnHand read by ExecuteScalar to oQty object
                object oQty = cmd.ExecuteScalar();
                intQty = (int)oQty;
                mDB.Close();
                return;
            }
            catch (Exception ex)
            {
                mDB.Close(); MessageBox.Show(ex.Message);
            }
        } // closing brace for Stocks(string strPName) method
        // method below are codes to update Product's new quantity after a sale
        public Stocks(string strPName, int intQty)
        {
            DbConnect(mDB);
            String strSQL = "UPDATE Products SET QtyOnHand = @Qty WHERE ProductName = @Name";
            cmd = new OleDbCommand(strSQL, mDB);
            cmd.Parameters.AddWithValue("@Qty", intQty);
            cmd.Parameters.AddWithValue("@Name", strPName);
            try
            {
                mDB.Open();
                cmd.ExecuteNonQuery();
                mDB.Close();
                MessageBox.Show("Stock quatity updated");
                return;
            }
            catch (OleDbException e)
            {
                mDB.Close(); MessageBox.Show(e.Message);
            }
        } 
    } 

}

Which lines give you errors, and what errors are they? You need to give us as much information as possible.

okay. this is my codes.

using System;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;

namespace WineForUs
{
    public class Stocks
    {
        OleDbCommand mDB = new OleDbCommand();
        OleDbCommand cmd;
        public int intQty;

        //method below are codes for connecting the database
        private static OleDbCommand DbConnect(OleDbCommand mDB)
        {
            mDB.ConnectionString = @"Provider = Microsoft.Jet.OLEDB.4.0; Data source=C:\Database\winbase.mdb";
            return mDB;
        }
        //method below are codes to retrieve Product's quantity on hand
        public Stocks(string strPName)
        {
            DbConnect(mDB);//this command calls DbConnect to activate the connection string
            string strSQL = "Select QtyOnHand From Products Where ProductName = @Name";
            cmd = new OleDbCommand(strSQL, mDB);
            cmd.Parameters.AddWithValue("@Name", strPName);
            // a try catch is used here to catch database errors
            try
            {
                mDB.Open();
                // the line below stores the QtyOnHand read by ExecuteScalar to oQty object
                object oQty = cmd.ExecuteScalar();
                intQty = (int)oQty;
                mDB.Close();
                return;
            }
            catch (Exception ex)
            {
                mDB.Close(); MessageBox.Show(ex.Message);
            }
        } // closing brace for Stocks(string strPName) method
        // method below are codes to update Product's new quantity after a sale
        public Stocks(string strPName, int intQty)
        {
            DbConnect(mDB);
            String strSQL = "UPDATE Products SET QtyOnHand = @Qty WHERE ProductName = @Name";
            cmd = new OleDbCommand(strSQL, mDB);
            cmd.Parameters.AddWithValue("@Qty", intQty);
            cmd.Parameters.AddWithValue("@Name", strPName);
            try
            {
                mDB.Open();
                cmd.ExecuteNonQuery();
                mDB.Close();
                MessageBox.Show("Stock quatity updated");
                return;
            }
            catch (OleDbException e)
            {
                mDB.Close(); MessageBox.Show(e.Message);
            }
        } //closing brace for stocks(string strPName, int intQty) method
    } // closing brace for class

}// closing brace for namespace

there are 5 errors.
1. 'System.Data.OleDb.OleDbCommand' does not contain a definition for 'Close' and no extension method 'Close' accepting a first argument of type 'System.Data.OleDb.OleDbCommand' cound be found.
Line 35 Column 21, Line 40 Column 21, Line 55 Column 21, Line 61 Column 21.
2. 'System.Data.OleDb.OleDbCommand' does not contain a definition for 'ConnectionString' and no extension method 'ConnectionString' accepting a first argument of type 'System.Data.OleDb.OleDbCommand' cound be found.
Line 18 Column 17
3. 'System.Data.OleDb.OleDbCommand' does not contain a definition for 'Open' and no extension method 'Open' accepting a first argument of type 'System.Data.OleDb.OleDbCommand' cound be found.
Line 31 Column 21, Line 53 Column 21
4.Argument'2' : cannot convert from 'System.Data.OleDb.OleDbCommand'to 'System.Data.OleDb.OleDbConnection'
Line 26 Column 44, Line 48 Column 44
5. The best overloaded method match for 'System.Data.OleDb.OleDbCommand.OleDbCommand(string,System.Data.OleDb.OleDbConnection)' has some invalid arguments.
Line 26 Column 19, Line 48 Column 19

These errors has nothing to do with the connection string to your database. Your code is a bit weird.
Do you use "using" statement while accessing to db?

like this:

try
{
 using (SqlConnection sqlConn = new SqlConnection( connectionString ) ) 
 {
  //query string and opening the connetion
  sqlConn.Open();
 }
}
catch ( Exception ) 
{
 //catch errors
}
finally
{
 //closing connection
}

Example:

private static void CreateCommand(string queryString, string connectionString)
{
  using (SqlConnection connection = new SqlConnection(connectionString))
  {
    SqlCommand command = new SqlCommand(queryString, connection);
    command.Connection.Open();
    command.ExecuteNonQuery();
  }
}

This is the best approach, I would say.

To ensure that connections are always closed, open the connection inside of a using block, as shown in the following code fragment. Doing so ensures that the connection is automatically closed when the code exits the block.

This article has been dead for over six months. Start a new discussion instead.