Wednesday, March 28, 2012

web.config holding connection settings for SQL

Hi All,
Having some problems using the we.config file to hold sql connection information (ASP.NET 1.1 using Web Matrix)
Here is my web.config file....
<?xml version="1.0" encoding="UTF-8" ?>

<configuration>

<appSettings>
<add key="PBL" value="server=localhost;uid=****;password=****;database=PlannedShutdown"/>
</appSettings>

<system.web>
<sessionState mode="InProc"
stateConnectionString="tcpip=127.0.0.1:42424"
sqlConnectionString="data source=127.0.0.1;trusted_connection=true"
cookieless="false"
timeout="20" />

<customErrors mode="RemoteOnly" defaultRedirect="GenericErrorPage.htm">
<error statusCode="403" redirect="NoAccess.htm"/>
<error statusCode="404" redirect="FileNotFound.htm"/>
<customErrors>

<authentication mode="Windows">

<forms name=".ASPXAUTH"
loginUrl="login.aspx"
protection="Validation"
timeout="999999" />

</authentication>


<authorization>
<allow users="joeuser" />
<allow roles="Admins" />
<deny users="*" />
</authorization>


</system.web>

</configuration>
When I run the page, all I get is the dreaderConnectionString is not Initialized...
I have imported the System.Configuration namespace in the code (all) :-
<%@dotnet.itags.org. Page Language="VB" %>
<%@dotnet.itags.org. Register TagPrefix="n1" Namespace="MetaBuilders.WebControls.MasterPages" Assembly="MetaBuilders.WebControls.MasterPages, Version=1.5.5000.0, Culture=neutral, PublicKeyToken=8f1b87f9edb00944" %>
<%@dotnet.itags.org. Register TagPrefix="n2" Namespace="MetaBuilders.WebControls.MasterPages" Assembly="MetaBuilders.WebControls.MasterPages, Version=1.5.5000.0, Culture=neutral, PublicKeyToken=8f1b87f9edb00944" %>
<%@dotnet.itags.org. Register TagPrefix="n3" Namespace="MetaBuilders.WebControls.MasterPages" Assembly="MetaBuilders.WebControls.MasterPages, Version=1.5.5000.0, Culture=neutral, PublicKeyToken=8f1b87f9edb00944" %>
<%@dotnet.itags.org. import Namespace="System.Data" %>
<%@dotnet.itags.org. import Namespace="System.Data.SqlClient" %>
<%@dotnet.itags.org. import Namespace="System.Configuration" %>
<script runat="server">

Sub Page_Load(Sender As Object, e As EventArgs)
if Not Page.IsPostBack Then
ddl_ServerName.DataSource = ServerList()
ddl_ServerName.DataBind()
end if
End Sub

Function ServerList() As System.Data.DataSet

Dim strConnection As String

strConnection = ConfigurationSettings.AppSettings("PBL")

Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(strConnection)

Dim queryString As String = "SELECT DISTINCT [SuppressList].[ServerName] FROM [SuppressList]"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter
dataAdapter.SelectCommand = dbCommand
Dim dataSet As System.Data.DataSet = New System.Data.DataSet
dataAdapter.Fill(dataSet)

Return dataSet
End Function

Function GetServerEntry(ByVal serverName As String) As System.Data.IDataReader

Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))

Dim queryString As String = "SELECT [SuppressList].* FROM [SuppressList] WHERE ([SuppressList].[ServerName] = @dotnet.itags.org.ServerName)"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Dim dbParam_serverName As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_serverName.ParameterName = "@dotnet.itags.org.ServerName"
dbParam_serverName.Value = serverName
dbParam_serverName.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_serverName)

dbConnection.Open
Dim dataReader As System.Data.IDataReader = dbCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

Return dataReader
End Function

Sub Sddl_ServerName_SelectedIndexChanged(sender As Object, e As EventArgs)
dg_vbs.DataSource = GetServerEntry(ddl_ServerName.SelectedItem.Value)
dg_vbs.DataBind()
End Sub

</script>
<html>
<head>
</head>
<body>
<p>
<n2:ContentContainer id="ContentContainer1" MasterPageFile="~/siteMaster.ascx" runat="server">
<n3:Content id="mainRegion" runat="server">View By Server Name
<p>
<asp:Label id="lbl_ServerName" runat="server">Server Name</asp:Label>
<asp:DropDownList id="ddl_ServerName" runat="server"></asp:DropDownList>
</p>
<p>
<asp:DataGrid id="dg_vbs" runat="server" Width="817px"></asp:DataGrid>
</p>
</n3:Content>
</n2:ContentContainer>
</p>
<p>
</p>
<p>
</p>
<p>
</p>
</body>
</html>

Any help would be appreciated as otherwise I will have to go back to coding each connection in every sub I need it to !

In your web.config, the connection string looks to be accessed via thekey PBL, and in one case you do access it withConfigurationSettings.AppSettings("PBL").
But you also access it a second time when creating a SqlConnection withConfigurationSettings.AppSettings("ConnectionString")which probably doesn't evaluate to anything, and thus gives you an error.

thanks for the reply... after I posted I saw this error and corrected it. Also removed all the junk from the web.config to just what was needed and hey presto all is working...
thanks again

0 comments:

Post a Comment