CODEWITHSUNDEEP
c#sqlsql-serverincrement
Alfred Intal
Alfred Intal

Reputation: 15

Auto increment a non-primary key (SQL Server 2014) in C#

private void btnOk_Click(object sender, EventArgs e)
{
        Helper.openConnection();
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = Helper.cn;
        cmd.CommandText = "insert into Characters values(@Name, 'Novice', 1, 40, 10, 0, @STR, @AGI, @VIT, @DEX, @LUK, @INT, 1, 1)";
        cmd.Parameters.AddWithValue("@Name", txtName.Text);
        cmd.Parameters.AddWithValue("@STR", txtStr.Text);
        cmd.Parameters.AddWithValue("@AGI", txtAgi.Text);
        cmd.Parameters.AddWithValue("@VIT", txtVit.Text);
        cmd.Parameters.AddWithValue("@DEX", txtDex.Text);
        cmd.Parameters.AddWithValue("@LUK", txtLuk.Text);
        cmd.Parameters.AddWithValue("@INT", txtInt.Text);
        cmd.ExecuteNonQuery();
        Helper.cn.Close();
        MessageBox.Show("Character successfully created!");
        this.Close();
}

Written above is my code for a simple character creation screen for my project in C#. I have a problem of doing an auto increment of the CharacterSlot column, which is a non-primary key since in the picture below, CharacterID is my primary key.

I also chose CharacterID as primary since we can have multiple characters per account. Below are images from my SQL Server database.

Characters Table

Users Table

EDIT: Per character slot is a clickable picture box that changes the values of the stats written on the character selection screen, the thing is, if the CharacterSlot is always 1, it will just show Helios' stats and not the other characters that I will create (up to 3).

Character Creation and Character Selection

How do I work around this?

Upvotes: 1

Views: 944

Answers (2)

Jonathan Allen
Jonathan Allen

Reputation: 70337

So if you want the recount to restart per use, you need to do this. Notice how the command text declares its own parameter for the count that it wants to look up.

CREATE Database Test1
GO
Use Test1
GO
CREATE TABLE TestTable
     (CounterColumn int IDENTITY PRIMARY KEY,
    Name nvarchar(25) NOT NULL,
    SlotNumber INT NOT nULL,
    UserId INT NOT NULL

    )
GO


---- SQL Command Parameters----

Declare @Name nvarchar(25) = 'Tom';
Declare @UserId INT = 1;


----------------------- SQL Command Text------------
Declare @SlotNumber INT;
SELECT @SlotNumber = IsNull(Max(SlotNumber), 0) + 1 FROM TestTable WHERE UserId=@UserId;
INSERT INTO TestTable (Name, SlotNumber, UserId) VALUES (@Name, @SlotNumber, @UserId);

--------Check results -----------

SELECT * FROM TestTable

Upvotes: 0

Jonathan Allen
Jonathan Allen

Reputation: 70337

That's what the Sequence object is for.

CREATE SEQUENCE Test.CountBy1
    START WITH 1
    INCREMENT BY 1 ;
GO

CREATE TABLE Test.TestTable
     (CounterColumn int PRIMARY KEY,
    Name nvarchar(25) NOT NULL) ; 
GO

INSERT Test.TestTable (CounterColumn,Name)
    VALUES (NEXT VALUE FOR Test.CountBy1, 'Syed') ;
GO

SELECT * FROM Test.TestTable; 
GO

https://msdn.microsoft.com/en-us/library/ff878370.aspx

You can also use NEXT VALUE FOR in the DEFAULT constraint on a column.

Upvotes: 2

Related Questions