asked on

ASP.NET Connection String in Web.Config

Hello,

I have a problem with my connection string in my web.config file. A little history.... I set up a ASP.NET website in the US and used a connection string in the web config file for my ASP pages, it works with no problems. In France I copied the same config file and accessed it in the same way on the ASP page but it is not working for me. I am getting the following error when I try to load the page (ConnectionString not declared)....

Erreur de compilation
Description : Une erreur s'est produite lors de la compilation d'une ressource requise pour répondre à cette demande. Veuillez consulter ci-dessous les détails relatifs à l'erreur en question, puis modifier votre code source de manière appropriée.

Message d'erreur du compilateur: BC30451: Le nom 'ConnectionString' n'est pas déclaré.

<connectionStrings>
		<add name="HubbardR&amp;DConnectionString" connectionString="Data Source=xxx.xxx.x.x;Initial Catalog=&quot;Hubbard R&amp;D&quot;;Integrated Security=True"
            providerName="System.Data.SqlClient" />
	</connectionStrings>
 
 
-----------------------------------------------------------------
 
<%@ import Namespace="system.data" %>
<%@ import Namespace="system.data.SqlClient" %>
 
    Function InsertMortality(ByVal LineNumber As Integer, ByVal HatchYear As Integer, ByVal Building As String, ByVal WingBandNumber As Integer) As System.Data.DataSet
        Dim sqlConnection As SqlConnection = New SqlConnection
        Dim sqlCommand As SqlCommand = New SqlCommand
        Dim Conn As New SqlConnection(ConnectionString)
    
        Dim cmd As New SqlCommand("[MortalityEdit]", Conn)
    
        cmd.CommandType = Data.CommandType.StoredProcedure
    
        cmd.Parameters.Add(New SqlParameter("@LineNumber", Data.SqlDbType.Int))
        cmd.Parameters("@LineNumber").Value = lineNumber
    
        cmd.Parameters.Add(New SqlParameter("@HatchYear", Data.SqlDbType.Int))
        cmd.Parameters("@HatchYear").Value = HatchYear
    
        cmd.Parameters.Add(New SqlParameter("@WingBandNumber", Data.SqlDbType.Int))
        cmd.Parameters("@WingBandNumber").Value = WingBandNumber
    
        cmd.Parameters.Add(New SqlParameter("@Building", Data.SqlDbType.String))
        cmd.Parameters("@Building").Value = Building
    
        Conn.Open()
        cmd.ExecuteNonQuery()
        Conn.Close()
    End Function

Open in new window

Navicerts

ASKER

Added note:

"HubbardR&DConnectionString" looks a little funny to me (the database name is actually HubbardR&D", but it has worked OK in the US.

sunithnair

Try this. I think you missed the ConnectionString variable

<%@ import Namespace="system.data" %>
<%@ import Namespace="system.data.SqlClient" %>
<%@ import Namespace="System.Configuration" %>
 
    Function InsertMortality(ByVal LineNumber As Integer, ByVal HatchYear As Integer, ByVal Building As String, ByVal WingBandNumber As Integer) As System.Data.DataSet
        Dim sqlConnection As SqlConnection = New SqlConnection
        Dim sqlCommand As SqlCommand = New SqlCommand
        Dim ConnectionString = ConfigurationManager.ConnectionStrings["HubbardR&amp;DConnectionString"].ConnectionString;
        Dim Conn As New SqlConnection(ConnectionString)
    
        Dim cmd As New SqlCommand("[MortalityEdit]", Conn)
    
        cmd.CommandType = Data.CommandType.StoredProcedure
    
        cmd.Parameters.Add(New SqlParameter("@LineNumber", Data.SqlDbType.Int))
        cmd.Parameters("@LineNumber").Value = lineNumber
    
        cmd.Parameters.Add(New SqlParameter("@HatchYear", Data.SqlDbType.Int))
        cmd.Parameters("@HatchYear").Value = HatchYear
    
        cmd.Parameters.Add(New SqlParameter("@WingBandNumber", Data.SqlDbType.Int))
        cmd.Parameters("@WingBandNumber").Value = WingBandNumber
    
        cmd.Parameters.Add(New SqlParameter("@Building", Data.SqlDbType.String))
        cmd.Parameters("@Building").Value = Building
    
        Conn.Open()
        cmd.ExecuteNonQuery()
        Conn.Close()
    End Function

Open in new window

ASKER CERTIFIED SOLUTION

sunithnair

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Navicerts

ASKER

It worked!! Thank you!!

I wonder why I can use it with just "Dim Conn As New SqlConnection(ConnectionString)" in the US? Oh well, no matter I can start making my web page now, thank you!

Navicerts

ASKER

Sure thing. I should add that after selecting ASP I wasn't prompted any sub categories (such as .NET). I may have been doing it wrong but I did whatever felt quick and intuitive. Cheers.

Danielcmorris

I would recommend that you NEVER use ConfigurationManager.ConnectionStrings["HubbardR&DConnectionString"].ConnectionString;

Make a function in the app_code folder that gives you that data. Then, you can base your connection string on dynamic criteria.

For example, if you have a development server and you want to be able to drop the entire site on another box, you can simply move it without worry:

Public Class DataLayer

function getconnected()
Dim constr As String
If System.Web.HttpContext.Current.Request.Url.Host = "localhost" Then

constr = "Data Source=mydevserver;Initial Catalog= mydb;USER=sa;PASSWORD=mypassword"
Else
constr = "Data Source=myliveserver;Initial Catalog=mydb;USER=site_username;PASSWORD= AdifferentPassword!"
End If
return constr
end function

end class

then, you can simply do:

dim dl as new datalayer

myrecordsetobject.open sql, dl.getconnected()