VB.Net MsSQL connection test

[enigma]

hi everyone,

im making a little code to test the funktionality of Linux and Windows web and DB servers.

i am having a little problem with the DB test on the .Net side (as im not so experienced with .Net)

can someone maybe give me a VB.Net code which connects to a MsSQL DB and then returns a success or failed message?

that would be all i need to finish my little quicky server tester =P
therefore thank you very much for your help!

[kenobewan]

Use a try/catch block and write err.description in the catch. Here is a site that will provide your - connection string:

[enigma]

Use a try/catch block and write err.description in the catch. Here is a site that will provide your - connection string:

can you provide me a syntax for the catch?

[kenobewan]

Try
'some code
Catch
label1.text = err.description
Finally
'optional code
End Try

[enigma]

so this should work?

Try
    Data Source=localhost;Initial Catalog=sq0001;User Id=***;Password=***;
Catch
    label1.text = err.description
Finally
    label1.text = "success"
End Try
 

[bergy]

I had to do the same thing a week or so ago, here is my exact source code. Obviously you're going to have declare a connectString variable and put your SQL connection string inside of it.

Dim connectString as String = ""
Try
     Dim objConn As SqlConnection = New SqlConnection(connectString)
     objConn.Open()
     objConn.Close()
     Msgbox("Successfully connected to database!")
Catch ex As Exception
     Msgbox("Cannot connect, Error:" & ex.Message)
End Try

[kenobewan]

Normally we want to connect & execute so we place these in the try. Success occurs in the try, so better to label it there and leave the finally to tidy up and close connections.

[AricC]

Don't know if this will help, but here is how I do it

    ' SQL Server Connection string                             
    'Private g_strDatabaseConnectionString As String = "Server=(Local);" & _
    '                                                  "Database=CoursesAndStudents;" & _
    '                                                  "User ID=sa;" & _
    '                                                  "Password=;"
 

Then open and close:

    '-------------------------------------------------------------------------
    '  Name: OpenDatabaseConnection
    '  Abstract: Open a connection to the database.
    '-------------------------------------------------------------------------
    Public Function OpenDatabaseConnection() As Boolean
 
        Try
 
            ' Open a connection to the database
            g_conAdministrator.ConnectionString = g_strDatabaseConnectionString
            g_conAdministrator.Open()
 
            ' Set connection for Command object
            g_cmdSelect.Connection = g_conAdministrator
 
            ' Set the SQL Command for the data adapter
            g_daCoursesAndStudents.SelectCommand = g_cmdSelect
            g_cbSQLCommands.DataAdapter = g_daCoursesAndStudents
 
            ' Return result
            OpenDatabaseConnection = True
 
        Catch excError As Exception
 
            ' Display error message
            WriteLog(excError.ToString)
 
        End Try
 
    End Function
 
 
    '-------------------------------------------------------------------------
    '  Name: CloseDatabaseConnection
    '  Abstract: If the database connection is open then close it.  Release the
    '           memory.
    '-------------------------------------------------------------------------
    Public Function CloseDatabaseConnection() As Boolean
 
        Try
 
            ' If the connection is open then close it
            If g_conAdministrator.State <> ConnectionState.Closed Then g_conAdministrator.Close()
 
            CloseDatabaseConnection = True
 
        Catch excError As Exception
 
            ' Display error message
            WriteLog(excError.ToString)
 
        End Try
 
    End Function
 

I hope I didn't confuse you.

[enigma]

not too confused =P i can code php, vb(wsh), js, as and some other things its just that im new to vb.net (knowing the syntax is the biggest problem)

well lets get to the point, the only problem i have with understanding is this part:

WriteLog(excError.ToString)

what does this do egsactly? and how can i output the string to a html page?

[enigma]

ok i tried this code to test it, i just left the error return part out to make a test but i get a runtime error. heres the egsact code (well with the password changed to ****) =P ::

