469,280 Members | 1,998 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,280 developers. It's quick & easy.

VB.Net MsSQL connection test

9
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!
Mar 13 '07 #1
14 28059
kenobewan
4,871 Expert 4TB
Use a try/catch block and write err.description in the catch. Here is a site that will provide your - connection string:
Mar 13 '07 #2
enigma
9
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?
Mar 13 '07 #3
kenobewan
4,871 Expert 4TB
Try
'some code
Catch
label1.text = err.description
Finally
'optional code
End Try
Mar 13 '07 #4
enigma
9
so this should work?

Expand|Select|Wrap|Line Numbers
  1. Try
  2.     Data Source=localhost;Initial Catalog=sq0001;User Id=***;Password=***;
  3. Catch
  4.     label1.text = err.description
  5. Finally
  6.     label1.text = "success"
  7. End Try
  8.  
Mar 13 '07 #5
bergy
89
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.

Expand|Select|Wrap|Line Numbers
  1. Dim connectString as String = ""
  2. Try
  3.      Dim objConn As SqlConnection = New SqlConnection(connectString)
  4.      objConn.Open()
  5.      objConn.Close()
  6.      Msgbox("Successfully connected to database!")
  7. Catch ex As Exception
  8.      Msgbox("Cannot connect, Error:" & ex.Message)
  9. End Try
Mar 13 '07 #6
kenobewan
4,871 Expert 4TB
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.
Mar 14 '07 #7
AricC
1,892 Expert 1GB
Don't know if this will help, but here is how I do it
Expand|Select|Wrap|Line Numbers
  1.     ' SQL Server Connection string                             
  2.     'Private g_strDatabaseConnectionString As String = "Server=(Local);" & _
  3.     '                                                  "Database=CoursesAndStudents;" & _
  4.     '                                                  "User ID=sa;" & _
  5.     '                                                  "Password=;"
  6.  
Then open and close:
Expand|Select|Wrap|Line Numbers
  1.     '-------------------------------------------------------------------------
  2.     '  Name: OpenDatabaseConnection
  3.     '  Abstract: Open a connection to the database.
  4.     '-------------------------------------------------------------------------
  5.     Public Function OpenDatabaseConnection() As Boolean
  6.  
  7.         Try
  8.  
  9.             ' Open a connection to the database
  10.             g_conAdministrator.ConnectionString = g_strDatabaseConnectionString
  11.             g_conAdministrator.Open()
  12.  
  13.             ' Set connection for Command object
  14.             g_cmdSelect.Connection = g_conAdministrator
  15.  
  16.             ' Set the SQL Command for the data adapter
  17.             g_daCoursesAndStudents.SelectCommand = g_cmdSelect
  18.             g_cbSQLCommands.DataAdapter = g_daCoursesAndStudents
  19.  
  20.             ' Return result
  21.             OpenDatabaseConnection = True
  22.  
  23.         Catch excError As Exception
  24.  
  25.             ' Display error message
  26.             WriteLog(excError.ToString)
  27.  
  28.         End Try
  29.  
  30.     End Function
  31.  
  32.  
  33.     '-------------------------------------------------------------------------
  34.     '  Name: CloseDatabaseConnection
  35.     '  Abstract: If the database connection is open then close it.  Release the
  36.     '           memory.
  37.     '-------------------------------------------------------------------------
  38.     Public Function CloseDatabaseConnection() As Boolean
  39.  
  40.         Try
  41.  
  42.             ' If the connection is open then close it
  43.             If g_conAdministrator.State <> ConnectionState.Closed Then g_conAdministrator.Close()
  44.  
  45.             CloseDatabaseConnection = True
  46.  
  47.         Catch excError As Exception
  48.  
  49.             ' Display error message
  50.             WriteLog(excError.ToString)
  51.  
  52.         End Try
  53.  
  54.     End Function
  55.  
I hope I didn't confuse you.
Mar 14 '07 #8
enigma
9
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?
Mar 14 '07 #9
enigma
9
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 ::

Expand|Select|Wrap|Line Numbers
  1. Private g_strDatabaseConnectionString As String = "Server=(Local);" & _
  2.                                                   "Database=sq0001;" & _
  3.                                                   "User ID=sq0001;" & _
  4.                                                   "Password=******;"
  5.  
  6.     Public Function CloseDatabaseConnection() As Boolean
  7.  
  8.         Try
  9.  
  10.             ' If the connection is open then close it
  11.             If g_conAdministrator.State <> ConnectionState.Closed Then g_conAdministrator.Close()
  12.  
  13.             CloseDatabaseConnection = True
  14.  
  15.         tst2.Text="MsSQL Test: successful"
  16.  
  17.         Catch excError As Exception
  18.  
  19.             ' Display error message
  20.             tst2.Text="MsSQL Test: Failed"
  21.  
  22.         End Try
  23.  
  24.     End Function
  25.  
