Can't ALTER COLUMN to IDENTITY

If you ever try to alter existing primary column with integer datatype to identity from sql script you will get error.

Because you cant alter column whose value is filled prior.

you have options like

1) Create new table with same name and now set that column as identity

2) drop old table

But visual studio gives you abilty to do it in design time. In design just modify table structure and set identity to yes.

Internally visual management studio drop your old table and create new table with same name and structure now will copy old data to this new table.

Keep in mind you can't set Identity by altering existing column.

Raise DBConcurrency Exception FROM DataAdapter

Hello!

In .Net Ado.net provides many features of database operation and also you can have disconnected architecture so you have good performance.

But for Concurrency you have to take care of one thing.

When you have used storedprocedure for command and want to raise DBConcurrency Exception then it will not raise.

For that you have to set 

SET NOCOUNT OFF; in stored procedure.

Then when you are going to update dataset with DataAdapter's update method. It will raise DBConcurrency Error If no row is affected in stored procedure.

Please do this by testing. As just set SET NOCOUNT ON and same code will not generate any Exception.

This little thing will save many time to handle concurrency and also reduce many line of code.

Remove Data Concurrency From SQL SERVER

First what mean by Data Concurrency.

If our appliction is used by many users. More than one user has opened up a record and simaltenously all user are trying to update that record.

So by whom saved is done in database. If in noraml condition that only detect concurrency when both will save at same time otherwise last save is made to database.

But the solution is that once first user saved record to database then other will not be able to save and inform him that record is changed by some one so please reload that changes.

In this case we have one solution is to use timestamp data field in database table.

timestamp filed will be modified each time when we are changing record. 

Lets have a look on some sample

I have a table Person in my database with fileds

• ID 
• Name
• Notes
• EditStamp  
  

Here EditStamp is timestamp field.

When you will going to add record you simple need to write

INSERT INTO Person (Name,Notes) VALUES ('AAAA','First Person')

as we have kept ID as autoincrement and EditStamp will be added by SQL server you will not to worried about its.

Now when you try to update record. then you will also pass EditStamp of that record

 @EditStamp timestamp

 @ID int

SELECT @EditStamp = EditStamp FROM Person WHERE ID = @ID

UPDATE PERSON 

SET Name ='BBBB'

WHERE  ID = @ID AND EditStamp = @EditStamp

UPDATE PERSON 

SET Name ='CCCC'

WHERE  ID = @ID AND EditStamp = @EditStamp

When you will execute above statement you will surprised that only Name is update with 'BBBB'

It will not update with 'CCCC'

Because after first update value of EditStamp will be changed. 

So with this we can overcome data concurrency problem we can also raise some error and detecting it.

Get Current Identity Value In SQL

Table that contain auto increment field as their primary key, we won't provide that value at the time of insert of data. So after insertion done we need to know which value is assigned to our primary key.

So at the time of insertion we will provide some output parameter as 

@ID AS INT 

After insertion statement just write 

SET @ID = @@IDENTITY 

OR

SET @ID = SCOPE_IDENTITY()

OR

SET @ID = IDENT_CURRENT

above all returns value inserted into identity columns.

Reassing AutoIncrement Field Value

If we have deleted all values in a table that contains auto-increment field. Now if we add any values to table then autoincrement field value would be start from last deleted value + step of increment

Say an example 

you have last value of auto increment field is  10 in deleted records and now want to add new record then increment value would be 11 if step is 1.

So what to do if we want to reevalutate value of autoincrement to 1.

Just fire below syntax 

DBCC CHECKIDENT(table Name,RESEED,0)

This will reevalute value of autoincrement field to 0.

Hope this will be useful ....

Guid Datatype In Access

Many of us have faced this problem. As In SQL Server we have data type Guid but when we use Access as our database. We will wonder for this datatype.

For that we need to use some tric. Its availble but we have not tried so we could not able to use it in access.

For Guid Data Type What should you need to do is as follows:

1) Add Field in design view of table

2) Set datatype of field to Number 

3) In Field properties shown below , set Field Size to "Replication ID" 

Now you have Guid in your access database.