Question SQL identity does not work

[gs99]

I set a primary key to have identity. Seed 239, increment 1.
In SSMS (2008), I inserted 2 records, correctly being assigned 239 and 240.
When I run the file in VisualBasic.NET, these 2 records appear, so I know the DB is current.
When I add a record in VB, I expect it to be 241. But the column is blank.

Does this not work in Express VB?

 

[gs99]

This thread was moved from VB to SQL.
The "identity" works fine (automatically) while in SSMS, so I didn't think it was a SQL problem.

I was asking about Visual Basic operation. When user clicks the 'plus' sign to add a record, is VB supposed to insert the next identity?
Most DB's are meant to exist in an application (not within SSMS), so I thought the identity function would be passed with the mdf file into Visual Basic.
But then, I have the Express version of VB, so perhaps it is not supported?

 

[Menthos]

Your database is independent of your VB application - if you're using SSMS to create/view your db, then it's likely you're using SQL Express - you'll find that it's the database engine that's hosting your database.

There is no 'identity function' passed by Visual Basic, if your table is created correctly, then an identity field will be automatically incremented when a new record is added. I assume you set the field to auto increment?

Have you checked via SSMS that the new records are being added to the table?

 

[gs99]

it's likely you're using SQL Express

Does it matter?
I have SQL Server 2008 R2. On the ‘Connect to Server’ dialog, Server name is set to ‘.\SQLExpress’.
About shows: Microsoft SQL Server Management Studio Version 10.50.1617.0
Does that mean I’m using SQL Express?
If the SSMS I have is not Express, couldn’t I create the database the same way?

Have you checked via SSMS that the new records are being added to the table?

I mentioned that when I did the two INSERT queries in SSMS, the column was correctly incremented.

The table’s first column is person_ID, set as the Primary Key and Identity set to ‘yes’.
When I run the VB application, it shows 240 records. If I click ‘Add New’ button, it changes to record 241, with all blank columns, including person_ID. Therefore VB recognizes the ‘identity’ property of person_ID.
So if VB knows what the next record should be, why doesn’t it insert that value into person_ID?

If I put values in other fields of record 241 (leaving person_ID blank), and click the Save button, I get an Exception error “Column 'person_ID' does not allow nulls.” On line Me.PersonsBindingSource.EndEdit().

 

[Menthos]

That's my point, it's not down to VB - VB doesn't 'know' what the next identity is, and doesn't need to... it's the job of the database engine to handle those sorts of things.

It sounds like you're using BindingSource to access the data, which is fine, but you may need to alter your insert/update adapters and omit the person_id field - you shouldn't have that in those queries.

I don't use BindingSources myself (still prefer the dark ages route of hand coding my db access)... so one of the other contributors here might chip in with some better advice.

 

[gs99]

I would like to clarify the 'Express' question.

In my initial post I said “In SSMS (2008), I inserted 2 records”.
You then said “if you're using SSMS to create/view your db, then it's likely you're using SQL Express”
My reply provided info about my SQL:
>I have SQL Server 2008 R2.
>On the ‘Connect to Server’ dialog, Server name is set to ‘.\SQLExpress’.
>About shows: Microsoft SQL Server Management Studio Version 10.50.1617.0
and asked
Does that mean I’m using SQL Express?

On my computer (Windows 7 Premium) if I Start, All Programs, I see “Microsoft SQL Server 2008 R2”.
There is no “Microsoft SQL Server 2008 R2 Express” (whereas I see “Microsoft Visual Studio 2010 Express”).

Only after SSMS loads the Connect dialog do I see Server name: ‘.\SQLExpress’.
Apparently the SQL Server 2008 R2 on my computer is the Express version so that's the only Server available to SSMS, correct?
In what situation would other servers be shown in the list?

If a user has paid for ‘regular’ SQL Server, couldn’t she “create/view your db” as I did? Does a professional db person not use SSMS to do these things? It seems like a fine tool to design and maintain databases. I would expect the normal SQL SSMS to have more features, but the basic functions to be the same as in Express. Do you have access to both for comparison?