查询表达式'@ID = @@ IDENTITY'II中的语法错误(缺少运算符) [英] Syntax error (missing operator) in query expression '@ID = @@IDENTITY' II

问题描述

Access 2003

Access 2003

VS 2010 C#

VS 2010 C#

正如主题标题所说，我遇到了问题.这与我先前问过的问题有关，此处.我希望该线程的mod可以正常运行，但我不确定.

As subject title says I am having problems with. This is related to my previous question I asked, Here. I hope the mod's will be OK with this thread but I am not sure.

在将@@Identity与C#和MS-Access一起使用时，Martin Parkin建议不要关闭插入"和选择"之间的连接.我以为我可以使它工作，直到发现情况并非如此.老实说，我不知道如何解决这个问题.因此，如果有人可以帮助我，我将不胜感激.

Martin Parkin advised not to close the connection between Insert and Select when using @@Identity with C# and MS-Access. I thought I got it working until I discovered that was not the case. To be honest I don't know how to solve this issue. So if anyone can help me I would appreciate it.

这是我的btnLogin方法.

This is my btnLogin method..

        cmd.CommandText = "INSERT INTO LoginLogTable (UserName, LoggedInDate, LoggedInTime) VALUES (@UserName, @LoggedInDate, @LoggedInTime)";
        cmd.Parameters.AddWithValue("@UserName", txtUserName.Text);
        cmd.Parameters.AddWithValue("@LoggedInDate", DateTime.Now.ToShortDateString());
        cmd.Parameters.AddWithValue("@LoggedInTime", DateTime.Now.ToString("HH:mm"));
        cmd.Connection = myCon;
        myCon.Open();
        cmd.ExecuteNonQuery();

        cmd.CommandText = "SELECT @ID = @@IDENTITY";
     // cmd.Parameters.AddWithValue("@ID", OleDbType.WChar); << tried this, unsuccessful
        int id = (int)cmd.ExecuteScalar(); // getting the same error? 
        myCon.Close();

这是我的btnLogOut方法...

This is my btnLogOut method...

        OleDbCommand cmd = new OleDbCommand();
        cmd.CommandType = CommandType.Text;

         int id = 0;
        cmd.CommandText = " UPDATE [LoginLogTable] SET [LoggedOutDate] = @LoggedOutDate, [LoggedOutTime] = @LoggedOutTime WHERE [ID] = @ID";
        cmd.Parameters.AddWithValue("@ID", id);
        cmd.Parameters.AddWithValue("@LoggedOutDate", DateTime.Now.ToShortDateString());
        cmd.Parameters.AddWithValue("@LoggedOutTime", DateTime.Now.ToString("HH:mm"));
        cmd.Connection = myCon;
        myCon.Open();
        cmd.ExecuteNonQuery();
        Close();

或

在btnLogin方法中

cmd.CommandText = "SELECT @ID = @@IDENTITY";

并在其后隐藏cmd.ExecuteNonQuery();.然后，日期和时间将被记录在数据库中，但日期和时间将不会被保存在数据库中以进行注销.

and hide the cmd.ExecuteNonQuery(); after it. Then date and time will get logged in the database but the date and time will not get saved in the database, for logging out.

我不确定问题是否出在btnLogin方法或btnLogOut方法，或两者​​都有.

I am not sure if the problem is with btnLogin method or btnLogOut method, or both.

工作解决方案

我原来是

 cmd.CommandText = " UPDATE [LoginLogTable] SET [LoggedOutDate] = @LoggedOutDate,   
 [LoggedOutTime] = @LoggedOutTime WHERE [ID] = @ID";
 cmd.Parameters.AddWithValue("@ID", id);
 cmd.Parameters.AddWithValue("@LoggedOutDate", DateTime.Now.ToShortDateString());
 cmd.Parameters.AddWithValue("@LoggedOutTime", DateTime.Now.ToString("HH:mm"));

然后我做到了

 cmd.CommandText = " UPDATE [LoginLogTable] SET [UserName] = @UserName, [LoggedOutDate] = 
 @LoggedOutDate, [LoggedOutTime] = @LoggedOutTime WHERE ID = @ID";
 cmd.Parameters.AddWithValue("@UserName", txtUserName.Text);
 cmd.Parameters.AddWithValue("@LoggedOutDate", DateTime.Now.ToShortDateString());
 cmd.Parameters.AddWithValue("@LoggedOutTime", DateTime.Now.ToString("HH:mm"));
 cmd.Parameters.AddWithValue("@ID", id);

感谢D Stanley和Gord Thompson.

Thanks to D Stanley and Gord Thompson.

推荐答案

@ID变量不会像您认为的那样保留在数据库中.当连接关闭时(可能更快)，它将超出范围.我建议您将新身份存储在应用程序中:

The @ID variable does not persist in the database the way you seem to think it does. It will go out of scope when the connection is closed (possibly sooner). I would advise that you store the new identity within your application instead:

假设这些是表单上的方法的按钮处理程序，则可以将ID存储为表单的属性:

Assuming these are button handlers that are methods on the form, you could store the ID as a property of the form:

    // somewhere in the form definition:
    private int ID {get; set;}
    ...

    cmd.CommandText = "SELECT @@IDENTITY";
    int id = (int)cmd.ExecuteScalar();
    this.ID = id;

然后在您的注销方法中使用ID:

Then use the ID in your Logout method:

    // get the id from the form
    int id = this.ID;

    cmd.CommandText = " UPDATE [LoginLogTable] SET [LoggedOutDate] = @LoggedOutDate, [LoggedOutTime] = @LoggedOutTime WHERE [ID] = @ID";
    cmd.Parameters.AddWithValue("@ID", id);
    cmd.Parameters.AddWithValue("@LoggedOutDate", DateTime.Now.ToShortDateString());
    cmd.Parameters.AddWithValue("@LoggedOutTime", DateTime.Now.ToString("HH:mm"));

这篇关于查询表达式'@ID = @@ IDENTITY'II中的语法错误(缺少运算符)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！