sqldatasource select with argument then bind to dataview

 <asp:SqlDataSource ID="srcPaperTypes" runat="server" ConnectionString="<%$ ConnectionStrings:ODAC_ConnString %>" ProviderName="<%$ ConnectionStrings:ODAC_ConnString.ProviderName %>"
                     SelectCommand="SELECT        DOC_SER, DOC_NAME
FROM            SHR.DOCS_SCAN
WHERE        (SYSNO = 7)
AND DOC_SER=:DOC_SER">
                    <SelectParameters>
                        <asp:Parameter Name="DOC_SER" />
                    </SelectParameters>
                </asp:SqlDataSource>






--------------------------------------------------------------------
 srcPaperTypes.SelectParameters.Clear();
        srcPaperTypes.SelectParameters.Add(new Parameter("DOC_SER",TypeCode.String, papertype.ToString()));
        DataSourceSelectArguments args = new DataSourceSelectArguments();
        DataView DV = new DataView();
        DV = (DataView)srcPaperTypes.Select(DataSourceSelectArguments.Empty);
        if(DV != null)
        result = DV.Table.Rows[0]["DOC_NAME"].ToString();

sql server trigger for update table auditing keep track changes

http://www.dotnet-tricks.com/Tutorial/sqlserver/OPUH170312-After-Trigger,-Instead-of-Trigger-Example.html

sql server 2008 add shared network mapped drive to backup select screen UNC SQL SERVER

if you have shared folder or network drive and you need to access it from sql server management studio or add this path to backup plan
use this command

 EXEC xp_cmdshell 'net use y: \\sharedLoc /user:username psw'

ex
 EXEC xp_cmdshell 'net use Z: \\10.5.39.100\Backups /user:sqluser P@$$w0rd'



which Y is the name of network drive will appear in SSMS screen
sharedLoc  is location for shared folder such as \\10.10.10.5\x

username  : permitted user to access shared folder
psw    password needed for user to access the folder



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

You need to enable it. Check out the Permission section of the xp_cmdshell MSDN docs:

http://msdn.microsoft.com/en-us/library/ms190693.aspx:

-- To Add Backup Network Shared Location Run the Following Script
-- 1- Change show advanced Options to one
EXEC sp_configure 'show advanced options', 1;
GO
-- 2-Reconfigure
RECONFIGURE;
GO
-- 3-Enable command Shell 
EXEC sp_configure 'xp_cmdshell',1
GO
--4-Reconfigure Again
RECONFIGURE
GO
--**************************************************** if the following commands not working let it run using command prompt net use 
-- To Delete connection To Drive
--EXEC XP_CMDSHELL 'net use */delete   /persistent:yes'


-- set Drive Name and Network Location.
EXEC XP_CMDSHELL 'net use R: \\172.19.8.65\FileServer\Eservices\ServersBackups\SQLServerBackps\ProdDBsBackups /USER:MEDMWA\SQLESER $@l@dmin'

check corrupted .tif tiff file using vb.net OpenTiff Library

-install package related .tif using nuget
PM> Install-Package BitMiracle.LibTiff.NET

Imports BitMiracle.LibTiff.Classic

 Public Shared Function CheckCorruptedTiff(ByVal path As String) As Boolean
        Dim result As Boolean = False
        Using image As Tiff = Tiff.Open(path, "r")
            If image Is Nothing Then
                result = True
            End If

            Dim numberOfDirectories As Integer = image.NumberOfDirectories()
            For i As Integer = 0 To numberOfDirectories - 1
                image.SetDirectory(CShort(i))

                Dim width As Integer = image.GetField(TiffTag.IMAGEWIDTH)(0).ToInt()
                Dim height As Integer = image.GetField(TiffTag.IMAGELENGTH)(0).ToInt()

                Dim imageSize As Integer = height * width
                Dim raster As Integer() = New Integer(imageSize) {}

                If Not image.ReadRGBAImage(width, height, raster, True) Then
                    result = False
                End If
            Next
        End Using
        Return result
    End Function

Prevent Duplicate Event when inserting or Updating when refresh Page Using C# asp.net

To Prevent Duplicate Event on page after insert or update if user press F5 to refresh page you can handle it using this code sample
1- in page_load when !postback create session and save datetime to that session

  if (!IsPostBack)
        {
            Session["update"] = Server.UrlEncode(System.DateTime.Now.ToString());
}

2- save the session value to view State on Page_PreRender Event like this
   protected void Page_PreRender(object sender, EventArgs e)
    {
        ViewState["update"] = Session["update"];
    }

3- on Button_Click Event You will Check That viewstate value equal to session value if true do action and then update session value with the new datetime .
on the second time the condition will not executed because of change in value between session value and viewstate value.
 protected void BTN_SEND_Click(object sender, EventArgs e)
    {
if (Session["update"].ToString() == ViewState["update"].ToString())
                {
//write your action code here
  Session["update"] = Server.UrlEncode(DateTime.Now.ToString());
                  }
else
{
     //write exception that no duplicate allowed or do any thing else.
}
}

sql server can't connect using remote computer

the soultion is to add port in connection string , shit
http://stackoverflow.com/questions/18462821/cannot-connect-remotely-to-sql-server-instance

An unexpected error occurred in the Oracle Data Provider for .NET. Please contact the provider vendor to resolve this problem

Error Message : An unexpected error occurred in the Oracle Data Provider for .NET. Please contact the provider vendor to resolve this problem

Solving : 
Delete any registry keys where you find {D601BB95-E404-4A8E-9F24-5C1A462426CE} (Oracle Developer Tools for .NET)

An unexpected error occurred in the Oracle Data Provider for .NET. Please contact the provider vendor to resolve this problem

Error Message : An unexpected error occurred in the Oracle Data Provider for .NET. Please contact the provider vendor to resolve this problem

Solving : 

javascript full screen fullscreen window for internet explorer script

  <meta http-equiv="X-UA-Compatible" content="IE=8" />
<body dir="rtl" onload="FullScreenWindow();">



function FullScreenWindow() {
    //window.moveTo(0,0);
    //resizeTo(screen.availWidth,screen.availHeight);

    window.moveTo(0, 0);
    if (document.layers) // navigator 4+
    {
        var deltaX = screen.availWidth - window.outerWidth;
        var deltaY = screen.availHeight - window.outerHeight;
        window.resizeBy(deltaX, deltaY);
    } else if (document.all) // exploder 4+
    { resizeTo(screen.availWidth, screen.availHeight); }

}

gridview custom pager user control

http://yugalpandya.wordpress.com/2013/09/30/custom-paging-user-control-for-large-amount-of-data-in-gridview/

Elmah Error Logging Http Handler Links

http://www.hanselman.com/blog/NuGetPackageOfTheWeek7ELMAHErrorLoggingModulesAndHandlersWithSQLServerCompact.aspx

http://docs.nuget.org/docs/start-here/Using-the-Package-Manager-Console