Mar 14 '07 #10
AricC
1,892 Expert 1GB
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.
Mar 15 '07 #11
AricC
1,892 Expert 1GB
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 ::

Expand|Select|Wrap|Line Numbers
  1. Private g_strDatabaseConnectionString As String = "Server=(Local);" & _
  2.                                                   "Database=sq0001;" & _
  3.                                                   "User ID=sq0001;" & _
  4.                                                   "Password=******;"
  5.  
  6.     Public Function CloseDatabaseConnection() As Boolean
  7.  
  8.         Try
  9.  
  10.             ' If the connection is open then close it
  11.             If g_conAdministrator.State <> ConnectionState.Closed Then g_conAdministrator.Close()
  12.  
  13.             CloseDatabaseConnection = True
  14.  
  15.         tst2.Text="MsSQL Test: successful"
  16.  
  17.         Catch excError As Exception
  18.  
  19.             ' Display error message
  20.             tst2.Text="MsSQL Test: Failed"
  21.  
  22.         End Try
  23.  
  24.     End Function
  25.  
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.
Mar 15 '07 #12
AricC
1,892 Expert 1GB
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.
Mar 15 '07 #13
enigma
9
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:

Expand|Select|Wrap|Line Numbers
  1. <%@ Page Language="VB" %>
  2. <script runat="server">
  3.  
  4.     Sub Page_Load
  5.         tst1.Text=".Net Test: successful"
  6.     End Sub
  7.  
  8.  
  9. Private g_strDatabaseConnectionString As String = "Server=(Local);" & _
  10.                                                   "Database=sq0002;" & _
  11.                                                   "User ID=sq0002;" & _
  12.                                                   "Password=******;"
  13.  
  14.     Public Function CloseDatabaseConnection() As Boolean
  15.  
  16.         Try
  17.  
  18.             ' If the connection is open then close it
  19.             If g_conAdministrator.State <> ConnectionState.Closed Then g_conAdministrator.Close()
  20.  
  21.             CloseDatabaseConnection = True
  22.  
  23.         tst2.Text="MsSQL Test: successful"
  24.  
  25.         Catch excError As Exception
  26.  
  27.             ' Display error message
  28.             tst2.Text="MsSQL Test: Failed"
  29.  
  30.         End Try
  31.  
  32.     End Function
  33.  
  34. </script>
  35.  
  36. <html>
  37. <head></head>
  38. <body>
  39.  
  40.     <form runat="server">
  41.         <asp:Label id="tst1" runat="server"></asp:Label>
  42.         <asp:Label id="tst2" runat="server"></asp:Label>
  43.     </form>
  44.  
  45. </body>
  46. </html>
  47.  
  48.  
