s
Contact Login Register
h M

Integrating Dynamic Forms and the jQuery Tooltip Widget

Dynamic Forms has a feature named “Help Icons” which work as Tooltips for your Questions. By Default this feature is disabled, so to enable you’ll need to go to Module Configuration –> General Settings and Uncheck the checkbox “Hide Help Icons”. Below is an image of how this feature operates.

FireShot Screen Capture #046 - 'GoToTraining Page 1' - www_datasprings_com_gototraining-page-1

However I always enjoy pursuing alternate methods to achieve a more efficient and dynamic solution. This example will be showcasing the jQuery UI Tooltip Widget. I will be integrating Dynamic Forms and the jQuery Tooltip Widget in this example. Below is an image of the Dynamic Form we’ll be using for this example.

FireShot Screen Capture #042 - 'GoToTraining Page 1' - www_datasprings_com_gototraining-page-1

To implement this behavior, I’ll show you how to do this in 3 simple steps:

Step 1:                

Accumulate all of the HTML Id’s of the controls that you wish to have the Tooltips.

For instance, I have Inspected the First Name, Last Name and Email Address Textboxes using FireBug in Firefox and located the following ID’s in my Dynamic Form:

First Name HTML ID = dnn_ctr2640_DynamicForms_TBR_GUIDf329fd1d-a30c-4cd9-b7ce-44fd1c54518aFName

Last Name HTML ID = dnn_ctr2640_DynamicForms_TBR_GUID7169778c-218e-4345-be2c-d98f224fe7cfLName

Email Address HTML ID = dnn_ctr2640_DynamicForms_TBR_GUIDf91097e8-4251-49b8-ad31-c93905ad6101EmailAddress

** NOTE: Your Dynamic Forms Question HTML ID’s will be completely different than the one’s in this example. Copy/Paste won’t work for Step 1.

Monday, November 26, 2012/Author: Chad Nash/Number of views (54113)/Comments (-)/ Article rating: No rating
Categories: In The Flow
Tags:

T-SQL Script Generator BETA

Hello Everyone,

I have just opened our SQL Scripting website to the public. Over the past month I have been piecing this website together. I am now confident and feel that we’re in a good place for user trialing.

Website Purpose:

The purpose of this website is to provide SQL Scripting tools to users with registered accounts. Currently there is a T-SQL Table Generator tool and a T-SQL Stored Procedure Generator tool.  With these tools a user will

  1. Interact by inputting information into the Generators.
  2. Retrieve an output of T-SQL based on your inputted information.
  3. All Generated Scripts are saved under “My Account”. 

***The overall goal is to assist users that may not understand the syntax and semantics of SQL.

Try the T-SQL Generating Tools:

Please login to http://scripts.datasprings.com using either of the two available accounts:
 
User #1
Username: TestAccount1
Password: 1234567
 
User #2
Username: TestAccount2
Password: 1234567

This is a BETA site that we'd like to dedicate more development to. Data Springs would like your opinion, can you see these tools being of assistance to you?

Future Improvements Include:

  • MySQL Mode for Creating Tables and Stored Procedures
  • PostgreSQL Mode for Creating Tables and Stored Procedures
  • Oracle Mode for Creating Tables and Stored Procedures
  • New tool for SQL Triggers, SQL Constraints, SQL Indexes, and SQL Functions.

 

Contribute to Data Springs by:

Providing feedback based on your experience with http://scripts.datasprings.com. You can provide feedback by leaving a comment to this blog post or by emailing us at dnnsupport@datasprings.com

Tuesday, November 20, 2012/Author: Chad Nash/Number of views (54759)/Comments (-)/ Article rating: No rating
Categories: In The Flow
Tags:

SQL Server Triggers

Triggers are a special type of procedure that can be executed in response to one of the following conditions:

  • INSERT
  • UPDATE
  • DELETE

You can create a Trigger to fire a query based on the insertion, modification or deletion of a record against a table. I like to think of a Trigger as an Event Listener.

To demonstrate a Trigger, I will create two tables for the example.

