Inserting into Access DB using C#

Question

I'm having a little trouble inserting new entries into an Access DB using C#. I think the problem is my insert statement, but I'm including most of the program just in case someone sees another serious mistake. The error I'm getting happens after entering values into a second form. I would say that maybe I did something wrong getting values from the second form, but looking at the message box I put in to check that with, the values are clearly being received. It throws this error right after I close the message box says Data type mismatch in criteria expression.

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

namespace AHamblin_Larrys1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
    private void Form1_Load(object sender, EventArgs e)
    {

    }

    private void InitializeDataGridView(string nameOfTable, string[] fieldNames)
    {

        //Define database connection string and dataset
        String connectionString =
                  @"Provider=Microsoft.ACE.OLEDB.12.0;Data"
                + @" Source=C:\Users\cryow_000\Desktop\AhamblinLarrys1.accdb";
        String tableName = nameOfTable;
        String selectStatement = String.Format(
                      "select * from [{0}]", tableName);
        DataSet ds = new DataSet();
        OleDbConnection connection =
              new OleDbConnection(connectionString);

        try
        {
            //Open Database Connection
            connection.Open();
            OleDbDataAdapter da =
                   new OleDbDataAdapter(selectStatement, connection);
            OleDbCommandBuilder cmdB =
                   new OleDbCommandBuilder(da);
            da.MissingSchemaAction =
                   MissingSchemaAction.AddWithKey;

            //Fill the DataSet
            da.Fill(ds, tableName);

            // Initialize a DataGridView.
            dataGridView1.Rows.Clear();
            dataGridView1.ColumnCount = ds.Tables[tableName].Columns.Count;
            dataGridView1.ColumnHeadersVisible = true;

            // Set the column header style.
            DataGridViewCellStyle columnHeaderStyle = new DataGridViewCellStyle();

            columnHeaderStyle.BackColor = Color.Beige;
            columnHeaderStyle.Font = new Font("Verdana", 10, FontStyle.Bold);
            dataGridView1.ColumnHeadersDefaultCellStyle = columnHeaderStyle;

            // Set the column header names.
            string[] fieldTitle = fieldNames;

            for (int i = 0; i < dataGridView1.Columns.Count; i++)
            {
                dataGridView1.Columns[i].Name = fieldTitle[i];
            }

            // Populate the dataset rows. 
            string[,] table = new string[ds.Tables[tableName].Rows.Count, ds.Tables[tableName].Columns.Count];

            for (int i = 0; i < ds.Tables[tableName].Rows.Count; i++)
            {
                for (int k = 0; k < ds.Tables[tableName].Columns.Count; k++)
                {
                    table[i, k] = Convert.ToString(ds.Tables[tableName].Rows[i][k]);
                }
            }

            //Populate the DataGridView with dataset rows.
            var rowCount = table.GetLength(0);
            var rowLength = table.GetLength(1);

            for (int rowIndex = 0; rowIndex < rowCount; ++rowIndex)
            {
                var row = new DataGridViewRow();

                for (int columnIndex = 0; columnIndex < rowLength; ++columnIndex)
                {
                    row.Cells.Add(new DataGridViewTextBoxCell()
                    {
                        Value = table[rowIndex, columnIndex]
                    });
                }

                dataGridView1.Rows.Add(row);
            }

            //Close the Database Connection
            connection.Close();


        }
        catch (OleDbException exp)
        {
            MessageBox.Show("Database Error:" + exp.Message.ToString());
        }
        finally
        {
            if (connection.State == ConnectionState.Open)
            {
                connection.Close();
            }
        }
    }

    private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
    {

    }

    private void CustomerButton_Click(object sender, EventArgs e)
    {
        {
            // Define table to use
            string nameOfTable = "Customer";

            // Define field names
            string[] fieldNames = new string[] { "Cust. ID" , "Timestamp" , "Name" , "Street" , "City" , "State" , "ZIP" , "Telephone" , "Email" , "Balance" };

            // Send data to DataGridView
            InitializeDataGridView(nameOfTable, fieldNames);

            insertButton.Text = "New Customer";
            updateButton.Text = "Update Selected";
            deleteButton.Text = "Delete Selected";
        }
    }

    private void EmployeeButton_Click(object sender, EventArgs e)
    {
        string nameOfTable = "Employee";
        string[] fieldNames = new string[] { "Emp. ID", "Timestamp", "Name", "Street", "City", "State", "ZIP", "Telephone", "Email", "Department" , "Manager" };
        InitializeDataGridView(nameOfTable, fieldNames);
    }

    private void InventoryButton_Click(object sender, EventArgs e)
    {
        string nameOfTable = "Inventory";
        string[] fieldNames = new string[] { "Item ID", "Created", "Updated", "Description", "Price", "Quantity", "Vendor" };
        InitializeDataGridView(nameOfTable, fieldNames);
    }

    private void TransButton_Click(object sender, EventArgs e)
    {
        string nameOfTable = "Transaction";
        string[] fieldNames = new string[] { "Trans. ID", "Timestamp", "Cust. ID", "Item ID", "Emp. ID", "Quantity", "Subtotal", "Tax", "Total" };
        InitializeDataGridView(nameOfTable, fieldNames);
    }

    private void VendorButton_Click(object sender, EventArgs e)
    {
        string nameOfTable = "Vendor";
        string[] fieldNames = new string[] { "Vendor ID", "Timestamp", "Name", "Street", "City", "State", "ZIP", "Telephone", "Email", "Products" };
        InitializeDataGridView(nameOfTable, fieldNames);
    }

    private void insertButton_Click(object sender, EventArgs e)
    {
        if (insertButton.Text == "New Customer")
        {
            var Info = new CustomerInfo();
            Info.Text = "New Customer";
            Info.ShowDialog();
            if (Info.DialogResult == DialogResult.OK)
            {
                string custname = Info.ReturnValue1;            //values preserved after close
                string dateString = Info.ReturnValue2;
                string street = Info.ReturnValue3;
                string city = Info.ReturnValue4;
                string state = Info.ReturnValue5;
                string zip = Info.ReturnValue6;
                string phone = Info.ReturnValue7;
                string email = Info.ReturnValue8;
                string balance = Info.ReturnValue9;

                MessageBox.Show(custname + " " + dateString + " " + street + " " + city + " " + state + " " + zip + " " + phone + " " + email + " " + balance);

                //int cellselected = Convert.ToInt32(dataGridView1.CurrentCell.Selected);
                String connectionString =
                       @"Provider=Microsoft.ACE.OLEDB.12.0;Data"
                     + @" Source=C:\Users\cryow_000\Desktop\AhamblinLarrys1.accdb";
                String tableName = "Customer";

                OleDbConnection connection = new OleDbConnection(connectionString);

                OleDbCommand cmd = new OleDbCommand("INSERT INTO Customer([timestamp],[cust_name],[street],[city],[zip],[state],[telephone],[email],[balance]) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)", connection);

                cmd.Parameters.AddWithValue("@timestamp", OleDbType.DBTimeStamp).Value = dateString;
                cmd.Parameters.AddWithValue("@cust_name", OleDbType.Char).Value = custname;
                cmd.Parameters.AddWithValue("@street", OleDbType.Char).Value = street;
                cmd.Parameters.AddWithValue("@city", OleDbType.Char).Value = city;
                cmd.Parameters.AddWithValue("@state", OleDbType.Char).Value = state;
                cmd.Parameters.AddWithValue("@zip", OleDbType.Numeric).Value = zip;
                cmd.Parameters.AddWithValue("@telephone", OleDbType.Char).Value = phone;
                cmd.Parameters.AddWithValue("@email", OleDbType.Char).Value = email;
                cmd.Parameters.AddWithValue("@balance", OleDbType.Currency).Value = street;

                cmd.Connection = connection;
                connection.Open();
                cmd.ExecuteNonQuery();
                System.Windows.Forms.MessageBox.Show("An Item has been successfully added", "Caption", MessageBoxButtons.OKCancel, MessageBoxIcon.Information);

                // Define field names
                string[] fieldNames = new string[] { "Cust. ID", "Timestamp", "Name", "Street", "City", "State", "ZIP", "Telephone", "Email", "Balance" };

                // Send data to DataGridView
                InitializeDataGridView(tableName, fieldNames);

            }

        }

    }


}
}

Answer 1

Hey this usually means the data type you are trying to insert into the database fields is not the same as what it was declared as the data type in the database. For example trying to insert a string such as "hello" into an integer field on a database eg amount.

For example (excuse the poor freehand sql) INSERT INTO Transaction(amount) VALUES ("HELLLO") Wouldn't make sense as you have decelerated amount of transaction as an integer and are trying to insert a string into it.

               "INSERT INTO Customer([timestamp],[cust_name],[street],[city],[zip],[state],[telephone],[email],[balance]) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)", connection);

So therefore as mentioned in the answer above a parameter type being inserted does not match so check if you have everything the right way around and in the right order ect in the insert statement.

Answer 2

The problem is almost certainly the order in which you specified the parameters for your insert. It seems the OleDbCommand doesn't support named parameters as would be the common way of doing this. Instead the actual order is significant. This can cause this problem of not referencing what you think you are.

Your insert goes: [timestamp],[cust_name],[street],[city],[zip],[state],[telephone],[email],[balance]

But the parameters are:

@timestamp @cust_name @street @city @state @zip @telephone @email @balance

Causing the numeric into char and vice-versa data type mismatch problem.

MSDN article about this crazy ordering neccessity