DROP PROCEDURE IF EXISTS [dbo].[lp_GetNewID]
GO

CREATE PROCEDURE [dbo].[lp_GetNewID] @type INT, @id INT OUTPUT
as
set nocount on
begin tran
select @id =  LastID from tblID where [ID] = @type
if (@id = 0) or (@id is null)
    begin
        raiserror ('Error:  Unknown type of id - is there a row for it in tblID?',15,1)
        rollback
        select @id = -99
        return -1 --no record for this type
    end
else 
  begin
    select @id = @id + 1
    update tblID set LastID = @id where [ID] = @type
    commit
  end
set nocount off
return 0  --successful

static public bool IsEmailValid(string emailAddress)
            {
            // Make sure that there is a string passed    
            if (String.IsNullOrEmpty(emailAddress))
            {
                return false;
            }
            // Make sure the email address 
            //  -- does not have a space
            //  -- does not have two dots in a row
            //  -- does not have a comma
            //  -- the @ symbol is at least the 3rd character
            //  -- the . is at least the 3rd character
            //  -- there is a period after the @ symbol
            if (emailAddress.IndexOf(" ") > 0 || emailAddress.IndexOf("..") > 0 || (emailAddress.IndexOf(",") > 0)
                || emailAddress.IndexOf("@") < 2 || emailAddress.IndexOf(".") < 2
                || (Math.Abs(emailAddress.IndexOf("@") - emailAddress.IndexOf(".")) < 2))
                {
                return false;
                }
            // Make sure that the email address does not containg 2 @ symbols
            if (Regex.Matches(emailAddress, "@").Count > 1)
                {
                return false;
                }
            // Make sure last period is not too close to the end of the email address
            // hello@hello || hello@hello. || hello@hello.c are invalid
            // hello@helloa.ca is valid
            if (emailAddress.LastIndexOf(".") == emailAddress.Length || emailAddress.LastIndexOf(".") > (emailAddress.Length -3))
                { return false; }

            return true;
            }

function IsValidZipCode(vTheZipCode : string;
                        vIsCanadian : boolean; var vMsg : string) : boolean;
begin
  result := true;
  //don't initialize vMsg.  You might want to append to an existing msg.
  if vIsCanadian then
    begin
      //Must be 6 chars, or 6 chars + space
      if ((pos(' ', vTheZipCode) > 0) and (length(vTheZipCode) <> 7))
           OR ((pos(' ', vTheZipCode) = 0) and (length(vTheZipCode) <> 6)) then
        begin
          result := false;
          vMsg := vMsg + 'Canadian postal code must be 6 characters, '
                       + ' or 7 including space.' + #10 + #13;
        end;
      if (pos('-', vTheZipCode) > 0) then
        begin
          result := false;
          vMsg := vMsg
               +  'Canadian postal code may not contain a dash' + #10 + #13;
        end;
      //if have space, must be middle character
      if length(vTheZipCode) = 7 then
        if pos(' ', vTheZipCode) <> 4 then
          begin
            result := false;
            vMsg := vMsg
               +  'Space in Canadian postal code must be in the middle' + #10 + #13;
          end;
    end else
      begin
        //must be 5 chars, or 10 chars including dash
        if ((pos('-', vTheZipCode) > 0) and (length(vTheZipCode) <> 10))
            OR ((pos('-', vTheZipCode) = 0) and (length(vTheZipCode) <> 5)) then
          begin
            result := false;
            vMsg := vMsg
                 +  'Zip code must be either 5 or 10 digits long' + #10 + #13;
          end;
        //must be numeric except for the 6th char
        if not AllDigits(copy(vTheZipCode,1,5) + copy(vTheZipCode,7,4)) then
          begin
            result := false;
            vMsg := vMsg
                 +  'Zip code must be all numbers, except for dash in Zip+4' + #10 + #13;
          end;
      end;
end;