Avatar of CloudApps
CloudApps
 asked on

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

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,
.NET ProgrammingMicrosoft SQL Server

Avatar of undefined
Last Comment
CloudApps

8/22/2022 - Mon
srikanthreddyn143

You have to create sqlparameter object and assign it to SQL command oobject. Please chech below link.

http://www.csharp-station.com/Tutorial/AdoDotNet/Lesson07
SOLUTION
gamarrojgq

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
CloudApps

ASKER
I am sorry that I have not selected a solution, but I am getting a runtime error on the dbcon.Open() statement. It appears that I have not established a connnection to the database.

I have the following lines in web.config:

<connectionStrings>
      <add name="DatabaseDB916_HighPlainsWaterDistrict1" connectionString="Data Source=999.88.777.666;Database=HPWD_SQL;Trusted_Connection=no;User Id=myusername;Password=mypassword" providerName="System.Data.SqlClient"/>
</connectionStrings>

I have changed the Dim statement to the following, but nothing seems to work.

Dim dbcon As New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("DatabaseDB916_HighPlainsWaterDistrict1"))

Thanks for the help!
Miguel Oz

Regarding your previous post. check link about how to read web.config:
http://msdn.microsoft.com/en-us/library/ms178411.aspx#Y0
Basically you need to fix:
web.config: Replace  Database  with Initial Catalog
vb.net: Use sample from link, AppSettings does not read web.config.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
CloudApps

ASKER
mas_oz2003

I am attempting to implement your last recommendation.
------------------------------------------------------------------------------------------------------------------------------
            'Execute stored procedure.
            If Me.UpdateEventHistory.Checked = True Then
                Dim rootWebConfig As System.Configuration.Configuration
                rootWebConfig = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration("/HPWD_SQL")
                Dim dbcon As System.Configuration.ConnectionStringSettings
                dbcon = rootWebConfig.ConnectionStrings.ConnectionStrings("DatabaseDB916_HighPlainsWaterDistrict1")
                dbcon.Open()
                Dim dbcmd As New SqlCommand("pWellEventHistoryAddExpirationNotices", dbcon)
                dbcmd.CommandType = System.Data.CommandType.StoredProcedure
                dbcmd.Parameters("@p_MonthsToExpiration").Value = Me.MonthsToExpiration.SelectedValue
                dbcmd.ExecuteNonQuery()
                dbcmd.Dispose()
                dbcon.Dispose()
            End If
-----------------------------------------------------------------------------------------------------------------------------
Now the dbcon.Open() and dbcon.Dispose() are not members of System.Configuration.ConnectionStringSettings.

On: Dim dbcmd As New SqlCommand("pWellEventHistoryAddExpirationNotices", dbcon)
I get the message dbcon value of type 'System.Configuration.ConnectionString.ConnectionStringSettings' cannot be converted to 'System.Data.SqlClient.SqlConnection'.

I have not made any changes to my web.config at this time. I am using a product named Iron Speed Designer to generate my website (110 mb), so I am reluctant to change the web.config.

Thanks,
CloudApps

ASKER
Thanks, you both pointed me in the right direction.

mas_oz2003

When you suggested modifying Web.Config, it triggered a thought that I needed to see if Iron Speed Designer offered a solution. It turns out that they provide functionality to call a stored procedure and pass parameters.

Thanks again,