Friday, March 20, 2009

Friday night javascript notes

Uhg! A few things that are worth writing down - the kind of things you know but then forget you know when you are programming.



First - The DOM is case sensitive in Firefox, not in I.E. though... So imageId3 is different than ImageId3 as far as Mozilla is concerned.



O.K. that first one is like Duh! but this second point is not so much, as it turns out that if you have a variable that hasn't been assigned yet I.E. doesn't really care so much, you can try and talk to it and nothing happens - Firefox on the other hand blows up, at the least the function does. the minute you try and access that null variable Mozilla throws an error and the rest of the function won't execute. The trick is to see if there is a value assigned to it. I searched the web for a while and finally over at webdeveloper.com someone gave me this code.



if(typeof imageContainer == 'undefined')



There is an explanation at the original post. http://webdeveloper.com/forum/showthread.php?p=990761#post990761



OK - That's it for now, hope that helps you out on your JS travels.

Wednesday, March 11, 2009

My stored procedure template

OK - It is really just a nice example that you can cut and paste and customize... You get the point though!

If you use MSSQL you probably know that you can right click on the stored procedures folder and click "new stored procedure" - this gives you a fairly workable template but it uses the CREATE syntax, then you have to change that to ALTER when you make a change to the proc. This can become a real pain if several folks are trying to run this on different development databases, then run it out the production server / test server, whatever, you get the point... The template below lets you modify and run the procedure as many times as you like and wherever you like with no regard for weather or not the procedure exists.

It also grants permissions for who can run this proc, you can adjust that as you please.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

-- ============================================================================

-- Create date:

-- Description:

============================================================================

USE [AdventureWorks]

GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'InsertContact')

BEGIN

PRINT 'Dropping Procedure InsertContact'

DROP Procedure InsertContact

END

GO

PRINT 'Creating Procedure InsertContact'

GO

CREATE Procedure InsertContact

@CorporateAccountId int,

@Title varchar(10),

@FirstName varchar(50),

@LastName varchar(50),

@ProfessionalTitle varchar(100),

@Company varchar(100),

@CompanyWebUrl varchar(100),

@Industry varchar(10),

@ContactAddress1 varchar(50),

@ContactCity varchar(50),

@ContactPostalCode varchar(50),

@ContactCountryRegionId int,

@ContactState varchar(100) = null,

@Email varchar(50),

@Telephone varchar(50),

@Fax varchar(50),

@Username varchar(256),

@Password varchar(50),

@SecretQuestion varchar(100),

@SecretAnswer varchar(100),

@CreatedBy int,

@ModifiedBy int,

@ContactTypeId char(1),

@ReferrerId varchar(25),

@ReferrerHost varchar(100),

@Id int OUTPUT

AS

BEGIN

SET NOCOUNT ON;

IF EXISTS (SELECT Id

FROM [dbo].[Users]

WHERE Username=@Username)

BEGIN

SELECT @ID = (SELECT 0)

RETURN @ID

END

INSERT INTO [dbo].[Contact]

(

CorporateAccountId,

Title,

FirstName,

LastName,

ProfessionalTitle,

Company,

CompanyWebUrl,

Email,

Telephone,

Fax,

SecretQuestion,

SecretAnswer,

ContactTypeId,

Active,

IndustriesForContactsId,

ReferrerId,

ReferrerHost,

CreatedOn,

CreatedBy,

ModifiedOn,

ModifiedBy

)

VALUES

(

@CorporateAccountId,

@Title,

@FirstName,

@LastName,

@ProfessionalTitle,

@Company,

@CompanyWebUrl,

@Email,

@Telephone,

@Fax,

@SecretQuestion,

@SecretAnswer,

@ContactTypeId,

1,

@Industry,

@ReferrerId,

@ReferrerHost,

getdate(),

@CreatedBy,

getdate(),

@ModifiedBy

)

SELECT @Id = @@Identity

INSERT INTO [dbo].[Address]

(

ContactId,

AddressTypeId,

Address1,

City,

State,

PostalCode,

CountryRegionId

)

VALUES

(

@Id,

'B',

@ContactAddress1,

@ContactCity,

@ContactState,

@ContactPostalCode,

@ContactCountryRegionId

)

INSERT INTO [dbo].[Users]

(

ContactId,

Username,

Password,

CreatedOn,

CreatedBy,

ModifiedOn,

ModifiedBy

)

VALUES

(

@Id,

@Username,

@Password,

getdate(),

@CreatedBy,

getdate(),

@ModifiedBy

)

END

GO

GRANT EXEC ON InsertContact TO PUBLIC

GO


Tuesday, March 10, 2009

using data from sub queries as an argument to an insert statement

I have run into this situation more than once, I need to take some piece of data supplied to a stored procedure, use it to get another piece of data from a different table and then take all of that and create and INSERT with it.

If you simply try and stick a sub select into your INSERT you get an error… This of course isn’t what you want.

You will see something like this when using MSSQL 2005.

“Subqueries are not allowed in this context. Only scalar expressions are allowed.”

The way to overcome this problem is to declare a variable and the use SET and a sub query to (not surprisingly) set its value. Once you have set the value of your variable you can then use it in your stored procedure just like any other variable.I tend to learn by example so take a look below.

Check out this example.

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: Kenn

-- Create date: 3/10/2009

-- Description: Adds an entry into the press portal for a document

--

-- =============================================

CREATE PROCEDURE InsertPressPortalDocument

-- Add the parameters for the stored procedure here

@studyIdNumber varchar(5),

@numericDirectory varchar(5)

AS

BEGIN

DECLARE @documentId int

SET @documentId = (SELECT Id FROM Document WHERE StudyIdNumber = @studyIdNumber and SectionNumber = 0)

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

INSERT INTO PressReleases

(DocumentId,

PressReleaseUri,

PressReleaseTypeId,

ReleaseDate,

WordUri,

PDFUri,

HTMLUri)

VALUES(@documentId,

@numericDirectory + '\' + @studyIdnumber + '.htm',

'Industry Study',

GETDATE(),

@numericDirectory + '\' + @studyIdnumber + '.doc',

@numericDirectory + '\' + @studyIdnumber + 'pr.pdf',

@numericDirectory + '\' + @studyIdnumber + '.htm')

END

GO

Can you see where I am declaring the variable documentId and then on the next line setting the value of it using a sub select? Once that is done you can see where I place the documentId variable in the INSERT. It is pretty simple…

Hopefully this helps someone in the same boat looking for an answer.

Followers