Web.config and connection string

.NET
1

Hey there,

I seem to be having a somewhat common ASP.NET issue.

I’m trying to connect to SQL Server Express using the web.config file to set my connection string, but I seem to keep getting a null reference.

Here’s the connectionstrings portion of my web.config file:


<configuration>
	<appSettings/>
  <connectionStrings>
    <add name="NorthindConnectionString"
     connectionString="Data Source=ACER-OKTNB0BF6J\\SQLEXPRESS;Initial Catalog=myDB;Integrated Security=True;"
     providerName="System.Data.SqlClient" />
  </connectionStrings>

Here’s what I’ve got in my script:

void Page_Load(Object sender , EventArgs e)
{
    dgrdProducts.DataSource = Cache["table_mytable"];
	dgrdProducts.DataBind();

    //declare web.config connection variable conStringSettings
    System.Configuration.Configuration rootWebConfig = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration(null);
    System.Configuration.ConnectionStringSettings conStringSettings;

    //assign value to conStringSettings
    conStringSettings = rootWebConfig.ConnectionStrings.ConnectionStrings["NorthwindConnectionString"];

    if (conStringSettings == null)
    {
         Response.Write("WHY ARE YOU DOING THIS TO ME!!!???");
    }

It seems like no matter what I do, I get the output “WHY ARE YOU DOING THIS TO ME???”… which seems to indicate the null reference. Any ideas would be much appreciated. Is this likely a result of something I need to adjust in SQL Server, my code or my web.config file.

Thanks.

2

Here’s the problem.
System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration(null);

If you read the MSDN docs on this.  You'll notice it says that passing null will give you the root web.config.

So you may think that's what you want. But it's not.  The root web.config is actually in the .NET installation path....

usually c:\windows\Microsoft.NET\Framework\[.NET VERSION]\Config

You want the web.config in the root of your application.
So pass "/" rather than null.  And that should work.

Or if you are just looking for the connection string...try
System.Configuration.ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString should work too...and it's only line of code...
3

Hi Sei,

Thanks. That helped. I’m able to loop through connection strings at this point using:

foreach (ConnectionStringSettings connection in
       rootWebConfig.ConnectionStrings.ConnectionStrings)
    {
        Response.Write("Name: " +
        connection.Name + "<br>");

        Response.Write("Connection String: " +
           connection.ConnectionString + "<br><br>");
    }

Here’s what I did:

System.Configuration.Configuration rootWebConfig
=System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration(Request.ApplicationPath);

However, when I test whether or not the connectionString associated with Northwind is NULL, it returns that it is null. I don’t get it because the loop above was outputting connections… so that tells me that the web config file is working.


conStringSettings = rootWebConfig.ConnectionStrings.ConnectionStrings["NorthwindConnectionString"];

    if(conStringSettings == null || conStringSettings.ConnectionString.Trim() == "")
    {
        Response.Write("WHY ARE YOU DOING THIS TO ME!!!???");
    }
    else
    {
        Response.Write("WICKED!");
    }
4

Why are you even opening it like that? Why not just use configurationManager? That should work fine.

By the way, you might want to check out the enterprise Library’s data access application block. You still deal with connection strings, but can set a default connection so that no connection string needs to be passed. For example:

Database db = DatabaseFactory.CreateDatabase();

You can then go on to retrieve a dataset with something like:

DBCommand dbCommand = db.GetStoredProcCommand("StoredProcedureName");
DataSet ds = db.ExecuteDataset(dbCommand);

It just seems a bit cleaner to me.

5

Like devbanana was asking. Is there any reason you aren’t using the ConfigurationManager?

System.Configuration.ConfigurationManager.ConnectionStrings[“NorthwindConnectionString”]

Does seem a bit simpler.

Oh well that isn’t your problem. Your problem probably has to do with your key being named NorthindConnectionString and you trying to acess NorthwindConnectionString.
Notice the w is missing in your key.

6

Thanks guys,

That worked perfectly in terms of being able to seemingly pass the connection from the web.config file, but now when I pass the variable associated with

System.Configuration.ConfigurationManager.ConnectionStrings[“NorthwindConnectionString”]

to the SqlCommand Object, I get an invalid aguments error. Is it not possible to pass this data type to the SQL Command Class?

7

In other words, you need to first instantiate an SqlConnection object, wherein you will pass that connection string.

8

Thanks a bunch…

Thought I’d post the code that worked for me for anyone else trying to set up a basic connection using the web.config file.


<%@ Page Language="C#" Debug="true" %>
<%@ Import Namespace="System" %>
<%@ Import Namespace="System.Data"%>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Configuration" %>
<%@ Import Namespace="System.Configuration.Provider" %>

<script runat=server>
void Page_Load(Object sender , EventArgs e)
{
    //DECLARE web.config connection variable conStringSettings
    //AS PER CONNECTION STRING SPECIFIED IN THE DATABASE EXPLORER
    System.Configuration.ConnectionStringSettings conStringSettings;

    //SET THE CONNECTION STRING VALUE
    conStringSettings = System.Configuration.ConfigurationManager.ConnectionStrings["NorthwindConnectionString"];

    //DECLARE OTHER VARIABLES TO BE USED IN SCRIPT
    string strSelect;
    SqlDataAdapter dadProducts;
    SqlCommand dbCommand;
    SqlConnection conn;

    //SET THE CONNECTION
    conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString);

    //SET THE QUERY TO BE USED
    strSelect = "Select * From tbl_registration";

    //SET THE SQLCOMMAND TO BE PASSED TO THE DATABASE
    dbCommand = new SqlCommand(strSelect,conn);

    //OPEN THE CONNECTION AND EXECUTE THE QUERY
    conn.Open();
        dgrdProducts.DataSource = dbCommand.ExecuteReader();
        dgrdProducts.DataBind();
    conn.Close();
}//end void page load
</Script>

<html>
<head><title>Simon Test</title></head>
<body>

<asp:DataGrid ID="dgrdProducts" Runat="server" />

</body>
</html>
9

go there for for help with connectino strings

www.connstr.com

regards
Humair

10

Thanks!