Steps for apply elmah for Project (WebApplication - WebSite)
1- open your solution using visual studio.
2- Open Package Manager Console
3- Install-package elmah
4-install-package elmah.sqlserver

5- go to your solution folder and  find Package Folder then elmah.sqlserver.1.2 you will find sql file

Elmah.SqlServer.sql

run the script to your sql server to create elmah database.

6- after creating elmah script on your sql server Change Connection String in your web.config to be linked to your sql server instance.

7- run your project to open elmah.axd to get errors occured as http errors

8- To Apply Elmah For All Solution to log All Errors you should write this code in your functions exception handling

try

{

// your function code

}

   Catch ex As Exception

                Elmah.ErrorSignal.FromCurrentContext().Raise(ex)

            End Try

9- to enable send Mail Using Elmah to your Mail instead of monitor errors using elmah.axd

you should change mail configuration for elmah in your web.config 

<elmah>

    <security allowRemoteAccess="true" />

    <errorLog type="Elmah.SqlErrorLog, Elmah" connectionStringName="ELMAH_CONSTR" />

    <errorMail from="portal@mwa.gov.sa" to="msibrahim@mwa.gov.sa" subject="خطأ في البوابة الداخلية تجريبي" async="false">

    </errorMail>

    <errorFilter>

      <test>

        <or>

          <regex binding="Exception.Message" pattern="^The operation has timed out$" />

          <and>

            <equal binding="HttpStatusCode" value="404" type="Int32" />

            <equal binding="HttpStatusCode" value="504" type="Int32" />

            <regex binding="FilterSourceType.Name" pattern="mail" />

          </and>

          <and>

            <regex binding="Exception.Message" pattern="^Logon failure: unknown user name or bad password" />

            <regex binding="Exception.Message" pattern="^Microsoft.Exchange.WebServices.Data.AutodiscoverLocalException: The Autodiscover service couldn't be located." />

            <regex binding="FilterSourceType.Name" pattern="mail" />

          </and>

        </or>

      </test>

    </errorFilter>

  </elmah>

10- you should edit your smtp mail server configuration in IIs Or your Web.config file

<system.net>

    <mailSettings>

      <smtp>

        <network host="your smtp mail server" port="25"  password="****" userName="******" />

      </smtp>

    </mailSettings>

  </system.net>

11- now you will receive automatic mail when any error occurs for your web site.

thanks.

SQL SERVER LOOP RESULT SET - QUERY BY TEMP TABLE SAMPLE CODE

BEGIN
Drop table #TEMP_STAGES_STAGES
Declare @P_STAGE_ID Varchar(20)
SET ROWCOUNT 0
SELECT STAGE_ID ,STAGE_NAME into #TEMP_STAGES FROM dbo.WORK_FLOWS_LOAD_CURRENT_STAGES_BY_ORDER_FUNCTION(3, 1, 2);
SET ROWCOUNT 1
Select @P_STAGE_ID = STAGE_ID from #TEMP_STAGES
While @@rowcount <> 0
Begin
select * from MEDICAL_REPORTS_APPROVAL_STAGES Where  REQUEST_STAGE_ID = @P_STAGE_ID
PRINT(@P_STAGE_ID);
Delete from #TEMP_STAGES where STAGE_ID = @P_STAGE_ID
Select @P_STAGE_ID = STAGE_ID from #TEMP_STAGES
End
Set Rowcount 0
Drop table #TEMP_STAGES
END

jquery umalqura Calender with Gregorien Date Selector User Control Example

output :

description :
the article source build on ApI related
http://keith-wood.name/datepick.html

the Sample contains user Control with two textboxes the right one to select Umalqura date , the Second to select Gregorien Date .
when you select date (umalqura or gregorien ) the second date captured automatic using conversion method.

public Properties For User Control :
1- getGregorianDateText
return selected Gregorien date for the calender control.

2- getHijriDateText
get Hijri date using Umalqura Calender user selected .

3- SelectedCalendareDate
get or set the selected date for calender ,

4- SetHijriDate
set hijri date for the calender.
-------------------------------------------------------------
you can download Full Sample using this dropbox Link.
https://www.dropbox.com/sh/h840agq0v6xgnix/AAB2uJ-xLstnTQfzOCZTqOrza?dl=0

sql server Cursor template Example

using cursor in sql server :
cursor used to capture collection of result used after for looping or fetching in sub query .
example :

USE yourdbname
GO
declare cur cursor for
select  user_id from USERS
open cur
declare @id int
fetch next from cur into @id
while (@@FETCH_STATUS = 0)
begin
    print(@id)
    fetch next from cur into @id
end
close cur
deallocate cur
GO

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

to get values from cursor should declare variable or scalar table keep cursor values.

SQL SERVER PAGING OUTPUT DATA TO GRIDVIEW SAMPLE CODE

when you create gridview display multiple rows so you can divide rows query for sql server according your paging selection this will demanded to pass page size and page index to get query result .
this is best for performance to get from database rows needed to show not load all rows every postback for gridview paging.

sql server sample  code:

BEGIN
SET NOCOUNT ON
SET XACT_ABORT ON
---------------------------------------------------------------
DECLARE @INTPAGE INT =2 ;
DECLARE @INTPAGESIZE INT =10 ;
DECLARE @REQUEST_REPORT_NO VARCHAR(50) = NULL
   DECLARE @PATIENT_PROFILE_NO VARCHAR(50) = NULL
    DECLARE @REQUEST_STAGE_ID VARCHAR(50) = NULL
    DECLARE @REQUEST_STATUS_ID VARCHAR(50) = NULL
    DECLARE @FROM_DATE DATETIME = NULL
    DECLARE @TO_DATE DATETIME = NULL
   
DECLARE @INTSTARTROW INT;
DECLARE @INTENDROW INT;

SET @INTSTARTROW = (@INTPAGE -1) * @INTPAGESIZE + 1;
SET @INTENDROW = @INTPAGE * @INTPAGESIZE;  
--------------------------------------------------------------

WITH BLOGS AS
(
SELECT VR.*  ,
 ROW_NUMBER() OVER(ORDER BY VR.CREATE_DATE DESC) AS INTROW ,
      COUNT(VR.REQUEST_REPORT_NO) OVER() AS TOTAL_ROWS_COUNT
FROM VACATION_REPORTS_REQUEST VR
RIGHT JOIN WORK_FLOWS_TYPES_STAGES T ON VR.REQUEST_STAGE_ORDER = T.STAGE_ORDER
WHERE
        T.TYPE_ID = [DBO].[WORK_FLOWS_TYPE_GET_BY_USER_ID](6,VR.CREATE_BY_USER_ID)
  AND T.STAGE_ORDER =VR.REQUEST_STAGE_ORDER
  AND ([REQUEST_REPORT_NO] = @REQUEST_REPORT_NO OR @REQUEST_REPORT_NO IS NULL)
  AND  (@PATIENT_PROFILE_NO IS NULL OR VR.PATIENT_PROFILE_NO = @PATIENT_PROFILE_NO)
  AND  (@REQUEST_STAGE_ID IS NULL  OR T.STAGE_ID = @REQUEST_STAGE_ID)
  AND  (@REQUEST_STATUS_ID IS NULL OR VR.REQUEST_STATUS_ID = @REQUEST_STATUS_ID)
  AND (@FROM_DATE IS NULL OR VR.CREATE_DATE > = @FROM_DATE)
  AND (@TO_DATE IS NULL OR VR.CREATE_DATE < = @TO_DATE)
  AND (@FROM_DATE IS NULL OR
(@TO_DATE IS NOT NULL AND @FROM_DATE IS NOT  NULL AND  VR.CREATE_DATE BETWEEN @FROM_DATE AND @TO_DATE)
)
 
  )
 SELECT * FROM BLOGS
