C#数据库查询错误消息
本文关键字：消息 错误 查询 数据库 | 更新日期: 2023-09-27 18:29:21
我目前正在为大学里的第二堂编程课做期末考试，在按数据库搜索特定员工姓名时遇到了问题，如果用户输入的用户名不在数据库中，程序会崩溃，并给出错误，而不是显示我创建的错误消息。
搜索方法：
public void searchNameDbMethod()
{
OleDbConnection Myconnection = null;
OleDbDataReader dbReader = null;
string selectionText = "";
bool errorFlag = true;
do
{
string searchName = "";
Console.Write("Search for Employee Name: ");
searchName = Console.ReadLine();
searchName = searchName.ToUpper();
Console.WriteLine("'n");
Myconnection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0; Data Source= payrolldb.accdb");
Myconnection.Open();
selectionText = "SELECT * FROM Table1;";
OleDbCommand cmd = Myconnection.CreateCommand();
cmd.CommandText = selectionText;
dbReader = cmd.ExecuteReader();
if (dbReader.HasRows)
{
while (dbReader.Read())
{
// since the query produces one column, the GetValue(0)
//must be set
// with multiple column queries, you have to know which
//column holds
// the value that you are looking for
//field 0 = ID
dbName = dbReader.GetValue(1).ToString(); //1 is field 1 of the db
if (dbName == searchName)
{
dbName = dbReader.GetValue(1).ToString(); //1 is field 1 of the db
dbID = dbReader.GetValue(2).ToString(); //2 is field 2 of the db
dbHourlyWage = dbReader.GetValue(3).ToString();
dbDependents = dbReader.GetValue(4).ToString();
Console.Clear();
Console.ResetColor();
Console.ForegroundColor = ConsoleColor.Green;
Console.WriteLine("*******************************************************");
Console.WriteLine("**************** {0} *****************", date);
Console.WriteLine("*******************************************************");
Console.WriteLine("Employee Name: ", dbName);
Console.WriteLine("Employee ID: {0}", dbID);
Console.WriteLine("Hourly Wage: {0}", dbHourlyWage);
Console.WriteLine("Number of Dependents: {0}", dbDependents);
Console.WriteLine("*******************************************************");
Console.ResetColor();
Console.Write("'n'n");
errorFlag = false;
}//closes if
}// end of while
}// end of if
if (errorFlag == true)
{
Console.ResetColor();
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("Name is not in our database!");//shows the data accumulated from above
Console.ResetColor();
}//closes if
dbReader.Close();
Myconnection.Close();
}//close do
while (errorFlag == true);
}//closes searchNameDbMethod
错误：http://puu.sh/4cPWU.png
请记住，我正在使用Microsoft Access作为该项目的数据库（不确定这是否重要）。
我该如何做到这一点，以便如果在DB中找不到名称，它将显示我创建的错误消息，而不是链接的图像中的错误（崩溃程序）
我认为您应该在SQL Where子句中搜索您的员工。
selectionText = "SELECT * FROM Table1 WHERE <EmployeeName> like @Name;";
并在SQL查询中添加一个参数。
将军，我会这样写我的代码：
void searchNameDbMethod()
{
OleDbConnection Myconnection = null;
OleDbDataReader dbReader = null;
string selectionText = "";
string searchName = "";
Console.Write("Search for Employee Name: ");
searchName = Console.ReadLine();
searchName = searchName.ToUpper();
Console.WriteLine("'n");
Myconnection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0; Data Source= payrolldb.accdb");
Myconnection.Open();
selectionText = "SELECT * FROM Table1 WHERE Employee like @Name;";
OleDbCommand cmd = Myconnection.CreateCommand();
cmd.CommandText = selectionText;
cmd.Parameters.Add(new OleDbParameter() { ParameterName = "@Name", Value = searchName, DbType = System.Data.DbType.String });
try
{
dbReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (dbReader.Read())
{
// since the query produces one column, the GetValue(0)
//must be set
// with multiple column queries, you have to know which
//column holds
// the value that you are looking for
//field 0 = ID
string dbName = dbReader.GetValue(1).ToString(); //1 is field 1 of the db
if (dbName == searchName)
{
dbName = dbReader.GetValue(1).ToString(); //1 is field 1 of the db
string dbID = dbReader.GetValue(2).ToString(); //2 is field 2 of the db
string dbHourlyWage = dbReader.GetValue(3).ToString();
string dbDependents = dbReader.GetValue(4).ToString();
Console.Clear();
Console.ResetColor();
Console.ForegroundColor = ConsoleColor.Green;
Console.WriteLine("*******************************************************");
Console.WriteLine("**************** {0} *****************", date);
Console.WriteLine("*******************************************************");
Console.WriteLine("Employee Name: ", dbName);
Console.WriteLine("Employee ID: {0}", dbID);
Console.WriteLine("Hourly Wage: {0}", dbHourlyWage);
Console.WriteLine("Number of Dependents: {0}", dbDependents);
Console.WriteLine("*******************************************************");
Console.ResetColor();
Console.Write("'n'n");
}
}
}
catch
{
if (dbReader != null)
{
dbReader.Close();
}
}
finally
{
if (Myconnection != null)
{
Myconnection.Close();
}
}
}
只是一个例子，以改进您的解决方案。
我能够根据@BendEg 提供的一些代码来计算它
以下是我所做的：
public void searchNameDbMethod()
{
OleDbConnection Myconnection = null;
OleDbDataReader dbReader = null;
string selectionText = "";
bool errorFlag = true;
do
{
string searchName = "";
Console.Write("Search for Employee Name: ");
searchName = Console.ReadLine();
searchName = searchName.ToUpper();
Console.WriteLine("'n");
Myconnection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0; Data Source= payrolldb.accdb");
Myconnection.Open();
selectionText = "SELECT * FROM Table1 WHERE employee_name='" + searchName + "'";
OleDbCommand cmd = Myconnection.CreateCommand();
cmd.CommandText = selectionText;
dbReader = cmd.ExecuteReader();
if (dbReader.HasRows)
{
while (dbReader.Read())
{
// since the query produces one column, the GetValue(0)
//must be set
// with multiple column queries, you have to know which
//column holds
// the value that you are looking for
//field 0 = ID
dbName = dbReader.GetValue(1).ToString(); //1 is field 1 of the db
if (dbName == searchName)
{
dbName = dbReader.GetValue(1).ToString(); //1 is field 1 of the db
dbID = dbReader.GetValue(2).ToString(); //2 is field 2 of the db
dbHourlyWage = dbReader.GetValue(3).ToString();
dbDependents = dbReader.GetValue(4).ToString();
Console.Clear();
Console.ResetColor();
Console.ForegroundColor = ConsoleColor.Green;
Console.WriteLine("*******************************************************");
Console.WriteLine("**************** {0} *****************", date);
Console.WriteLine("*******************************************************");
Console.WriteLine("Employee Name: ", dbName);
Console.WriteLine("Employee ID: {0}", dbID);
Console.WriteLine("Hourly Wage: {0}", dbHourlyWage);
Console.WriteLine("Number of Dependents: {0}", dbDependents);
Console.WriteLine("*******************************************************");
Console.ResetColor();
Console.Write("'n'n");
errorFlag = false;
}//closes if
}// end of while
}// end of if
else if (!dbReader.HasRows)
{
Console.ResetColor();
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("Name is not in our database!");//shows the data accumulated from above
Console.ResetColor();
}//closes if
dbReader.Close();
Myconnection.Close();
}//close do
while (errorFlag == true);
}//closes searchNameDbMethod
正如你所看到的，我按照他的建议更改了我的查询（但没有额外复杂的东西），并将我的if（errorFlag==true）语句更改为else-if（！dbReader.HasRows），它似乎很有魅力！