System.Data.SqlServerCe.dll 中出现类型为“System.Data.SqlServerCe.SqlCeException"的异常，但未在用户代码中处理 [英] An exception of type 'System.Data.SqlServerCe.SqlCeException' occurred in System.Data.SqlServerCe.dll but was not handled in user code

问题描述

当我尝试执行此代码时会导致错误.

This code causes an error when I try to execute it.

我的要求是获取最新插入的增量 id

My requirement get latest inserted incrementation id

_connection.Open();
cmd.Connection = _connection;

cmd.CommandText = "Insert into Finalresult(Section_name, userId, examid) Select Section_name, User_id, Exam_id from result" +
                  "WHERE (User_id = '" + userid + "' Exam_id='" + examis + "' And Section_name='" + section + "')SELECT SCOPE_IDENTITY()";

Int32 newId = (Int32)cmd.ExecuteScalar();

错误发生在行

Int32 newId = (Int32)cmd.ExecuteScalar(); 

错误是

System.Data.SqlServerCe.dll 中出现类型为System.Data.SqlServerCe.SqlCeException"的异常，但未在用户代码中处理

An exception of type 'System.Data.SqlServerCe.SqlCeException' occurred in System.Data.SqlServerCe.dll but was not handled in user code

推荐答案

您需要在此处进行一些更改，例如添加错误处理.要了解异常背后的原因，您需要检查异常的Errors 属性:

You need a few changes here, like adding error handling. To get the reason behind the exception, you need to check the Errors property of the exception:

try
{
    //Your code here
}
catch (SqlCeException e)
{
    foreach (SqlCeError error in e.Errors)
    {
        //Error handling, etc.
        MessageBox.Show(error.Message);
    }
}

这样做，它会准确地告诉您错误是什么.

Doing that, it will tell you exactly what the error is.

我认为您的 User_id 和 Exam_id '参数' 在 SQL 语句中被视为字符串，因为您用单引号将它括起来.猜测，这将是您的问题以及 WHERE 子句中缺少逻辑运算符.

I think your User_id and Exam_id 'parameters' are being treated as strings in the SQL statement, as you are surrounding it with single quotes. At a guess, this will be your problem along with missing logic operators in the WHERE clause.

但是不要以这种方式进行参数化！当您以这种方式连接查询时，您会容易受到 SQL 注入攻击.MSDN 上有很多关于如何做到这一点的文章和信息，或者看看 Jeff Atwood 的这个 - http://blog.codinghorror.com/give-me-parameterized-sql-or-give-me-death/

However don't do parameterization this way! You leave yourself open to SQL Injection attacks when you concatenate your query this way. There's lots of articles and information on MSDN on how to do this, or take a look at this from Jeff Atwood - http://blog.codinghorror.com/give-me-parameterized-sql-or-give-me-death/

更新

好的，进一步细分，根据 ma​​rc_s 的评论，您不能在 SQL CE 中使用 SCOPE_IDENTITY().所以你正在考虑这样做:

Ok, to break it down further, based on the comment by marc_s, you can't use SCOPE_IDENTITY() in SQL CE. So you're looking at doing this:

参数化插入:

    var sqlString = "Insert into Finalresult(Section_name, userId, examid) Select Section_name, User_id, Exam_id from result " +
                      "WHERE (User_id = @userId AND Exam_id = @examId AND Section_name = @sectionName"

    cmd.CommandText = sqlString;
    cmd.Parameters.Add("@userId", userid); 
    cmd.Parameters.Add("@examId", examId); 
    cmd.Parameters.Add("@sectionName", section); 

    cmd.ExecuteNonQuery();

然后在相同连接上(当然是不同的命令)，获取插入的id:

And then on the same connection (but different command of course), get the inserted id:

cmd.Connection = _connection;
cmd.CommandText = "SELECT @@identity";
Int32 newId = (Int32)cmd.ExecuteScalar();

我还没有测试或编译过这个，所以只是把它当作一个想法/指导.

I haven't tested or compiled this, so just take it as an idea/guidance.

这篇关于System.Data.SqlServerCe.dll 中出现类型为“System.Data.SqlServerCe.SqlCeException"的异常，但未在用户代码中处理的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！