ado control connectionstring setting

[antvon]

Hi

I apologise for repeating this question but I posted the
original with an incorrect subject heading.

I need to programmatically change an ado's connectionstring
at runtime.

But I have a problem with the original string in the adocontrol's connectionstring property

ie. ado control's connectionstring set to
Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=CompanyX;Data Source=MRX

In my main form's load event I set the control's
connectionstring to
cnnstr =
Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=CompanyY;Data Source=MRY

adocontrol.connectionstring = cnnstr
adocontrol.refresh

As soon as the form load event occurs however it tries
to connect to the control's original connection string
and fails to connect.Then the form load event passes the
correct string and it connects fine.

How do I set the connection without connecting to the
original connection?

 

[mattKnight]

I suggest you could clear the connection string property of the adocontrol at design time.

Alternatively, you could start the project from sub main() in a module a bit like this..

dim frmTempMain as frmMain 'or whatever your form is called
dim strConn as string
set frmTempMain = new frmMain
' set your connection string up from wherever
' e.g. registry or text file
' or in this case hardcoded

strConn = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=CompanyY;Data Source=MRY"




frmtempMain.adocontrol.connectionstring = strConn

frmTempMain.show vbmodal

'tidy up after main app
set frm = nothing

end sub

This way the connection string is set before the adocontrol tries to connect.

I haven't tried this code, especially as I don't usually use the ADODC controls,but it should solve your problem. If it doesn't post back and i'll help more


Take Care

Matt
If at first you don't succeed, skydiving is not for you.

 

[antvon]

Hi Matt

Thanks for the reply
I've tried your suggestion as I did have a Sub Main()
but it still tries to connect to the ado original
connection setting .

I've tried the same scenario on a Access db connection
and it doesn't complain. Is there perhapes some sort
of constantly active linking to sql server going on in
the background I wonder (sucking on straws).There could
be a setting on the control I'm not aware of that allows
us to change it with out linking evertime its changed.

I cannot believe that this is so hard for me to do.I must
be missing something silly somewhere

What do you normally use? ado code and listview?

Anton

 

[mattKnight]

antvon

the following works for me

    Dim frm1 As Form1

    Set frm1 = New Form1

    frm1.Show vbModal

    Set frm1 = Nothing
 
    strTemp = GetNewConnectionStringFromSomewhere()
    Set frm1 = New Form1
    frm1.Adodc1.ConnectionString = strTemp
    frm1.Show vbModal
    Set frm1 = Nothing

I had both the recordsource and the authentication
set in the ADODC properties. The change in string actually just pointed the code at a different DB.

I suspect that the thing that will resolve it is moveing all references to the adodc control from the form load event to the from activate event. What I suggest is happening is that as you load the form the ADODC opens the connection to the server using its pre-set connection string, then some time after you try to alter the connection string to something else on an already open connection

As I say, it worked for me!


Take Care

Matt
If at first you don't succeed, skydiving is not for you.

 

[mattKnight]

BTW I was connecting to SQL server 2000

Take Care

Matt
If at first you don't succeed, skydiving is not for you.

 

[CasperTFG]

antvon. Make sure that you change the connection string before loading or activating the form.

So from a BAS module have a sub main. Then do these items in this order.

...
frm1.AdoCon.ConnectionString = "...."
...
frm1.Show
...

Craig

"I feel sorry for people who don't drink. When they wake up in the morning, that's as good as they're going to feel all day."
~Frank Sinatra

 

[antvon]

Hi

Sorry for the extremely late reply
Had no time , just became a dad
Thanks for the tips I do appreciate them

Anton