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);
}
}
}
}
}
@balancevalue to thestreetvariable, which will result in a type mismatch. Also, not a real issue, but using.AddWithValuethat way, you're setting the actual value of the parameter to the type specified, then using.Valueto set it to the variable value. Just use.AddWithValue("@balance", balance);