#1
  1. No Profile Picture
    Contributing User
    ASP Discoverer (100 - 499 posts)

    Join Date
    Jul 2010
    Posts
    138
    Rep Power
    13

    Help setting up a connection string from a web.config file


    I have several subs that call the same connection string in the back code to a web page so in order to reduce coding clutter I am trying to set up 1 sub that will call the connection string from the web.config, and then use that connection string throughout all the subs on the page, but I'm having trouble setting it up. I used the example on the microsoft developers network link below

    How to: Read Connection Strings from the Web.config File

    On the page load sub, i set the sub (WOConnect) to run first, however when i use it, i get the error BC30451: 'connString' is not declared. It may be inaccessible due to its protection level.

    Here is 2 images of the code used. If i move the Dim connString to into the main DIM area, i get a different error, BC30311: Value of type 'System.Configuration.ConnectionStringSettings' cannot be converted to 'System.Data.SqlClient.SqlConnection'.





    Looking for help because i would rather not declare the connection string in every sub that needs data from the database
  2. #2
  3. No Profile Picture
    Contributing User
    ASP Discoverer (100 - 499 posts)

    Join Date
    Jul 2010
    Posts
    138
    Rep Power
    13
    Got it working, had to change plenty of code but i created a sub routine called loadconnection, then in every other sub that required a data connection i would declare an SQL statement for what i needed, then call the sub routine below

    Code:
    Protected Sub LoadConnection()
            MyConnection = New SqlConnection
            MyConnection.ConnectionString = ConfigurationManager.ConnectionStrings("WorkOrders").ConnectionString
            MyCommand = New SqlCommand(sqlstr, MyConnection)
            MyCommand.Connection.Open()
    End Sub
    If you think there is a better way, or have any comments feel free to voice them!
    Last edited by Superdawg; January 2nd, 2014 at 03:08 PM.
  4. #3
  5. I do .NET for a living
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Sep 2003
    Location
    Florida
    Posts
    1,558
    Rep Power
    215
    Originally Posted by Superdawg
    Got it working, had to change plenty of code but i created a sub routine called loadconnection, then in every other sub that required a data connection i would declare an SQL statement for what i needed, then call the sub routine below

    Code:
    Protected Sub LoadConnection()
            MyConnection = New SqlConnection
            MyConnection.ConnectionString = ConfigurationManager.ConnectionStrings("WorkOrders").ConnectionString
            MyCommand = New SqlCommand(sqlstr, MyConnection)
            MyCommand.Connection.Open()
    End Sub
    If you think there is a better way, or have any comments feel free to voice them!
    Why not just load directly from the web.config?
    Code:
                SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["PurchaseRequestsConnectionString"].ConnectionString);
                     //Creates a new connection object using the connection string from the web.config file.
                SqlCommand cmd = cnn.CreateCommand();
                    //Creates a new command object and associates it with the connection object already created.
                cmd.CommandType = System.Data.CommandType.Text;
                    //Set command object command type property
                cmd.CommandText = "Select * from Center where Inactive <> 1 Order By CenterID";
                   //Set command object text property
    The way you're doing it will work, but if you need to load different connection strings to hit other servers or even other databases on the same server you will either have to have another sub or at least a parameter to pass in to get the correct connection string back.
    Roger (.NET MCP)

Similar Threads

  1. Update sql connection string in web config
    By peebman2000 in forum .NET Development
    Replies: 4
    Last Post: April 18th, 2008, 12:05 PM
  2. Get connection string from app.config file
    By mdateeq2003 in forum .NET Development
    Replies: 1
    Last Post: September 3rd, 2007, 08:24 AM
  3. Replies: 4
    Last Post: April 20th, 2007, 02:05 PM
  4. Get Connection String from Web.Config in .NET 2.0 ( VB )
    By lonniegriffin in forum .NET Development
    Replies: 9
    Last Post: June 13th, 2006, 06:26 AM
  5. Storing connection string in web config file
    By Red in forum .NET Development
    Replies: 6
    Last Post: January 3rd, 2005, 01:02 AM

IMN logo majestic logo threadwatch logo seochat tools logo