Auto-displaying identity primary key on a textbox

[airhalynn101]

I have an identity (auto-increment) column in my mssql table, which is also my primary key. I want to use these numbers on my form, such that when I open that form, the auto-increment id will already be displayed on a textbox (so I wont have to type it in anymore). It's like, trying to display the next auto-increment id on form load while no data is entered yet. Like on paper forms where the form numbers are already there before it is even used or filled up. I hope I'm making it clear. Is it possible? How do you do that? I really have no idea how to do it.

[Reverend Jim]

Do you mean that when you bring up a blank form (so you can create a new entry) the ID number of the record that will be added is displayed? The answer depends on whether or not there is only one user hitting the database at a time.

[airhalynn101]

Yes, that's exactly what I want to do. But I'm sure that only one person will use that form.

[Reverend Jim]

Then you can get the next available ID number by

SELECT nextID=MAX(ID)+1 FROM mytable

Just remember not to specify ID when you save the record or you will get an error.

[airhalynn101]

Uhh...I'm afraid I don't know how I should use it. I tried

conn.Open()
com.CommandText = "SELECT nextID=MAX(crc_keyid)+1 FROM tblCRClearance"
com.Connection = conn
dr = com.ExecuteReader
dr.Read()

txtCRCNo.Text = dr!crc_keyid

but of course it didn't work. If there are other ways of accessing the database from vb codes, well, sadly, I don't know about them.

[airhalynn101]

oh... I should use nextID instead of crc_keyid right?

[airhalynn101]

It works now. Thank you very much!