Solved

VB App - Connection to SQL

Posted on 2006-07-19
2
222 Views
Last Modified: 2010-04-23
I have a VB.net that while in the development environment was connected up to a development database.  Now that the app is being moved to the production environment the connection has to changes.

Here is my problem:   All of the DataAdapters were created with the Wizard and that included the connection string.   There are 100's of adapters.

How can I change these?

If this were a web app I would have a reusable piece of code that would be called and the move to Production would only take a quick change to the connnection string in that code and not have to touch all the pages....is there the ability to do that in VB.net?

If I'm going to have to touch all the pages I'd rather add something like this rather than change the dbname.

Thanks in advance.
0
Comment
Question by:TimSweet220
2 Comments
 
LVL 14

Accepted Solution

by:
ptakja earned 500 total points
ID: 17142893
Ahhhh, the benefits and drawbacks of the .NET wizards. This is why I create my adapters in code. My suggestion is to store your connection string someplace central and secure (ie., encrypted in the registry or in the App.Config file).

Then write a routine that will pull that string and then loop through all your adapters setting the Adapter.Connection property appropriately. Call this routine when your app initializes.

Or, alternatively, write a function that returns a Connection object (SQLConnection, ODBCConnection, or whatever type you have). The do a global search & replace for the ".Connection = " in your project and replace the connection information with your function. The end result for an adapter would look like this:

MyAdapter.Connection = GetConnection()

hope that helps.
0
 

Author Comment

by:TimSweet220
ID: 17157058
Here is what I ended up doing.

Editing the appconfig file to like so:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
      <appSettings>
            <!--   User application and configured property settings go here.-->
            <!--   Example: <add key="settingName" value="settingValue"/> -->
            <add key="txbPGY.ReadOnly" value="False" />
            <add key="ColProgramYear.Text" value="Program Year" />
            <add key="ColProgramYear.Width" value="77" />
            <add key="ColStart.Text" value="Start Date" />
            <add key="ColCredit.Text" value="Credit" />
            <add key="ColVacDays.Text" value="Vac Days" />
            <add key="ColDateSent.Text" value="Date Sent" />
            <add key="ColTrain_Id.Text" value="Train_ID" />
            <add key="ColReference_Id.Text" value="Reference_ID" />
            <add key="SqlSelectCommand2.CommandText" value="dbo.[sp_GetRefData]" />
            <add key="ColRotationDesc.Text" value="Rotation Description" />
            <add key="ColWeeks.Text" value="Weeks" />
            <add key="ColEvaluation.Text" value="Evaluation" />
            <add key="ColTrainingYear.Text" value="Training Year" />
            <add key="ColTrainingType.Text" value="Training Type" />
            <add key="ColStartDate.Text" value="Start Date" />
            <add key="ColEndDate.Text" value="End Date" />
            <add key="ColumnHeader213.Text" value="TrackTitle" />
            <!-- Minime Connection -->
            <!--add key="ABRConn" value="workstation id=AD072;packet size=4096;user id=sa;data source=Production;persist security info=True;database=dbblah;password=blah" -->
            <!-- Smallme Connection -->
            <add key="ABRConn" value="workstation id=AD072;packet size=4096;user id=sa;data source=DEV;persist security info=True;initial catalog=dbblah;password=blah" />
      </appSettings>
</configuration>

then at the time of creating the build to distribute comment out the DEV source and UNcommenting the Production.

In the code we use this piece of code to set the connection

 'SqlConnection1
        '
        Me.SqlConnection1.ConnectionString = AppSettings.Get("ABRConn")



This works great.

however...it eliminates the use of the DataAdapter wizard because changes there remove the code in the above code InitializeComponent  - sure why.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question