Example SQL Table 1(Table to store Books):

CREATE TABLE Books
(
BookID INT IDENTITY(1,1) PRIMARY KEY,
BookName NVARCHAR(200) NOT NULL,
Genre NVARCHAR(200) NOT NULL,
DateCreated DATETIME DEFAULT GETDATE()
)

Example SQL Table 2(Table to log events):

CREATE TABLE Book_Log
(
LogID INT IDENTITY(1,1) PRIMARY KEY,
Event NVARCHAR(200) NOT NULL,
EventDescription NVARCHAR(200) NOT NULL,
LogDate DATETIME DEFAULT GETDATE()
)

 

Now you can create a Trigger that listens for specific events on the Books table. Below is the syntax to add a Trigger in SQL Server:

CREATE TRIGGER TR_BookEvent
ON BOOKS
AFTER INSERT, UPDATE, DELETE
AS
INSERT INTO Book_Log(Event, EventDescription)VALUES('Book Affected', 'A Book was Added, Modified, or Deleted')

The Trigger that was created will fire anytime an INSERT, UPDATE OR DELETE statement is ran against the Books table.  In the above Trigger example, anytime I INSERT, UPDATE or DELETE a book from the Books table, the following query will be executed:

INSERT INTO Book_Log(Event, EventDescription)
VALUES('Book Affected', 'A Book was Added, Modified, or Deleted')

In essence, a record will be inserted into my Book_Log table.

 

You can also specify a Trigger to fire on only one event, rather than all three at once:

CREATE TRIGGER TR_BookEvent
ON BOOKS
AFTER INSERT
AS
INSERT INTO Book_Log(Event, EventDescription)
VALUES('INSERT','Book was Created' )

In the above Trigger example, only when an INSERT into the Books table is execute, will a record be logged into my Book_Log table.

Below is the syntax to remove a Trigger:

DROP TRIGGER NameOfTrigger

Important facts on Triggers:

  • Cant be created on temporary tables.
  • Must be created on tables in the current database.
  • Cant be created on Views
  • When a table is dropped, all triggers associated with that table are dropped.
Monday, November 19, 2012/Author: Chad Nash/Number of views (38689)/Comments (-)/ Article rating: No rating
Categories: In The Flow
Tags:

SQL Server Indexes

Data Retrieval Methods within databases:

Method 1: Sequential access method, by searching through each individual record looking for a match. Unindexed table AKA Full-table scan

Method 2: Direct Access Method. Use of Index(es) on a table.

What are Indexes?

An Index is a way of presenting data differently than the way it appears on the disk(within a SQL Table). Special types of Indexes reorder the records physical location within a table. An index can be created and assigned to a single column or multiple columns depending on your database implementation and what’s supported by your RDBMS.

An Index is very similar to the Order by clause however the key differences are:

  • Order By Clause Re-sorts and orders the data each time you execute the corresponding SQL Statment.
  • When using an Index, the database system creates a physical index object and reuses the same index each time you query the table.

*** Please note that SQL indexes require physical storage and resides on the disk hard drive.

Is there an appropriate and inappropriate time to use indexes?

The answer is yes. Indexes exist to assist with query optimization. Indexes if used incorrectly can cause additional overhead to your query. It’s important to analyze your Table and understand the best and worst case to use/remove an index.

Appropriate times to use Indexes:

  • Indexes yield the greatest improvement when the columns you have indexed contain a wide variety of data.
  • Indexes can optimize your queries when those queries are returning a small amount of data
  • Always index fields that are used in joins between tables. This technique can greatly increase the speed of a join.

Inappropriate times to use Indexes:

  • Indexes can improve the speed of data retrieval, however they cause slow data updates. When performing large inserts, updates, deletes, you may want to drop the index before doing so and adding it back after success.
  • For small tables, the use of indexes does not result in any performance improvement.
  • Indexes should not be used on columns that contain high number of NULL values.
  • Do not index on fields that are manipulated frequently. This will result with additional overhead.
  • Indexes take up space within your database. If you’re trying to manage space, then be aware of the space being allocated and used by your indexes.

