Thickbox(3.1) not working with latest jQuery?

Issue while using Thickbox(3.1) jquery

We have implemented thick box jquery image popup in one of our products and it is not working properly. But the refered site shown that it is working fine. We have debugged the issue and found that the issue happening once the jquery trying to get all images with same rel attribute. In the refered site it is achieved by the code

       TB_TempArray = $("a[@rel="+imageGroup+"]").get();
 

Here we are getting error and was not able to get all images with same attribute. After the long checking we have found that, the issue is due to the latest jquery files that we used. In latest jquery @ is not valid, so we have removed the @ from above code and it is working fine now. 

How to get all images with same rel attribute in JQuery

    
      TB_TempArray = $("a[rel="+imageGroup+"]").get();
 

We are hoping that someone may have great help fron this post, because we were spending so many time for resolving the issue.

How to send mail using Gmail in ASP.Net with Godaddy Server?

Cannot send email in ASP.Net through GoDaddy Servers?

In ASP.Net we can sennd mail to anyone using gmail smtp server. We have created codes for sending mail using gmail credentials and it is working fine in local. But when we uploaded to the server we are unable to send mail using the options. The thing is that we are using GoDaddy Server as hosting server and it is not supported the gmail smtp server to send mail.

How to Send mail in ASP.Net with GoDaddy Server

Inorder to send mail using ASP.Net we got a support from godaddy department and now it is working fine. Following are the codes for sending mail in ASP.Net with GoDaddy Server:

using System.Web.Mail;

public static void sendEmail(EmailDetails objEmail)
        {
            try
            {
                string SERVER = ConfigurationSettings.AppSettings["MailServer"].ToString();

                MailMessage oMail = new System.Web.Mail.MailMessage();
                oMail.From = objEmail.From;
                oMail.To = objEmail.To;
                oMail.Cc = ConfigurationSettings.AppSettings["AdminEmailID"].ToString();
                oMail.Subject = objEmail.Subject;
                oMail.BodyFormat = MailFormat.Html;   // enumeration
                oMail.Priority = MailPriority.High;   // enumeration
                oMail.Body = objEmail.Message;
                SmtpMail.SmtpServer = SERVER;
                SmtpMail.Send(oMail);
                oMail = null; // free up resources
            }
            catch (Exception ex)
            {
                throw;
            }
        }

Here we have a object ‘objEmail’ which holds the mail details such as from address, to address, subject etc. We have to use System.Web.Mail class for send mail. Here we are taking server details from web.config as given below.

  <add key="MailServer" value="mail.gmail.com" />

 

When we are running in local system the above code is working fine, but when we uploaded to goDaddy server send mail is not working. Then we have to change mail server details in web.config to following one.

<add key="MailServer" value="relay-hosting.secureserver.net" />

How to create a news scroller using HTML marquee.

Very simple News Scroller using HTML

In most of the website we can see that there is a portion for updates from the company. This updates can be shown by javscript,jquery,ajax etc technologies and it should be some effort also we have to include some pages for this.

For eg if we are implemented javscript newsticker the user’s browser must enable javascript, otherwise nothing going to happen. So the better method is if we can create a simple news ticker by using html elements only, it will be more faster and reliable in all browsers.

Following are the very simple and attractive vertical news ticker using HTML elements only. (Without using jquery,js,ajax..) So it should be work in all browsers even in the javascript disabled mode. We can give more attractive color and style for the news ticker.

Simple HTML code for showing news updates.

<div style="width: 180px; text-align: center; margin-top: 30px; 
margin-bottom: 2px;
        font-weight: bold;">
        Announcements</div>
    <div id="news-container">
        <marquee bgcolor="#ffffff" scrollamount="1" height="80" 
direction="up" loop="true"
            width="95%">
        We offers shopping cart <span style="color: blue;">
domain hosting free</span>
for limitted customers.
        <br /><br />
        You can choose your own style,color and domain 
for your shopping site.
        <br /><br />
        Supershope has largest number of viewers in the world. 
<span style="color: blue;">
You can sell your products here.</span><br />
        <br />
        <span style="color: blue;">Gift Paintings on Sale..!</span> 
There are thousands of
paintings available in supershope.
        Please contact us to buy paintings for affordable price.
        <br /><br />
        The Shopping cart product with <span style="color: blue;">
'supershope.com' domain also
            avaliable </span>for first customer with special package.
    </marquee>
    </div>

Where is simple HTML news ticker implemented

You can see the implemented HTML news ticker in the left hand side of the websit supershope used as Announcements section

