How can I populate a textbox from an Access 2010 database in C#?

Question

I'm trying to populate a textbox from a MS-Access 2010 database table. The table has 2 fields, Night and Seats. I want to put the current value in the Seats field into the textbox, assuming there is a current value for the date selected in the Night field. The code I have right now is:

       //connect to the database to get how many seats are available
       System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection();
       con.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Duncan\Documents\Visual Studio 2012\Projects\TheatreUI\TheatreUI\bin\Debug\PlayHouse.accdb";
       OleDbCommand cmd = con.CreateCommand();

        //open the connection
        con.Open();

        // read from the Nights Table in the database
        cmd.CommandText = "SELECT Seats FROM Nights WHERE Night = '" + System.DateTime.Now.ToShortDateString() + "';";
        MessageBox.Show(System.DateTime.Now.ToShortDateString());
        OleDbDataReader reader = cmd.ExecuteReader();
        MessageBox.Show(reader["Seats"].ToString());
        SeatsText.Text = reader["Seats"].ToString();

        //close the connection
        con.Close();

Not only does this code not populate the textfield properly (or at all) it seems to delete the record for today's date from the database entirely. The first messagebox shows the correct date, but the second one shows blank. How can I fix this code so it populates the textbox and doesn't delete the entry in the database?

Answer 1

If you must use OleDb, the following will work. I have tested the code myself.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();


            string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data
                Source=C:\Users\James\Desktop\Programming\2004RAW.accdb";

                string query = "SELECT FIRST_NAME FROM 2004RAW";

            GetQuery(query, connectionString);
        }

        public void GetQuery(string query, string connectionString)
        {
            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                OleDbCommand command = new OleDbCommand(query, connection);
                connection.Open();
                OleDbDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    Console.WriteLine(reader["FIRST_NAME"]);
                }
                reader.Close();
            }
        }
    }
}

Answer 2

public List<DataTable> GetWithQuery(string query)
{
    DataTable dataTable = new DataTable();

    using (OleDbConnection source = new
        OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data
        Source=C:\Users\Duncan\Documents\Visual Studio
        2012\Projects\TheatreUI\TheatreUI\bin\Debug\PlayHouse.accdb"))
    {
        using (OleDbCommand sourceCommand = new OleDbCommand(query, source))
        {
            source.Open();

            using (OleDbDataReader dr = sourceCommand.ExecuteReader())
            {
                try
                {
                    dataTable.Load(dr);
                }
                catch (Exception)
                {
                    //Do nothing
                }
                finally
                {
                    source.Close();
                }
            }
        }
    }

    return dataTable;
}

Then you can simply call the following:

string query = "SELECT Seats FROM Nights WHERE Night = '" + System.DateTime.Now.ToShortDateString();
DataTable dataTable = GetWithQuery(query);
Console.WriteLine(dataTable["Seats"]);

Now I free hand this for the most part so it may not work out of the box but it should give you an idea.