Database

try
  'Begin a transaction
  '......
  If ds.Table(0).HasChanges then
    'Update table(0)
  end if
  if ds.Table(1).HasChanges then
    'Update table(0)
  end if
  'commit the transaction
  '.....
catch ex
  'Rollback the transaction
  '.....
finally
  'set any variable to nothing
  '.....
end try

SELECT @@IDENTITY

OleDbCommand vcommand = new OleDbCommand();
vcommand.CommandText = "SELECT @@IDENTITY";
vcommand.Connection = urOleDbConnection;
int vvalue = (int)vcommand.ExecuteScalar();

Dim vCommand as new OleDbCommand()
vcommand.CommandText = "SELECT @@IDENTITY";
vCommand.Connection = urOleDbConnection
Dim vInt as Integer = CType(vCommand.ExecuteScalar(),Integer)

if exists (select * from sysobjects where type = 'U' and name = 'SearchResults')
  exec ('drop table SearchResults')
GO
create table SearchResults
(
  SearchSeqNo             int            NOT NULL IDENTITY,
  UserKey                 int            NOT NULL,
  SearchType              tinyint        NOT NULL,
   -- 1 = Settlement Online Approval (SOA)
   -- 2 = Dispatch Tower Activity (DSP)
   -- 3 = Assigment Activity (AL)
   -- 4 = Salvage Checkin (CI)
   -- 5 = Salvage Image (IMG)
   -- 6 = Salvage Provider (SPL)
  KeyValue                int            NOT NULL,
  Dummy                   int            NULL
)
GO
create unique clustered index SearchResults_idx
  on SearchResults (UserKey, SearchType, SearchSeqNo)
GO

if exists (select * from sysobjects where type = 'P' and name = 'ProviderOpenSearch')
  drop procedure ProviderOpenSearch
GO
create procedure ProviderOpenSearch @iUserKey int,
                                    @cCriteria varchar(8000),
                                    @iEntriesPerPage int = 0,
                                    @bReturnResults int = 0,
                                    @iSortColumn int = 0
AS

declare
  @cUserKey varchar(10),
  @cSortBy varchar(7000),
  @cSortDirection varchar(20),
  @iRowsFound int,
  @iPageCount int

set nocount on

delete from SearchResults where UserKey = @iUserKey and SearchType = 6

if IsNull(@iEntriesPerPage,0) = 0
  select @iEntriesPerPage = 20

if IsNull(@cCriteria,'') = ''
  select @cCriteria = ' 1 = 1'

select @cSortDirection = '', @cSortBy = 'SP.Salvage_Provider_ID', @iSortColumn = IsNull(@iSortColumn,0)

if @iSortColumn > 100
  select @cSortDirection = 'DESC', @iSortColumn = @iSortColumn - 100

if @iSortColumn = 1
  select @cSortBy = 'SP.Salvage_Provider_ID'
else if @iSortColumn = 2
  select @cSortBy = 'SP.Salvage_Provider_Name'
else if @iSortColumn = 3
  select @cSortBy = 'SP.Address_Line_1'
else if @iSortColumn = 4
  select @cSortBy = 'SP.City_Name'
else if @iSortColumn = 5
  select @cSortBy = 'SP.State_Abbreviation'
else if @iSortColumn = 6
  select @cSortBy = 'S.Status_Description'
else if @iSortColumn = 7
  select @cSortBy = 'SPG.Salvage_Provider_Group_Name'
else if @iSortColumn = 8
  select @cSortBy = 'OT.Office_Type_Description'


select @cUserKey = Convert(varchar(10),@iUserKey)

exec ('insert SearchResults
(
  KeyValue,
  UserKey,
  SearchType
)
select
  Salvage_Provider_ID,
  ' + @cUserKey + ',
  6
from
  Salvage_Provider
where
  ' + @cCriteria + '
order by 
  ' + @cSortBy + ' ' + @cSortDirection
)


select @iRowsFound = @@rowcount

select @iPageCount = (@iRowsFound/@iEntriesPerPage)+sign(@iRowsFound%@iEntriesPerPage)

select
  @iRowsFound 'SearchResultsCount',
  @iPageCount 'PageCount'

if IsNull(@bReturnResults,1) = 1
  exec ProviderGetPage @iUserKey, 1, @iEntriesPerPage

GO




if exists (select * from sysobjects where type = 'P' and name = 'ProviderGetPage')
  drop procedure ProviderGetPage
GO
create procedure ProviderGetPage @iUserKey int,
                                 @iPageNo int,
                                 @iEntriesPerPage int = 0
AS

declare
  @iMinKey int,
  @iMaxKey int

set nocount on

select
  @iEntriesPerPage = IsNull(@iEntriesPerPage,20),
  @iUserKey = IsNull(@iUserKey,0)

select
  @iMinKey = MIN(SearchSeqNo)
from
  SearchResults
where
  UserKey = @iUserKey and
  SearchType = 6

select @iMinKey = @iMinKey + ((@iPageNo-1) * @iEntriesPerPage)
select @iMaxKey = @iMinKey + @iEntriesPerPage - 1


select
  SPL.Salvage_Provider_ID, 
  SPL.Salvage_Provider_Name, 
  SPL.Address_Line_1, 
  SPL.City_Name, 
  SPL.State_Abbreviation, 
  dbo.CommaDelimitedLocationTypes(SPL.Salvage_Provider_ID) as Office_Type_Description,
  SPL.Salvage_Provider_Group_Name,
  SPL.Salvage_Provider_Group_ID, 
  SPL.Salvage_Provider_Status_Description
from
  SearchResults SR,
  Salvage_Provider_List_View_New SPL
where 
  SR.UserKey = @iUserKey and
  SR.SearchType = 6 and
  SR.SearchSeqNo BETWEEN @iMinKey and @iMaxKey and
  SPL.Salvage_Provider_ID = SR.KeyValue
order by
  SR.SearchSeqNo


GO