Pagination Stored Procedure in MySQL

Simple steps to create pagination store procedure in MySQL

We have already posted how to do pagination in sql server in last post. But in the case of MySQL as database we need to create procedure some little bit different from SQL. MySQL provides a technique to fetch some small amount of data by giving starting row and end row using keyword LIMIT.

How to select paging data from MySQL using Limit Keyword

Limit keyword is used to fetch paging data from mySQL. We can send parameter as firstRow and lastRow that we need to fetch from the table.

Eg : Select * from items Limit 0,10   >> return firt 10 records
     Select * from items Limit 11,10   >> return 10 records starts from 11

But the the problem is that in stored procedure if we send fromRow and lastRow as parameter, we are unable to set this parameter with Limit keyword, because this facility is not supported by mySQL.

ie:  Select * from items Limit 0,10 >> it will work
     Select * from items Limit firstRow,lastRow >> It will not work

Dynamic query in pagination stored procedure in mySQL

For resolving the issue, we need to create dynamic stored procedure and execute the statement. Dynamic query is nothing but we are dynamically creating query, means set a query to variable and run this variable using EXECUTE keyword.

Following Pagination stored procedure accept firstRow and lastRow parameter and create a dynamic query and executed. It will send only the records between firtRow and lastRow. In order to maintain the pagination in development we need to get total number of records, here it is achieving by the query select found_rows().

 

DELIMITER $$

DROP PROCEDURE IF EXISTS `myDB`.`usp_getItems` $$
CREATE PROCEDURE ` myDB `.`usp_getItems` (
firstrow int,
lastrow int
)
BEGIN

set @sql = concat(
'SELECT items.ID,items.Name,items.Description,items.Overview,
items.Price,items.Image,items.HasOffer,'
, 'items.OfferPrice,items.OfferDescription,
items.ItemCode,category.ID as CategoryID,'
, ' category.Name as category,subcategory.ID as SubCategoryID, '
, 'subcategory.Name as subcategory, items.IsActive, 
items.ReleaseDate, items.Specefications '
, 'FROM items INNER JOIN subcategory ON '
, 'items.SubCategoryID = subcategory.ID 
INNER JOIN category ON subcategory.CategoryID = category.ID '
, ' ORDER BY Name LIMIT '
, firstrow , ',' , lastrow
);

/*select @sql; */

PREPARE STMT FROM @sql;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;

select found_rows();

END $$

DELIMITER ;

Pagination Stored Procedure in SQL

When listing large amount of data from database, we have to face performance issue in ASP.net pages. There is a default paging mechanism available for some listing controls like Gridview, Listview etc. But all of them have to get total amount of data for manipulation, and from that data they are listing small amount of data using paging mechanism.

 That means, if there are 1 crore rows of data in the table, first of all we need to query out this all records and set to listing control. So it will take very long time to query out this amount of data from the table. 

Better method of pagination in SQL 

So the better mechanism for pagination is, we need to query only small amount of data that we need to display to the user at a time. In order to fetch this function, we have to create a store procedure in SQL with start and end row as parameter. Then If we pass 21 and 30 to the following procedure it will return only 21 to 30 (10 records) and total number of records. So it will be very fast mechanism for pagination.

Pagination Stored Procedure in SQL

 

CREATE PROCEDURE [dbo].[scheduler_view_syncjobs_paging]
@fromRow INT,
@toRow INT
AS
BEGIN

WITH SyncJobs AS
(
      SELECT coalesce(instance.Schedule_Time,
scope.ScheduleTime,getdate()) as ScheduleTime
    ,scope.ScopeName
    ,instance.Status
      ,instance.Schedule_Time as starttedTime
      ,logs.logDetails
      ,ROW_NUMBER() OVER (ORDER BY scope.ScopeID DESC) AS 'RowNumber'
      FROM scheduler_scope scope
      LEFT JOIN scheduler_SyncInstance instance on 
instance.ScopeId=scope.Scopeid
      LEFT JOIN scheduler_logs logs on 
logs.InstanceID=instance.InstanceId
)
SELECT Count(*) as TotalCount
FROM SyncJobs;

WITH SyncJobs AS
(
      SELECT coalesce(instance.Schedule_Time,
scope.ScheduleTime,getdate()) as ScheduleTime
    ,scope.ScopeName
    ,instance.Status
      ,instance.Schedule_Time as starttedTime
      ,logs.logDetails
      ,ROW_NUMBER() OVER (ORDER BY scope.ScopeID DESC) AS 'RowNumber'
      FROM scheduler_scope scope
      LEFT JOIN scheduler_SyncInstance instance on 
instance.ScopeId=scope.Scopeid
      LEFT JOIN scheduler_logs logs on 
logs.InstanceID=instance.InstanceId
)
SELECT *
FROM SyncJobs
WHERE RowNumber BETWEEN @fromRow AND @toRow;