Why would you use an Index?

Indexes are typically used for three primary reasons:

1.) To enforce referential integrity.
2.) To facilitate the ordering of data based on the contents of the indexes field or fields.
3.) To optimize the execution speed of queries.

Syntax to Create Indexes on SQL Server:

CREATE INDEX NameOfIndex
ON TableName(Column1)

You also have the capability to store the column information in ASC or DESC order. Indexes help sort all of the information in the tablebased on the column you’re sorting on.

CREATE INDEX NameOfIndex
ON TableName(Column1 ASC)
-- No need to specify ASC for ascending order since this is the default behavior.

Sunday, November 11, 2012/Author: Chad Nash/Number of views (38301)/Comments (-)/ Article rating: No rating
Categories: In The Flow
Tags:

SQL Server Constraints

Getting Started:

Constraints are rules assigned to columns in your SQL tables. The main use of a Constraint is to help ensure that data integrity measures are met. Data integrity is very important. The whole idea of Data Integrity is to maintain a consistency of data. Constraints assist with Data Integrity, referential integrity(Primary Keys and Foreign Keys), and aids Normalization for your Database tables.

Data Integrity:

Lets say that we have a SQL table named “Contacts” and one of the columns in this table is “PhoneNumber”. Now when you think of storing a phone number, there’s many formats that come to mind:

  1. (xxx) xxx – xxxx
  2. xxx-xxx-xxxx
  3. xxxxxxxxxx

Data Integrity is ensuring that you pick a format for “Phone Numbers” and stick to that format.  You would be violating Data Integrity if you chose to store format #1, #2 and #3 into the PhoneNumber column of the Contacts Table.

 

Types of Constraints:

On SQL Server, there are many types of Constraints that you can utilize. Below are the most commonly used:

  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE
  • NOT NULL
  • CHECK

Using “PRIMARY KEY” Constraint:

The Primary Key Constraint, enforces that a column value must be unique and specifies the column as the Primary Key.

Example of Creating a Primary key on a CREATE TABLE statement:
-----------------------------------------------

CREATE TABLE TableName
(
[ID] INT IDENTITY(1,1) PRIMARY KEY,
[Column1] NVARCHAR(200),
[Column2] NVARCHAR(200)
)

-----------------------------------------------

You can add a Primary Key Constraint to an existing table:
-----------------------------------------------

ALTER TABLE TableName
ADD CONSTRAINT Give_Constraint_a_Name PRIMARY KEY(ColumnName)

-----------------------------------------------

Using “FOREIGN KEY” Constraint:

The Foreign Key Constraint allows you to specify that a column in your table is the Primary key to another table, thus granting you linking access to the other table via the value in this column. This promotes referential integrity, meaning that you're making your SQL Table a dependent to another table(Child of Parent).

I’ve created a simple example, there are two tables “Table1” and “Table2”":
-----------------------------------------------

/*Parent Table */

CREATE TABLE Table1
(
[Table1ID] INT IDENTITY(1,1) PRIMARY KEY,
[Column1] NVARCHAR(200),
[Column2] NVARCHAR(200),
[Column3] NVARCHAR(200)
)

/*Dependent Table */

CREATE TABLE Table2
(
[Table2ID] INT IDENTITY(1,1) PRIMARY KEY,
[Table1ID] INT FOREIGN KEY REFERENCES Table1 (Table1ID),
[Column1] NVARCHAR(200),
[Column2] NVARCHAR(200),
[Column3] NVARCHAR(200)
)

-----------------------------------------------

NOTE: By adding a FOREIGN KEY constraint t

Tuesday, November 6, 2012/Author: Chad Nash/Number of views (39826)/Comments (-)/ Article rating: No rating
Categories: In The Flow
Tags:
RSS
12345

Enter your email below AND grab your spot in our big giveaway!

The winner will receive the entire Data Springs Collection 7.0 - Designed to get your website up and running like a DNN superhero (spandex not included).

  
Subscribe