I'm developing an intranet site in ASP.NET 2.0 but I can't seem to
connect to the DB from within my code. I've created a .vb class that
houses a private Connection() that other functions within the class can
call to connect to the database. In the calling function, I've declared
my connection object and called the "Open" method on the object.
However, when I attempt to execute the stored procedure command by
calling the "ExecuteSca lar" method, I get the following error:
"ExecuteSca lar requires an open and available Connection. The
connection's current state is closed."
Here's the code from my class:
Imports System.Data
Imports System.Data.Sql Client
Namespace Encompass
Public Class EncompassSecuri ty
Public Shared Function GetHRIDByNTUser ID(ByVal strNTUserID) As String
Dim strHRID As String
'Create command object
Dim cmd As New SqlCommand("usp _Get_HRID_By_NT UserID", Connection())
cmd.CommandType = CommandType.Sto redProcedure
'Open DB connection
Dim DBConnection As SqlConnection = Connection()
DBConnection.Op en()
'Input parameters
Dim inNTUserParam As New SqlParameter("@ NT_UserID", SqlDbType.VarCh ar)
inNTUserParam.D irection = ParameterDirect ion.Input
inNTUserParam.V alue = strNTUserID
cmd.Parameters. Add(inNTUserPar am)
'Output parameters
Dim outHRIDParam As New SqlParameter("@ HRID", SqlDbType.Int)
outHRIDParam.Di rection = ParameterDirect ion.Output
cmd.Parameters. Add(outHRIDPara m)
'Run stored procedure
strHRID = cmd.ExecuteScal ar()
Return (strHRID)
'Close DB connection
DBConnection.Cl ose()
End Function
Private Shared Function Connection() As SqlConnection
Dim strConnectionSt ring As String
strConnectionSt ring = ConfigurationMa nager.Connectio nStrings(
"Conn").Connect ionString
Return New SqlConnection(s trConnectionStr ing)
End Function
End Class
End
Namespace
Here's the code from my web.config file:
<?
xml version="1.0"?>
<!--
Note: As an alternative to hand editing this file you can use the
web admin tool to configure settings for your application. Use
the Website->Asp.Net Configuration option in Visual Studio.
A full list of settings and comments can be found in
machine.config. comments usually located in
\Windows\Micros oft.Net\Framewo rk\v2.x\Config
-->
<
configuration
xmlns="http://schemas.microso ft.com/.NetConfigurati on/v2.0">
<
connectionStrin gs>
<
add name="Conn" connectionStrin g="Data Source=ServerNa me;Initial
Catalog=NPASDV; uid=UserName;pa ssword=*******; "
providerName="S ystem.Data.SqlC lient" />
</
connectionStrin gs>
<system.web>
<!--
Set compilation debug="true" to insert debugging
symbols into the compiled page. Because this
affects performance, set this value to true only
during development.
Visual Basic options:
Set strict="true" to disallow all data type conversions
where data loss can occur.
Set explicit="true" to force declaration of all variables.
-->
<
roleManager defaultProvider ="AspNetWindows TokenRoleProvid er" />
<
compilation debug="true" strict="false" explicit="true" />
<
pages>
<
namespaces>
<
clear />
<
add namespace="Syst em" />
<
add namespace="Syst em.Collections" />
<
add namespace="Syst em.Collections. Specialized" />
<
add namespace="Syst em.Configuratio n" />
<
add namespace="Syst em.Text" />
<
add namespace="Syst em.Text.Regular Expressions" />
<
add namespace="Syst em.Web" />
<
add namespace="Syst em.Web.Caching" />
<
add namespace="Syst em.Web.SessionS tate" />
<
add namespace="Syst em.Web.Security " />
<
add namespace="Syst em.Web.Profile" />
<
add namespace="Syst em.Web.UI" />
<
add namespace="Syst em.Web.UI.WebCo ntrols" />
<
add namespace="Syst em.Web.UI.WebCo ntrols.WebParts " />
<
add namespace="Syst em.Web.UI.HtmlC ontrols" />
</
namespaces>
</
pages>
<!--
The <authentication > section enables configuration
of the security authentication mode used by
ASP.NET to identify an incoming user.
-->
<
authentication mode="Windows" />
<!--
The <customErrors > section enables configuration
of what to do if/when an unhandled error occurs
during the execution of a request. Specifically,
it enables developers to configure html error pages
to be displayed in place of a error stack trace.
<customErrors mode="RemoteOnl y" defaultRedirect ="GenericErrorP age.htm">
<error statusCode="403 " redirect="NoAcc ess.htm" />
<error statusCode="404 " redirect="FileN otFound.htm" />
</customErrors>
-->
</
system.web>
</
configuration>
What am I doing wrong? Any help would be most appreciated!!
--
Manuel Garr, MCP
