locked
How To Re-code Ashx For Correct Parameter, asp.net RRS feed

  • Question

  • User-940083561 posted

    http://172.20.100.12:888/rpt_vehicle.ashx?cardno=0000055799&txtLog_Date='2/2/2018'&txtCompleted_Date='2/3/2018'

    Hi, all.
    Attached is showing how the 3 parameters are passed to ahsx asp.net. But in the ashx, it would use something like as below :

    http://172.20.100.12:888/rpt_Detail_dept.ashx?cardno=0000008904&year=2018&month=2

    This is to select all the record based on month.
    Now my requirement is to change to cardno=0000055799&txtLog_Date='2/2/2018'&txtCompleted_Date='2/3/2018'

    can somebody help to revise below ashx coding so that it can pass correct parameter to my store procedure in MSsql..

    <%@ WebHandler Language="VB" Class="rpt_Detail_Dept" %>

    Imports Microsoft.Reporting.WebForms

    Public Class rpt_Detail_Dept : Implements IHttpHandler, IRequiresSessionState
    Private dConn As New SqlConnection(ConfigurationManager.ConnectionStrings("DataDB").ConnectionString)

    Public Sub ProcessRequest(Page As HttpContext) Implements IHttpHandler.ProcessRequest
    Try
    Dim dComm As New SqlCommand("", dConn), tData As New DataTable()

    dComm.CommandText = "[$_RptDetail]"
    dComm.CommandType = CommandType.StoredProcedure
    dComm.Parameters.Add(New SqlParameter("@Card", CardNo(Page)))
    dComm.Parameters.Add(New SqlParameter("@Mnth", Month(Page)))
    dComm.Parameters.Add(New SqlParameter("@Year", Year(Page)))
    dComm.CommandTimeout = 0

    Dim dData As New SqlDataAdapter(dComm) : dData.Fill(tData) : dData.Dispose() : dComm.Dispose()
    Dim iStrm As String() = Nothing, iWarn As Warning() = Nothing, iEncd As String = "", iExtn As String = "", iMime As String = ""
    Dim iRepo As New LocalReport(), iData As New ReportDataSource()

    iData.Name = "tMain" : iData.Value = tData
    iRepo.ReportPath = Page.Server.MapPath("~/rpt_Detail_Dept.rdlc")
    iRepo.DataSources.Add(iData)

    Dim iByte As Byte() = iRepo.Render("PDF", Nothing, iMime, iEncd, iExtn, iStrm, iWarn)

    Page.Response.Buffer = True
    Page.Response.Clear() : Page.Response.ContentType = iMime
    Page.Response.AppendHeader("content-disposition", "attachment; filename=" & Now.ToString("yyyyMMdd_HHmmss") & "." & iExtn)
    Page.Response.OutputStream.Write(iByte, 0, iByte.Length)
    Page.Response.Flush()
    Catch ex As Exception
    Page.Response.ContentType = "text/html" : Throw ex
    Finally
    If Not dConn.State = ConnectionState.Closed Then dConn.Close()
    End Try
    End Sub

    Public ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable
    Get
    Return False
    End Get
    End Property

    Public ReadOnly Property CardNo(Page As HttpContext) As String
    Get
    Dim iData As String

    If Not String.IsNullOrWhiteSpace(Page.Request.QueryString("cardno")) Then
    iData = Page.Request.QueryString("cardno")
    Else
    iData = ""
    End If

    Return iData
    End Get
    End Property

    Public ReadOnly Property Month(Page As HttpContext) As Int32
    Get
    Dim iData As Int32

    If Not Double.TryParse(Page.Request.QueryString("month"), iData) Then iData = Now.Month

    Return iData
    End Get
    End Property

    Public ReadOnly Property Year(Page As HttpContext) As Int32
    Get
    Dim iData As Int32

    If Not Double.TryParse(Page.Request.QueryString("year"), iData) Then iData = Now.Year

    Return iData
    End Get
    End Property

    End Class

    --------------------------- end of ashx ------------------------------------------------------------


    Above ashx will pass the parameter to sp as below :

    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    GO
    ALTER PROC [dbo].[$_Rpt_vehicle]
    @Card VARCHAR(16),
    @txtLog_Date Datetime,
    @txtCompleted_Date Datetime

    AS SET NOCOUNT ON

    BEGIN
    set @card = @card

    SELECT cardno, trdate
    FROM VW_RPT_van2
    where
    cardno = @Card
    and
    trdate between @txtLog_Date and @txtCompleted_Date

    END
    ----------------------------- end of sp --------------------------


    FYI, my sp is able to display all those record which are based on cardno and txtlog_date and txtCompleted_date
    how can I modify the ashx so that I can display record on my asp page?

    TQ

    asp page download pdf button

    Friday, February 2, 2018 8:28 AM

