Database

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

CREATE PROCEDURE dbo.SPSearchRmiInfoLastname
(
	@Lastname varchar(50)
)
AS
	select * from RmiInfo where Lastname like @Lastname+'%' order by Lastname

GO

dg.DataSource = null;

INSERT INTO Table(Column1)
VALUES('Conan O''brien')

Dim cmd As New SqlCommand
cmd.CommandText = "INSERT INTO tbl VALUES (@username)"
 
Dim param As SqlParameter = _
  New SqlParameter( "@username", SqlDbType.VarChar, 20 )
 
param.Value = "Conan O'brien"
 
cmd.Parameters.Add( param )