Error Handling on SQL DB connection

[Rob7412]

How can I bring up my login.frm if the app cannot connect to a SQL db so they can change their login credentials. I store the values in the registry and I change these values through my login.frm. I basically need it NOT to Crash and show the user the login.frm

 

[Andrzejek]

What do you have so far?

What's your error handling looks like?

What do you want to happen when you can not connect to your db?

Show your code.

---- Andy

 

[Rob7412]

I am pulling the connection values from the registry all works fine unless the login is incorrect or the server address is incorrect Basically have this connection string going.

conn.Open "Provider=SQLOLEDB.1;User ID=" & dbUser & ";Password=" & dbPassword & ";Database=" & dbname & ";Server=" & server1 & ""

Really all i want is if the connection cannot be made using the above connection string show me the login.frm so they can change the login values.

 

[Andrzejek]

Wrap your code with an error handler

On Error GoTo MyErrorHandler

... your code here
   conn.Open "Provider=SQLOLEDB.1;User ID=" & dbUser & ";Password=" & dbPassword & ";Database=" & dbname & ";Server=" & server1 & ""

Exit Sub

MyErrorHandler:
If Err.Number = 1234 then
    'Connection not established
    .... put some code here
End If

Detect what is the Error Number (it is not 1234) when the connection fails, trap that error and do what you want.

HTH

---- Andy

 

[Rob7412]

I used this below but still get a runtime error 3709 and the app crahes


On Error GoTo MyErrorHandler

... your code here
conn.Open "Provider=SQLOLEDB.1;User ID=" & dbUser & ";Password=" & dbPassword & ";Database=" & dbname & ";Server=" & server1 & ""

Exit Sub

MyErrorHandler:
If Err.Number = 1234 then
'Connection not established
frmlogin.show
End If


If Knowledge were power I would be a AAA Battery!

 

[Rob7412]

Sorry this is the correct code used

On Error GoTo MyErrorHandler

... your code here
conn.Open "Provider=SQLOLEDB.1;User ID=" & dbUser & ";Password=" & dbPassword & ";Database=" & dbname & ";Server=" & server1 & ""

Exit Sub

MyErrorHandler:
If Err.Number <> 0 then
'Connection not established
frmlogin.show
End If


If Knowledge were power I would be a AAA Battery!

 

[Andrzejek]

Where did you put this code with the error handler?
Form_Load? Button_Click?

Show me the whole code for the Sub.

error 3709 - is that the error when connection is not established?

--- Andy

 

[Rob7412]

The code is in a module which is called from the form load of the form. OpenDB() Below is the code in the module.

Public Sub OpenDB()
'this procedure is to be called in every form under the Form_Load event
If GetString(HKEY_LOCAL_MACHINE, "SOFTWARE\ License Manager", "") <> ERROR_SUCCESS Then
Dim RegkeyExist
RegkeyExist = "1"
Else
Call SaveString(HKEY_LOCAL_MACHINE, "SOFTWARE\ License Manager", "", "")
End If
If GetString(HKEY_LOCAL_MACHINE, "SOFTWARE\ License Manager", "SQL SERVER") <> ERROR_SUCCESS Then
Dim RegkeyExist1
RegkeyExist1 = "1"
Else
Call SaveString(HKEY_LOCAL_MACHINE, "SOFTWARE\ License Manager", "SQL SERVER", "")
End If
If GetString(HKEY_LOCAL_MACHINE, "SOFTWARE\ License Manager", "Username") <> ERROR_SUCCESS Then
Dim RegkeyExist2
RegkeyExist2 = "1"
Else
Call SaveString(HKEY_LOCAL_MACHINE, "SOFTWARE\ License Manager", "Username", "")
End If
If GetString(HKEY_LOCAL_MACHINE, "SOFTWARE\ License Manager", "Password") <> ERROR_SUCCESS Then
Dim RegkeyExist3
RegkeyExist3 = "1"
Else
Call SaveString(HKEY_LOCAL_MACHINE, "SOFTWARE\ License Manager", "Password", "")
End If
If GetString(HKEY_LOCAL_MACHINE, "SOFTWARE\ License Manager", "SQL Port") <> ERROR_SUCCESS Then
Dim RegkeyExist4
RegkeyExist4 = "1"
Else
Call SaveString(HKEY_LOCAL_MACHINE, "SOFTWARE\ License Manager", "SQL Port", "1433")
End If
If GetString(HKEY_LOCAL_MACHINE, "SOFTWARE\ License Manager", "DBNAME") <> ERROR_SUCCESS Then
Dim RegkeyExist5
RegkeyExist5 = "1"
Else
Call SaveString(HKEY_LOCAL_MACHINE, "SOFTWARE\ License Manager", "DBNAME", "licensing")
End If
If GetString(HKEY_LOCAL_MACHINE, "SOFTWARE\ License Manager", "SQL SERVER") = "" Then

