如何获取 SQL 代码以在用 C# 编写时发送错误消息
本文关键字:消息 错误 何获取 获取 代码 SQL | 更新日期: 2023-09-27 18:34:27
我有代码,每个用户上传一个 excel 电子表格,然后上传后,我让它基于另一个 SQL 表更新 SQL 中的一个列。我想验证 Excel 电子表格中的数据是否与 SQL 表中要从中更新的选项之一匹配。最简单的方法是什么?即,如果返回 NULL 值,我可以添加一个 SQL 语句以在网站上给我一条错误消息,还是我必须用 C# 编写一个复杂的 IF 语句?以下是我到目前为止所拥有的(请参阅 SQL 语句):
try
{
FileUpload.SaveAs(path);
if (TextBox.Text != "")
sheetname = TextBox.Text;
System.Data.OleDb.OleDbConnection MyConnection;
System.Data.DataSet DtSet;
System.Data.OleDb.OleDbDataAdapter MyCommand; //check sheet name
MyConnection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;");
MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [" + sheetname + "$]", MyConnection);
//MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [July Noms$]", MyConnection);
MyCommand.TableMappings.Add("Table", "TestTable");
DtSet = new System.Data.DataSet();
MyCommand.Fill(DtSet);
SqlConnection curConnection = new SqlConnection(@"Data Source=1tc-Techcomm2;Initial Catalog=EventRegistration;Integrated Security=True");
curConnection.Open();
SqlCommand curCommand;
SqlParameter param;
string str;
for (int i = 0; i < DtSet.Tables[0].Rows.Count; i++)
{
if (DtSet.Tables[0].Rows[i][1] == DBNull.Value)
continue;
curCommand = curConnection.CreateCommand();
curCommand.CommandText = @"Insert into TestSP (SessionHead_EventId, SessionHead_Title, SessionHead_TypeId, SessionHead_Description, SessionHead_Status, SessionHead_Presenter, SessionHead_Champion, SessionHead_AnnounceDate, SessionHead_Objectives, SessionHead_LaunchDate, SessionHead_Notes, SessionHead_Schedule, SessionHead_SpecialNeeds, SessionHead_Equipment, SessionHead_CDA) Values (@a,@b,@c,@d,@e,@f,@g,@h,@i,@j,@k,@l,@m,@n,@o)";
for (int j = 0; j < 15; j++)
{
param = new SqlParameter();
str = "@";
str += (char)('a' + j);
param.ParameterName = str;
param.SqlDbType = SqlDbType.VarChar;
param.Value = DtSet.Tables[0].Rows[i][j];
curCommand.Parameters.Add(param);
}
Label1.Text = "THE EXCEL DATE HAS SUCCESSFULLY BEEN SENT TO THE DATABASE";
curCommand.ExecuteNonQuery();
curCommand.CommandText = "UPDATE TestSP SET SessionHead_TypeId = ( SELECT SessionType.SessionType_Id FROM SessionType WHERE SessionType.SessionType_Title = TestSP.SessionHead_TypeId) WHERE EXISTS ( SELECT SessionType.SessionType_Id FROM SessionType WHERE SessionType.SessionType_Title = TestSP.SessionHead_TypeId)";
//****Can I add a statement here stating that if a NULL value is returned, to give me an error message on the website?****
curCommand.ExecuteNonQuery();
}
MyConnection.Close();
curConnection.Close();
}
catch (Exception ex)
{
//Response.Write(ex.ToString());
Label1.Text = ex.Message;
Label2.Text = ex.Message;
}
}
任何帮助将不胜感激!!
我建议您使用存储过程返回sql错误消息。
如果
查询为 false,则 System.Data.SqlClient.SqlException
的捕获将为您提供错误消息。此外,ExecuteNonQuery
会为您提供受影响行的整数。因此,0
如果没有受影响的行,-1
是否有错误(例如回滚)。