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

VB Connect to ADODB Only if Necesary

I have a connect string that I am attempting to use in VB to connect only if I do not currently have a connection active.  This is the code:

    If NotificationsConn.State = 0 Then
        SQLString = "Data Source=DSource;UID=UserID;PWD=password"
        NotificationsCmd.ActiveConnection = SQLString
    End If

The problem with this code is that it creates a connection to the database every time it is run, even if there is already a connection present.  What am I doing wrong?  Thanks very much.
0
UPRRDevelopers
Asked:
UPRRDevelopers
2 Solutions
 
hesCommented:
Try this

With NotificationsConn
        If .State = adStateClosed Then .Open
End With

Open in new window

0
 
UPRRDevelopersAuthor Commented:
Thanks, but I must be doing something wrong, because even if I put your statement directly after mine, when my connect runs and successfully connects, your code still believes "adStateClosed" is true and tries to open the connection.  So this:

    If NotificationsConn.State = 0 Then
        SQLString = "Data Source=DSource;UID=UserID;PWD=password"
        NotificationsCmd.ActiveConnection = SQLString
    End If

With NotificationsConn
        If .State = adStateClosed Then .Open
End With

Always tries to re-open the connection.




0
 
jkaiosCommented:
How does NotificationsConn variable defined?  Is it defined as a "public" variable in a module?

There are some thing to consider here:

1) If the NotificationsConn variable is defined as a "private" variable in a sub-routine, then this variable will be implicitly set to nothing, when the procedure in which it is defined, ends (thus setting the State to adStateClosed).

2) If it is defined as a "public" variable, then that little piece of code will work.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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