END

GO

How to find out the tables and columns having particular data in SQL?

How to find out the tables and columns that hold the particular data

 In some of the scenario a developer have to check is there any particular value is stored in the any of the table in the entire db. It is very hard to find out the column and table that holds the particular data, by calling select query for each and every table in the database.

Following store procedure will search the particular data that we are given as the paramaeter to stored procedure, in the all tables and coloumns in the entire db and return the results. If we want to perfect match of the search key given the second parameter as 1 else 0.

 StoredProcedure for search a keyword in the entire database.

CREATE PROCEDURE FindMyData_String
    @DataToFind NVARCHAR(4000),
    @ExactMatch BIT = 0
AS
SET NOCOUNT ON

DECLARE @Temp TABLE(RowId INT IDENTITY(1,1),
SchemaName sysname,
TableName sysname,
ColumnName SysName,
DataType VARCHAR(100), DataFound BIT)

INSERT  INTO @Temp(TableName,SchemaName, ColumnName, DataType)
SELECT  C.Table_Name,C.TABLE_SCHEMA, C.Column_Name, C.Data_Type
FROM    Information_Schema.Columns AS C
        INNER Join Information_Schema.Tables AS T
            ON C.Table_Name = T.Table_Name
    AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
WHERE   Table_Type = 'Base Table'
        And Data_Type In
        ('ntext','text','nvarchar','nchar','varchar','char')

DECLARE @i INT
DECLARE @MAX INT
DECLARE @TableName sysname
DECLARE @ColumnName sysname
DECLARE @SchemaName sysname
DECLARE @SQL NVARCHAR(4000)
DECLARE @PARAMETERS NVARCHAR(4000)
DECLARE @DataExists BIT
DECLARE @SQLTemplate NVARCHAR(4000)

SELECT  @SQLTemplate = CASE WHEN @ExactMatch = 1
    THEN 'If Exists(Select *
                  From   ReplaceTableName
                  Where  Convert(nVarChar(4000), [ReplaceColumnName])
                               = ''' + @DataToFind + '''
                  )
             Set @DataExists = 1
         Else
             Set @DataExists = 0'
    ELSE 'If Exists(Select *
                  From   ReplaceTableName
                  Where  Convert(nVarChar(4000), [ReplaceColumnName])
                               Like ''%' + @DataToFind + '%''
                  )
             Set @DataExists = 1
         Else
             Set @DataExists = 0'
    END,
    @PARAMETERS = '@DataExists Bit OUTPUT',
    @i = 1

SELECT @i = 1, @MAX = MAX(RowId)
FROM   @Temp

WHILE @i <= @MAX
BEGIN
    SELECT  @SQL = REPLACE(REPLACE(@SQLTemplate, 'ReplaceTableName',
    QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName)),
    'ReplaceColumnName', ColumnName)
    FROM    @Temp
    WHERE   RowId = @i

    PRINT @SQL
    EXEC SP_EXECUTESQL @SQL, @PARAMETERS, @DataExists = @DataExists OUTPUT

    IF @DataExists =1
        UPDATE @Temp SET DataFound = 1 WHERE RowId = @i

    SET @i = @i + 1
END

SELECT  SchemaName,TableName, ColumnName
FROM    @Temp
WHERE   DataFound = 1
GO

How to get size of the database in SQL / SQL Azure?

Some of the case database exceeds the db size and got error while trying to insert new row into the db. Most of the case this will happen after some years of using database. So we cannot identify the issue very quickly and may be our application need to shut down for some days. To monitoring this issue we can create a user interface (if we needed) which showing currently used size of the database.

Query for getting size of the database in SQL / SQL Azure

To get the table wise size we can use following query, which returns the name of the tables with size of the table.

select    
      sys.objects.name, sum(reserved_page_count) * 8.0 / 1024 as size_in_MB 
from    
      sys.dm_db_partition_stats, sys.objects 
where    
      sys.dm_db_partition_stats.object_id = sys.objects.object_id
group by sys.objects.name

To get the entire database used size we can use following query.

select
      sum(reserved_page_count) * 8.0 / 1024 as size_in_MB
from
      sys.dm_db_partition_stats
GO