WHERE (@INTPAGESIZE =0 OR INTROW BETWEEN @INTSTARTROW AND @INTENDROW)
END

sql server send mail ftored Procedure + trigger

send mail stored
-----------------------------
USE [HES_Intranet]
GO
/****** Object:  StoredProcedure [dbo].[EmpWorkFlowSendEmail]    Script Date: 06/03/2014 05:45:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE[dbo].[EmpWorkFlowSendEmail]
@ISEMPMANGER AS BIT =0,
@EMAIL_TYPE AS INT =1 ,
@EMAIL_LANG   AS INT = 1 ,
 @REQ_ID INT  = NULL ,
 @REQUEST_DATE NVARCHAR(50) = N'',
 @WORK_FLOW_NAME NVARCHAR(100) = N'' ,
 @STAGE_NAME_CURRENT NVARCHAR(250) = N'' ,
 @STATUS_NAME_CURRENT NVARCHAR(250)  = N'' ,
 @REQUEST_OWNER_USER_FULL_NAME NVARCHAR(250)  = N'' ,
 @STAGE_NAME_NEXT NVARCHAR(250) = N'' ,
 @WORK_FLOW_TYPE_NAME NVARCHAR(1000) = N''  ,
 @USER_MAIL VARCHAR(200),
 @USER_FULL_NAME NVARCHAR(500) = N'' ,
 @NEXT_STAGE_USERS NVARCHAR(MAX) = N'' ,
 @REQUEST_DETAILS NVARCHAR(MAX) = N''
AS
BEGIN
--PRINT N'@WORK_FLOW_NAME = ' + RTRIM(CAST(@WORK_FLOW_NAME AS NVARCHAR(3000))) + N'.';
-- PRINT N'BODY = ' + RTRIM(CAST(@WORK_FLOW_TYPE_NAME AS NVARCHAR(3000))) + N'.';

SET NOCOUNT ON;
----------------------------------------------------------------------------------------------------------
------------------DEFINITION OF VARIABLES-----------------------------------------------------------------
----------------------------------------------------------------------------------------------------------
DECLARE @EMAILSUBJECT AS  NVARCHAR(MAX)
DECLARE @EMAILBODY AS  NVARCHAR(MAX)
DECLARE @MYLINKUSER AS  NVARCHAR(MAX) =N''
DECLARE @MYLINKMANAGER AS  NVARCHAR(MAX)
DECLARE @MNAGERTRVISIBLE AS  NVARCHAR(50)
DECLARE @ISMANGER AS INT
DECLARE @MANGERDISCRIPTIN AS NVARCHAR(20)
DECLARE @INFO AS  NVARCHAR(MAX)
DECLARE @CURRENTMESSAGE AS NVARCHAR(200)
DECLARE @HLEVEL AS INT
SET @EMAILSUBJECT=@WORK_FLOW_NAME + ' - ' + @WORK_FLOW_TYPE_NAME
SET @MYLINKMANAGER='';
SELECT @EMAILBODY= T.MESSAGEBODY  FROM  WORKFLOW_MAILTEMPLATES T
WHERE
[TYPE]=@EMAIL_TYPE
AND T.LANGUAGE_ID =@EMAIL_LANG
----------------------------------------------------------------------------------------------------------
IF (@ISMANGER=0)
SET @MANGERDISCRIPTIN =N' - '+ N'المكلف'
ELSE
SET @MANGERDISCRIPTIN = N' '
----------------------------------------------------------------------------------------------------------
--------------------------------FILL MESSAGE CONTENT------------------------------------------------------
----------------------------------------------------------------------------------------------------------

 --SET @MYLINK='HTTP://INTRANET.MOWE.GOV.SA/WEB/ADMIN/EMPWORKFLOW/REPORT/REPORT.ASPX?ORDERGID='+ CONVERT(NVARCHAR(50) , @ORDER_GID ) +'&REPORTTYPE='+CONVERT(NVARCHAR(50) , @ORDERTYPEID)+'&EMPID='+CONVERT(NVARCHAR(10) , @EMPID_SUBMITTED );
     --  SET @MYLINKUSER='HTTP://INTRANET.MOWE.GOV.SA/WEB/ADMIN/EMPWORKFLOW/REPORT/REPORT.ASPX?ORDERGID='+ CONVERT(NVARCHAR(50) , @ORDER_GID ) +'&REPORTTYPE='+CONVERT(NVARCHAR(50) , @ORDERTYPEID)+'&EMPID='+CONVERT(NVARCHAR(10) , @EMPID_SUBMITTED );
 SET @MYLINKUSER='';
   --SET @EMAILBODY= REPLACE(@EMAILBODY,'#EMAILSUBJECT#' ,@EMAILSUBJECT);
--SET @EMAILBODY= REPLACE(@EMAILBODY,'#CREATDATE#' ,@CREATEDATE);
--SET @EMAILBODY= REPLACE(@EMAILBODY,'#NOTE#' ,@CURRENTMESSAGE);
--SET @EMAILBODY= REPLACE(@EMAILBODY,'#EMPNAME#' ,(@EMPNAME_SUBMITTED+' '+@SECTIONNAMESUBMITTED));
--SET @EMAILBODY= REPLACE(@EMAILBODY,'#INFO#' ,@INFO);
--SET @EMAILBODY= REPLACE(@EMAILBODY,'#LINK#' ,@MYLINK);
-- SET @EMAILBODY= REPLACE(@EMAILBODY,'ID="MANGERTITELNAME" STYLE="VISIBILITY: VISIBLE"' ,'ID="MANGERTITELNAME" STYLE="VISIBILITY: HIDDEN"');
----------------------------------------------------------------------------------------------------------
-------------------------------SEND EMAIL TO SUBMITTED EMPLOYEE-------------------------------------------
----------------------------------------------------------------------------------------------------------
-- IF(@ISEMPMANGER=0)
-- BEGIN
----------------------------------------------------------------------------------------------------------
-------------------------------SEND EMAIL TO SUBMITTED EMPLOYEE-------------------------------------------
----------------------------------------------------------------------------------------------------------
SET @EMAILSUBJECT=N'' + @WORK_FLOW_NAME + N'  -  '  + N'' + @WORK_FLOW_TYPE_NAME
SET @EMAILBODY= REPLACE(@EMAILBODY,'#link#',@MYLINKUSER);
SET @EMAILBODY= REPLACE(@EMAILBODY,('#Emailsubject#'),N'' +@EMAILSUBJECT);
SET @EMAILBODY= REPLACE(@EMAILBODY,('#EMPNAME#'),N'' +@REQUEST_OWNER_USER_FULL_NAME);
SET @EMAILBODY= REPLACE(@EMAILBODY,('#CreatDate#'),N'' +@REQUEST_DATE);
SET @EMAILBODY= REPLACE(@EMAILBODY,('#Note#'),N'' +@STATUS_NAME_CURRENT);
SET @EMAILBODY= REPLACE(@EMAILBODY,('#Current_stage#'), N'' +@STAGE_NAME_CURRENT);
SET @EMAILBODY= REPLACE(@EMAILBODY,('#Next_stage#'), N'' +@STAGE_NAME_NEXT);
SET @EMAILBODY= REPLACE(@EMAILBODY,('#Next_Satge_users#'), N'' +@NEXT_STAGE_USERS);
SET @EMAILBODY= REPLACE(@EMAILBODY,('#Notified_User_Name#'), N'' +@USER_FULL_NAME);
SET @EMAILBODY= REPLACE(@EMAILBODY,('#info#'), N'' +@REQUEST_DETAILS);
--SET @EMAILBODY=N'هللو'
PRINT N'BODY = ' + RTRIM(CAST(@EMAILBODY AS NVARCHAR(MAX))) + N'.';
PRINT N'SUBJECT  = ' + RTRIM(CAST(@EMAILSUBJECT AS NVARCHAR(3000))) + N'.' ;
EXEC [MSDB].[DBO].SP_SEND_DBMAIL @PROFILE_NAME='HOSPITALMAIL',
@RECIPIENTS=@USER_MAIL,
-- @RECIPIENTS='ENGMOSBAH11@GMAIL.COM' ,
@SUBJECT=@EMAILSUBJECT ,
@BLIND_COPY_RECIPIENTS='ELSAYED.IBRAHIME@GMAIL.COM',
@copy_recipients ='ENG_MOSBAH11@HOTMAIL.COM',
@BODY=@EMAILBODY,
@BODY_FORMAT = 'HTML'

--DECLARE @C1 CURSOR
-- DECLARE @C1_USER_ID INT = NULL
--  DECLARE @C1_USER_FULL_NAME NVARCHAR(1000) = NULL
--  DECLARE @C1_USER_FULL_NAME_EN NVARCHAR(1000) = NULL
--  DECLARE @C1_USER_MAIL NVARCHAR(250) = NULL
-- SET @C1 = CURSOR FORWARD_ONLY STATIC FOR
-- SELECT  USER_ID  ,USER_FULL_NAME , USER_FULL_NAME_EN ,USER_EMAIL
-- FROM @TBL_USERS
-- OPEN @C1
-- FETCH NEXT
-- FROM @C1 INTO @C1_USER_ID ,@C1_USER_FULL_NAME ,@C1_USER_FULL_NAME_EN , @C1_USER_MAIL
-- WHILE (@@FETCH_STATUS = 0)
--  BEGIN

-- FETCH NEXT FROM @C1 INTO @C1_USER_ID ,@C1_USER_FULL_NAME ,@C1_USER_FULL_NAME_EN , @C1_USER_MAIL
--  END
--  CLOSE @C1
--  DEALLOCATE @C1
----------------------------------------------------------------------------------------------------------
-- END
-- ELSE
-- BEGIN
------------------------------------------------------------------------------------------------------------
----------------------------------SEND EMAIL TO MANGER -----------------------------------------------------
------------------------------------------------------------------------------------------------------------
-- IF NOT(@HLEVEL=998 OR @HLEVEL=999 )
-- BEGIN
-- SET @EMAILSUBJECT=@EMAILSUBJECT + N' - ' + @WORK_FLOW_NAME + N'-' + @WORK_FLOW_TYPE_NAME
-- SET @EMAILBODY= REPLACE(@EMAILBODY,'#LINK#',@MYLINKMANAGER);
-- --SET @EMAILBODY= REPLACE(@EMAILBODY,'#MANGERTITELNAME#' ,(N'<TR  CLASS="VIEW" ><TD  ALIGN="RIGHT" BGCOLOR="#FFFFCC" CLASS="STYLE3">' +@MANGERJOBTITEL + @MANGERDISCRIPTIN+ N'</TD><TD   ALIGN="RIGHT" CLASS="STYLE4" WIDTH="120PX">:سعادة </TD></TR>'));
-- SET @EMAILBODY= REPLACE(@EMAILBODY,('#EMAILSUBJECT#'),@EMAILSUBJECT);
-- SET @EMAILBODY= REPLACE(@EMAILBODY,('#EMPNAME#'),@REQUEST_OWNER_USER_FULL_NAME);
-- SET @EMAILBODY= REPLACE(@EMAILBODY,('#CREATDATE#'),@REQUEST_DATE);
-- SET @EMAILBODY= REPLACE(@EMAILBODY,('#NOTE#'),@STATUS_NAME_CURRENT);
-- SET @EMAILBODY= REPLACE(@EMAILBODY,('#INFO#'),N'المرحلة التالية : ' + @STAGE_NAME_NEXT);
-- EXEC [MSDB].[DBO].SP_SEND_DBMAIL @PROFILE_NAME='HOSPITALMAIL',
-- @RECIPIENTS='ENGMOSBAH11@GMAIL.COM' ,
-- @SUBJECT=@EMAILSUBJECT,
-- @BLIND_COPY_RECIPIENTS='ELSAYED.IBRAHIME@GMAIL.COM',
-- @BODY=@EMAILBODY,
-- @BODY_FORMAT = 'HTML'
-- END
------------------------------------------------------------------------------------------------------------
-- END
----------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------

END

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

trigger code

USE [HES_Intranet]

GO

/****** Object:  Trigger [dbo].[VACATION_REPORTS_APPROVAL_STAGES_SEND_MAIL]    Script Date: 06/03/2014 05:11:52 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

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

-- AUTHOR: <MOSBAH ELSAYED>

-- CREATE DATE: <31-05-2014>

-- DESCRIPTION: <TRIGGER TO SEND MAIL FOR USERS RELATED INSERTED APPROVAL ACTION >

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

ALTER TRIGGER [dbo].[VACATION_REPORTS_APPROVAL_STAGES_SEND_MAIL]

   ON [dbo].[VACATION_REPORTS_APPROVAL_STAGES]    AFTER INSERT 

AS 

BEGIN

SET NOCOUNT ON;

-------------------------------------------------------------DECLARE VARIABLES

-------------------------------------------------------------MAIL PASSED PARAMETERS

DECLARE @WORK_FLOW_ID INT = 6

DECLARE @REQ_ID INT 

DECLARE @CURRENT_STAGE_ID INT

DECLARE @CURRENT_STATUS_ID INT

DECLARE @REQUEST_DATE NVARCHAR(50) 

SELECT @REQ_ID = REQUEST_REPORT_NO , @CURRENT_STAGE_ID=REQUEST_STAGE_ID  ,@CURRENT_STATUS_ID=REQUEST_STATUS_ID 

FROM INSERTED

SELECT @REQUEST_DATE =CONVERT(NVARCHAR(50) , R.CREATE_DATE ,120)  

FROM  VACATION_REPORTS_REQUEST R WHERE R.REQUEST_REPORT_NO=@REQ_ID

DECLARE @REQ_CREATED_BY_USER INT =( SELECT R.CREATE_BY_USER_ID FROM VACATION_REPORTS_REQUEST R WHERE R.REQUEST_REPORT_NO=@REQ_ID)

DECLARE @WORK_FLOW_NAME NVARCHAR(100) = (SELECT W.WORK_FLOW_NAME  FROM  DBO.WORK_FLOWS W WHERE W.WORK_FLOW_ID=@WORK_FLOW_ID)

DECLARE @WORK_FLOW_NAME_EN NVARCHAR(100) =(SELECT W.WORK_FLOW_NAME_EN  FROM  DBO.WORK_FLOWS W WHERE W.WORK_FLOW_ID=@WORK_FLOW_ID)

DECLARE @STAGE_NAME_CURRENT NVARCHAR(250) =(SELECT DBO.WORK_FLOWS_STAGES_GET_NAME_BY_STAGE_ID((@CURRENT_STAGE_ID)))

DECLARE @STAGE_NAME_CURRENT_EN NVARCHAR(250) =(SELECT DBO.WORK_FLOWS_STAGES_GET_NAME_EN_BY_STAGE_ID(@CURRENT_STAGE_ID))

DECLARE @STATUS_NAME_CURRENT NVARCHAR(250) =(SELECT S.REQUEST_STATUS_NAME FROM DBO.VACATION_REPORTS_REQUEST_STATUS S 

 WHERE S.REQUEST_STATUS_ID=@CURRENT_STATUS_ID  )

DECLARE @STATUS_NAME_CURRENT_EN NVARCHAR(250) =(SELECT S.REQUEST_STATUS_NAME_EN FROM DBO.VACATION_REPORTS_REQUEST_STATUS S 

 WHERE S.REQUEST_STATUS_ID=@CURRENT_STATUS_ID  )

DECLARE @REQUEST_OWNER_USER_FULL_NAME NVARCHAR(250) = (SELECT USER_FULL_NAME FROM USERS WHERE USER_ID =@REQ_CREATED_BY_USER)

DECLARE @REQUEST_OWNER_USER_FULL_NAME_EN NVARCHAR(250) = (SELECT USER_FULL_NAME_EN FROM USERS WHERE USER_ID =@REQ_CREATED_BY_USER)

DECLARE @STAGE_NAME_NEXT NVARCHAR(250)=N''

DECLARE @STAGE_NAME_NEXT_EN NVARCHAR(250)=N''

DECLARE @WORK_FLOW_TYPE_NAME NVARCHAR(1000)=N''

DECLARE @WORK_FLOW_TYPE_NAME_EN NVARCHAR(1000)=N''

DECLARE @NEXT_STAGE_USERS NVARCHAR(MAX) = N''

DECLARE @NEXT_STAGE_USERS_EN NVARCHAR(MAX) = N''

DECLARE @REQUEST_DETAILS NVARCHAR(MAX) =N''

SELECT @REQUEST_DETAILS= DBO.VACATION_REQUESTS_REQUEST_DETAILS_MAIL_STRING(@REQ_ID)

DECLARE @REQUEST_DETAILS_EN NVARCHAR(MAX) =N''

  SELECT @REQUEST_DETAILS_EN = DBO.VACATION_REQUESTS_REQUEST_DETAILS_MAIL_STRING_EN(@REQ_ID)

-------------------------------------------------------- STORED VARIABLES

DECLARE @TBL_USERS USERS_MAIL_LIST

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

------------------------------------------------------------ GET ALL REQUEST USERS

INSERT INTO @TBL_USERS(USER_ID ,USER_FULL_NAME  ,USER_FULL_NAME_EN , USER_EMAIL , MOBILE_NO ,MESSAGE_LANGUAGE)  

SELECT  U.USER_ID , U.USER_FULL_NAME , USER_FULL_NAME_EN ,USER_EMAIL  , MOBILE_NO , MESSAGE_LANGUAGE

FROM USERS U

WHERE U.USER_ID IN(

SELECT V.CREATE_BY_USER_ID

FROM VACATION_REPORTS_APPROVAL_STAGES V

WHERE V.REQUEST_REPORT_NO =@REQ_ID

)

------------------------------------------------------------------------------------ GET USERS FOR  NEXT STAGE

DECLARE @C1 CURSOR

DECLARE @C1_STAGE_ID INT = NULL

DECLARE @C1_WORK_FLOW_TYPE_ID INT = NULL 

DECLARE @C1_STAGE_NAME NVARCHAR(500) = NULL 

DECLARE @C1_STAGE_NAME_EN NVARCHAR(500) = NULL 

SET @C1 = CURSOR FORWARD_ONLY STATIC FOR

SELECT  W.TYPE_ID , W.STAGE_ID ,W.STAGE_NAME ,W.STAGE_NAME_EN ,W.TYPE_NAME ,W.TYPE_NAME_EN 

FROM DBO.WORK_FLOWS_LOAD_NEXT_STAGE_FUNCTION(@WORK_FLOW_ID ,@REQ_CREATED_BY_USER,@CURRENT_STAGE_ID) AS W

OPEN @C1

FETCH NEXT

FROM @C1 INTO @C1_WORK_FLOW_TYPE_ID , @C1_STAGE_ID ,@C1_STAGE_NAME , @C1_STAGE_NAME_EN ,@WORK_FLOW_TYPE_NAME ,@WORK_FLOW_TYPE_NAME_EN

WHILE (@@FETCH_STATUS = 0)

 BEGIN

 SET @STAGE_NAME_NEXT = N'' + @STAGE_NAME_NEXT +  N'' + N'' + @C1_STAGE_NAME 

 SET @STAGE_NAME_NEXT_EN = N'' + @STAGE_NAME_NEXT_EN + N'' + N'' + @C1_STAGE_NAME_EN 

 DECLARE @C2 CURSOR

 DECLARE @C2_USER_ID INT = NULL 

 DECLARE @C2_USER_FULL_NAME NVARCHAR(1000) = NULL 

 DECLARE @C2_USER_FULL_NAME_EN NVARCHAR(1000) = NULL 

 DECLARE @C2_USER_MAIL NVARCHAR(250) = NULL 

 DECLARE @C2_MOBILE_NO VARCHAR(15) 

 DECLARE @C2_MESSAGE_LANGUAGE INT

 

 SET @C2 = CURSOR FORWARD_ONLY STATIC FOR

SELECT UU_ID ,UUSER_FULL_NAME,UUSER_FULL_NAME_EN ,UU_USERMAIL ,UU_MOBILE_NO ,UU_MESSAGE_LANGUAGE

FROM [DBO].[WORK_FLOWS_PERMISSION_MANAGERIAL_GET_STAGE_USERS_BY_REQUEST_ID_FUNCTION](@WORK_FLOW_ID,@C1_WORK_FLOW_TYPE_ID,@C1_STAGE_ID ,@REQ_ID)

                OPEN @C2

FETCH NEXT FROM @C2 INTO @C2_USER_ID ,@C2_USER_FULL_NAME ,@C2_USER_FULL_NAME_EN ,@C2_USER_MAIL , @C2_MOBILE_NO ,@C2_MESSAGE_LANGUAGE 

WHILE @@FETCH_STATUS = 0

BEGIN

INSERT INTO @TBL_USERS(USER_ID ,USER_FULL_NAME  ,USER_FULL_NAME_EN , USER_EMAIL  , MOBILE_NO ,MESSAGE_LANGUAGE)   

VALUES( @C2_USER_ID ,@C2_USER_FULL_NAME , @C2_USER_FULL_NAME_EN ,@C2_USER_MAIL , @C2_MOBILE_NO ,@C2_MESSAGE_LANGUAGE)

  

  SELECT DISTINCT @NEXT_STAGE_USERS =@NEXT_STAGE_USERS + N'' + USER_FULL_NAME  + N'<BR>' ,

@NEXT_STAGE_USERS_EN =@NEXT_STAGE_USERS_EN + N'' + USER_FULL_NAME_EN  + N'<BR>'

  FROM @TBL_USERS T WHERE T.USER_ID =@C2_USER_ID

 --PRINT N'@XXXXX = ' + RTRIM(CAST(@NEXT_STAGE_USERS AS NVARCHAR(3000))) + N'.';

 

FETCH NEXT FROM @C2 INTO @C2_USER_ID ,@C2_USER_FULL_NAME ,@C2_USER_FULL_NAME_EN ,@C2_USER_MAIL , @C2_MOBILE_NO ,@C2_MESSAGE_LANGUAGE

END

CLOSE @C2

DEALLOCATE @C2

FETCH NEXT FROM @C1 INTO @C1_WORK_FLOW_TYPE_ID , @C1_STAGE_ID ,@C1_STAGE_NAME , @C1_STAGE_NAME_EN ,@WORK_FLOW_TYPE_NAME ,@WORK_FLOW_TYPE_NAME_EN

 END

 CLOSE @C1

 DEALLOCATE @C1 

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

--------------------------------------------------------------------------LOOP FOR USERS AND SEND MAIL ACCORDING USER DEFAULT LANG

-- SEND MAIL FOR ALL REQUEST USERS AND NEXT STAGE(S) USERS

 DECLARE @C3 CURSOR

     DECLARE @C3_USER_ID INT = NULL 

 DECLARE @C3_USER_FULL_NAME NVARCHAR(1000) = NULL 

 DECLARE @C3_USER_FULL_NAME_EN NVARCHAR(1000) = NULL 

 DECLARE @C3_USER_MAIL NVARCHAR(250) = NULL 

 DECLARE @C3_MESSAGE_LANGUAGE INT

SET @C3 = CURSOR FORWARD_ONLY STATIC FOR

SELECT  USER_ID  ,USER_FULL_NAME , USER_FULL_NAME_EN ,USER_EMAIL , MESSAGE_LANGUAGE

FROM @TBL_USERS

OPEN @C3

FETCH NEXT

FROM @C3 INTO @C3_USER_ID ,@C3_USER_FULL_NAME ,@C3_USER_FULL_NAME_EN , @C3_USER_MAIL ,@C3_MESSAGE_LANGUAGE

WHILE (@@FETCH_STATUS = 0)

 BEGIN

--  PRINT N'@REQ_ID = ' + RTRIM(CAST(@REQ_ID AS NVARCHAR(3000))) + N'.';

--PRINT N'@REQUEST_DATE = ' + RTRIM(CAST(@REQUEST_DATE AS NVARCHAR(50))) + N'.';

-- PRINT N'@WORK_FLOW_NAME = ' + RTRIM(CAST(@WORK_FLOW_NAME AS NVARCHAR(3000))) + N'.';

-- PRINT N'@@STAGE_NAME_CURRENT = ' + RTRIM(CAST(@STAGE_NAME_CURRENT AS NVARCHAR(3000))) + N'.';

--  PRINT N'@@STATUS_NAME_CURRENT = ' + RTRIM(CAST(@STATUS_NAME_CURRENT AS NVARCHAR(3000))) + N'.';

--   PRINT N'@REQUEST_OWNER_USER_FULL_NAME = ' + RTRIM(CAST(@REQUEST_OWNER_USER_FULL_NAME AS NVARCHAR(3000))) + N'.';

-- PRINT N'@STAGE_NAME_NEXT = ' + RTRIM(CAST(@STAGE_NAME_NEXT AS NVARCHAR(3000))) + N'.';

-- PRINT N'@@WORK_FLOW_TYPE_NAME = ' + RTRIM(CAST(@WORK_FLOW_TYPE_NAME AS NVARCHAR(3000))) + N'.';

-- PRINT N'@@USER_MAIL = ' + RTRIM(CAST(@C3_USER_MAIL AS NVARCHAR(3000))) + N'.';

-- PRINT N'@NEXT_STAGE_USERS = ' + RTRIM(CAST(@NEXT_STAGE_USERS AS NVARCHAR(3000))) + N'.';

IF(@C3_MESSAGE_LANGUAGE =2)

--SEND ENGLISH MAIL

BEGIN

EXEC [DBO].[EMPWORKFLOWSENDEMAIL] 

@ISEMPMANGER = 0,

@EMAIL_TYPE = 1 ,

@EMAIL_LANG = 2 ,

@REQ_ID   = @REQ_ID ,

@REQUEST_DATE =@REQUEST_DATE ,

@WORK_FLOW_NAME =@WORK_FLOW_NAME_EN,

@STAGE_NAME_CURRENT =@STAGE_NAME_CURRENT_EN ,

@STATUS_NAME_CURRENT = @STATUS_NAME_CURRENT_EN,

@REQUEST_OWNER_USER_FULL_NAME  = @REQUEST_OWNER_USER_FULL_NAME_EN ,

@STAGE_NAME_NEXT = @STAGE_NAME_NEXT_EN ,

@WORK_FLOW_TYPE_NAME  = @WORK_FLOW_TYPE_NAME_EN ,

@USER_MAIL = @C3_USER_MAIL ,

@USER_FULL_NAME= @C3_USER_FULL_NAME_EN,

@NEXT_STAGE_USERS = @NEXT_STAGE_USERS_EN ,

@REQUEST_DETAILS = @REQUEST_DETAILS_EN

END

ELSE

BEGIN

EXEC [DBO].[EMPWORKFLOWSENDEMAIL] 

@ISEMPMANGER = 0,

@EMAIL_TYPE = 1 ,

@EMAIL_LANG = 1 ,

@REQ_ID   = @REQ_ID ,

@REQUEST_DATE =@REQUEST_DATE ,

@WORK_FLOW_NAME =@WORK_FLOW_NAME,

@STAGE_NAME_CURRENT =@STAGE_NAME_CURRENT ,

@STATUS_NAME_CURRENT = @STATUS_NAME_CURRENT,

@REQUEST_OWNER_USER_FULL_NAME  = @REQUEST_OWNER_USER_FULL_NAME ,

@STAGE_NAME_NEXT = @STAGE_NAME_NEXT ,

@WORK_FLOW_TYPE_NAME  = @WORK_FLOW_TYPE_NAME ,

@USER_MAIL = @C3_USER_MAIL ,

@USER_FULL_NAME= @C3_USER_FULL_NAME,

@NEXT_STAGE_USERS = @NEXT_STAGE_USERS ,

@REQUEST_DETAILS = @REQUEST_DETAILS

END

 

FETCH NEXT FROM @C3 INTO @C3_USER_ID ,@C3_USER_FULL_NAME ,@C3_USER_FULL_NAME_EN , @C3_USER_MAIL ,@C3_MESSAGE_LANGUAGE

 END

 CLOSE @C3

 DEALLOCATE @C3

 

END

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

SQL SERVER Convert DateTime to String and Print Result to output window

DECLARE @REQUEST_DATE NVARCHAR(50)
SELECT @REQUEST_DATE =CONVERT(NVARCHAR(50) , R.CREATE_DATE ,120)

----------------
print result to output window messages

PRINT N'@REQ_ID = ' + RTRIM(CAST(@REQ_ID AS NVARCHAR(3000))) + N'.';
PRINT N'@REQUEST_DATE = ' + RTRIM(CAST(@REQUEST_DATE AS NVARCHAR(50))) + N'.';
PRINT N'@WORK_FLOW_NAME = ' + RTRIM(CAST(@WORK_FLOW_NAME AS NVARCHAR(3000))) + N'.';


-------------------------------------------
CONVERT INT TO NVARCHAR

SELECT @REQUEST_DETAILS =N'الجهة الطالبة : ' + R.INCOME_SIDE_NAME +N'<BR>'
+N'عدد أيام الاجازة' +  RTRIM(CAST(R.VACATION_NO_OF_DAYS AS NVARCHAR(50)))  + N'<BR>'
+N'من تاريخ' + CONVERT(NVARCHAR(50) , R.VACATION_FROM_DATE ,120)   + N'<BR>'
FROM VACATION_REPORTS_REQUEST R
WHERE R.REQUEST_REPORT_NO =@REQUEST_ID

SQL SERVER SELECT USING PAGING DATA

begin
DECLARE @intPage INT =4
DECLARE  @intPageSize INT =20

DECLARE @intStartRow int;
DECLARE @intEndRow int;
SET @intStartRow = (@intPage -1) * @intPageSize + 1;
SET @intEndRow = @intPage * @intPageSize;  
WITH blogs AS
    (SELECT USER_FULL_NAME,
     ROW_NUMBER() OVER(ORDER BY USER_ID DESC) as intRow,
     COUNT(USER_ID) OVER() AS intTotalHits
     FROM USERS)
SELECT USER_FULL_NAME, intTotalHits FROM blogs
WHERE intRow BETWEEN @intStartRow AND @intEndRow
end

convert dates gregorian to Umalqura and opposite sample funciton

the function take date with format yyyy-MM-dd and language ar or en
if passed date ar date so gregorian date string will returns
else umalqura date will returns.

using System.Globalization;

  public static string ServerSideMethod(string date, string lang)
    {
        string result = string.Empty;
        if (date.Length > 0)
        {
            UmAlQuraCalendar umAlQuraCal = new UmAlQuraCalendar();
            CultureInfo umAlQuraCulture = new CultureInfo("ar-SA");
            GregorianCalendar gregCal = new GregorianCalendar();
            CultureInfo gregculture = new CultureInfo("en-US");
            if (lang == "ar")
            {
                DateTime tempDate = DateTime.ParseExact(date, "yyyy-MM-dd", umAlQuraCulture.DateTimeFormat, DateTimeStyles.AllowWhiteSpaces);
                result = tempDate.ToString("yyyy/MM/dd", gregculture.DateTimeFormat);
            }
            else
            {
                DateTime tempDate = DateTime.ParseExact(date, "yyyy-MM-dd", gregculture.DateTimeFormat, DateTimeStyles.AllowWhiteSpaces);
                result = tempDate.ToString("yyyy/MM/dd", umAlQuraCulture.DateTimeFormat);
            }
        }
        return result;
    }

Convert Gregorian Date to UmAlQura Date using .Net CLR function with SQL Server

http://code.msdn.microsoft.com/windowsdesktop/Convert-Gregorian-Date-to-89e35a4d

calling sql server stored dynamic parameters search using null values for datetime example

the following example idea is to create search form using c# used stored procedure dynamic parameters according to user input search Criteria.

the idea behind the example :
1- create Dynamic sqlParameter List to pass to stored procedure with optional Parameters.
2- Pass From and To Dates Dynamically to the Procedure so if user enter from date the search will handle begining date and no end date for query. and so on.
3- Handle automatic empty values to null parameters to pass query check for form fields.

SELECT ORACLE VARCHAR2 FILTER DIGITS NUMERIC NUMBER VALUES

if you have column in your table contains varchar2 values and need to check for digit values
or set constraint to enforce inserted data to be integer digits not special characters or alphabetic characters you can use this sample query to create your constraint.

SELECT COUNT(*) FROM yourschema.yourtable
WHERE
REGEXP_LIKE(columnName,'[[:digit:]]')


-----------------------------------------------------------------------------------------
to check Digit with specific length you can use this sample
ex : get data with 9 characters digit values

SELECT * FROM xx
WHERE
REGEXP_LIKE(columnName, '^[[:digit:]]{9}$')

-------------------------------------------------------------------------------------
to check for digits and characters with specific length you can use this template
ex: the query will select data contains 4 alpha chars and 3 digits.
SELECT * FROM xx
WHERE

REGEXP_LIKE(SEGIL_NO, '[[:alpha:]]{4}[[:digit:]]{3}')

[[:alpha:]]{4}[[:digit:]]{3}

oracle stored procedure select template return cursor with optional Parameters

Description :
i need to create Stored Procedure select from table with optional parameters to filter and return cursor.
then receive cursor using vb.net function and mapping data to data table to check data existence.

Stored Procedure Template
Stored Specification (with Package Declaration)

PACKAGE     USER_COMPANY IS
 TYPE GET_CO_INFO_CUR IS REF CURSOR ;
PROCEDURE   GET_COMPANY_DATE  -------------                            
                       (VGENERAL_ON    IN    NUMBER  DEFAULT 0       ,
                        VSEGIL_NO      IN    VARCHAR2 DEFAULT NULL    ,                                            
                        VGET_DATE      OUT   GET_DATE,
                        VEXCEPTION  OUT NUMBER);
END;  -- end Package Declaration

---------------------------------------------------------
Stored Body(with Package Body)

PACKAGE BODY   USER_COMPANY IS
PROCEDURE   GET_COMPANY_DATE  --------------                                            
                       (VGENERAL_ON    IN    NUMBER  DEFAULT 0       ,
                        VSEGIL_NO      IN    VARCHAR2 DEFAULT NULL    ,                     
                        VGET_DATE      OUT   GET_DATE,
                        VEXCEPTION  OUT NUMBER)  IS
BEGIN
     OPEN VGET_DATE  FOR          
      SELECT  GENERAL_ON, SEGIL_NO, EMAIL, PASS_WORD, MOBIL_NO, PHONE_NO, MNG_MOBIL_NO,USER_TYPE
      FROM    TND.TND_COMPANY_REGISTER 
      WHERE  (VGENERAL_ON = 0 OR GENERAL_ON = VGENERAL_ON)
      AND    (VSEGIL_NO   IS NULL OR  SEGIL_NO   = VSEGIL_NO );
EXCEPTION WHEN OTHERS  THEN
          VEXCEPTION := SQLCODE;      
    
END  GET_COMPANY_DATE;
END; --end Package Body
--------------------------------------------------------------------------------
Notes : if you passed two optional parameters so the stored procedure query will filter using two values
else if the number value is 0 or varchar2 value is null or empty string the query execution will ignore it in filter so you can use this template for search optionally for your input parameters
the other benefit is if you pass one parameter only for the procedure the execution will succeed because of default value binding option.

-----------------------------------------------------------------------
vb.net function to get cursor data

note : the template code using Oracle.DataAccess.Client library.

 Public Function Get_Company_Data(ByVal general_No As String, ByVal sejelno As String) As DataTable
        Dim mydt As New DataTable()
        Try
            Dim OConn As New OracleConnection
            OConn.ConnectionString = connect.Tender
            Dim cmd As New OracleCommand
            cmd.CommandType = Data.CommandType.StoredProcedure
            cmd.CommandText = "SchemaName.PackageName.ProcedureName"
            cmd.Connection = OConn
            cmd.BindByName = True
            Dim O_par_1, O_par_2, O_par_3, O_par_4 As OracleParameter
            O_par_1 = cmd.Parameters.Add("VGENERAL_ON", OracleDbType.Double, 22)
            O_par_2 = cmd.Parameters.Add("VSEGIL_NO", OracleDbType.Varchar2, 20)
            O_par_3 = cmd.Parameters.Add("VGET_DATE", OracleDbType.RefCursor)
            O_par_4 = cmd.Parameters.Add("VEXCEPTION", OracleDbType.Double, 20)
            O_par_1.Direction = Data.ParameterDirection.Input
            O_par_2.Direction = Data.ParameterDirection.Input
            O_par_3.Direction = Data.ParameterDirection.Output
            O_par_4.Direction = Data.ParameterDirection.Output

            O_par_1.Value = general_No
            O_par_2.Value = sejelno


            If OConn.State = ConnectionState.Closed Then
                OConn.Open()
            End If
            Dim adp As New OracleDataAdapter(cmd)

            adp.Fill(mydt)

            cmd.Connection.Close()
            OConn.Close()
            If mydt.Rows.Count > 0 Then
                Return mydt
            Else
                Return Nothing
            End If

        Catch ex As Exception
            Return mydt
        End Try
    End Function

jquery update panel ajax problem solve , updatepanel may need to register scripts outside body also solve the update panel script not working problem

if you fire jquery event in document.ready function and you use update panel you will face problem
after postback inside update panel the jquery function not loaded

to Solve this Problem follow this scenario :

<script type="text/javascript">

        $(document).ready(function () {
         //put you code here to fire on the first.
        });
        function pageLoad(sender, args) {
            if (args.get_isPartialLoad()) {

            //put your code here to fire after postback event .
            }
        }
 
    </script>


-------------------------------------------------------------------------------------------------------------------
i face problem with bootstrap scirpts with update panel to be run.
first time page visited the script exists on head tag working but after any asynchronous request inside update panel stop running scripts.
so i solve this problem by set master content outside form tag and register the script inside , this solve the problem>


   

Toad how to test Stored Procedure with return values (how to see result)

if you have stored procedure return single or multiple values separately not in cursor you can't see the stored Procedure Result

follow the following steps
1- right click on you stored procedure that returns single values as output parameters
2-Click Execute Procedure
3-Copy Code Generated in execute window and paste it to new Editor
4-Right Click On Script Output panel and check DBMS output

5-after Execute Line write the flowing
 DBMS_OUTPUT.PUT_LINE('DESCRIPTION TEXT ' || TO_CHAR(YOUR_OUTPUT_VAR));
EXAMPLE:

DECLARE   P_SEGELNO VARCHAR2(200);  P_ORDERNO VARCHAR2(200);  P_ORDDATEH VARCHAR2(200);  P_BEGDATEH VARCHAR2(200);  P_PERIOD NUMBER;  P_ENDDATEH VARCHAR2(200);  P_EMP_NAME VARCHAR2(200);  P_MESSAGE_TEXT VARCHAR2(200);
BEGIN   P_SEGELNO := '1073286849';  P_ORDERNO := NULL;  P_ORDDATEH := NULL;  P_BEGDATEH := NULL;  P_PERIOD := NULL;  P_ENDDATEH := NULL;  P_EMP_NAME := NULL;  P_MESSAGE_TEXT := NULL;
  SHR.NET_EMP_MULTI.NOT_ALLOW_EXTEND ( P_SEGELNO, P_ORDERNO, P_ORDDATEH, P_BEGDATEH, P_PERIOD, P_ENDDATEH, P_EMP_NAME, P_MESSAGE_TEXT );    DBMS_OUTPUT.PUT_LINE('ORDER NUMBER = ' || TO_CHAR(P_ORDERNO));     DBMS_OUTPUT.PUT_LINE('ORDER DATE = ' || TO_CHAR(P_ORDDATEH));    DBMS_OUTPUT.PUT_LINE('ORDER PERIOD = ' || TO_CHAR(P_PERIOD));    DBMS_OUTPUT.PUT_LINE('ORDER ENDDATEH = ' || TO_CHAR(P_ENDDATEH));    DBMS_OUTPUT.PUT_LINE('EMP NAME = ' || TO_CHAR(P_EMP_NAME));    DBMS_OUTPUT.PUT_LINE('EMP NAME = ' || TO_CHAR(P_MESSAGE_TEXT));      COMMIT; END; 

6-Click on DBMS or Press Keyboard ALT_CTR_D
you should see your result