Joseph Michael Pesch
VP Programming

SQL Server Printing Long Text Fields

by 26. March 2010 20:11

declare @pos int, @len int, @txt varchar(500)

 

 

select @pos = 0, @len = datalength(TextColumnHere)

from TableNameHere where CriteriaHere

 

 

while (@pos * 500) < @len begin

  select @txt = substring(TextColumnHere, @pos * 500 + 1, 500)

  from TableNameHere where CriteriaHere

  print @txt + char(10)

  set @pos = @pos + 1

end

Tags:

SQL Server

SQL Server Replace Many Characters at Once (NumberOnly Function)

by 26. March 2010 15:34

  create table #replace(val char(1))

  insert into #replace values('_')

  insert into #replace values('-')

  insert into #replace values('a')

  insert into #replace values('b')

  insert into #replace values('c')

  insert into #replace values('d')

  insert into #replace values('e')

  insert into #replace values('f')

  insert into #replace values('g')

  insert into #replace values('h')

  insert into #replace values('i')

  insert into #replace values('j')

  insert into #replace values('k')

  insert into #replace values('l')

  insert into #replace values('m')

  insert into #replace values('n')

  insert into #replace values('o')

  insert into #replace values('p')

  insert into #replace values('q')

  insert into #replace values('r')

  insert into #replace values('s')

  insert into #replace values('t')

  insert into #replace values('u')

  insert into #replace values('v')

  insert into #replace values('w')

  insert into #replace values('x')

  insert into #replace values('y')

  insert into #replace values('z')

  declare @tmp varchar(100)

  select @tmp = '-_abcdefghijklmnopqrstuvwxyz0123456789'

  select @tmp = replace(@tmp, val, '') from #replace

  select @tmp

  drop table #replace

Tags:

SQL Server

SQL Random (rand) Number Generator

by 18. March 2010 13:53

  declare

    @RandomNumber float

  , @RandomInteger int

  , @MaxValue int

  , @MinValue int

 

 

  set @MaxValue = 6

  set @MinValue = 1

  set @RandomNumber = RAND()

  set @RandomInteger = ((@MaxValue + 1) - @MinValue) * @RandomNumber + @MinValue

 

 

 

  select @RandomNumber as RandomNumber

       , @RandomInteger as RandomInt

       , @RandomInteger + round(@RandomNumber, 3) as RandomDecimal

 

Sample results running above SQL inside 10 iteration loop:

 

Tags:

SQL Server

Linq To SQL Error: Cannot access a disposed object.

by 17. March 2010 14:06

Cannot access a disposed object. Object name: 'DataContext accessed after Dispose.'.  This can happen if you send a Linq query result entity out of a procedure as shown below.  The issues is that the inner procedure has not actually evaluated the query (i.e. results have not been retrieved from the SQL server, just the construct for the query exists).  When you leave the inner procedure and attempt to access the data within the outer procedure the database context is gone (since it was wrapped in a using statement).  Only at the point when you attempt to access the data members will the actual query results be requested from the SQL server and at that point there is no database context under which to execute the query.  You may get the process to work by removing the using statement; however, that is not a correct practice.  The better solution is to return an IEnumerable list using the .ToList() method on the result entity you are returning.  This will force the query to execute and the full result set will be populated into the returned list.

Outer Procedure Code:
...
   IEnumerable<BidMortgage.BMObject.LowestBid> bids = BidMortgage.BMObject.GetLowestBids();

   /* Error here if not using .ToList<>() */
   foreach (BidMortgage.BMObject.LowestBid bid in bids)
{
...}
...

 


Inner Procedure Code:

public class LowestBid

{

   public LowestBid()

   {

   }

   public int LoanTypeID { get; set; }

   public string LoanTypeName { get; set; }

   public decimal CalculatedAPR { get; set; }

   public decimal InterestRate { get; set; }

}

 

 

public static IEnumerable<LowestBid> GetLowestBids()

{

  DataTable tbl = new DataTable();

  try

  {

    using (BidMortgage.BidMortgageDataContext db = new BidMortgageDataContext())

    return (from row in db.LoanRequestBids

    group row by row.tbl_LoanRequest.LoanTypeID

    into g

    select new LowestBid

    { LoanTypeID = g.Key

      , LoanTypeName = (from itm in db.ListValues_MortgageTypes

                        where itm.TypeValue == g.Key

                        select itm.TypeName).SingleOrDefault()

      , CalculatedAPR = g.Min(t => t.CalculatedAPR)

      , InterestRate = (from itm in db.LoanRequestBids

                        where itm.CalculatedAPR == g.Min(t => t.CalculatedAPR)

                        && itm.tbl_LoanRequest.LoanTypeID == g.Key

                        select itm.InterestRate).SingleOrDefault()

    }).ToList<LowestBid>();

    /*
     If you don't use the .ToList<LowestBid>() and just return the select entity you will
     receive this error when attempting to access the data outside of this using statement:
     Cannot access a disposed object. Object name: 'DataContext accessed after Dispose.'.
     */ 

  }

  catch (Exception)

  {

    return null;

  }

}

 

Tags:

ASP.Net | C# | LINQ to SQL

ASP.Net Error on Master Page with <%=ResolveUrl(...)%> Code

by 4. March 2010 20:12

For any type of relative URL reference in a Master page structure, I typically use the ResolveUrl() function (as shown in images below).  Notice, if you attempt to use the equal sign (equivelant to Response.Write()) you will receive this error message:  "The Controls collection cannot be modified because the control contains code blocks (i.e. <% ... %>)."  To solve this problem, instead use the pound sign (# data binding expression) and make sure you add: Page.Header.DataBind(); and Page.DataBind(); to the PageLoad() event of the Master page.

GOOD STYLE:

BAD STYLE:

 

Tags:

[None]

Regular Expression Find/Replace in Visual Studio

by 4. March 2010 17:32

Here is an example to find all the references like ..."images/filename.extension"... and replacing them with ..."<%=ResolveUrl("~/images/filename.extension")%>"...

Find: {images/[A-Za-z0-9.]*}

Replace: <%=ResolveUrl("~/\1")%>

Tags:

Regular Expression | VPC

Oracle Common Table Expression, Oracle Partition

by 3. March 2010 16:23

Common table expression allows "with" statement to assign a temporary table name to a select statement (similar to SQL server "using" statement).  Also, the below example shows how to use the Oracle "partition" function to group results (this sample shows how to get the first row of each group of data).

with MaxStatus as
(
  select * from
  (
    select
      h.*
    , row_number() over (partition by h.ap_num order by h.change_date) as RowNumber
    from history h
  ) where RowNumber = 1
)
select * from MaxStatus

Tags:

Oracle