Elementary question on connection string

Oct 30, 2007

Hi all

Using VB.Net 2005 & SQL 2005.

I tried to use a database on my local PC and a remote database.

Now is there a way I can make the connection string flexible. I tried to
change the connection string in the APP.CONFIG file however I was not
successful. It would be nice if I am able to store the connection string in
some kind of a text file.

What I may be doing wrong.

Mike TI.

Andrew
Tue Oct 30 22:41:40 PDT 2007

A connection string is just a string. You can store it however you want. In
the app configuration, in a text file, in some other database, in the
registry or even just create it dynamically based on a configuration dialog
box you present to the user.

What errors are you getting when trying to work with App.Config?

--
Andrew Faust
andrew[at]andrewfaust.com
http://www.andrewfaust.com


"Mike TI" <tanveer@creativepk.com> wrote in message
news:u%23%23%23003GIHA.3600@TK2MSFTNGP06.phx.gbl...
> Oct 30, 2007
>
> Hi all
>
> Using VB.Net 2005 & SQL 2005.
>
> I tried to use a database on my local PC and a remote database.
>
> Now is there a way I can make the connection string flexible. I tried to
> change the connection string in the APP.CONFIG file however I was not
> successful. It would be nice if I am able to store the connection string
> in
> some kind of a text file.
>
> What I may be doing wrong.
>
> Mike TI.
>
>
>
>

Cor
Tue Oct 30 22:48:05 PDT 2007

Mike,

You can do it in any way you want as long as you set the connectionString
property of the xxxCommand before the actual action of the xxxCommand (this
can be an enclosed command by instance in the DataAdapter or all the classes
that inherit that).

"xxxCommand can be SqlCommand, OleDBCommand etc")

Cor

Mike
Wed Oct 31 01:43:29 PDT 2007

Oct 31, 2007
01:45pm

Hi

I have just started to do a small application.

Can you please guide me where and how I should put the connection string so
as it to be flexible. Also if you can refer me to some examples it would be
excellent.

Thank you in advance.
Mike TI

"Cor Ligthert[MVP]" <notmyfirstname@planet.nl> wrote in message
news:1D92F17E-1BB3-4692-AD37-7B625CCC8F7B@microsoft.com...
> Mike,
>
> You can do it in any way you want as long as you set the connectionString
> property of the xxxCommand before the actual action of the xxxCommand
> (this can be an enclosed command by instance in the DataAdapter or all the
> classes that inherit that).
>
> "xxxCommand can be SqlCommand, OleDBCommand etc")
>
> Cor

Jeff
Wed Oct 31 02:26:27 PDT 2007

On 31/10/2007 in message <OS2#do5GIHA.280@TK2MSFTNGP03.phx.gbl> Mike TI
wrote:

>I have just started to do a small application.
>
>Can you please guide me where and how I should put the connection string
>so as it to be flexible. Also if you can refer me to some examples it
>would be excellent.

I tend to do this:

internal class JHelpRecordUpdater
{
private static OleDbConnection m_OleDbConnection;
private static OleDbDataAdapter m_OleDbDataAdapter;
private static DataSet m_DataSet;
private static DataTable m_DataTable;
private static string m_DBPath = @"E:\Data\Database\JGToolBox.accdb";
private static string m_TableName = "tblHelpRecord";

... rest of class

And then one of the functions looks like this:

public static bool UpdateRecord(JHelpRecord helpRecord)
{
//Update Only - Use AddRecord for new records
bool blnOK = false;

//Create Connection
m_OleDbConnection = GetOleDbConnection();

// Create the SelectCommand.
string strSelect = "SELECT * FROM " + m_TableName;
strSelect += " WHERE RecordNumber = ";
strSelect += helpRecord.RecordNumber.ToString();

//Select Record For This Record Number
m_OleDbDataAdapter = CreateSelectAdapter(m_OleDbConnection, strSelect);

// Open Connection
m_OleDbConnection.Open();

//Create Command To Update Record
CreateUpdateCommand(ref m_OleDbDataAdapter, m_OleDbConnection, helpRecord);

//Execute
try
{
m_OleDbDataAdapter.UpdateCommand.ExecuteNonQuery();
blnOK = true;
}
catch
{
blnOK = false;
}

//Close connection
m_OleDbConnection.Close();

return blnOK;
}

This means that if I change the layout of my hard disks I just have to
change the one line:

private static string m_DBPath = @"E:\Data\Database\JGToolBox.accdb";


--
Jeff Gaines

William
Wed Oct 31 09:39:38 PDT 2007

Are you building an ASP, Windows Forms, XML web service or what?

Each of these architectures has different requirements for the Connection
string.
Based on some of your other questions, I'm going to assume that you want to
create a Windows Forms application that accesses SQL Server. So, why would
you want to change the connection string? To set the user credentials? Well,
in this case it might make sense to use an application-based set of
credentials that does not change. You might also consider using "trusted"
(SSPI) authentication in which case the connection string does not change.
If you're planning to use the User Instance feature, you probably are going
to need to use SSPI in which case the connection string does not change.

You also might benefit from the day-long workshop I'm giving in Vancouver BC
on November 26th (DevTeach).

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

"Mike TI" <tanveer@creativepk.com> wrote in message
news:u%23%23%23003GIHA.3600@TK2MSFTNGP06.phx.gbl...
> Oct 30, 2007
>
> Hi all
>
> Using VB.Net 2005 & SQL 2005.
>
> I tried to use a database on my local PC and a remote database.
>
> Now is there a way I can make the connection string flexible. I tried to
> change the connection string in the APP.CONFIG file however I was not
> successful. It would be nice if I am able to store the connection string
> in
> some kind of a text file.
>
> What I may be doing wrong.
>
> Mike TI.
>
>
>
>

Cor
Wed Oct 31 23:01:01 PDT 2007

There are a lot of tips here doing that, we don't seldom use the config
because it makes the reading more difficult.

http://www.vb-tips.com/dbpages.aspx?IA=AD1

We are again busy recreating the site, it is functional however not nice. By
instance the text in this first tip header is so long here that you probably
not see that it is just a listbox with a slider at the end.

Cor