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].[pWellEventHistoryAd dExpiratio nNotices]
-- 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.WellI D, dbo.WellEventHistory.Clien tID, GetDate() AS EventDate, 32 AS WellEventID
FROM dbo.WellEventHistory INNER JOIN dbo.Wells ON dbo.WellEventHistory.WellI D = dbo.Wells.WellID
WHERE (((dbo.WellEventHistory.We llEventID) =1 Or (dbo.WellEventHistory.Well EventID)=2 ) AND ((dbo.WellEventHistory.Exp irationDat e)>GetDate ()) AND
((dbo.Wells.PermitStatusID )=4) AND ((Month(dbo.WellEventHisto ry.Expirat ionDate))= Month(Date Add("m",@p _MonthsToE xpiration, 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("Ex pirationNo ticesMaili ngLabels.r pt"))
Dim boConnectionInfo As CrystalDecisions.Shared.IC onnectionI nfo
For Each boConnectionInfo In rd.DataSourceConnections
boConnectionInfo.SetLogon( "UserName" , "Password")
Next
Session.Add("report", rd)
CrystalReportViewer1.Repor tSource = Session("Report")
rd.SetParameterValue("Mont hs To Expiration", Me.MonthsToExpiration.Sele ctedValue)
'Execute stored procedure.
Dim dbcon As New SqlConnection
dbcon.Open()
Dim dbcmd As New SqlCommand("pWellEventHist oryAddExpi rationNoti ces", dbcon)
dbcmd.CommandType = System.Data.CommandType.St oredProced ure
dbcmd.ExecuteNonQuery()
dbcmd.dispose()
dbcon.dispose()
End If
End Sub
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -
Thanks,
--------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[pWellEventHistoryAd
-- 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.WellI
FROM dbo.WellEventHistory INNER JOIN dbo.Wells ON dbo.WellEventHistory.WellI
WHERE (((dbo.WellEventHistory.We
((dbo.Wells.PermitStatusID
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
If (Session("report") Is Nothing) Then
Dim rd As New ReportDocument
rd.Load(Server.MapPath("Ex
Dim boConnectionInfo As CrystalDecisions.Shared.IC
For Each boConnectionInfo In rd.DataSourceConnections
boConnectionInfo.SetLogon(
Next
Session.Add("report", rd)
CrystalReportViewer1.Repor
rd.SetParameterValue("Mont
'Execute stored procedure.
Dim dbcon As New SqlConnection
dbcon.Open()
Dim dbcmd As New SqlCommand("pWellEventHist
dbcmd.CommandType = System.Data.CommandType.St
dbcmd.ExecuteNonQuery()
dbcmd.dispose()
dbcon.dispose()
End If
End Sub
--------------------------
Thanks,
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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_HighPl ainsWaterD istrict1" connectionString="Data Source=999.88.777.666;Data base=HPWD_ SQL;Truste d_Connecti on=no;User Id=myusername;Password=myp assword" 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.Confi guration.C onfigurati onManager. AppSetting s("Databas eDB916_Hig hPlainsWat erDistrict 1"))
Thanks for the help!
I have the following lines in web.config:
<connectionStrings>
<add name="DatabaseDB916_HighPl
</connectionStrings>
I have changed the Dim statement to the following, but nothing seems to work.
Dim dbcon As New SqlConnection(System.Confi
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.
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.
ASKER
mas_oz2003
I am attempting to implement your last recommendation.
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
'Execute stored procedure.
If Me.UpdateEventHistory.Chec ked = True Then
Dim rootWebConfig As System.Configuration.Confi guration
rootWebConfig = System.Web.Configuration.W ebConfigur ationManag er.OpenWeb Configurat ion("/HPWD _SQL")
Dim dbcon As System.Configuration.Conne ctionStrin gSettings
dbcon = rootWebConfig.ConnectionSt rings.Conn ectionStri ngs("Datab aseDB916_H ighPlainsW aterDistri ct1")
dbcon.Open()
Dim dbcmd As New SqlCommand("pWellEventHist oryAddExpi rationNoti ces", dbcon)
dbcmd.CommandType = System.Data.CommandType.St oredProced ure
dbcmd.Parameters("@p_Month sToExpirat ion").Valu e = Me.MonthsToExpiration.Sele ctedValue
dbcmd.ExecuteNonQuery()
dbcmd.Dispose()
dbcon.Dispose()
End If
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------
Now the dbcon.Open() and dbcon.Dispose() are not members of System.Configuration.Conne ctionStrin gSettings.
On: Dim dbcmd As New SqlCommand("pWellEventHist oryAddExpi rationNoti ces", dbcon)
I get the message dbcon value of type 'System.Configuration.Conn ectionStri ng.Connect ionStringS ettings' cannot be converted to 'System.Data.SqlClient.Sql Connection '.
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,
I am attempting to implement your last recommendation.
--------------------------
'Execute stored procedure.
If Me.UpdateEventHistory.Chec
Dim rootWebConfig As System.Configuration.Confi
rootWebConfig = System.Web.Configuration.W
Dim dbcon As System.Configuration.Conne
dbcon = rootWebConfig.ConnectionSt
dbcon.Open()
Dim dbcmd As New SqlCommand("pWellEventHist
dbcmd.CommandType = System.Data.CommandType.St
dbcmd.Parameters("@p_Month
dbcmd.ExecuteNonQuery()
dbcmd.Dispose()
dbcon.Dispose()
End If
--------------------------
Now the dbcon.Open() and dbcon.Dispose() are not members of System.Configuration.Conne
On: Dim dbcmd As New SqlCommand("pWellEventHist
I get the message dbcon value of type 'System.Configuration.Conn
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,
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,
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,
http://www.csharp-station.com/Tutorial/AdoDotNet/Lesson07