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

CloudApps
CloudApps used Ask the Experts™
on
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,
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
Hi,

You can do it adding the following line to your code like this:

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

dbcmd.Parameters.Add(New SqlClient.SqlParameter("@p_MonthsToExpiration", Me.MonthsToExpiration.text))

 dbcmd.ExecuteNonQuery()
 dbcmd.dispose()
 dbcon.dispose()

Hope that helps
Senior Software Engineer
Top Expert 2009
Commented:
create sqlparameter objects for both input and output parameter of your stored proc call as follows:
Dim dbcon As New SqlConnection
            dbcon.Open()
            Dim dbcmd As New SqlCommand("pWellEventHistoryAddExpirationNotices", dbcon)
            dbcmd.CommandType = System.Data.CommandType.StoredProcedure
'start new code
'Input param.
Dim param As SqlParameter= new SqlParameter()
param.ParameterName = "@p_MonthsToExpiration";
param.SqlDbType = SqlDbType.Int;
' You may need to cast Me.MonthsToExpiration.SelectedValue to integer.
param.Value = Me.MonthsToExpiration.SelectedValue;
'The default direction is in fact Input, but to be clear:
param.Direction = ParameterDirection.Input;
dbcmd.Parameters.Add(param);
// Output param.
param = new SqlParameter();
param.ParameterName = "@p_WellEventHistoryID_out";
param.SqlDbType = SqlDbType.Int;
param.Direction = ParameterDirection.Output;
dbcmd.Parameters.Add(param);
'end new code
dbcmd.ExecuteNonQuery()
dbcmd.dispose()
dbcon.dispose()
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

CloudAppsOwner

Author

Commented:
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 OzSenior Software Engineer
Top Expert 2009

Commented:
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.
CloudAppsOwner

Author

Commented:
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,
CloudAppsOwner

Author

Commented:
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,

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial