troubleshooting Question

Pass a parameter from a web page to a SQL Server Stored Procedure

Avatar of CloudApps
CloudApps asked on
Microsoft SQL Server.NET Programming
7 Comments2 Solutions426 ViewsLast Modified:
I have the following Stored Procedure in SQL Server 2008.
---------------------------------------------------------------------------------------------------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[pWellEventHistoryAddExpirationNotices]
      -- Add the parameters for the stored procedure here
    @p_MonthsToExpiration int,
    @p_WellEventHistoryID_out int output
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
    INSERT
    INTO [dbo].[WellEventHistory]
        (
            [WellID],
            [ClientID],
            [EventDate],
            [WellEventID]
        )
    -- Insert statements for procedure here
      SELECT dbo.WellEventHistory.WellID, dbo.WellEventHistory.ClientID, GetDate() AS EventDate, 32 AS WellEventID
      FROM dbo.WellEventHistory INNER JOIN dbo.Wells ON dbo.WellEventHistory.WellID = dbo.Wells.WellID
      WHERE (((dbo.WellEventHistory.WellEventID)=1 Or (dbo.WellEventHistory.WellEventID)=2) AND ((dbo.WellEventHistory.ExpirationDate)>GetDate()) AND

((dbo.Wells.PermitStatusID)=4) AND ((Month(dbo.WellEventHistory.ExpirationDate))=Month(DateAdd("m",@p_MonthsToExpiration,GetDate()))));
      
END
------------------------------------------------------------------------------------------------------------------------------
I have the following Visual Basic code for an aspx web page. I need to pass a value from the Me.MonthsToExpiration textbox control on an aspx page to the @p_MonthsToExpiration parameter in the Stored Procedure.
-------------------------------------------------------------------------------------------------------------------------------
   Protected Sub Button_Preview_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button_Preview.Click

        If (Session("report") Is Nothing) Then

            Dim rd As New ReportDocument
            rd.Load(Server.MapPath("ExpirationNoticesMailingLabels.rpt"))
            Dim boConnectionInfo As CrystalDecisions.Shared.IConnectionInfo

            For Each boConnectionInfo In rd.DataSourceConnections
                boConnectionInfo.SetLogon("UserName", "Password")
            Next

            Session.Add("report", rd)
            CrystalReportViewer1.ReportSource = Session("Report")
            rd.SetParameterValue("Months To Expiration", Me.MonthsToExpiration.SelectedValue)

            'Execute stored procedure.
            Dim dbcon As New SqlConnection
            dbcon.Open()
            Dim dbcmd As New SqlCommand("pWellEventHistoryAddExpirationNotices", dbcon)
            dbcmd.CommandType = System.Data.CommandType.StoredProcedure
            dbcmd.ExecuteNonQuery()
            dbcmd.dispose()
            dbcon.dispose()

        End If

    End Sub
-------------------------------------------------------------------------------------------------------------------------------
Thanks,
ASKER CERTIFIED SOLUTION
Miguel Oz
Senior Software Engineer
Join our community to see this answer!
Unlock 2 Answers and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros