Member Avatar for Sebastian_1

I have a app, that requires login with SQL server as backend.
I have users who are on laptops and not always connected to the network.

I want a way to check for the SQL server, and if it does not exist then roll over to local auth
for local info.

My app works fine if connected to SQL, if SQL is not there the app locks up and takes a long time to popup saying
could not connect to sql and does not show local access options.

Here is code:

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim command As New SqlClient.SqlCommand
        Dim adaptor As New SqlClient.SqlDataAdapter
        Dim dataset1 As New DataSet
        Try
            connection.ConnectionString = ("Data Source=OPS\DRAGONNET;Initial Catalog=LCARS;Persist Security Info=True" & _
              ";User ID=***;Password=*************")
            command.CommandText = "SELECT * FROM Code WHERE cmdlvl='" & TextBox2.Text & "' AND cmdcode='" & TextBox1.Text & "';"
            connection.Open()
            command.Connection = connection
        adaptor.SelectCommand = command
        adaptor.Fill(dataset1, "0")
        Dim count = dataset1.Tables(0).Rows.Count
        If count > 0 Then
                MsgBox("ok")
                End
        Else
            MsgBox("Command Code Invalid")
            TextBox1.Clear()
            Failure_Splash.Show()
            Me.Close()
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
            MsgBox("No SQL")
            Exit Sub
        End Try
    End Sub`
  • 2 Contributors
  • 2 Replies
  • 198 Views
  • 8 Hours Discussion Span
  • Latest Post Latest Post by Sebastian_1

Dani AI

Generated

Nice direction from — check reachability before doing the full DB call. The lockup you see is because connection.Open() runs on the UI thread and waits for the network timeout. Two practical, reliable patterns: (1) do a small, fast reachability probe on a background thread (Ping or TCP port), then pick remote vs local auth; (2) if you prefer to try SQL directly, use a short Connect Timeout and perform the open asynchronously so the UI never freezes.

Example helper routines (run these off the UI thread):

Imports System.Net.NetworkInformation

Function IsHostPingable(host As String, Optional timeoutMs As Integer = 500) As Boolean
    Try
        Using p As New Ping()
            Dim r = p.Send(host, timeoutMs)
            Return r.Status = IPStatus.Success
        End Using
    Catch
        Return False
    End Try
End Function

If ICMP is blocked, try a TCP probe to the SQL port:

Function IsTcpPortOpen(host As String, port As Integer, Optional timeoutMs As Integer = 500) As Boolean
    Try
        Using client As New System.Net.Sockets.TcpClient()
            Dim ar = client.BeginConnect(host, port, Nothing, Nothing)
            If Not ar.AsyncWaitHandle.WaitOne(timeoutMs) Then Return False
            client.EndConnect(ar)
            Return True
        End Using
    Catch
        Return False
    End Try
End Function

Or attempt the real SQL open with a small connect timeout and async/await:

Async Function TrySqlConnectAsync(connString As String, Optional timeoutSec As Integer = 3) As Task(Of Boolean)
    Try
        Dim b = New System.Data.SqlClient.SqlConnectionStringBuilder(connString)
        b.ConnectTimeout = timeoutSec
        Using cn As New System.Data.SqlClient.SqlConnection(b.ConnectionString)
            Await cn.OpenAsync()
            Return True
        End Using
    Catch
        Return False
    End Try
End Function

A few practical notes: named instances (like OPS\DRAGONNET) may use dynamic ports or require the SQL Browser, so TCP port checks against 1433 can be inaccurate unless the instance uses a fixed port. Ping can be blocked by firewalls. Always run network checks off the UI thread, update the UI via Invoke, and store credentials securely. If the check fails, immediately present local-auth UI instead of waiting for long timeouts.

Recommended Answers

All 2 Replies

Member Avatar for Reverend Jim

You could try pinging the server at startup to see if the machine is accessible. That would get a much faster response than trying to connect and waiting for the timeout.

Member Avatar for Sebastian_1

had not thought of that. Thanks....

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.