error when adding connection string

[AzizKamal]

I created a project in Visual Web Developer 2008. (Visual Basic -> Web -> ASP.NET Web Application). Name of the Project is PFMS and it is located at c:\inetpub\

http://wwwroot\pfms.

I did not check Create Directory for solution

In this project, I have written code in the file connect.aspx.vb as follows:

Public Partial Class connect
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim cn As New ConnectionStringSettings()
        cn.ConnectionString = _
             "Data Source=developers;User ID=abc;Password=123;" & _
             "Persist Security Info=True"
        cn.Name = "pfms"
        cn.ProviderName = "MSDAORA.1"

        Dim config As System.Configuration.Configuration = _
            System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration("/pfms")
        config.ConnectionStrings.ConnectionStrings.Add(cn)
        config.Save()
    End Sub

End Class

When I run this code with Debug -> Start Debugging, code executes without error up to this line:

config.ConnectionStrings.ConnectionStrings.Add(cn)

But when I press F8 on this line:

config.Save()

I receive the error message:
A configuration file cannot be created for the requested Configuration object.

Based on what I had searched to resolve this issue, I tried the following but there was no success:

Selected the folder c:\inetpub\

http://wwwroot\PFMS

Right-Click -> Properties -> Web Sharing
Selected Share this folder
An Edit Alias dialog box appeared
Clicked OK
A message appeared: The alias /Root/Pfms already exists. Please choose a different alias. So I renamed the alias to Pfms2, clicked OK, OK.
I am using Windows XP Service Pack 2. ASP.NET version is 2.0.50727. My own machine is also the server for this application.

 

[ca8msm]

Can I ask why you need to add an entry to the config file at run time? Doing so will restart the application pool as well so you may need to take this into account for what you are trying to accomplish.

Mark,

Darlington Web Design[tab]|[tab]Experts, Information, Ideas & Knowledge[tab]|[tab]ASP.NET Tips & Tricks

 

[AzizKamal]

In our scenario, on our login page, we ask the following information from user:

User ID
Password
Book Code (Database Name like HRIS,Payroll)
Server

We have a decentralized setup, where same application say HRIS is installed in say region1 as well as in region2,region3.

So, region1 user will enter different name for Server, region2 user will enter another name for server and so on...

Hence I need to add an entry to the config file at run time. We store the information entered by user in the session variables:

session("username")
session("password")
session("bookcode")
session("severname")

whenever we write any sql statement, we prefix the table name with session("bookcode"). And I intend to replace User ID, Password and Data Source with session variables too in the following code:

cn.ConnectionString = _
             "Data Source=developers;User ID=abc;Password=123;" & _
             "Persist Security Info=True"

 

[ca8msm]

I'm still not sure why it needs to be written to the config file. If you are requesting info from the user, and storing the results in session variables, then why would you write it to the the config file as surely you would end up doing this every time someone visited the page?

The way I see it, you would either do one of two things:

1. This is if the details need to be permanently persisted. As part of the setup in each region, request the user details in the setup package and overwrite it from there

2. This is if the details need to be requested from each user. Build the connection string in the fly based on the session variables that the user entered.



Mark,

Darlington Web Design[tab]|[tab]Experts, Information, Ideas & Knowledge[tab]|[tab]ASP.NET Tips & Tricks

 

[AzizKamal]

Our scenario is option 2; i.e., the details need to be requested from each user

Your recommendation:
Build the connection string in the fly based on the session variables that the user entered.

What I am being told is it is the 'best practice' to store connection strings in web.config file (when the user enters username,password,bookcode and server info and hits Log In button), then it can be referenced directly from code using:

Dim connStr As String = ConfigurationManager.ConnectionStrings("pfms").ConnectionString

As per your recommendation (please correct me if I am wrong), you are recommending using the following??

Dim dbconn
dbconn = New OleDbConnection("Provider=MSDAORA.1;data source=" & Session("servername") & ";User ID=" & Session("username") & ";Password=" & Session("password"))
dbconn.Open()

 

[ca8msm]

What I am being told is it is the 'best practice' to store connection strings in web.config file

Yes, that would usually be the case if the connection string is permanent for every user of that site, however this is not really needed for your case.

As per your recommendation (please correct me if I am wrong), you are recommending using the following??

Yes, that approach should be fine, although I would make sure you sanitise your user's input.

Mark,

Darlington Web Design[tab]|[tab]Experts, Information, Ideas & Knowledge[tab]|[tab]ASP.NET Tips & Tricks

 

[AzizKamal]

I changed from System.Data.OleDb to System.Data.OracleClient due to the following reasons:

1. Oledb is generic and does not take advantage of functionalities specific to Oracle Client.

2. OLE DB layer is implemented as a COM library and not .NET

Imports System.Data
Imports System.Data.OracleClient
Imports System.Xml

Partial Class Units
    Inherits System.Web.UI.Page
    Dim cn As OracleConnection = New OracleConnection

cn.ConnectionString = "Data Source=" & Session("servername") & ";User Id=" & Session("username") & ";Password=" & Session("password") & ";Integrated Security=no;"
cn.Open()

 

[ca8msm]

OK, in that case you should also be aware that:

The types in System.Data.OracleClient are deprecated. The types are supported in version 4 of the .NET Framework but will be removed in a future release. Microsoft recommends that you use a third-party Oracle provider.

For more info, see:

http://msdn.microsoft.com/en-us/library/77d8yct7.aspx

Mark,

Darlington Web Design[tab]|[tab]Experts, Information, Ideas & Knowledge[tab]|[tab]ASP.NET Tips & Tricks

 

[AzizKamal]

Thank you for mentioning this important point.

I would choose not to opt for third-party Oracle provider. As far as OLEDB provider is concerned, isn't it so that COM is also now deprecated in favor of .NET? It is not discontinued though but not recommended too.

Visual Studio 2008 uses .NET framework 3.5 and Visual Studio 2010 uses .NET framework 4.0. Since I am developing in VS 2008, so I am safe for one upgrade (from 2008 to 2010). So between these two options, still system.data.oracleclient looks better to me than system.data.oledb