sql return no records found if there is no data in table

I am facing an issue in which if i don't have any record in table so i need to show No record is found.

DECLARE  @customerid  AS numeric=14922  
select 'test' as 'Column',(case when  IsEnabled=1 then 'Yes' else 'No' end)as 'Status' from customer where Customer_id=@customerid
UNION ALL
SELECT  'test' as 'Column','No' AS Status
WHERE NOT EXISTS (SELECT 1
   FROM customer S
   WHERE s.Customer_id = @customerid)
 
   UNION ALL
   select 'test' as 'Column',(case when  IsEnabled=1 then 'Yes' else 'No' end)as 'Status' from customer where Customer_id=@customerid
UNION ALL
SELECT  'test' as 'Column','No' AS Status
WHERE NOT EXISTS (SELECT 1
   FROM customer S
   WHERE s.Customer_id = @customerid)
   

Use of COALESCE

Dealing with parameter when it send null or with value normally we user condition like this
WHERE  (user_name = @user_name  OR @user_name IS NULL )
There is sql function that do this thing smartly called COALESCE
WHERE  (user_name = COALESCE(@user_name,user_name)
Above where clause is same as the we are using COALESCE.For further user of COALESCE read this article its very usefull.

Reference :
http://www.sqlbook.com/sql-string-functions/sql-coalesce/

javascript replace not replacing all occurrences

I am facing an issue where i need to replace all the character in string.
replace function will not work. it will replace only the first value and the rest of remain same so you need to use RegExp.

use this sample code to repalce all "#####" with "'"


var myval="Sums#####ka Oblast#####tests#####sunny#####abcs#####oks#####"
var regex1 = new RegExp('#####', 'g')
myval = myval.replace(regex1,"'");
alert(myval)


Refernce :
http://stackoverflow.com/questions/22145822/javascript-replace-all-comma-in-a-string

new RegExp('#####', 'g') // g means perform a global match (find all matches rather than stopping after the first match

How to track the user selected values in pages

As i discuss yesterday in which i track the time spend of the user on a single page there is a parameter variable as well .Please go to the link
https://engineerhacks.blogspot.ae/2017/03/how-to-measure-time-spent-on-page.html

so in parameter variable i need to track  what are the parameter user is selected  on pages.Currently i am working in MIS section so there are lots of reports. So I need to track  all the parameters that  generate report. So for now my requirements is to capture all the text boxes and drop downs values. you can modify this code as you requirements .

As always suggestion coming from seniors you can do it by passing in query string .... or you have to do one by one single pages because you don't know
the text boxes ids etc :)

I think differently  i think i can do this by using jquery confuse ???? :) . let me explain

Master page is perfect for this so when the user submit a button to generate reports it always input type="Submit" in my case i am lucky :)

so what i did i make a function that  find the click event for submit button and find all the fill text boxes and selected drop down values.

I also make
 <asp:HiddenField ID="hdn_parm" runat="server" /> 
 it will send the value to the server side


   <script type="text/javascript">
        var arr_txt = [];
        var arr_ddl = [];
 
        $(document).ready(function () {
            function BindValues() {
                $('input[type="text"]').each(function () {
                    if (this.value != '') {
                        var id = this.id;
                        id = id.split("_").pop();
                        arr_txt.push("ID: " + id + " : " + this.value);
                    }
                });

                $("select").each(function () {
                    var id = this.id;
                    id = id.split("_").pop();
                    if ($(this).find('option:selected').text() != '') {
                        arr_ddl.push("ID: " + id + " : " + $(this).find('option:selected').text());
                    }
                });

            }
         
            $('input[type="Submit"]').click(function (event) {
                BindValues();
             
                $("input[id*='hdn_parm']").val(" TextBox ID " + arr_txt.join(",") + " Drop Down ID " + arr_ddl.join(","));
                var parm_selection = $("input[id*='hdn_parm']").val();
                $.ajax({
                    url: 'pagename.aspx/parameters',
                    type: 'POST',
                    data: JSON.stringify({
                        parm_selection: parm_selection
                    }),
                    contentType: 'application/json; charset=utf-8',
                    dataType: "json",
                    success: function (response) {
                        //your success code
                    },
                    error: function (request, status, error) {
                       // console.log(request.responseText);
                     
                    }
                });
            });    
        });


Server Side :