Mar 15 '07 #14
AricC
1,892 Expert 1GB
Here 3 pretty cool functions that have really helped me with debugging.
Expand|Select|Wrap|Line Numbers
  1. #Region "Error Log"
  2.  
  3.     '-------------------------------------------------------------------------
  4.     '  Name: DeleteOldFiles
  5.     '  Abstract: Delete any files older than 10 days.
  6.     '-------------------------------------------------------------------------
  7.     Private Sub DeleteOldFiles(ByVal strFileMask As String)
  8.  
  9.         Try
  10.  
  11.             Dim strLogFile As String
  12.             Dim strLogFilePath As String
  13.             Dim dtmFileCreated As Date
  14.             Dim intDaysOld As Integer
  15.  
  16.             ' Path
  17.             strLogFilePath = Application.StartupPath & "\Log\"
  18.  
  19.             ' Look for any file
  20.             strLogFile = Dir(strLogFilePath & strFileMask, vbNormal)
  21.  
  22.             ' Are there any?
  23.             While strLogFile <> ""
  24.  
  25.                 ' When was the file created
  26.                 dtmFileCreated = FileDateTime(strLogFilePath & strLogFile)
  27.  
  28.                 ' How old is the file
  29.                 intDaysOld = DateDiff("d", dtmFileCreated, Now)
  30.  
  31.                 ' Is the file older than 10 days?
  32.                 If intDaysOld > 10 Then
  33.  
  34.                     ' Yes.  Delete it.
  35.                     Kill(strLogFilePath & strLogFile)
  36.  
  37.                 End If
  38.  
  39.                 ' Keep checking
  40.                 strLogFile = Dir()
  41.  
  42.             End While
  43.  
  44.         Catch excError As Exception
  45.  
  46.             ' Display error message
  47.             MsgBox("Error:" & vbCrLf & vbCrLf & excError.ToString, MsgBoxStyle.Critical)
  48.  
  49.         End Try
  50.  
  51.     End Sub
  52.  
  53.  
  54.     '-------------------------------------------------------------------------
  55.     '  Name: GetLogFile
  56.     '  Abstract: Open the log file for writing.  Use today's date as part of
  57.     '           the file name.  Each day a new log file will be created.
  58.     '           Makes debug easier.
  59.     '-------------------------------------------------------------------------
  60.     Private Function GetLogFile() As Integer
  61.  
  62.         Try
  63.  
  64.             Dim strToday As String     ' Todays data YYMMDD format
  65.             Dim strLogFilePath As String
  66.  
  67.             ' Get todays date
  68.             strToday = Format(Now, "yyyyMMdd")     ' Used in name of log file
  69.  
  70.             ' Log everything in a log directory off of the current application directory
  71.             strLogFilePath = Application.StartupPath & "\Log\" & strToday & g_strLOG_FILE
  72.             'Application.
  73.  
  74.             ' Is this a new day?
  75.             If g_strOldLogFilePath <> strLogFilePath Then
  76.  
  77.                 ' Does the log directory exist?
  78.                 If Dir(Application.StartupPath & "\Log", vbDirectory) = "" Then
  79.  
  80.                     ' No, so create it
  81.                     MkDir(Application.StartupPath & "\Log")
  82.  
  83.                 End If
  84.  
  85.                 ' Save the log file name
  86.                 g_strOldLogFilePath = strLogFilePath
  87.  
  88.                 'Close file.
  89.                 If g_intLogFile > 0 Then FileClose(g_intLogFile)
  90.  
  91.                 'Get a free file handle
  92.                 g_intLogFile = FreeFile()
  93.  
  94.                 'Open file and append
  95.                 FileOpen(g_intLogFile, strLogFilePath, OpenMode.Append)
  96.  
  97.                 ' Delete old log files
  98.                 DeleteOldFiles("*.Log")
  99.  
  100.             End If
  101.  
  102.             ' Return result
  103.             GetLogFile = g_intLogFile
  104.  
  105.         Catch excError As Exception
  106.  
  107.             ' Display error message
  108.             MsgBox("Error:" & vbCrLf & vbCrLf & excError.ToString, MsgBoxStyle.Critical)
  109.  
  110.         End Try
  111.  
  112.     End Function
  113.  
  114.  
  115.     '-------------------------------------------------------------------------
  116.     '  Name: WriteLog
  117.     '  Abstract: Write a message to the error log.
  118.     '-------------------------------------------------------------------------
  119.     Public Sub WriteLog(ByVal strMessage As String, Optional ByVal blnDisplay As Boolean = True)
  120.  
  121.         Try
  122.  
  123.             Dim intLogFile As Integer    ' File handle
  124.  
  125.             ' Warn the user?
  126.             If blnDisplay = True Then
  127.  
  128.                 ' Yes
  129.                 MsgBox(strMessage, vbCritical)
  130.  
  131.             End If
  132.  
  133.             ' Append a date/time stamp
  134.             strMessage = Format(Now, "yyyy/MM/dd HH:mm:ss") & " - " & strMessage & vbCrLf
  135.  
  136.             ' Get a free file handle
  137.             intLogFile = GetLogFile()
  138.  
  139.             ' Is the file handle OK?
  140.             If intLogFile > 0 Then
  141.  
  142.                 ' Log it
  143.                 PrintLine(intLogFile, strMessage)
  144.  
  145.             End If
  146.  
  147.         Catch excError As Exception
  148.  
  149.             ' Display error message
  150.             MsgBox("Error:" & vbCrLf & vbCrLf & excError.ToString, MsgBoxStyle.Critical)
  151.  
  152.         End Try
  153.  
  154.     End Sub
  155.  
  156. #End Region
  157.  
Mar 15 '07 #15

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

7 posts views Thread by mj | last post: by
2 posts views Thread by Josh Close | last post: by
reply views Thread by dan | last post: by
14 posts views Thread by guswebb | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.