I am a bit new to coding so please excuse my lack of knowledge. Using C#, .NET 8.0 with Microsoft.Data.SqlClient and System.Data.OleDb NuGet packages.
I have an application that I have written that imports information from a PDF form and exports the data to an Access database. I recently added a feature to the application that uses tables in the Access database to populate 3 dropdown boxes in the form. This code works perfectly fine to populate the drop box options and the rest of the code works fine to input information into the database.
The error "An error occurred: Column 'Special' does not belong to table ." comes up when I try to pull information from the database into my form. one of three of these drop boxes (Status) works without issue but the other two (Special and Priority) provide this error.
I have validated there are no spelling or capitalization errors and my code references and imports data to these columns without issue.
Below is a simplified version of my method. I removed most of the repetitive code. The three code sections pertaining to my issue are labeled as // Revised inline selection for X ComboBox
private void btnExistingRequest_Click(object sender, EventArgs e)
{
// Store the current RequestNum value
string tempRequestNum = RequestNum.Text;
// Clear the form
ClearForm();
// Restore the RequestNum value
RequestNum.Text = tempRequestNum;
using (OleDbConnection connection = dbHelper.GetConnection())
{
// Fetch data from ToolingRequests table
string queryToolingRequests = "SELECT * FROM ToolingRequests WHERE RequestNum = ?";
using (OleDbCommand cmd = new OleDbCommand(queryToolingRequests, connection))
{
cmd.Parameters.AddWithValue("?", RequestNum.Text);
try
{
connection.Open();
using (OleDbDataReader reader = cmd.ExecuteReader())
{
if (reader.Read()) // if data found
{
// Load Text fields
RequestNum.Text = reader["RequestNum"] == DBNull.Value ? string.Empty : reader["RequestNum"].ToString();
// Prevent StatusDate from updating when Status is set
shouldUpdateStatusDate = false;
// Revised inline selection for Status ComboBox
string statusValue = reader["Status"]?.ToString();
var statusItem = Status.Items.Cast<DataRowView>().FirstOrDefault(item => item.Row["Status"].ToString() == statusValue);
if (statusItem != null)
Status.SelectedItem = statusItem;
else
Status.SelectedIndex = -1;
// Revised inline selection for Special ComboBox
string specialValue = reader["Special"]?.ToString();
var specialItem = Special.Items.Cast<DataRowView>().FirstOrDefault(item => item.Row["Special"].ToString() == specialValue);
if (specialItem != null)
Special.SelectedItem = specialItem;
else
Special.SelectedIndex = -1;
// Revised inline selection for Priority ComboBox
string priorityValue = reader["Priority"]?.ToString();
var priorityItem = Priority.Items.Cast<DataRowView>().FirstOrDefault(item => item.Row["Priority"].ToString() == priorityValue);
if (priorityItem != null)
Priority.SelectedItem = priorityItem;
else
Priority.SelectedIndex = -1;
// Load ComboBox values
UOM.SelectedItem = reader["UOM"] == DBNull.Value ? null : reader["UOM"].ToString();
// Re-enable updating StatusDate
shouldUpdateStatusDate = true;
// Load DateTimePicker values
SetDateTimePickerValue(MaintCalDate, reader["MaintCalDate"]);
// Load CheckBox values
OpShtCheck.Checked = reader["OpShtCheck"] != DBNull.Value && reader["OpShtCheck"].ToString().Equals("Yes", StringComparison.OrdinalIgnoreCase);
}
else
{
MessageBox.Show("No data found for the specified Request Number.", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
catch (Exception ex)
{
MessageBox.Show($"An error occurred: {ex.Message}", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
// After reading data and populating fields
if (!string.IsNullOrWhiteSpace(ToolNum.Text))
{
// Check if ToolNum is populated
btnExistingTool_Click(null, null);
}
}
}
}
}
I tried pulling this information with the same lines I use for pulling the other comboboxes
UOM.SelectedItem = reader["UOM"] == DBNull.Value ? null : reader["UOM"].ToString();
but this didn't pull any information for the fields. So I revised the code to the new snippet but it only works for the Status field.
Edit: Updated code section per Dour High Arch recommendation still not working.
// Fetch data from ToolingRequests table
string queryToolingRequests = "SELECT RequestNum, WO, PO, ToolNum, ProdPart, Type, RefTool, Qty, UOM, ProdCustomer, Other, Reason, ReqDate, Priority, Department, Requestor, Status, SupPE, supext, reqext, StatusDate, ToolNotes, APCheck, AP, APDate, RefDocCheck, RefDoc, RefDocDate, MaintCalCheck, MaintCalSch, MaintCalDate, CalProCheck, CalPro, CalProDate, OtherCheck, OtherCrit, OtherDate, SF343Check, SF343, SF343Date, ERCheck, ERNum, ERDate, OtherValCheck, OtherVal, OtherValDate, DOECheck, DOERej, DOEDate, DOQCheck, DOQRej, DOQDate, Reject, CompDate, ME, MEDate, OpSht, OpShtDate, OpShtCheck, PEApv, PEDate, QEApv, QEDate, Special FROM ToolingRequests WHERE RequestNum = ?";
using (OleDbCommand cmd = new OleDbCommand(queryToolingRequests, connection))
{
// Use .Add method with the correct data type
cmd.Parameters.Add("?", OleDbType.VarChar).Value = RequestNum.Text;
I fiend of mine was able to diagnose the problem and it revolved around the item array. When the form was attempting to populate the combobox the item array needed to be set to [0] and not the search value. This allowed the code to iterate through the array of values provided by the Access database.
// Revised inline selection for Status ComboBox
string statusValue = reader["Status"]?.ToString();
var statusItem = Status.Items.Cast<DataRowView>().FirstOrDefault(item => item.Row.ItemArray[0].ToString() == statusValue);
if (statusItem != null)
{
Status.SelectedItem = statusItem;
}
else
{
Status.SelectedIndex = -1;
}