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.

How to disable PageTab?

Many times we have requirement that, we have more than one tab pages and we want to disable other tab untill we complete work on a tab.

To simple answer to this situation is NO as there is no property to disable tabpage.

You can use third party control to do this.

Other solution for this is to disable all controls on that tab page. We can achieve this with the help of follwoing code snippest ..

 For Each tab As Control In Me.tabMain.Controls

                Dim c As TabPage = TryCast(tab, TabPage)

                If Not c Is Nothing Then

                    tab.Enabled = False

                End If

            Next

Above code we can put on TabSelectedIndexChange event, and can disable or enable.

Please let you know that you can able to select tab but all control on that is disabled so you can not do anything.

ALTER TABLE Syntax

to change table column datatype, add new column, remove column you can use below syntax rather than to modify from design view of table.

--add new column
ALTER TABLE "Tablename"
ADD  "ColumnName" "datatyepe" "constraints"
 
 --alter table column
ALTER TABLE "TableName"
ALTER COLUMN "ColumnName" "datatype" "constraints"
 
 --drop/remove column from table
ALTER TABLE "TableName"
DROP COLUMN "ColumnName"

datatype can be any valid datatype of sql and constraints is any like not null, null, primary key etc..

display dialog box before confirm closing of form..

in some sitation we want to display dialog box to user before closing form. Either by clicking on some button or close by pressing Alt +F4 This can be done by writing code in formClosing event.

Private Sub frmMyForm_FormClosing(ByVal sender As System.Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles MyBase.FormClosing

        If MessageBox.Show("Change has been made to this form. Do you want to continue?", "Are you sure?", MessageBoxButtons.YesNo) = System.Windows.Forms.DialogResult.Yes Then

            e.Cancel = True

        End If

    End Sub

At, that time we just need to cancle closing event. Nothing else otherwise form will be closed whatever you have write.

How to allow only Numeric value in textbox?

this code snippest allow you to enter only numeric value to text box...

Private Function TrapKey(ByVal KCode As String) As Boolean

   If (KCode >= 48 And KCode <= 57) Or KCode = 8 Then

       TrapKey = False

   Else

       TrapKey = True

    End If

End Function

Private Sub TextBox1_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TextBox1.KeyPress

        e.Handled = TrapKey(Asc(e.KeyChar))

End Sub

Alternate you can write common function like

 Public Sub NumbericText(ByVal sender As System.Object, ByVal e As KeyPressEventArgs)

        Dim ch As Integer = Asc(e.KeyChar)

        If (ch >= 48 AndAlso ch <= 57) Or ch = 8 Then

            e.Handled = False

        Else

            e.Handled = True

        End If

    End Sub

then add eventhandler for text box,

suppose ,in textbox txtId you just want to allow numeric value then you can do 

 AddHandler txtId.KeyPress, AddressOf  NumbericText