connectionfrm.Show
frmSample.Hide
End If
If GetString(HKEY_LOCAL_MACHINE, "SOFTWARE\ License Manager", "Username") = "" Then

connectionfrm.Show
frmSample.Hide
End If
If GetString(HKEY_LOCAL_MACHINE, "SOFTWARE\ License Manager", "Password") = "" Then

connectionfrm.Show
frmSample.Hide
End If


Dim dbUser As String, dbPassword As String, dbname As String, server1 As String, sqlport As String
dbUser = GetString(HKEY_LOCAL_MACHINE, "SOFTWARE\ License Manager", "Username")
dbPassword = GetString(HKEY_LOCAL_MACHINE, "SOFTWARE\ License Manager", "Password")
dbname = GetString(HKEY_LOCAL_MACHINE, "SOFTWARE\ License Manager", "DBNAME")
server1 = GetString(HKEY_LOCAL_MACHINE, "SOFTWARE\ License Manager", "SQL Server")
sqlport = GetString(HKEY_LOCAL_MACHINE, "SOFTWARE\ License Manager", "SQL Port")
'dbServer value to be entered in the txtServer textbox under the Login Form
'If NetworkSelect.netselect2 = True Then


Set conn = New ADODB.Connection
On Error GoTo MyErrorHandler
conn1 = "Provider=SQLOLEDB.1;User ID=" & dbUser & ";Password=" & dbPassword & ";Database=" & dbname & ";Server=" & server1 & """"
On Error GoTo MyErrorHandler
conn.Open "Provider=SQLOLEDB.1;User ID=" & dbUser & ";Password=" & dbPassword & ";Database=" & dbname & ";Server=" & server1 & ""
conntxt = conn
Exit Sub


MyErrorHandler:
If Err.Number <> 0 Then
'Connection not established
frmLogin.Show
End If



End Sub


If Knowledge were power I would be a AAA Battery!

 

[Andrzejek]

Set conn = New ADODB.Connection

On Error GoTo MyErrorHandler

conn1 = "Provider=SQLOLEDB.1;User ID=" & dbUser & ";Password=" & dbPassword & ";Database=" & dbname & ";Server=" & server1 & """"
[green]'On Error GoTo MyErrorHandler[/green]
conn.Open "Provider=SQLOLEDB.1;User ID=" & dbUser & ";Password=" & dbPassword & ";Database=" & dbname & ";Server=" & server1 & ""
conntxt = conn

Exit Sub

MyErrorHandler:
If Err.Number <> 0 Then
    'Connection not established
    [red]MsgBox "Connection not established"[/red]
    frmLogin.Show
    [red]Exit Sub[/red]
End If

End Sub

Make sure in VB you have: Tools - Options...General Tab:
Error Trapping set to Break in Class Module (middle option)

If you have the first option chosen (Break on All Errors) you will never get to MyErrorHandler

Have fun

---- Andy

 

[Andrzejek]

Line

On Error GoTo MyErrorHandler

once is enough, you do not need to have this line twice in your code, that's why I comment it out.

You have 2 connections, so I assume if you do not make one of them, you should not have any connections (correct?)

You may want to:

... code here
Exit Sub

MyErrorHandler:
If Err.Number <> 0 Then
    'Connections not established [blue]
    If conn1.State = 1 Then conn1.Close
    If conn.State = 1 Then conn.Close[/blue]
 
    MsgBox "Connection not established"
    frmLogin.Show
    Exit Sub
End If

End Sub

