使用OleDB将Devices表ID插入Log表的登录功能异常排查

问题说明 #

点击登录按钮时，需根据当前设备名称（System.Environment.MachineName）检查DEVICES表：

• 若设备名称存在，获取对应的DEVICE_ItemNumber（自增ID），插入LOG表的LOG_DeviceNumber字段
• 当前代码可运行，但插入的LOG_DeviceNumber始终为1，无法正确获取设备ID

表结构 #

DEVICES表 #

DEVICE_ItemNumber (AutoNumber; 与LOG_DeviceNumber关联)
DEVICE_PCNumber (Short Text)
DEVICE_Room (Short Text)
DEVICE_Name (Short Text)

LOG表 #

ID (AutoNumber)
LOG_UserID (Short Text)
LOG_Date (Date/Time)
LOG_Time (Date/Time)
LOG_DeviceNumber (Short Text)
LOG_Type (Short Text)

USERS表 #

USER_ID (Short Text; 与LOG_UserID关联)
USER_Password (Short Text)
USER_GivenName (Short Text)
USER_Surname (Short Text)
USER_Course (Short Text)
USER_YearLevel (Short Text)
USER_Type (Short Text)
USER_Position (Short Text)

现有代码的问题分析 #

1. GetDeviceNumber方法错误使用ExecuteNonQuery：
   • ExecuteNonQuery返回SQL语句影响的行数，而非查询结果。查询到设备时返回1，这就是插入值始终为1的根本原因。
2. 参数名与字段不匹配：
   • 查询语句中条件写成DEVICE_Name = @DEVICE_ItemNumber，参数名与字段名错位，应改为DEVICE_Name = @DEVICE_Name。
3. 不必要的deviceNumber++操作：
   • 获取到正确的DEVICE_ItemNumber后额外执行加1，导致ID被错误修改。
4. LOG插入语句参数格式错误：
   • VALUES中的参数被错误包裹在方括号中（如[@LOG_UserID]），OleDB参数无需方括号。
5. 日期时间字段处理不当：
   • 将DateTime.Now转为字符串插入，易导致格式不兼容，应直接传入DateTime类型。
6. 空异常捕获：
   • InsertLoginLog中的异常无任何处理，无法排查插入失败原因。

修复后的代码 #

#region ShowsDashboardForm
public void OpenStudentForm()
{
     StudentForm stuForm = new StudentForm();
     stuForm.FormClosed += (s, args) => this.Close();
     stuForm.Show();
     StudentForm.GetCurrentUser.currentuser.Text = userIdLoginTextBox.Texts;
     this.Hide();
}

public void OpenInstructorForm()
{
     InstructorForm insForm = new InstructorForm();
     insForm.FormClosed += (s, args) => this.Close();
     insForm.Show();
     InstructorForm.GetCurrentUser.currentuser.Text = userIdLoginTextBox.Texts;
     this.Hide();
}

public void OpenSystemAdminForm()
{
     SystemAdminForm sysAdminForm = new SystemAdminForm();
     sysAdminForm.FormClosed += (s, args) => this.Close();
     sysAdminForm.Show();
     SystemAdminForm.GetCurrentUser.currentuser.Text = userIdLoginTextBox.Texts;
     this.Hide();
}
#endregion

#region Login
private void passwordLoginCheckBox_CheckedChanged(object sender, EventArgs e)
{
     passwordLoginTextBox.PasswordChar = !showPasswordLoginCheckBox.Checked;
}

private void btnLogin_Click(object sender, EventArgs e)
{
     string userIDLogin = userIdLoginTextBox.Texts;
     string passwordLogin = passwordLoginTextBox.Texts;
     string deviceName = System.Environment.MachineName;
     int deviceNumber = GetDeviceNumber(deviceName);

     try
     {
         connection.Open();
         cmd = new OleDbCommand("SELECT [USER_ID], [USER_Type] FROM USERS WHERE USER_ID = @USER_ID AND USER_Password = @USER_Password", connection);
         cmd.Parameters.AddWithValue("@USER_ID", userIDLogin);
         cmd.Parameters.AddWithValue("@USER_Password", passwordLogin);

         OleDbDataReader reader = cmd.ExecuteReader();
     
         if (reader.Read())
         {
             string userID = reader["USER_ID"].ToString();
             string userType = reader["USER_Type"].ToString();

             switch (userType)
             {
                 case "Student":
                     OpenStudentForm();
                     break;
                 case "Instructor":
                     MessageBox.Show("Instructor");
                     OpenInstructorForm();
                     break;
                 case "System Admin":
                     MessageBox.Show("System Admin");
                     OpenSystemAdminForm();
                     break;
                 default:
                     MessageBox.Show("Unknown User Type");
                     break;
             }

             if (deviceNumber != -1)
             {
                 InsertLoginLog(userID, deviceNumber);
                 MessageBox.Show("Login successful!");
             }
             else
             {
                 MessageBox.Show("This device is not registered.");
             }
         }
         else
         {
             MessageBox.Show("Invalid User ID or Password");
         }
     }
     catch (Exception ex)
     {
         MessageBox.Show("Error: " + ex.Message);
     }
     finally
     {
         if (connection.State == ConnectionState.Open)
             connection.Close();
     }
}

private int GetDeviceNumber(string deviceName)
{
     using (OleDbConnection connection = new OleDbConnection(connectionString))
     {
         connection.Open();
         string query = "SELECT DEVICE_ItemNumber FROM DEVICES WHERE DEVICE_Name = @DEVICE_Name";

         using (OleDbCommand command = new OleDbCommand(query, connection))
         {
             command.Parameters.AddWithValue("@DEVICE_Name", deviceName);
             object result = command.ExecuteScalar();

             if (result != DBNull.Value && result != null)
             {
                 return Convert.ToInt32(result);
             }
         }
     }
     return -1;
}

public void InsertLoginLog(string userID, int deviceNumber)
{
     try
     {
         using (OleDbConnection insertConnection = new OleDbConnection(connectionString))
         {
             string status = "In";
             insertConnection.Open();
             cmd = new OleDbCommand("INSERT INTO LOG (LOG_UserID, LOG_Date, LOG_Time, LOG_DeviceNumber, LOG_Type) VALUES (@LOG_UserID, @LOG_Date, @LOG_Time, @LOG_DeviceNumber, @LOG_Type)", insertConnection);
             cmd.Parameters.AddWithValue("@LOG_UserID", userID);
             cmd.Parameters.AddWithValue("@LOG_Date", DateTime.Now.Date);
             cmd.Parameters.AddWithValue("@LOG_Time", DateTime.Now.TimeOfDay);
             cmd.Parameters.AddWithValue("@LOG_DeviceNumber", deviceNumber.ToString());
             cmd.Parameters.AddWithValue("@LOG_Type", status);
             cmd.ExecuteNonQuery();
         }
     }
     catch (Exception ex)
     {
         MessageBox.Show("Insert log error: " + ex.Message);
     }
}
#endregion

关键修复点总结 #

• 用ExecuteScalar替代ExecuteNonQuery获取设备ID的查询结果
• 修正查询语句的参数名与字段匹配
• 移除多余的deviceNumber++操作
• 修正LOG插入语句的参数格式
• 直接传入DateTime类型处理日期时间字段
• 添加异常信息提示，便于排查问题

内容的提问来源于stack exchange，提问作者Aguni