System.Data.SqlClient.SqlException："无效的列名'Project_ID_Backend'。

Question

我编写了这个C#应用程序，它充当了一个数据库来跟踪项目和项目注释。我做得很好，但后来我需要将ProjectID列更改为非整数值(项目id以字母开头(，所以我必须添加一个新列才能接受这一点。

我保留了旧的项目ID，并将其重命名为"；Project_ID_Backend；所以我的代码仍然可以工作。(我写的方式是，如果项目ID大于0，事情就会发生。

现在，每当我点击数据网格视图时，我都会收到一条错误消息。它过去的工作方式是，当你点击数据网格视图时，网格中那一行的内容会自动填充文本框，从而很容易对记录进行更改。

using System;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Data;
using System.Collections.Generic;
namespace SFTool
{
public partial class MainForm : Form
{
public MainForm()
{
InitializeComponent();
}
SqlConnection con = new SqlConnection("my connection string here");
public int ProjectIDBackend;
public int NoteID;
private void MainForm_Load(object sender, System.EventArgs e)
{
// load in sql data to the data grid view
GetProjectsDataset();

// load notes database into the notesDataGridView
GetNotesDataset();

PopulateListBoxes();

}
private void PopulateListBoxes()
{
List<string> ProjectStatusList = new List<string>();
ProjectStatusList.Add("Working");
ProjectStatusList.Add("Submitted");
ProjectStatusList.Add("Reviewed");
ProjectStatusList.Add("Completed");
projectStatusListBox.DataSource = ProjectStatusList;

List<string> ProjectTypeList = new List<string>();
ProjectTypeList.Add("New build");
ProjectTypeList.Add("MAC");
ProjectTypeList.Add("Decom");
projectTypeListBox.DataSource = ProjectTypeList;
}
private void GetNotesDataset()
{
SqlCommand cmd = new SqlCommand("SELECT * FROM sfNotes", con);
DataTable dtNotes = new DataTable();
con.Open();
SqlDataReader sdr = cmd.ExecuteReader();
dtNotes.Load(sdr);
con.Close();
notesDataGridView.DataSource = dtNotes;
// Automatically resizes the columns to fit the data grid view
notesDataGridView.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
notesDataGridView.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.None;
}
private void GetProjectsDataset()
{

SqlCommand cmd = new SqlCommand("SELECT Project_ID AS 'Project ID', First_Name AS 'First Name', Last_Name AS 'Last Name', Project_Type AS 'Type', Project_Status AS 'Project Status', Last_Updated AS 'Last Updated', Last_Updated_By AS 'Last Updated By', Project_ID_Backend AS 'P.ID Backend'  " +
"FROM sfProjects", con);
DataTable dt = new DataTable();
con.Open();
SqlDataReader sdr = cmd.ExecuteReader();
dt.Load(sdr);
con.Close();
dataGridView.DataSource = dt;
// Automatically resizes the columns to fit the data grid view
dataGridView.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
dataGridView.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.None;
}
private void insertButton_Click(object sender, EventArgs e)
{
// allow user to insert data into the database, or create new records
if (IsValid())
{
SqlCommand cmd = new SqlCommand("INSERT INTO sfProjects VALUES (@Project_ID, @FirstName, @LastName, @Project_Type, @Project_Status, @Last_Updated, @Last_Updated_By)", con);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@Project_ID", projectIDTextBox.Text);
cmd.Parameters.AddWithValue("@FirstName", firstNameTextBox.Text); // maps @FirstName to the firstName textbox
cmd.Parameters.AddWithValue("@LastName", lastNameTextBox.Text);
cmd.Parameters.AddWithValue("@Project_Type", projectTypeListBox.SelectedItem);
cmd.Parameters.AddWithValue("@Last_Updated", DateTime.Now);
cmd.Parameters.AddWithValue("@Last_Updated_By", System.Environment.MachineName);
cmd.Parameters.AddWithValue("@Project_Status", projectStatusListBox.SelectedItem);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
MessageBox.Show("New record has been successfully added to the database", "Saved", MessageBoxButtons.OK, MessageBoxIcon.Information);
// now update/REFRESH the database so it shows the new record
GetProjectsDataset();
// reset form controls
ResetFormControls();
}
}
// data validation. (Make sure name is never empty)
private bool IsValid()
{
if (firstNameTextBox.Text == string.Empty)
{
MessageBox.Show("First Name is required.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return false;
}
// else return true (it is valid)
return true; 
}
private bool IsNotesValid()
{
if (notesProjectIDTextBox.Text == string.Empty)
{
MessageBox.Show("Project ID is required in order to assign a note to a project.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return false;
}
// else return true (it is valid/Project ID was provided)
return true;
}

private void resetButton_Click(object sender, EventArgs e)
{
// resets, or clears, the textboxes 
ResetFormControls();
GetProjectsDataset();
GetNotesDataset();
}
private void ResetFormControls()
{
ProjectIDBackend = 0; // resets value of projectID after clicking reset
projectIDTextBox.Clear();
firstNameTextBox.Clear();
lastNameTextBox.Clear();
projectTypeListBox.ClearSelected();
projectStatusListBox.ClearSelected();
notesProjectIDTextBox.Clear();
notesTextBox.Clear();
searchProjectsTextBox.Clear();
projectIDTextBox.Focus();
}
private void dataGridView_CellClick(object sender, DataGridViewCellEventArgs e)
{
// WHen you click on a row in the data grid, the textboxes will automatically populate with that rows values
ProjectIDBackend = Convert.ToInt32(dataGridView.SelectedRows[0].Cells[7].Value);
projectIDTextBox.Text = dataGridView.SelectedRows[0].Cells[0].Value.ToString();
firstNameTextBox.Text = dataGridView.SelectedRows[0].Cells[1].Value.ToString();
lastNameTextBox.Text = dataGridView.SelectedRows[0].Cells[2].Value.ToString();
projectTypeListBox.SelectedItem = dataGridView.SelectedRows[0].Cells[3].Value.ToString();
projectStatusListBox.SelectedItem = dataGridView.SelectedRows[0].Cells[4].Value.ToString();

// Filter the notes grid to only show notes for the row the user clicks 
SqlCommand cmd = new SqlCommand("SELECT * FROM sfNotes WHERE Project_ID_Backend= @Project_ID_Backend", con);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@Project_ID_Backend", this.ProjectIDBackend); 
con.Open();
cmd.ExecuteNonQuery();
con.Close();

DataTable dtF = new DataTable();
con.Open();
SqlDataReader sdr = cmd.ExecuteReader();
dtF.Load(sdr);
con.Close();
notesDataGridView.DataSource = dtF;
// Automatically resizes the columns to fit the data grid view
notesDataGridView.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
notesDataGridView.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.None;

}
private void updateButton_Click(object sender, EventArgs e)
{

if (ProjectIDBackend > 0)
{
SqlCommand cmd = new SqlCommand("UPDATE sfProjects SET Project_ID= @Project_ID, First_Name= @First_Name, Last_Name= @Last_Name, Project_Type= @Project_Type, Project_Status= @Project_Status, Last_Updated= @Last_Updated, Last_Updated_By= @Last_Updated_By WHERE Project_ID= @Project_ID", con);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@Project_ID", projectIDTextBox.Text);
cmd.Parameters.AddWithValue("@First_Name", firstNameTextBox.Text);
cmd.Parameters.AddWithValue("@Last_Name", lastNameTextBox.Text);
cmd.Parameters.AddWithValue("@Project_Type", projectTypeListBox.SelectedItem);
cmd.Parameters.AddWithValue("@Project_Status", projectStatusListBox.SelectedItem);
cmd.Parameters.AddWithValue("@Last_Updated", DateTime.Now);
cmd.Parameters.AddWithValue("@Last_Updated_By", System.Environment.MachineName);
cmd.Parameters.AddWithValue("@Project_ID_Backend", this.ProjectIDBackend);


con.Open();
cmd.ExecuteNonQuery();
con.Close();
MessageBox.Show("Record is successfully updated", "Updated", MessageBoxButtons.OK, MessageBoxIcon.Information);
// now update/REFRESH the database so it shows the new record
GetProjectsDataset();
// reset form controls
ResetFormControls();
}
else
{
MessageBox.Show("Please select a record to update", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void deleteButton_Click(object sender, EventArgs e)
{
// Confirm user wants to delete by asking "Are you sure?"
var confirmDelete = MessageBox.Show("Are you sure you want to delete this record? This action cannot be undone.",
"Confirm Delete",
MessageBoxButtons.YesNo, 
MessageBoxIcon.Warning);
if (confirmDelete == DialogResult.Yes)
{
if (ProjectIDBackend > 0)
{
SqlCommand cmd = new SqlCommand("DELETE FROM sfProjects WHERE Project_ID_Backend= @Project_ID_Backend", con);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@Project_ID_Backend", this.ProjectIDBackend); // Project_ID equals "this" ProjectID that I clicked on
con.Open();
cmd.ExecuteNonQuery();
con.Close();
MessageBox.Show("Record is successfully deleted from the system", "Updated", MessageBoxButtons.OK, MessageBoxIcon.Information);
// now update/REFRESH the database so it shows the new record
GetProjectsDataset();
// reset form controls
ResetFormControls();
}
else
{
MessageBox.Show("Please select a record to delete", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
}

}
private void notesButton_Click(object sender, EventArgs e)
{
// Addes note into the database
if (IsNotesValid())
{
SqlCommand cmd = new SqlCommand("INSERT INTO sfNotes VALUES (@Project_ID, @Notes, @Note_Created, @Note_Created_By)", con);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@Project_ID", notesProjectIDTextBox.Text); // maps @Project_ID to the notesProjectID textbox
cmd.Parameters.AddWithValue("@Notes", notesTextBox.Text);
cmd.Parameters.AddWithValue("@Note_Created", DateTime.Now);
cmd.Parameters.AddWithValue("@Note_Created_By", System.Environment.MachineName);

con.Open();
cmd.ExecuteNonQuery();
con.Close();
MessageBox.Show("New note has been successfully added to the database for Project ID " + this.ProjectIDBackend, "Saved", MessageBoxButtons.OK, MessageBoxIcon.Information);
// now update/REFRESH the database so it shows the new record
GetProjectsDataset();
GetNotesDataset();
// reset form controls
ResetFormControls();
}
}
private void notesDataGridView_CellClick(object sender, DataGridViewCellEventArgs e)
{
// WHen you click on a row in the data grid, the textboxes will automatically populate with that rows values

NoteID = Convert.ToInt32(notesDataGridView.SelectedRows[0].Cells[0].Value);
notesProjectIDTextBox.Text = notesDataGridView.SelectedRows[0].Cells[1].Value.ToString();
notesTextBox.Text = notesDataGridView.SelectedRows[0].Cells[2].Value.ToString();


}
private void removeNote_Click(object sender, EventArgs e)
{
// Confirm user wants to delete by asking "Are you sure?"
var confirmDelete = MessageBox.Show("Are you sure you want to delete this note? This action cannot be undone.",
"Confirm Delete",
MessageBoxButtons.YesNo,
MessageBoxIcon.Warning);
if (confirmDelete == DialogResult.Yes)
{
if (NoteID > 0)
{
SqlCommand cmd = new SqlCommand("DELETE FROM sfNotes WHERE Note_ID= @Note_ID", con);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@Note_ID", this.NoteID); // @Note_ID equals "this" NoteID that I clicked on
con.Open();
cmd.ExecuteNonQuery();
con.Close();
MessageBox.Show("Note is successfully deleted from the system", "Updated", MessageBoxButtons.OK, MessageBoxIcon.Information);
// now update/REFRESH the database so it shows the new record
GetProjectsDataset();
GetNotesDataset();
// reset form controls
ResetFormControls();
}
else
{
MessageBox.Show("Please select a note to delete", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
}
}
private void searchProjectsButton_Click(object sender, EventArgs e)
{
SqlCommand cmd = new SqlCommand("SELECT * FROM sfProjects WHERE Project_ID= @Project_ID", con);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@Project_ID", searchProjectsTextBox.Text); // maps @Project_ID to the search projects textbox

con.Open();
cmd.ExecuteNonQuery();
con.Close();

DataTable dtS = new DataTable();
con.Open();
SqlDataReader sdr = cmd.ExecuteReader();
dtS.Load(sdr);
con.Close();
dataGridView.DataSource = dtS;
// Automatically resizes the columns to fit the data grid view
dataGridView.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
dataGridView.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.None;
// reset form controls
ResetFormControls();
}
private void searchNotesButton_Click(object sender, EventArgs e)
{
SqlCommand cmd = new SqlCommand("SELECT * FROM sfNotes WHERE Project_ID= @Project_ID", con);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@Project_ID", searchNotesTextBox.Text); // maps @Project_ID to the search projects textbox
con.Open();
cmd.ExecuteNonQuery();
con.Close();

DataTable dtS = new DataTable();
con.Open();
SqlDataReader sdr = cmd.ExecuteReader();
dtS.Load(sdr);
con.Close();
notesDataGridView.DataSource = dtS;
// Automatically resizes the columns to fit the data grid view
notesDataGridView.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
notesDataGridView.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.None;
// reset form controls
ResetFormControls();
}
}
}

这是发生错误的部分：

// Filter the notes grid to only show notes for the row the user clicks 
SqlCommand cmd = new SqlCommand("SELECT * FROM sfNotes WHERE Project_ID_Backend= @Project_ID_Backend", con);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@Project_ID_Backend", this.ProjectIDBackend); 
con.Open();
cmd.ExecuteNonQuery();
con.Close();

解决方案：问题是我没有"刷新"；保存后在SSMS中的数据库。

Answer 1

检查异常文档：https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlexception?view=dotnet-plat-ext-3.1

SQL Server返回警告或错误时引发的异常。

消息：

获取描述当前异常的消息

因此，异常表示查询是在数据库上执行的，该消息是数据库返回的消息。这让我相信了两件事中的一件。

1. 错误的连接字符串

您对一个与应用程序连接的数据库不同的数据库进行了更改。您尚未更新的开发或测试实例

2. 您提交了一个挂起的事务

您在尚未提交的事务中运行了脚本。

打开管理工作室并连接到应用程序所连接的数据库。尝试执行查询。

还要检查您正在构建/运行应用程序的配置文件。也许它发布了，你已经转换了连接字符串