---- Andy

 

[Rob7412]

It still crashes if the connection cannot be made. I do However get the message box twice for some reason and After I click ok on both the app still throws the 3709 error and closes. I have checked my error trapping and it is set correctly. Hmmm this is a bit of a head scratcher.

If Knowledge were power I would be a AAA Battery!

 

[Andrzejek]

That means you are getting to the error handler twice.

Put some breaks in your code and step thru it.

Just a guess here - you do not make a connection to your db, but you may, later in your code, request some info from db and that's why it crashes.

What code do you have in your Form after the line

Call OpenDB

???

---- Andy

 

[mattKnight]

I am confused...

Set conn = New ADODB.Connection
On Error GoTo MyErrorHandler
conn1 = "Provider=SQLOLEDB.1;User ID=" & dbUser & ";Password=" & dbPassword & ";Database=" & dbname & ";Server=" & server1 & """"
On Error GoTo MyErrorHandler
conn.Open "Provider=SQLOLEDB.1;User ID=" & dbUser & ";Password=" & dbPassword & ";Database=" & dbname & ";Server=" & server1 & ""
conntxt = conn

At the moment, how we obtain the values of dbUser, dbPassword, etc seems to me to be not relevant.

Why build a connection string conn1 and then not use it?

That being said, what do you get if you do a debug.print of teh connection string?

Take Care

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

 

[Andrzejek]

mattKnight,

Rob7412 is getting dbUser and dbPassword from the registry to use these 2 variables in his connection string:

Dim dbUser As String, dbPassword As String, dbname As String, server1 As String, sqlport As String
[blue][b]dbUser[/b][/blue] = GetString(HKEY_LOCAL_MACHINE, "SOFTWARE\ License Manager", "Username")
[green][b]dbPassword[/b][/green] = GetString(HKEY_LOCAL_MACHINE, "SOFTWARE\ License Manager", "Password")

[....]

Set conn = New ADODB.Connection
On Error GoTo MyErrorHandler
conn1 = "Provider=SQLOLEDB.1;User ID=" & [blue]dbUser[/blue] & ";Password=" & [green]dbPassword[/green] & ";Database=" & dbname & ";Server=" & server1 & """"
On Error GoTo MyErrorHandler
conn.Open "Provider=SQLOLEDB.1;User ID=" & [blue]dbUser[/blue] & ";Password=" & [green]dbPassword[/green] & ";Database=" & dbname & ";Server=" & server1 & ""
conntxt = conn
Exit Sub

MyErrorHandler:
If Err.Number <> 0 Then
    'Connection not established
frmLogin.Show
End If

End Sub

So his connection string may look like:

conn1 = "Provider=SQLOLEDB.1;User ID=BugsBunny;Password=MyCarrot;Database=MyDb;Server=Serv1"

What's so sonfusing about it?

--- Andy

 

[Andrzejek]

Hey, you are right mattKnight (ooops, it is late here...)

Ths code should be:

Set conn = New ADODB.Connection

On Error GoTo MyErrorHandler

conn1 = "Provider=SQLOLEDB.1;User ID=" & dbUser & ";Password=" & dbPassword & ";Database=" & dbname & ";Server=" & server1 & """"

conn.Open conn1

That's all.

---- Andy

 

[mattKnight]

Andrzejek

I am glad you got my point...

I am sure we both know there are multiple ways of building an ADODB connection.

rob7412

Please can you post a debug.print copy of your connection string
1 that works and 1 that fails?




Take Care

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

 

[harebrain]

This is "outside-the-box," but is the OP sure that the registry isn't corrupt?

 

[mattKnight]

harebrain
hence theqrequest for a debug.print of the connection string...

Take Care

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

 

[BobRodes]

<I do However get the message box twice for some reason

Often, an attempt to open a connection will cause exceptions to be thrown from more than one source. All of the errors will be contained in the Errors collection, but the error object will only contain the last error thrown. This is basically why there is an Errors collection for a Connection object in the first place. Check

http://support.microsoft.com/kb/167957

for more.

HTH

Bob

 

[eichen]

SQLOLEDB requires "InitialCatalog =" in the connection string; "Database = " is used for an MSDASQL-type connection,