Private g_strDatabaseConnectionString As String = "Server=(Local);" & _
                                                  "Database=sq0001;" & _
                                                  "User ID=sq0001;" & _
                                                  "Password=******;"
 
    Public Function CloseDatabaseConnection() As Boolean
 
        Try
 
            ' If the connection is open then close it
            If g_conAdministrator.State <> ConnectionState.Closed Then g_conAdministrator.Close()
 
            CloseDatabaseConnection = True
 
        tst2.Text="MsSQL Test: successful"
 
        Catch excError As Exception
 
            ' Display error message
            tst2.Text="MsSQL Test: Failed"
 
        End Try
 
    End Function
 

[AricC]

not too confused =P i can code php, vb(wsh), js, as and some other things its just that im new to vb.net (knowing the syntax is the biggest problem)

well lets get to the point, the only problem i have with understanding is this part:

WriteLog(excError.ToString)

what does this do egsactly? and how can i output the string to a html page?

WriteLog is another function I have in a Module that that writes the error to a text file (great for debugging) the ToString Alerts the user.

[AricC]

ok i tried this code to test it, i just left the error return part out to make a test but i get a runtime error. heres the egsact code (well with the password changed to ****) =P ::

Private g_strDatabaseConnectionString As String = "Server=(Local);" & _
                                                  "Database=sq0001;" & _
                                                  "User ID=sq0001;" & _
                                                  "Password=******;"
 
    Public Function CloseDatabaseConnection() As Boolean
 
        Try
 
            ' If the connection is open then close it
            If g_conAdministrator.State <> ConnectionState.Closed Then g_conAdministrator.Close()
 
            CloseDatabaseConnection = True
 
        tst2.Text="MsSQL Test: successful"
 
        Catch excError As Exception
 
            ' Display error message
            tst2.Text="MsSQL Test: Failed"
 
        End Try
 
    End Function
 

Did it work for you? Instead of a text box why not just use a msgbox? Same result just doesn't end up on your form.

[AricC]

WriteLog(excError.ToString)

Do you want to see this function? I use it in all of my VB.Net apps? It is great for debugging etc... If a user encounters a problem when they call I say send me this file in this location. It works wonders if you are dealing with people in other locations.

[enigma]

yeah send it me plz =)

i would actually preffer seeing the resulting message on the html page, not in a popup or simmilar, as the other codes i did in other languages display it that way.

and no it didnt work =/ i get a runtime error, but this might be caused by something else, ill show u the whole code:

<%@ Page Language="VB" %>
<script runat="server">
 
    Sub Page_Load
        tst1.Text=".Net Test: successful"
    End Sub
 
 
Private g_strDatabaseConnectionString As String = "Server=(Local);" & _
                                                  "Database=sq0002;" & _
                                                  "User ID=sq0002;" & _
                                                  "Password=******;"
 
    Public Function CloseDatabaseConnection() As Boolean
 
        Try
 
            ' If the connection is open then close it
            If g_conAdministrator.State <> ConnectionState.Closed Then g_conAdministrator.Close()
 
            CloseDatabaseConnection = True
 
        tst2.Text="MsSQL Test: successful"
 
        Catch excError As Exception
 
            ' Display error message
            tst2.Text="MsSQL Test: Failed"
 
        End Try
 
    End Function
 
</script>
 
<html>
<head></head>
<body>
 
    <form runat="server">
        <asp:Label id="tst1" runat="server"></asp:Label>
        <asp:Label id="tst2" runat="server"></asp:Label>
    </form>
 
</body>
</html>
 
 

[AricC]

Here 3 pretty cool functions that have really helped me with debugging.

