Increment primary key that is not an identity

[dghundt]

I have a mssql 2005 database table that someone else has created. The primary key has the following properties which I cannot change:
Name Unique ID
data type int
primary key true
allow nulls false
identity false
identity seed 0
identity increment 0
length 4

I need to insert a new row into the table making sure to use a primary key that is one more than the last one.

I have read about using this for the value of Unique ID in the insert statement.

isnull( (selectmax(Unique ID) from table), 0) +1 )

Then I must be able to retrieve the Unique ID in my application to work on the data row further. I am using C#, but any .net example should put me in the right direction.

I appreciate your advice.

[Shalex]

Here is a script that creates the table and a test project that shows a workaround for the Identity property. The query you are interested in is in the CommandText property of the insertCommand object.
SQL script:

Code: Select all

create table TestTable(
[Unique ID] int not null primary key,
val varchar(32)
)

This application contains a DataGridView and "Update" button on the Form. After clicking "Update" button inserted data in the form will be put to the database and [Unique ID] fields will be incremented. As a result, the form will contain the data from database.
C# code:

Code: Select all

public partial class Form1 : Form {

    SqlConnection connection = new SqlConnection(@"Data Source =.\SqlExpress;Database=test;Integrated Security=true");
    SqlDataTable table;

    public Form1() {
      InitializeComponent();

      SqlCommand command = connection.CreateCommand();
      command.CommandText = "select * from TestTable";
      SqlCommand insertCommand = connection.CreateCommand();
      insertCommand.CommandText = @"declare @uniqueId int;
select @uniqueId = COALESCE(max([Unique ID]) + 1, 1) from TestTable;
insert into TestTable([Unique ID], val) values(@uniqueId, @pValue);
select @uniqueId as [Unique Id]";
      SqlParameter parameter = insertCommand.Parameters.Add("@pValue", SqlType.VarChar);
      parameter.SourceColumn = "val";
      table = new SqlDataTable(command);
      table.InsertCommand = insertCommand;
    }


    private void Form1_Load(object sender, EventArgs e) {

      connection.Open();
      table.Open();
      table.Columns["Unique ID"].AllowDBNull = true;
      dataGridView.DataSource = table;
    }

    private void btUpdate_Click(object sender, EventArgs e) {

      table.Update();
    }

    private void Form1_FormClosed(object sender, FormClosedEventArgs e)
    {
      connection.Close();
    }
  }

If you give me (alexsh at devart.com) your e-mail, I will send you the whole project.

[dghundt]

Thanks for your post. I'll play with it some.