无法将日期插入到sql Server的日期时间列中。在第一个 executeNonQuery() 语句后调试时,调试器跳转到 catch 块并在 catch 块中的 sqlError 参数中显示以下消息:"语法错误接近 12"
public ActionResult AddStudentData(StudentDetails sd)
{
SqlConnection db = new SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\StudentDB.mdf;Integrated Security=True;User Instance=True");
SqlTransaction transaction;
db.Open();
transaction = db.BeginTransaction();
SqlCommand cmd = db.CreateCommand();
try
{
**cmd.CommandText = "insert into StudentDetails(StudentName, DOB, Description, Gender) values ('" + sd.StudentName + "'," +sd.DOB + ",'" + sd.Description + "','" + sd.Gender + "')";
cmd.Transaction = transaction;
cmd.ExecuteNonQuery();**
cmd.CommandText = "select max(StudentID) from StudentDetails";
int id = (int)cmd.ExecuteScalar();
cmd.CommandText = "insert into Qualification (StudentID, Qualification, POY) values ("+id+",'"+sd.Qualification+"',"+sd.POY+")";
cmd.Transaction = transaction;
cmd.ExecuteNonQuery();
transaction.Commit();
}
catch (SqlException sqlError)
{
string s = sqlError.Message.ToString();
transaction.Rollback();
}
db.Close();
return View("SuccessAddStudentData");
}
谢谢迪利普库马尔
请注意,日期值 (DOB) 还必须包含单引号。
cmd.CommandText = "insert into StudentDetails(StudentName, DOB, Description, Gender)
values ('" + sd.StudentName + "','" +sd.DOB + "','" +
sd.Description + "','" + sd.Gender + "')";
另外,请记住基于 SQL Server 的日期格式。最后,正如其他帖子提到的,使用绑定参数来防止SQL注入。
在访问 SQL 数据库时,应始终使用参数化查询,否则应用程序可能容易受到 SQL 注入的攻击。在处理用于数据库访问的对象时,还应使用 using
。例如,当使用块离开时,无论是否抛出异常,连接都将关闭。
try
{
using (var db = new SqlConnection("connection string"))
using (var transaction = db.BeginTransaction())
using (var cmd = db.CreateCommand())
{
cmd.Transaction = transaction;
db.Open();
cmd.CommandText = "insert into StudentDetails(StudentName, DOB, Description, Gender) values(@name, @dob, @desc, @gender)";
cmd.Parameters.AddWithValue("@name", sd.StudentName);
cmd.Parameters.AddWithValue("@dob", sd.DOB);
cmd.Parameters.AddWithValue("@desc", sd.Description);
cmd.Parameters.AddWithValue("@gender", sd.Gender);
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
// other queries
transaction.Commit();
}
}
catch (SqlException ex)
{
string s = ex.Message.ToString();
}