• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 332
  • Last Modified:

Changing VB Data Environment Connection

Hi Guys,

I need to set the CommandText of a Connection in the Data Environment at runtime. Basically the Datasource, Username, Password, Initial Catalog are all read in from the registry and may vary depending on the SQL server that the application is running on.

The problem I am having is that when I set the connection string I get an error. I am using "DE1.Conn1.ConnectionString = "Blah Blah Blah...."

Thanks in advance...

  • 5
  • 5
1 Solution
Éric MoreauSenior .Net ConsultantCommented:
>>I set the connection string I get an error
Which error?

You need to set the ConnectionString before using it. This is why, this setting is normally done into a "Sub Main" into a standard module and the startup object of your project is set to "Sub Main".
garrenbAuthor Commented:
My Conn1.ConnectionString is :
Provider=MSDataShape.1;Persist Security Info=True;User ID=projectx;Password=projectx;Data Provider=SQLOLEDB.1
(this is set in the sub-main section)

I then change my CommandText in MyCmd to ReQuery the database :
For Each myCmd In DE1.Commands
    If myCmd.Name = "Cmd1" Then
        'Debug.Print myCmd.CommandText
         strSQL = "SHAPE {SELECT PopHeader.PopNo, PopHeader.SuppAcNo, PopHeader.PopDate, PopHeader.Ref, PopHeader.Notes, PopHeader.CarraigeNet, PopHeader.CarraigeVAT, PopHeader.CarraigeTotal, Suppliers.SupplierName, Suppliers.SupplierStreet1, Suppliers.SupplierStreet2, Suppliers.SupplierTown, Suppliers.SupplierCounty FROM PopHeader INNER JOIN Suppliers ON PopHeader.SuppAcNo = Suppliers.SupplierAcNo WHERE (PopHeader.PopNo = " & PopId & ")}  AS Cmd1 APPEND ({SELECT PopDetails.PopNo, PopDetails.PartNo, PopDetails.Qty, PopDetails.UnitCost, PopDetails.SubTotal, PopDetails.PlusVat, PopDetails.Total, StockHeader.Description FROM PopDetails INNER JOIN StockHeader ON PopDetails.PartNo = StockHeader.PartNo}  AS CmdChild RELATE 'PopNo' TO 'PopNo') AS CmdChild"

         myCmd.CommandText = strSQL

However when I execute the command :

I get an error :
     "Invalid Object Name - PopDetails"

If I don't attempt to change the Conn1.connectionString the above code works perfect.

It only crashes if I programmatically change the Conn1.ConnectionString
Éric MoreauSenior .Net ConsultantCommented:
Are you opening the new connection?

Just before executing the command, print the value of the connection string to see which one is active.

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

garrenbAuthor Commented:
>>>Are you opening the new connection?

The value of the ConnectionString is as above:
Provider=MSDataShape.1;Persist Security Info=True;User ID=projectx;Password=projectx;Data Provider=SQLOLEDB.1
Éric MoreauSenior .Net ConsultantCommented:
where is your "Initial Catalog"?
garrenbAuthor Commented:
I suppose I should say that the data environment already exists and has a data report attached to it.

I don't know if this makes any difference.
Éric MoreauSenior .Net ConsultantCommented:
if you change the connection string before anything connects to it, there should be no problems.

Have you check for the "Initial Catalog"?
garrenbAuthor Commented:
emoreau :
in the Sub Main I used :
With DE1
        .Conn1.CursorLocation = adUseClient
        .Conn1.ConnectionString = "Provider......."
End With

Before I didn't use the CursorLocation bit. This seems to have worked. I'll try it out on a different server and if everything is ok - the points are yours...

Éric MoreauSenior .Net ConsultantCommented:
I don't think it should make differences. Any news about "Initial Catalog"?
Try this code over there.. this will be used to set
the dataenvironment at run time... it works .. i  think
this codes actually links to data report... see to it
if u can gain any idea from this one...

If you want to set the command at runtime:

   'close the enviroment if open
   If DataEnvironment1.Connection1.State = adStateOpen Then
   End If
   'change the sql statement, this also reopens the dataenvironment!
   DataEnvironment1.Commands("Command1").CommandText = "select * from tblClause"
   'change the datafield is nessacary
   DataReport1.Sections("Section1").Controls("Text1").DataField = "ClauseName"
   'open the report
   DataReport1.Show vbModal
   'destroy the report, vb sucks in doing this for you and causes memory leaks
   Unload DataReport1
garrenbAuthor Commented:
Thanks for the code sample but:
My problem was that I couldn't force a different connection source for the DE to read from. Requerying the data was no problem.

The initial catalog was set OK.
I did get it to work...
What I had to do was estalish a valid connection in the DE using the DE. I could then change the user id, password, datasource & initial catalog to anything I wanted. Then my code would work & force a new connection.

So therefore the lesson learned is that I needed to set the DE up initially with a valid connection string, then force my code to change it with the entries read in from the registry.


Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now