c# WPF:使用自动增量向数据库插入数据的问题

Question

我想做一个注册页面，所以我想插入新的帐户到我的数据库。我有数据库在微软SQL Server管理工作室。表Accounts有以下列:

user_id PK, int, not null, username, password

问题是user_id，它被设置为自动递增，所以我想在Oracle中运行这样的SQL查询:

INSERT INTO Accounts (user_id, username, password) 
VALUES (NULL, 'test', 'testpass');

所以我写这段代码:

sqlCmd.CommandText = "INSERT INTO [Accounts](user_id, username, password) VALUES (NULL, @regUser, @regPass)";

但是我得到一个错误:

无法将值NULL插入到表'NoteAppDB.dbo '的列'user_id'中。Accounts':列不允许为空。插入失败。语句已被终止。

我不能在SQL Server Management Studio中允许null，因为user_id是主键。

这是剩下的代码:

xaml:

<StackPanel Margin="30" Name="StackRegister" Visibility="Hidden">
<Label Content="Register"  Foreground="White" FontSize="50" HorizontalAlignment="Center" />
<Separator></Separator>
<Label Content="Username" Foreground="White" />
<TextBox Name="txtRegUsername" Background="#545d6a" Foreground="White" FontSize="35"/>
<Label Content="Password" Foreground="White" />
<PasswordBox Name="txtRegPassword" Background="#545d6a" Foreground="White" FontSize="35"/>
<Label Content="Repeat password" Foreground="White" />
<PasswordBox Name="txtRegPass" Background="#545d6a" Foreground="White" FontSize="35"/>
<Button Name="btnReg" Click="btnReg_Click" Content="Register" Margin="70,15,70,10" Background="#545d6a" Foreground="White" FontSize="35" />
</StackPanel>

c#:

SqlConnection sqlCon = new SqlConnection(@"Data Source=DESKTOP-32HBHCQ; Initial Catalog=NoteAppDB; Integrated Security=True;");
try
{
if (sqlCon.State == ConnectionState.Closed)
sqlCon.Open();
String query = "SELECT COUNT(1) FROM Accounts WHERE username = @user";
SqlCommand sqlCmd = new SqlCommand(query, sqlCon);
sqlCmd.CommandType = CommandType.Text;
sqlCmd.Parameters.AddWithValue("@user", txtRegUsername.Text);
int count = Convert.ToInt32(sqlCmd.ExecuteScalar());
if(txtRegPassword.Password != txtRegPass.Password)
{
MessageBox.Show("Username exists in database or passwords are different!", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
txtRegUsername.Text = "";
txtRegPassword.Password = "";
txtRegPass.Password = "";
}
else if (count == 0)
{
sqlCmd.CommandText = "INSERT INTO [Accounts](user_id, username, password)values(NULL,@regUser,@regPass)";
sqlCmd.Parameters.AddWithValue("@regUser", txtRegUsername.Text);
sqlCmd.Parameters.AddWithValue("@regPass", txtRegPassword.Password);
sqlCmd.Connection = sqlCon;
int a = sqlCmd.ExecuteNonQuery();
if (a == 1) 
MessageBox.Show("New user created!", "Success", MessageBoxButton.OK, MessageBoxImage.Information);
StackRegister.Visibility = Visibility.Hidden;
StackLogin.Visibility = Visibility.Visible;
}
else 
{
MessageBox.Show("Username exists in database or passwords are different!", "Błąd", MessageBoxButton.OK, MessageBoxImage.Error);
txtRegUsername.Text = "";
txtRegPassword.Password = "";
txtRegPass.Password = "";
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
sqlCon.Close();
}

Answer 1

Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table.(https://www.w3schools.com/sql/sql_autoincrement.asp)。这意味着在创建新的user Accounts条目时不必提供user_id值(无论是NULL还是INTEGER)，因为表本身会提供一个。这就是为什么你得到一个SQL错误。

Answer 2

因为它是一个" auto-increment ";列(在SQL Server中是带有IDENTITY属性的列)，不能把它包含在你的INSERT语句中。

试试这个:

INSERT INTO Accounts (username, password) 
VALUES ('test', 'testpass');