Link to home
Start Free TrialLog in
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,
Avatar of srikanthreddyn143
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
Avatar of gamarrojgq
gamarrojgq

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of 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!
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.
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,
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,