none
insert values and retrieve values from Access database using C# Windows Form Application RRS feed

  • Question

  • How do i insert values into an Access Database using a C# console or windows form application(preferable) and retrieve the values from Access and display it in a form .......


    namespace TestDbReadWrite
    {
        using System;
    using System.Data.ADO; // Database
    using System.Data; // State variables
    using System.Globalization; // Date

    /// <summary>
    /// This class demonstrates reading and writing some simple
    /// data types to and from a database in C#.
    /// </summary>
    public class MainConsole
        {
    //Attributes
    public const string DB_CONN_STRING = 
    "Driver={Microsoft Access Driver (*.mdb)}; "+
    "DBQ=D:\\CS\\TestDbReadWrite\\SimpleTest.mdb";

    /// <summary>
    /// This is where the console application start.
    /// Not really very exciting.
    /// </summary>
    /// <param name="args"> </param>
    public static int Main(string[] args)
            {
    // Say hello and get to work
    System.Console.WriteLine( "Simple Database access.\n" );
              
    //Display the current data
    DisplayData();

    //Set the age of John to 27
    UpdateRecordByName( "John", 27 );
               
    //Add a new record
    AddRecordData( 99, "Oldy", "A little bit dottery" );

    //Display the new data
    DisplayData();

    //Delete the Oldy records
    DeleteRecordByName( "Oldy" );

    //Set the age of John to 27
    UpdateRecordByName( "John", 55 );

    //Display the new data
    DisplayData();

    // Say goodbye
    System.Console.WriteLine( "\nSo long and thanks for all the fish." );
                return 0;
            }
    /// <summary>
    /// This function opens the database and displays the information
    /// that is available and meets the where clause of the SQL
    /// statement.
    /// </summary>
            private static void DisplayData()
            {
    // Connection string and object
    //string sSqlCmd = "SELECT * FROM Person WHERE [Age] > 30 ";
    string sSqlCmd = "SELECT * FROM Person";
    ADOConnection conn = null;
    try
    {
    // Open the database
    conn = new ADOConnection(DB_CONN_STRING);
    conn.Open();

    // Get the data stream
    ADODataReader dr;
    ADOCommand cmd = new ADOCommand( sSqlCmd, conn );
    cmd.Execute( out dr);

    // Iterate through all returned records
    while( dr.Read() )
    {
    // Display the Name and Description
    System.Console.WriteLine( "+++++++++++++++++++++++++++++++++++++" );
    System.Console.WriteLine( " Name : " + dr["FirstName"] );
    System.Console.WriteLine( " Desc.: " + dr["Description"] );

    // Display the Age which may be empty
    int nOrdinalAge = dr.GetOrdinal( "Age" );
    if( dr.IsNull( nOrdinalAge ) )
    {
    System.Console.WriteLine( " Age  : Not given!" );
    }
    else
    {
    int nAge = dr.GetInt32( nOrdinalAge );
    System.Console.WriteLine( " Age  : " + nAge );
    }

    // Display the Date and Time of the reading
    DateTime tUpdated = (DateTime)dr["Updated"];
    System.Console.WriteLine( " Updt.: " + 
    tUpdated.Format( "F", DateTimeFormatInfo.InvariantInfo ) );
    }
    dr.Close();
    }
    catch( Exception ex )
    {
    System.Console.WriteLine( "**************************************" );
    System.Console.WriteLine( "READING:" );
    System.Console.WriteLine( "  ERROR:" + ex.Message );
    System.Console.WriteLine( "  SQL  :" + sSqlCmd );
    System.Console.WriteLine( "  Conn.:" + DB_CONN_STRING );
    System.Console.WriteLine( "**************************************" );
    }
    finally
    {
    // Close the connection
    if( conn.State == DBObjectState.Open )
    conn.Close();
    }
            }

    /// <summary>
    /// Add a new record to the Person database.
    /// </summary>
    /// <param name="nAge"> Persons age </param>
    /// <param name="sName"> Name < 5 chars </param>
    /// <param name="sDescription"> Some notes about them </param>
            private static void AddRecordData( int nAge, string sName, string sDescription )
            {
    //Get and format the current time to unerversal time
    DateTime dtNow = DateTime.Now;
    string sNow = dtNow.Format( "u", DateTimeFormatInfo.CurrentInfo );
    sNow = sNow.Remove( sNow.Length-1, 1 );

    // Build the insert string
    String sCommand = "INSERT INTO Person (Age, FirstName, Description, Updated)  " +
       "VALUES( "  + nAge + ", " +
    "'" + sName + "', " +
    "'" + sDescription + "', " +
    "'" + sNow + "' );";
    try
    {
    // Execute the SQL command
    ADOCommand cmdAdder = new ADOCommand(
    sCommand,
    DB_CONN_STRING);
    cmdAdder.ActiveConnection.Open();
    int nNoAdded = cmdAdder.ExecuteNonQuery();
    System.Console.WriteLine( "\nRow(s) Added = " + nNoAdded + "\n" );

    // Force update of the Database
    cmdAdder.ActiveConnection.BeginTransaction();
    cmdAdder.ActiveConnection.CommitTransaction();
    }
    catch (Exception ex)
    {
    System.Console.WriteLine( "**************************************" );
    System.Console.WriteLine( "WRITING:" );
    System.Console.WriteLine( "  ERROR:" + ex.Message );
    System.Console.WriteLine( "  SQL  :" + sCommand );
    System.Console.WriteLine( "  Conn.:" + DB_CONN_STRING );
    System.Console.WriteLine( "**************************************" );
    }
            }


    /// <summary>
    /// Delete all records that ahve the [FirstName] field
    /// equal to sName. This could be several records
    /// </summary>
    /// <param name="sName"> Name to find in[FirstName] field  </param>
    private static void DeleteRecordByName( string sName )
            {
    // Build the DELETE string
    String sCommand = "DELETE FROM Person " +
    "WHERE FirstName = '" + sName + "'";
    try
    {
    // Execute the SQL command
    ADOCommand cmdAdder = new ADOCommand(
    sCommand,
    DB_CONN_STRING);
    cmdAdder.ActiveConnection.Open();
    int nDeleted = cmdAdder.ExecuteNonQuery();
    System.Console.WriteLine( "\nRow(s) Deleted = " + nDeleted + "\n" );

    // Force update of the Database
    cmdAdder.ActiveConnection.BeginTransaction();
    cmdAdder.ActiveConnection.CommitTransaction();
    }
    catch (Exception ex)
    {
    System.Console.WriteLine( "**************************************" );
    System.Console.WriteLine( "DELETING:" );
    System.Console.WriteLine( "  ERROR:" + ex.Message );
    System.Console.WriteLine( "  SQL  :" + sCommand );
    System.Console.WriteLine( "  Conn.:" + DB_CONN_STRING );
    System.Console.WriteLine( "**************************************" );
    }
            }
    /// <summary>
    /// Modify the record(s) with [FirstName] equal to sName
    /// with the new [Age] value
    /// </summary>
    /// <param name="sName"> Name to match on </param>
    /// <param name="nNewAge"> new age value </param>
    private static void UpdateRecordByName( string sName, int nNewAge )
            {
    // Build the UPDATE string
    String sCommand = "UPDATE Person " +
    " SET Age = " + nNewAge +
    " WHERE FirstName = '" + sName + "'";
    try
    {
    // Execute the SQL command
    ADOCommand cmdAdder = new ADOCommand(
    sCommand,
    DB_CONN_STRING);
    cmdAdder.ActiveConnection.Open();
    int nUpdated = cmdAdder.ExecuteNonQuery();
    System.Console.WriteLine( "\nRow(s) Updates = " + nUpdated + "\n" );

    // Force update of the Database
    cmdAdder.ActiveConnection.BeginTransaction();
    cmdAdder.ActiveConnection.CommitTransaction();
    }
    catch (Exception ex)
    {
    System.Console.WriteLine( "**************************************" );
    System.Console.WriteLine( "UPDATING:" );
    System.Console.WriteLine( "  ERROR:" + ex.Message );
    System.Console.WriteLine( "  SQL  :" + sCommand );
    System.Console.WriteLine( "  Conn.:" + DB_CONN_STRING );
    System.Console.WriteLine( "**************************************" );
    }
            }
    }
    }



    This program shows the following error code ...


    Error 1 The type or namespace name 'ADO' does not exist in the namespace 'System.Data' (are you missing an assembly reference?) C:\Documents and Settings\E458117\My Documents\Visual Studio 2005\Projects\CSDBConn\CSDBConn\Program.cs 4 19 CSDBConn



    Is there any ADO in namespace System.Data or not ?





    AN COMPUTER SCIENCE ENGINEERING STUDENT
    Saturday, January 31, 2009 3:47 AM

Answers