Sunday, December 23, 2012

SQL auto generate last column id without auto increment

If we want to get a table autogenerate column id, without auto increment column. we can do it easily.
we can also made it globally for all table. We can create a table name paramaterize store procedure that
will give us table last column ID without using auto increment column.

      @tableName [varchar](100),  
      @locationId [int]  
 declare @newID varchar(20)  
 declare @dbID varchar(20)  
 declare @generateID bigint  
 declare @ReturnValue varchar(100)  
  if exists(select * from DB_TablePKID where LocationID=@locationId and TableName=@tableName)  
   --table name exists  
   set @ReturnValue= (select MaxID+1 from DB_TablePKID where LocationID=@locationId and TableName=@tableName)  
  else --table name is not exists.  
   -- generate new first ID for the supplied table  
   select @dbID= DBid from DB where DBLocationID=@locationId  
   set @newID=@dbID+'00000000000001'  
   set @generateID=convert(bigint,@newID)  
   set @ReturnValue=@generateID  
   select @ReturnValue  

declar the store procedure in C#

 public long GetTablekeyID(string tableName, int locationId)  
       long id = 0;  
       // id = _db.spSET_GetDB_TablePKID(tableName, locationId);  
       System.Data.Objects.ObjectResult<string> sid = this.DataContext.spSET_GetDB_TablePKID(tableName, locationId);  
       foreach (string aa in sid)  
         id = long.Parse(aa.ToString());  
       return id;  

Now call from anywhere with your any table name. it will return last id of your table. here locationID is optional.

vfpoledeb.1 provider is not registered on the local machine

Have u Install VFP Ole DB Provider in your System ? If No then Please Install it from following link.