Howto check if sql connection is good.
Howto check if sql connection is good.
Howto check if sql connection is good.
Howto check if sql connection is good.
Howto check if sql connection is good.
Howto check if sql connection is good. Howto check if sql connection is good. Howto check if sql connection is good. Howto check if sql connection is good. Howto check if sql connection is good. Howto check if sql connection is good. Howto check if sql connection is good. Howto check if sql connection is good.
Howto check if sql connection is good. Howto check if sql connection is good.
Howto check if sql connection is good.
Go Back  Xtreme Visual Basic Talk > > > Howto check if sql connection is good.


Reply
 
Thread Tools Display Modes
  #1  
Old 05-31-2009, 08:56 AM
Kernal69 Kernal69 is offline
Newcomer
 
Join Date: Apr 2009
Posts: 16
Default Howto check if sql connection is good.


Hi

I have a little problem with checking if a sql connection is good.

Attached here i have a simple vb.net program that should check if the sql connection to the sql server is good.
If i just run it once it works very well.
But if i use it in a timer and check the connection to the sql server every 10sec it will only tell me that the connection is good (V=0).

I have tested the code with turning of the sql server service.

What is wrong ?
Please help me !


Dim connect As String = "Server=192.168.1.2;Database=Test;User ID=sa;Password=12345;Trusted_Connection=False"
Dim sqlconnection As New System.Data.SqlClient.SqlConnection(connect)


Dim v As Integer = 0

Try

sqlconnection.Open()
sqlconnection.Close()

Catch

v = 1

Finally

sqlconnection.Dispose()

End Try

TextBox1.Text = v
Reply With Quote
  #2  
Old 06-01-2009, 02:39 AM
michelleqw michelleqw is offline
Junior Contributor
 
Join Date: Jun 2005
Posts: 212
Default

Hello Kernal69,

After creating sql server test software it took 344 times checking before there was found a not running server.

The reason: ??

Nice regards,
Michelle.
Reply With Quote
  #3  
Old 06-01-2009, 11:32 AM
Kernal69 Kernal69 is offline
Newcomer
 
Join Date: Apr 2009
Posts: 16
Default

Hi

I know , this is driving me nuts.
And i really need this.


Somebody who know what the reason could be ?
Reply With Quote
  #4  
Old 06-01-2009, 01:35 PM
michelleqw michelleqw is offline
Junior Contributor
 
Join Date: Jun 2005
Posts: 212
Default

Hello Kernal69,

This afternoon I got the solution in my mind. If the connection don't gives the answer, a small selection must give an error with a stopped server.

It's working, check my script.

Private Function testConnection1() As Integer
Dim Conn As New ADODB.Connection
Dim RSselect As New ADODB.Recordset

Try
Conn.Open("your connection string to NorthWind")
RSselect.Open("select top 1 * from Products", Conn)

RSselect.Close()
Conn.Close()

testConnection1 = 1
Catch ex As Exception

End Try

End Function

Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick

Timer1.Enabled = False
TextBox1.Text = testConnection1()
Timer1.Enabled = True

System.Windows.Forms.Application.DoEvents()
End Sub

Nice regards,
Michelle.
Reply With Quote
  #5  
Old 06-02-2009, 10:53 AM
Kernal69 Kernal69 is offline
Newcomer
 
Join Date: Apr 2009
Posts: 16
Default

Hi

Thank you
That helped !

Here is what i did.

Dim connect As String = "Server=192.168.1.2;Database=Test;User ID=sa;Password=12345;Trusted_Connection=False"
Dim sqlconnection As New System.Data.SqlClient.SqlConnection(connect)

Dim command As String = "SELECT * from Table"
Dim sqlcommand As New System.Data.SqlClient.SqlCommand(Command)
SqlCommand.Connection = sqlconnection

Dim v As Integer = 0

Try

sqlconnection.Open()

sqlcommand.ExecuteNonQuery()

sqlconnection.Close()

Catch

v = 1
Finally
sqlconnection.Dispose()

End Try

TextBox1.Text = v


Best regards
Reply With Quote
  #6  
Old 06-21-2009, 01:28 AM
torpkevuk's Avatar
torpkevuk torpkevuk is offline
Contributor
 
Join Date: Oct 2003
Location: Nebraska, USA
Posts: 640
Default

your post is weird..im not sure what your actual problem is
you're checking if your connection is good. well connecting would do that

selecting from a table would do go further, but..

do NOT

SELECT * from table

what happens when that table is 5 million rows in.. your app slows down to ****..
Reply With Quote
  #7  
Old 06-21-2009, 01:31 AM
torpkevuk's Avatar
torpkevuk torpkevuk is offline
Contributor
 
Join Date: Oct 2003
Location: Nebraska, USA
Posts: 640
Default

as a side note, do NOT connect as sa. disable sa now and recreate a new user. sa is AWFUL.
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Howto check if sql connection is good.
Howto check if sql connection is good.
Howto check if sql connection is good. Howto check if sql connection is good.
Howto check if sql connection is good.
Howto check if sql connection is good.
Howto check if sql connection is good. Howto check if sql connection is good. Howto check if sql connection is good. Howto check if sql connection is good. Howto check if sql connection is good. Howto check if sql connection is good. Howto check if sql connection is good.
Howto check if sql connection is good.
Howto check if sql connection is good.
 
Howto check if sql connection is good.
Howto check if sql connection is good.
 
-->