All replies

  • User283571144 posted

    Hi Ipohtech,

    According to your description, if you want to use the new SP in the ashx, you could change the parameter send to the SP.

    dComm.CommandType = CommandType.StoredProcedure
    dComm.Parameters.Add(New SqlParameter("@Card", cardno(Page)))
    dComm.Parameters.Add(New SqlParameter("@txtLog_Date", txtLog_Date(Page)))
    dComm.Parameters.Add(New SqlParameter("@txtCompleted_Date", txtCompleted_Date(Page)))

    Best Regards,

    Brndo

    Monday, February 5, 2018 9:13 AM
  • User-940083561 posted

    Hi, Brando.

    Thanks for the input here.  I did input as what you did before. It ended up as below error :

    Server Error in '/' Application.


    Compilation Error

    Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

    Compiler Error Message: BC30451: 'txtLog_Date' is not declared. It may be inaccessible due to its protection level.

    Source Error:

    Line 13:             dComm.CommandType = CommandType.StoredProcedure
    Line 14:             dComm.Parameters.Add(New SqlParameter("@Card", CardNo(Page)))
    Line 15:             dComm.Parameters.Add(New SqlParameter("@txtlog_Date", txtLog_Date(Page)))
    Line 16:             dComm.Parameters.Add(New SqlParameter("@txtCompleted_Date", txtCompleted_Date(Page)))
    Line 17:             dComm.CommandTimeout = 0


    Source File: C:\CorpServer\AttdReport\rpt_vehicle.ashx    Line: 15


    Show Detailed Compiler Output:

    c:\windows\system32\inetsrv> "C:\Windows\Microsoft.NET\Framework64\v4.0.30319\vbc.exe" /t:library /utf8output /R:"C:\Windows\Microsoft.Net\assembly\GAC_64\System.EnterpriseServices\v4.0_4.0.0.0__b03f5f7f11d50a3a\System.EnterpriseServices.dll" /R:"C:\Windows\assembly\GAC_MSIL\Microsoft.ReportViewer.WebForms\12.0.0.0__89845dcd8080cc91\Microsoft.ReportViewer.WebForms.dll" /R:"C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.ServiceModel.Activities\v4.0_4.0.0.0__31bf3856ad364e35\System.ServiceModel.Activities.dll" /R:"C:\Windows\Microsoft.Net\assembly\GAC_64\System.Web\v4.0_4.0.0.0__b03f5f7f11d50a3a\System.Web.dll" /R:"C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.ComponentModel.DataAnnotations\v4.0_4.0.0.0__31bf3856ad364e35\System.ComponentModel.DataAnnotations.dll" /R:"C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.Drawing\v4.0_4.0.0.0__b03f5f7f11d50a3a\System.Drawing.dll" /R:"C:\Windows\Microsoft.Net\assembly\GAC_64\System.Data\v4.0_4.0.0.0__b77a5c561934e089\System.Data.dll" /R:"C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System\v4.0_4.0.0.0__b77a5c561934e089\System.dll" /R:"C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.ServiceModel.Activation\v4.0_4.0.0.0__31bf3856ad364e35\System.ServiceModel.Activation.dll" /R:"C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.ServiceModel.Web\v4.0_4.0.0.0__31bf3856ad364e35\System.ServiceModel.Web.dll" /R:"C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.Runtime.Serialization\v4.0_4.0.0.0__b77a5c561934e089\System.Runtime.Serialization.dll" /R:"C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.Xml\v4.0_4.0.0.0__b77a5c561934e089\System.Xml.dll" /R:"C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.Web.Services\v4.0_4.0.0.0__b03f5f7f11d50a3a\System.Web.Services.dll" /R:"C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.Web.Extensions\v4.0_4.0.0.0__31bf3856ad364e35\System.Web.Extensions.dll" /R:"C:\Windows\Microsoft.Net\assembly\GAC_MSIL\Microsoft.CSharp\v4.0_4.0.0.0__b03f5f7f11d50a3a\Microsoft.CSharp.dll" /R:"C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.Activities\v4.0_4.0.0.0__31bf3856ad364e35\System.Activities.dll" /R:"C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.Data.DataSetExtensions\v4.0_4.0.0.0__b77a5c561934e089\System.Data.DataSetExtensions.dll" /R:"C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.WorkflowServices\v4.0_4.0.0.0__31bf3856ad364e35\System.WorkflowServices.dll" /R:"C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.IdentityModel\v4.0_4.0.0.0__b77a5c561934e089\System.IdentityModel.dll" /R:"C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.Core\v4.0_4.0.0.0__b77a5c561934e089\System.Core.dll" /R:"C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.Configuration\v4.0_4.0.0.0__b03f5f7f11d50a3a\System.Configuration.dll" /R:"C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.Web.DynamicData\v4.0_4.0.0.0__31bf3856ad364e35\System.Web.DynamicData.dll" /R:"C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.Web.ApplicationServices\v4.0_4.0.0.0__31bf3856ad364e35\System.Web.ApplicationServices.dll" /R:"C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.ServiceModel\v4.0_4.0.0.0__b77a5c561934e089\System.ServiceModel.dll" /R:"C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.Xml.Linq\v4.0_4.0.0.0__b77a5c561934e089\System.Xml.Linq.dll" /out:"C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Temporary ASP.NET Files\root\ab46d8ff\2bc3d1c9\App_Web_rpt_vehicle.ashx.cdcab7d2.7qc-v7e7.dll" /D:DEBUG=1 /debug+ /nowarn:41008,40000,40008 /define:_MYTYPE=\"Web\" /imports:Microsoft.VisualBasic,System,System.Collections,System.Collections.Generic,System.Collections.Specialized,System.Configuration,System.Data,System.Data.SqlClient,System.Globalization,System.Net.Mail,System.Text,System.Text.RegularExpressions,System.Xml,System.Web,System.Web.Caching,System.Web.SessionState,System.Web.Security,System.Web.Profile,System.Web.UI,System.Web.UI.WebControls,System.Web.UI.WebControls.WebParts,System.Web.UI.HtmlControls /warnaserror- /optionInfer+  "C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Temporary ASP.NET Files\root\ab46d8ff\2bc3d1c9\App_Web_rpt_vehicle.ashx.cdcab7d2.7qc-v7e7.0.vb" "C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Temporary ASP.NET Files\root\ab46d8ff\2bc3d1c9\App_Web_rpt_vehicle.ashx.cdcab7d2.7qc-v7e7.1.vb"
    
    
    Microsoft (R) Visual Basic Compiler version 12.0.52400.36400
    Copyright (c) Microsoft Corporation.  All rights reserved.
    
    C:\CorpServer\AttdReport\rpt_vehicle.ashx(15) : error BC30451: 'txtLog_Date' is not declared. It may be inaccessible due to its protection level.
    
                dComm.Parameters.Add(New SqlParameter("@txtlog_Date", txtLog_Date(Page)))
                                                                      ~~~~~~~~~~~        
    C:\CorpServer\AttdReport\rpt_vehicle.ashx(16) : error BC30451: 'txtCompleted_Date' is not declared. It may be inaccessible due to its protection level.
    
                dComm.Parameters.Add(New SqlParameter("@txtCompleted_Date", txtCompleted_Date(Page)))
                                                                            ~~~~~~~~~~~~~~~~~        
    
    

     

    Show Complete Compilation Source:

    ----------------------------------------------------------- end------------------------------

    As written in the ashx as dComm.CommandText = "[$_Rpt_Vehicle]"  ,  I already created a sp in SQL named $_rept_vehicle. The variables are well defined in there.

    May I know do I need to check this file iRepo.ReportPath = Page.Server.MapPath("~/rpt_vehicle.rdlc") for its structure data ?

    TQ

    Tuesday, February 6, 2018 1:05 AM
  • User283571144 posted

    Hi Ipohtech,

    You have define the CardNo,Month,Year function to get the datetime from the querystring. You also need create some new property to get the  datetime from the querystring.

    Add below codes into class:

        Public ReadOnly Property txtLog_Date(Page As HttpContext) As DateTime
            Get
                Dim iData As DateTime
    
                If Not DateTime.TryParse(Page.Request.QueryString("txtCompleted_Date"), iData) Then iData = DateTime.Now
    
    
                Return iData
            End Get
        End Property
    
        Public ReadOnly Property txtCompleted_Date(Page As HttpContext) As DateTime
            Get
                Dim iData As DateTime
    
                If Not DateTime.TryParse(Page.Request.QueryString("txtCompleted_Date"), iData) Then iData = DateTime.Now
    
                Return iData
            End Get
        End Property

    Best Regards,

    Brando

    Tuesday, February 6, 2018 2:03 AM
  • User-940083561 posted

    Dear Brando

      Thanks for the guide. I finally got rid of the error from the asp page. It can generate pdf from the ashx file.  But it showed blank record . I know that I need to revise the store procedure and then can able to dump the data into rdlc file , this rdlc is used to generate the pdf.

    As showed in ashx

    Dim iRepo As New LocalReport(), iData As New ReportDataSource()

                iData.Name = "tMain" : iData.Value = tData
                iRepo.ReportPath = Page.Server.MapPath("~/rpt_vehicle.rdlc")
                iRepo.DataSources.Add(iData)

                Dim iByte As Byte() = iRepo.Render("PDF", Nothing, iMime, iEncd, iExtn, iStrm, iWarn)

    -----------------  ashx -----------------------

    there is a variable called idata. So, how can I add this idata into my store procedure as below :

    set ANSI_NULLS ON

    set QUOTED_IDENTIFIER ON

    GO

    ALTER PROC [dbo].[$_Rpt_vehicle]

    @Card VARCHAR(16),

    @txtLog_Date Datetime,

    @txtCompleted_Date Datetime

    AS SET NOCOUNT ON

    BEGIN

    set @card = @card

    SELECT cardno, trdate

    FROM VW_RPT_van2

    where

    cardno = @Card

    and

    trdate between @txtLog_Date and @txtCompleted_Date

    END

    ----------------------------------- sp -------------------------

    Thanks again

      

    Tuesday, February 6, 2018 9:41 AM
  • User283571144 posted

    Hi Ipohtech,

    Thanks for the guide. I finally got rid of the error from the asp page. It can generate pdf from the ashx file.  But it showed blank record . I know that I need to revise the store procedure and then can able to dump the data into rdlc file , this rdlc is used to generate the pdf.

    According to your SP, I think it will select the value if you set the right parameter.

    Since I don't have your application environment, I suggest you could try to debug the application by yourself.

    I suggest you could set a breakpoint at the dComm.CommandTimeout = 0 line to check the txtCompleted_Date(Page), txtLog_Date(Page) has the right data.

    Then I suggest you could check the tData has value.

    More details about how to use breakpoint in the VS, I suggest you could refer to below article.

    https://msdn.microsoft.com/en-us/library/5557y8b4.aspx?f=255&MSPPError=-2147217396 

    Best Regards,

    Brando

    Wednesday, February 7, 2018 7:54 AM
  • User-940083561 posted

    Thanks Brando.

    I don't know how to MS Visual studio for this task. I will try to use ms edge developer tool by pressing F12 on the asp page...Hope I can able set the breakpoint at the ashx file with the mentioned line ..FYI, the asp page is to call ashx file , If I set breakpoint F9 onto that asp page, will it able to go to ashx file?

    Thx

    Wednesday, February 7, 2018 10:08 AM
  • User283571144 posted

    Hi Ipohtech,

    I don't know how to MS Visual studio for this task. I will try to use ms edge developer tool by pressing F12 on the asp page...Hope I can able set the breakpoint at the ashx file with the mentioned line ..FYI, the asp page is to call ashx file , If I set breakpoint F9 onto that asp page, will it able to go to ashx file?

    Firstly, the browser develop tool(F12) is not as same as the visual studio develop tool, you couldn't set the breakpoint in the visual from the browser.

    I suggest you could set the breakpoint directly in the ashx file in VS.

    Then if the request send into the ashx, it will fired the breakpoint.

    Best Regards,

    Brando

    Thursday, February 8, 2018 5:26 AM
  • User-940083561 posted

    Hi, Brando.

    Almost done. Now, my report can be pulled out of system. see attached.

    http://www.utteraccess.com/forum/index.php?act=attach&type=post&id=85502

    But the data was pull out based on below SP which is to receive 3 parameters namely cardno, month, and year.  Could pls help me to turn these 3 parameters into my expected cardno, txtlog_date and txtCompleted_date  ?

    set ANSI_NULLS ON

    set QUOTED_IDENTIFIER ON

    GO

    ALTER PROC [dbo].[$_Rpt_vehicle]

    @Card VARCHAR(16), -- Employee Card No.

    @Year INT, -- Trx. Year

    @Mnth INT -- Trx. Month

    AS SET NOCOUNT ON

    BEGIN

    DECLARE @Temp TABLE ( [Id] INT IDENTITY(1,1), [Date] DATETIME, [WDay] NVARCHAR(100) )

    DECLARE @DtFr DATETIME, @DtTo DATETIME

    SET @DtFr = CAST(CAST(@Year AS VARCHAR) + '/' + CAST((@Mnth+0) AS VARCHAR) + '/01' AS DATETIME)

    SET @DtTo = DATEADD(month, 1,CAST(CAST(@Year AS VARCHAR) + '/' + CAST((@Mnth+0) AS VARCHAR) + '/01' AS DATETIME))

    ;WITH tData AS

    ( SELECT @DtFr [Date] UNION ALL SELECT DATEADD(DAY,1,[Date]) FROM tData WHERE DATEADD(DAY,1,[Date]) < @DtTo )

    INSERT INTO @Temp ( [Date], [WDay] ) SELECT mt.[Date], DATENAME(WEEKDAY,mt.[Date]) FROM tData mt

    SELECT

    '',

    CardNo [EmplCard], Name [EmplName], [DepartmentDesc] [EmplSrcs], [InOut] [InOutDes],

    TrDate [TrnxDate], TrTime [TrnxTmMn], TrController [TrnxTmMx],

    ''

    FROM VW_RPT_van2

    WHERE

    CardNo = @Card AND YEAR(TrDate) = @Year AND MONTH(TrDate) = @Mnth

    GROUP BY

    TrDate, [TrDay], TrTime, CardNo, Name, [DepartmentDesc], [InOut] , TrController

    END

    ----------------------------------- end of SP ----------------------------------

    Wednesday, February 21, 2018 8:08 AM
  • User283571144 posted

    Hi Ipohtech,

    Do you mean you wants to replace the DtFr and DtTo with  txtlog_date and xtCompleted_date?

    If this is your requirement, I suggest you could try below SP:

    GO
    
    ALTER PROC [dbo].[$_Rpt_vehicle]
    
    @Card VARCHAR(16), -- Employee Card No.
    
    @txtlog_date  DATETIME, -- Trx. Year
    
    @xtCompleted_date DATETIME -- Trx. Month
    
    AS SET NOCOUNT ON
    
    BEGIN
    
    DECLARE @Temp TABLE ( [Id] INT IDENTITY(1,1), [Date] DATETIME, [WDay] NVARCHAR(100) )
    
    
    ;WITH tData AS
    
    ( SELECT @txtlog_date [Date] UNION ALL SELECT DATEADD(DAY,1,[Date]) FROM tData WHERE DATEADD(DAY,1,[Date]) < @xtCompleted_date )
    
    INSERT INTO @Temp ( [Date], [WDay] ) SELECT mt.[Date], DATENAME(WEEKDAY,mt.[Date]) FROM tData mt
    
    SELECT
    
    '',
    
    CardNo [EmplCard], Name [EmplName], [DepartmentDesc] [EmplSrcs], [InOut] [InOutDes],
    
    TrDate [TrnxDate], TrTime [TrnxTmMn], TrController [TrnxTmMx],
    
    ''
    
    FROM VW_RPT_van2
    
    WHERE
    
    CardNo = @Card AND YEAR(TrDate) = @Year AND MONTH(TrDate) = @Mnth
    
    GROUP BY
    
    TrDate, [TrDay], TrTime, CardNo, Name, [DepartmentDesc], [InOut] , TrController
    
    END
    

    Best Regards,

    Brando

    Tuesday, February 27, 2018 2:32 AM