Best Method to Verify Database Availability 5

[UncleCake]

I am building a wireless bar code scanner application that communicates with SQL Server very frequently. The problem that I am running into is that the scanner is sometimes going out of range of the receiver. I would like to set up something that will test to see if it was in range by seeing if the database is accessible (unless there is a better way).

The two ideas that I have are before each select, update, or delete have a sub that would try to open the database or something that would let me know if it had access to it. The seconded one is to do some type of error catching in each select, update, or delete that would let me know. Either way I don’t know how to do it.

Are either of these a better way or can anyone suggest a better method? This is my first application in VB.net, so I am not an expert. I could post some of my code for connection if needed.

-Uncle Cake

 

[chrissie1]

private function testconnection() as boolean
dim contemp as new sqlconnection
' set connectionstring
try 
  contemp.open
  return true
catch ex as exception
  return false
end try
end function

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting

 

[UncleCake]

Christiaan,

I tried your example, which is exactly what I am looking for, but I can't get it to return a False. I even added the additional message boxes into the testConnection function. When I get back into range, it then pops up the True box. Below is my code, which I have been working with. Do you see anything wrong? I don't know if it makes any difference, but the scanner used Win Media 2003. I also looked the Try and Catch up in the help files and it looks correct.

-Uncle Cake

 Private Function testConnection() As Boolean
        Dim MyConnection As System.Data.SQLClient.SqlConnection
        MyConnection = New System.Data.SQLClient.SqlConnection("server=xx; initial catalog=xx;uid=xx;pwd=xx")
        Try
            MyConnection.Open()
            MsgBox("Return True")
            Return True
        Catch ex As Exception
            MsgBox("Return False")
            Return False
        End Try
    End Function

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        MsgBox(testConnection)
    End Sub

 

[chrissie1]

When I get back into range, it then pops up the True box

and when you are out of range it does nothing correct? or it just takes a while for it to return an answer?


Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting

 

[UncleCake]

It does nothing.

 

[UncleCake]

Christiaan,

I waited longer and it does return a False, but it takes about 25 seconds, which I assume that is a timeout from SQL Server. Do I just need to test and wait?

If it proceeds to the next part in the sub it will be in range, otherwise I will stop the process and alert the user.

 

[chrissie1]

then try this. But you have to be patient it depends on the timeout you have on the connection.

Private Function testConnection() As Boolean
dim bolreturn as boolean
        Dim MyConnection As System.Data.SQLClient.SqlConnection
        MyConnection = New System.Data.SQLClient.SqlConnection("server=xx; initial catalog=xx;uid=xx;pwd=xx")
bolreturn = false
        Try
            MyConnection.Open()
            MsgBox("Return True")
            bolreturn = true True
        Catch ex As Exception
        End Try
return boltrue
    End Function

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        MsgBox(testConnection)
    End Sub
[/quote]

Christiaan Baes
Belgium

[b]If you want to get an answer read this FAQ faq796-2540[/b]
[i]There's no such thing as a winnable war - Sting[/i]

 

[UncleCake]

That has the same results, but it will work. The user will just have to see that they can't proceed. I can live with that.

One other quick question. Every time I create a connection should I use the Try and Catch? Right now if the opening fails, it will bomb the program.

-Uncle Cake

 

[Rhys666]

Yes you should use try...catch...finally blocks sround this ideally. You should also close and dispose of the connection when you're finished with it as well. I.E.,

Private Function testConnection() As Boolean
dim bolreturn as boolean
        Dim MyConnection As System.Data.SQLClient.SqlConnection
        MyConnection = New System.Data.SQLClient.SqlConnection("server=xx; initial catalog=xx;uid=xx;pwd=xx")
bolreturn = false
        Try
            MyConnection.Open()
            MsgBox("Return True")
            bolreturn = true True
        Catch ex As Exception
        Finally
            MyConnection.Close()
            MyConnection.Dispose()
        End Try
return boltrue
    End Function

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        MsgBox(testConnection)
    End Sub

NB: Apologies for any poor Syntax, I'm usually a c# kinda guy but you should always ensure connection objects are closed and disposed when you finish with them.

Rhys

"Vampireware /n/,a project, capable of sucking the lifeblood out of anyone unfortunate enough to be assigned to it, which never actually sees the light of day, but nonetheless refuses to die."

"I see dead pixels!

 

[ThatRickGuy]

Try putting a TimeOut=5; in that connection string. That should make it give up trying to connect faster.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[ca8msm]

Now that you've all found a solution that works....

How about a simple "ping" to the database server? Would that work as well (and quicker)?

--------------------------------------------------------------------------------------------------------------------------------------------

Need help finding an answer?

Try the search facility (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[chrissie1]

the ping isn't any quicker believe me I've tried since you have to wait for it to timeout and over a wifi connection you should give it a little more time to timeout. especially when you're close to going out of range. You could also check the wifi signal you are getting. No, don't ask me how. I just know it is possible.



Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting

 

[Rhys666]

ca8msm
No need to be facetious really is there. It's a professional forum so if you've got a constructive comment feel free to make it, otherwise - don't.

Rhys

"Vampireware /n/,a project, capable of sucking the lifeblood out of anyone unfortunate enough to be assigned to it, which never actually sees the light of day, but nonetheless refuses to die."

"I see dead pixels!

 

[chrissie1]

Rhys666

ca8msm is a good member of this forum and I don't think he meant to be facetious (whatever that may be). And I think he was trying to be constructive, perhaps a simple ping would do.

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting

 

[UncleCake]

Rick,

I tried the TimeOut=3 and TimeOut=5 and they both return the false in about 15 seconds, about 1/2 of the original time, so that would be an option. I will also close and dispose my connection.

ca8msm,

How would I ping the database server?

 

[UncleCake]

Christiaan,

I tried asking the technical guy that sold us our Symbol scanner and he said that he didn't know how to tell if there was a connection or not. I also agree that there must be a way to tell. I will try to call them again. In the mean time, this will work for me.

-Uncle Cake

 

[Rhys666]

I know ca8msm is well respected and has a great deal of knowledge in the .Net arena, I wouldn't comment negatively in any way on his/her ability, knowledge or talent - but this is a professional forum where the house rules ask we conduct ourselves in a professional manner. I've seen people RF'd for less but personally thought it was more reasonable to make a small comment rather than a big gesture.

Rhys

"Vampireware /n/,a project, capable of sucking the lifeblood out of anyone unfortunate enough to be assigned to it, which never actually sees the light of day, but nonetheless refuses to die."

"I see dead pixels!

 

[ThatRickGuy]

Just for fun, check the .timeout property on the connection object, I don't beleive it is set by the connection string. Set it to 5 also prior to opening the connection and see if that doesn't bring your timeout down.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[UncleCake]

Rick,

As in reference to

http://www.tek-tips.com/faqs.cfm?fid=4864,

it looks like it would be in the connection string as "Connect Timeout=5". This still take about 15 seconds to return the false.

-Uncle Cake

 

[adam0101]

Rhys666,

What exactly are you feeling is unprofessional about ca8msm's post? Pinging the server? Sorry, I'm just not seeing why you're upset.

Adam