struggling with a prefix autonumber problem

[John Cassell]

Hi all, I have hit a brick wall with a problem I am having and am despearate for some help if possible..

If I just explain what I need instead of going into how I have tried to solve it, it may avoid any confusions...

I need my tblJobs to have a 'JobNumber' column in this format "AB1", where "AB" is a fixed 2 chars and 1 is an incremented digit, so next job would be automatically assigned "AB2", "AB3" etc..

I also need this column to be the primary key so I can create relationships with another table against the job number.

I am using SQL Server 2005 Express and Visual Studio 2005.

Hope someone can help!

Thanks

John

 

[cjard]

autoincrement on the client side is purely to assist new relationships.. you'd implement logic such that the value submitted to the DB was e.g. -1, and during the insert, the new ID is calced and written back to the parameter it came in on (via sproc, or similar.. see the wizard advanced tickbox "update the datatable with values calculated by the database upon save" .. it says something to that effect)
ADO.NET will pick up the new parameter..

note that if the ID always starts AB, you can argue the AB doesnt need to be present in the fild at all

 

[John Cassell]

Hi Cjard, thanks a lot for your reply. I've got so far that the application does this..

Lets say the last job number was 10

a) users enters details for NEW job.
b) presses save
c) check in SQL database and the job is there (job number 11)

problem is that on the front end, is hasn't populated the autoID box with the new number. User must find out what the number he has just created was.

I've used auto-incremented fields before which DO return the Job number upon hitting Save, but I can't figure out why this one isn't.

Would really appreciate the reply.

Thanks

John