C# WPF : problem with inserting data to database with auto increment-CodePudding

I want to do a register page, so I want to insert new account into my database. I have database in Microsoft SQL Server Management Studio. Table Accounts has these columns:

user_id PK, int, not null, username, password

The problem is with user_id, it is set to auto increment, so I wanted to run a SQL query like this in Oracle:

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

So I write this code:

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

But I get an error:

Cannot insert the value NULL into column 'user_id', table 'NoteAppDB.dbo.Accounts': column does not allow nulls. INSERT fails. The statement has been terminated.

I cannot allow nulls in SQL Server Management Studio, because user_id is the primary key.

This is the rest of the code:

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();
}

CodePudding user response：

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). This means that you do not have to supply a user_id value (be it NULL or an INTEGER) when creating a new user Accounts entry because the table will supply one itself. That is why you get a SQL error.

CodePudding user response：

Since it's an "auto-increment" column (which in SQL Server is a column with an IDENTITY attribute), you must NOT include it in your INSERT statement.

Try this:

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