#Region "Error Log"
 
    '-------------------------------------------------------------------------
    '  Name: DeleteOldFiles
    '  Abstract: Delete any files older than 10 days.
    '-------------------------------------------------------------------------
    Private Sub DeleteOldFiles(ByVal strFileMask As String)
 
        Try
 
            Dim strLogFile As String
            Dim strLogFilePath As String
            Dim dtmFileCreated As Date
            Dim intDaysOld As Integer
 
            ' Path
            strLogFilePath = Application.StartupPath & "\Log\"
 
            ' Look for any file
            strLogFile = Dir(strLogFilePath & strFileMask, vbNormal)
 
            ' Are there any?
            While strLogFile <> ""
 
                ' When was the file created
                dtmFileCreated = FileDateTime(strLogFilePath & strLogFile)
 
                ' How old is the file
                intDaysOld = DateDiff("d", dtmFileCreated, Now)
 
                ' Is the file older than 10 days?
                If intDaysOld > 10 Then
 
                    ' Yes.  Delete it.
                    Kill(strLogFilePath & strLogFile)
 
                End If
 
                ' Keep checking
                strLogFile = Dir()
 
            End While
 
        Catch excError As Exception
 
            ' Display error message
            MsgBox("Error:" & vbCrLf & vbCrLf & excError.ToString, MsgBoxStyle.Critical)
 
        End Try
 
    End Sub
 
 
    '-------------------------------------------------------------------------
    '  Name: GetLogFile
    '  Abstract: Open the log file for writing.  Use today's date as part of
    '           the file name.  Each day a new log file will be created.
    '           Makes debug easier.
    '-------------------------------------------------------------------------
    Private Function GetLogFile() As Integer
 
        Try
 
            Dim strToday As String     ' Todays data YYMMDD format
            Dim strLogFilePath As String
 
            ' Get todays date
            strToday = Format(Now, "yyyyMMdd")     ' Used in name of log file
 
            ' Log everything in a log directory off of the current application directory
            strLogFilePath = Application.StartupPath & "\Log\" & strToday & g_strLOG_FILE
            'Application.
 
            ' Is this a new day?
            If g_strOldLogFilePath <> strLogFilePath Then
 
                ' Does the log directory exist?
                If Dir(Application.StartupPath & "\Log", vbDirectory) = "" Then
 
                    ' No, so create it
                    MkDir(Application.StartupPath & "\Log")
 
                End If
 
                ' Save the log file name
                g_strOldLogFilePath = strLogFilePath
 
                'Close file.
                If g_intLogFile > 0 Then FileClose(g_intLogFile)
 
                'Get a free file handle
                g_intLogFile = FreeFile()
 
                'Open file and append
                FileOpen(g_intLogFile, strLogFilePath, OpenMode.Append)
 
                ' Delete old log files
                DeleteOldFiles("*.Log")
 
            End If
 
            ' Return result
            GetLogFile = g_intLogFile
 
        Catch excError As Exception
 
            ' Display error message
            MsgBox("Error:" & vbCrLf & vbCrLf & excError.ToString, MsgBoxStyle.Critical)
 
        End Try
 
    End Function
 
 
    '-------------------------------------------------------------------------
    '  Name: WriteLog
    '  Abstract: Write a message to the error log.
    '-------------------------------------------------------------------------
    Public Sub WriteLog(ByVal strMessage As String, Optional ByVal blnDisplay As Boolean = True)
 
        Try
 
            Dim intLogFile As Integer    ' File handle
 
            ' Warn the user?
            If blnDisplay = True Then
 
                ' Yes
                MsgBox(strMessage, vbCritical)
 
            End If
 
            ' Append a date/time stamp
            strMessage = Format(Now, "yyyy/MM/dd HH:mm:ss") & " - " & strMessage & vbCrLf
 
            ' Get a free file handle
            intLogFile = GetLogFile()
 
            ' Is the file handle OK?
            If intLogFile > 0 Then
 
                ' Log it
                PrintLine(intLogFile, strMessage)
 
            End If
 
        Catch excError As Exception
 
            ' Display error message
            MsgBox("Error:" & vbCrLf & vbCrLf & excError.ToString, MsgBoxStyle.Critical)
 
        End Try
 
    End Sub
 
#End Region