[WebMethod(EnableSession = true)]
public static void parameters(string parm_selection)
{

// in parm_selection you can get all the selected drop downs and text boxes values here 

}

How to measure a time spent on a page ?

I have to assign a task in which I have to calculate  how much time he/she spend on page.
For example we have multiples pages and we have menu too that is assign  unique Ids that bind from our menu table.
So what first thing in my mind is come every time when i click the menu  it will go the Default.Master page on Page_Load method.
So what I did i make a simple function that call on Page_Load method and the function is just pass the login user name, page name ,Current time and IP that it :) rest of the thing i handle in SQL .
So my table structure is like .

CREATE TABLE [dbo].[log](
[id] [int] IDENTITY(1,1) NOT NULL,
[user_name] [varchar](100) NOT NULL,
[page_name] [varchar](500) NOT NULL,
[currenttime] [datetime] NULL,
[spendtime] [int] NULL,
[page_id] [int] NULL,
[parameter] [varchar](5000) NULL,
[ip] [varchar](100) NULL
) ON [PRIMARY]

@parameter is secret ;) i will tell you in next post that how to get all the user selected drop downs and text boxes values.

Ok when I make a function that will call this Store Procedure .

CREATE PROCEDURE [dbo].[sp_name]          
@user_name varchar(100),          
@page_name varchar(500),          
@currenttime DATETIME,        
@ip varchar(100),      
@parameter varchar(5000),  
@is_download int        
AS          
         
 declare @page_id int    
   SELECT   @page_id=page_id from page where page_name = '' + @page_name + ''  ;  
  BEGIN        
 
   INSERT INTO log          
     ( user_name ,page_name ,currenttime,ip,parameter,page_id)          
   VALUES  ( @user_name ,@page_name ,@currenttime,@ip,@parameter,@page_id)                
   END

   the magic is here :) I made a trigger that will calculate the time for that specific user

 CREATE TRIGGER [dbo].[LogOnInsert]
ON [dbo].[log]
FOR INSERT
AS

declare @InsertedUser as nvarchar(50)
declare @InsertedTime as datetime
declare @PreviousID as int
declare @PreviousTime as datetime
declare @ip as varchar(100)


select @inserteduser = i.user_name, @insertedTime = i.currenttime,@ip=i.ip
from Inserted i

SELECT @previousid = id, @previoustime = currenttime FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY ID desc) AS rownumber,
    id, currenttime
  FROM log
where user_name = @inserteduser and ip=@ip
) AS foo
WHERE rownumber = 2
declare @timediff as int
set @timediff = DATEDIFF(SECOND, CONVERT(DATETIME, CONVERT(VARCHAR, @PreviousTime, 120)), CONVERT(DATETIME, CONVERT(VARCHAR, @InsertedTime, 120)))

update log
set spendtime = @timediff -- value in second
where id = @PreviousID


and this is function that i used to get the user IP

private string GetUserIP()
{
string ipList = Request.ServerVariables("HTTP_X_FORWARDED_FOR");

if (!string.IsNullOrEmpty(ipList)) {
return ipList.Split(',')(0);
}

return Request.ServerVariables("REMOTE_ADDR");
}

How to find duplicate data on the basis of column name in Sql Server

If you have to find the record of duplicate data for example in my case i need to find out how many user are exist with the same user name.


SELECT username, COUNT(*)
FROM  Customers
GROUP BY username
HAVING COUNT(*) > 1

It will show all the record with number of duplicate items.

SQL MERGE statement to insert-update data

If you want copy data from one table to another you can use MERGE. In my case I need to Insert/update different database table with  some fields so you can check I made a condition in ON ( T.UserName = S.UserName  AND t.customerid = s.customerid   ) its means if the customerid and UserName is not match it will insert otherwise I will update the T.First_Name.


CREATE PROCEDURE [sp_name] 
AS 
  BEGIN 
    MERGE tcustomers                         AS t 
    using            a_database.dbo.customer AS s 
    ON ( 
                                      t.username = s.username 
                     AND              t.customerid = s.customerid ) 
    WHEN NOT matched BY target THEN 
    INSERT 
                 ( 
                              col 
                 ) 
                 VALUES 
                 ( 
                              s.col 
                 ) 
    WHEN matched THEN 
    UPDATE 
    SET    t.first_name = s.first_name , 
  end