c# - 我的 SQL 插入语句 C# ASP.NET 有什么问题
问题描述
我正在使用网页来创建用户并将它们添加到表中(就像正在创建其他用户的管理员......)。我的代码不会将数据插入表中,但我可以使用从 ASP 表单收集的相同值手动将数据插入表中。
这将用于公司注册的网站,然后管理员将使用他们的个人资料创建用户(员工)。
这是我的表定义
[Id] INT IDENTITY (1, 1) NOT NULL,
[Username] NVARCHAR (100) NOT NULL,
[Password] NVARCHAR (100) NOT NULL,
[Profile] NVARCHAR (25) NOT NULL,
[ProfileGroup] NVARCHAR (25) NOT NULL,
[CompanyID] INT NOT NULL,
[CreateDate] DATE NOT NULL,
[LastLogin] DATE NOT NULL,
[FirstName] NVARCHAR (100) NOT NULL,
[LastName] NVARCHAR (100) NOT NULL,
[Department] NVARCHAR (100) NULL,
[Phone] NVARCHAR (25) NULL,
[Email] NVARCHAR (100) NULL,
[RecoveryEmail] NVARCHAR (100) NULL,
[Photo] NVARCHAR (100) NULL,
[Salt] NVARCHAR (25) NULL,
[LastPasswordChangedDate] DATE NOT NULL,
这是我的代码。已声明连接字符串。
if (Page.IsValid)
{
string newFName = TextBoxFirstName.Text.Trim();
string newLName = TextBoxLastName.Text.Trim();
string newUsername = TextBoxUsername.Text.Trim();
string newProfile = DropDownListProfile.SelectedValue;
string newUserEmail = TextBoxEmail.Text.Trim();
string newRecoveryEmail = TextBoxRecoveryEmail.Text.Trim();
string newDepartment = TextBoxDepartment.Text.Trim();
string newPhoneNumber = TextBoxPhone.Text.Trim();
//string newPhoto = "";
string newPassword = TextBoxPassword.Text.Trim();
string theSalt = GetRandomString(12);
int theCompanyID = Int32.Parse(LabelCompanyID.Text);
// Hash password: Salt + password
string hashedPassword = HashPassword(theSalt + newPassword);
// First verify if the username has already been taken
string verifyNewuserSql = "SELECT [UserAccount].[Username] FROM [UserAccount] WHERE [UserAccount].[Username] = @uname";
using (var connection = new SqlConnection(sixConnection))
{
connection.Open();
// Command to execut query connection
SqlCommand UserComm = new SqlCommand(verifyNewuserSql, connection);
UserComm.Parameters.AddWithValue("@uname", newUsername);
SqlDataReader UserDr = UserComm.ExecuteReader();
if (UserDr.HasRows)
{
// User exist
UserDr.Close();
connection.Close();
// Message: user exists
PanelResultSection.Visible = true;
PanelResultSection.CssClass = "alert-warning";
LabelMessage.Text = "username already exists. Nothing has been done. If you see this page by mistake, contact you manager.";
return;
}
else
{
// close previous data reader but keep connection opened
UserDr.Close();
//connection.Close();
// Get the Profile group value
string theProfileGroupe = null;
string profileGroupSql = "SELECT [UserProfile].[profileGroup] FROM [UserProfile] WHERE [UserProfile].[profile]= @newProfile";
SqlCommand ReadProfileComm = new SqlCommand(profileGroupSql, connection);
ReadProfileComm.Parameters.AddWithValue("@newProfile", newProfile);
SqlDataReader profileDr = ReadProfileComm.ExecuteReader();
if (profileDr.HasRows)
{
while (profileDr.Read())
{
theProfileGroupe = profileDr.GetString(0);
}
// close previous data reader and do not keep connection opened --> close connection
profileDr.Close();
connection.Close();
// add data to userdb
string varCreateDate = DateTime.Today.ToString("MM/dd/yyyy");
string varLastLogin = varCreateDate;
string varLastPasswordChangedDate = varCreateDate;
string insertNewUserSql = "INSERT INTO [UserAccount] ( ";
insertNewUserSql = (insertNewUserSql + "[Username], [Password], [Profile], [ProfileGroup] [CompanyID], [CreateDate], [LastLogin], [FirstName], [LastName], ");
insertNewUserSql = (insertNewUserSql + "[Department] , [Phone], [Email], [RecoveryEmail], [Photo], [Salt], [LastPasswordChangedDate]");
insertNewUserSql = (insertNewUserSql + "VALUES (");
insertNewUserSql = (insertNewUserSql + "@Username, @Password, @Profile, @ProfileGroup, @CompanyID, @CreateDate, @LastLogin, @FirstName, @LastName, ");
insertNewUserSql = (insertNewUserSql + "@Department, @Phone, @Email, @RecoveryEmail, @Photo, @Salt, @LastPasswordChangedDate)");
// Passing parameters
SqlCommand insertNewUser = new SqlCommand(insertNewUserSql, connection);
insertNewUser.Parameters.AddWithValue("@Username", newUsername);
insertNewUser.Parameters.AddWithValue("@Password", hashedPassword);
insertNewUser.Parameters.AddWithValue("@Profile", newProfile);
insertNewUser.Parameters.AddWithValue("@ProfileGroup", theProfileGroupe);
insertNewUser.Parameters.AddWithValue("@CompanyID", theCompanyID);
insertNewUser.Parameters.AddWithValue("@CreateDate", varCreateDate);
insertNewUser.Parameters.AddWithValue("@LastLogin", varLastLogin);
insertNewUser.Parameters.AddWithValue("@FirstName", newFName);
insertNewUser.Parameters.AddWithValue("@LastName", newLName);
insertNewUser.Parameters.AddWithValue("@Department", newDepartment);
insertNewUser.Parameters.AddWithValue("@Phone", newPhoneNumber);
insertNewUser.Parameters.AddWithValue("@Email", newUserEmail);
insertNewUser.Parameters.AddWithValue("@RecoveryEmail", newRecoveryEmail);
insertNewUser.Parameters.AddWithValue("@Photo", DBNull.Value);
insertNewUser.Parameters.AddWithValue("@Salt", theSalt);
insertNewUser.Parameters.AddWithValue("@LastPasswordChangedDate", varLastPasswordChangedDate);
try
{
// Perform data insertion
connection.Open();
insertNewUser.ExecuteNonQuery();
//var recordsAffected = insertNewUser.ExecuteNonQuery();
}
catch (Exception ex)
{
LabelMessage.Text = "Error at the catch: " + ex.ToString();
PanelResultSection.Visible = true;
PanelResultSection.CssClass = "alert-warning";
return;
}
finally
{
// Close connection
connection.Close();
// Send email and display link to login
PanelResultSection.Visible = true;
PanelResultSection.CssClass = "alert-success";
LabelMessage.Text = "Success! " + newFName + " " + newLName + " has been added to " + theProfileGroupe + " group.";
}
}
else
{
// user does not have profile group
// Message: user exists
PanelResultSection.Visible = true;
PanelResultSection.CssClass = "alert-warning";
LabelMessage.Text = "An error has occurred. Nothing has been done. If you see this page by mistake, contact you manager.";
return;
}
}
}
}
代码运行没有错误。它显示成功,但即使刷新后数据也不会显示在表中。在过去的三天里,我一直在努力解决这个问题,但没有任何结果。有人可以帮忙吗?
解决方案
您有一个错误处理错误,它会阻止您在查询失败后查看错误详细信息。
该finally
块将在之后运行catch
,因此您不应该在 finally 块中使用“成功”逻辑。
至少,更改为:
try
{
// Perform data insertion
connection.Open();
insertNewUser.ExecuteNonQuery();
}
catch (Exception ex)
{
LabelMessage.Text = "Error at the catch: " + ex.ToString();
PanelResultSection.Visible = true;
PanelResultSection.CssClass = "alert-warning";
return;
}
finally
{
// Close connection
connection.Close();
}
// Send email and display link to login
PanelResultSection.Visible = true;
PanelResultSection.CssClass = "alert-success";
LabelMessage.Text = "Success! " + newFName + " " + newLName + " has been added to " + theProfileGroupe + " group.";
推荐阅读
- antivirus - Sophos for Ubuntu 阻止 Flatpak 应用程序安装
- reactjs - 防止`useSelector`重新渲染组件?
- google-apps-script - 在 Google Apps 脚本中使用 events.patch
- kubernetes - 什么情况下建议多容器使用一个pod
- javascript - 我将属性应用于节点的所有子元素的方法有什么问题?
- bash - 用于在 sftp 上上传文件的 kubectl 的 Shell 脚本
- programming-languages - 影响编程风格的因素
- angular - Why switchMap does not cancel repeated request?
- android - 无法在使用 R 类(Kotlin)的 android studio 中通过 id 找到 EditText 资源
- javascript - React 中的三个.js - 缺少三个.ShaderTerrain