I'm trying to give my program a set amount of time to establish a connection with a remote drive before it fails, but I'm struggling to understand the different options available. I've tried timers and such, but the program still hangs on the section where the database is being contacted. I've researched and read that I need to run a background worker, or run it in another thread, but this is beyond my understanding at the moment.
Here's what I have so far:
Public Sub Initialise_View()
Dim I As Integer = 0
Dim Fault As Boolean = False
Main.VIEW_SavingMessage.Visible = False
Main.VIEW_Title.Text = "Establishing Connecion To Database... Please Wait"
Main.Refresh()
DataGridView_Setup.Set_Datasource(0) 'This subroutine opens a connection to
'the database and will pass a fault variable back if the database is not found,
'however this hangs for ages if the system is having trouble accessing the
'network drive, upwards of 5mins sometimes!
If Fault = True Then Main.VIEW_Title.Text = "Error In Connection..."
Main.Refresh()
End Sub
What I'd really like is something like:
If Connection is not established within 30 seconds Then
Msgbox "Error, Unable to establish connection"
Exit Sub
End If
Which would be easy, using a timer, as long as the program didn't hang when trying to actually connect.
So my question is, is there any way around this? If so, what's the best way of going about it?
TIA
**Update**
Following Answers, I have updated to the following:
Imports System.Threading
Module View_Initialise
Public t1 As Threading.Thread
Public Sub Initialise_View()
Main.Timer1.Interval = 20 * 1000
Main.Timer1.Start()
t1 = New Thread(New ThreadStart(AddressOf Run_Datasource))
t1.Start()
End Sub
Public Sub Run_Datasource()
Dim I As Integer = 0
Dim Fault As Boolean = False
Main.VIEW_SavingMessage.Visible = False
Main.VIEW_Title.Text = "Establishing Connecion To Database... Please Wait"
Main.Refresh()
DataGridView_Setup.Set_Datasource(0)
DataGridView_Setup.BindingUpdates()
If Fault = True Then Main.VIEW_Title.Text = "Error In Connection..."
Main.Refresh()
End Sub
End Module
This appears to be working, as it does fire all codes ect, but the Set_Datasource(0)
routine is not firing correctly, in parts of the code that tell the userform to update with information, this is not happening. Here's the code from the Set_Datasource(0)
: (Sorry, Its lenghtly)
Public Sub Set_Datasource(mode As Integer)
Try
Main.DataGridView1.DataSource.clear()
Catch ex As Exception
End Try
Dim connString As String = My.Settings.Database_String
Dim myConnection As OleDbConnection = New OleDbConnection
myConnection.ConnectionString = connString
' create a data adapter
Dim da As OleDbDataAdapter = New OleDbDataAdapter("SELECT ID, [Name Of Person], [SAP Job Number], [Site Name], [Asset Description], [Spares Supplier], [Supplier Contact Name], [Supplier Contact Phone Number], [Supplier Contact Email], [Spares Description], [Part Number], [Quantity To Order], Cost, [Comments], [Request Date], [Date Ordered], [Ordered By], [Invoice Received], [Invoice Paid], [Method Of Payment], [Date Item Received], [Quote Attatchment] FROM Spares", myConnection)
'create a new dataset
Dim ds As DataSet = New DataSet
'fill DataSet
Try
da.Fill(ds, "Spares")
Catch ex As Exception
MsgBox("Sorry, An Error Occurred" & vbNewLine & _
"Database contents could not be loaded" & vbNewLine & vbNewLine & _
"Error Message: " & ex.Message, MsgBoxStyle.OkOnly, "Could Not Load Database Information")
Exit Sub
End Try
Main.DataGridView1.DataSource = ds.Tables(0)
Main.DataGridView1.AllowUserToAddRows = False
'Set Site Listbox
Dim SiteString = My.Settings.SETTINGS_SiteNames
Dim SiteBox = Main.VIEW_Site.Items
SiteBox.Clear()
Do Until SiteString = ""
Dim ActiveSiteName = Left(SiteString, InStr(SiteString, "¦"))
ActiveSiteName = ActiveSiteName.Remove(ActiveSiteName.Length - 1)
With SiteBox
.Add(ActiveSiteName)
End With
SiteString = Replace(SiteString, ActiveSiteName + "¦", "")
Loop
'Set DataBindings
Main.VIEW_Ref.DataBindings.Clear()
Main.VIEW_Ref.DataBindings.Add(New Binding("Text", ds, "Spares.ID", False, DataSourceUpdateMode.Never))
Main.VIEW_NameOfPerson.DataBindings.Clear()
Main.VIEW_NameOfPerson.DataBindings.Add(New Binding("Text", ds, "Spares.Name Of Person", False, DataSourceUpdateMode.Never))
Main.VIEW_SAPJobNo.DataBindings.Clear()
Main.VIEW_SAPJobNo.DataBindings.Add(New Binding("Text", ds, "Spares.SAP Job Number", False, DataSourceUpdateMode.Never))
Main.VIEW_Site.DataBindings.Clear()
Main.VIEW_Site.DataBindings.Add(New Binding("Text", ds, "Spares.Site Name", False, DataSourceUpdateMode.Never))
Main.VIEW_AssetDesc.DataBindings.Clear()
Main.VIEW_AssetDesc.DataBindings.Add(New Binding("Text", ds, "Spares.Asset Description", False, DataSourceUpdateMode.Never))
Main.VIEW_SparesSupplier.DataBindings.Clear()
Main.VIEW_SparesSupplier.DataBindings.Add(New Binding("Text", ds, "Spares.Spares Supplier", False, DataSourceUpdateMode.Never))
Main.VIEW_SupplierContactName.DataBindings.Clear()
Main.VIEW_SupplierContactName.DataBindings.Add(New Binding("Text", ds, "Spares.Supplier Contact Name", False, DataSourceUpdateMode.Never))
Main.VIEW_SupplierContactNumber.DataBindings.Clear()
Main.VIEW_SupplierContactNumber.DataBindings.Add(New Binding("Text", ds, "Spares.Supplier Contact Phone Number", False, DataSourceUpdateMode.Never))
Main.VIEW_SupplierContactNumber.DataBindings.Clear()
Main.VIEW_SupplierContactNumber.DataBindings.Add(New Binding("Text", ds, "Spares.Supplier Contact Phone Number", False, DataSourceUpdateMode.Never))
Main.VIEW_SupplierContactEmail.DataBindings.Clear()
Main.VIEW_SupplierContactEmail.DataBindings.Add(New Binding("Text", ds, "Spares.Supplier Contact Email", False, DataSourceUpdateMode.Never))
Main.VIEW_SparesDesc.DataBindings.Clear()
Main.VIEW_SparesDesc.DataBindings.Add(New Binding("Text", ds, "Spares.Spares Description", False, DataSourceUpdateMode.Never))
Main.VIEW_PartNumber.DataBindings.Clear()
Main.VIEW_PartNumber.DataBindings.Add(New Binding("Text", ds, "Spares.Part Number", False, DataSourceUpdateMode.Never))
Main.VIEW_QuantityToOrder.DataBindings.Clear()
Main.VIEW_QuantityToOrder.DataBindings.Add(New Binding("Text", ds, "Spares.Quantity To Order", False, DataSourceUpdateMode.Never))
Main.VIEW_CostEach.DataBindings.Clear()
Main.VIEW_CostEach.DataBindings.Add(New Binding("Text", ds, "Spares.Cost", False, DataSourceUpdateMode.Never))
Main.VIEW_DateRequested.DataBindings.Clear()
Main.VIEW_DateRequested.DataBindings.Add(New Binding("Text", ds, "Spares.Request Date", False, DataSourceUpdateMode.Never))
Main.VIEW_DateOrdered.DataBindings.Clear()
Main.VIEW_DateOrdered.DataBindings.Add(New Binding("Text", ds, "Spares.Date Ordered", False, DataSourceUpdateMode.Never))
Main.VIEW_OrderedBy.DataBindings.Clear()
Main.VIEW_OrderedBy.DataBindings.Add(New Binding("Text", ds, "Spares.Ordered By", False, DataSourceUpdateMode.Never))
Main.VIEW_InvoiceReceivedDate.DataBindings.Clear()
Main.VIEW_InvoiceReceivedDate.DataBindings.Add(New Binding("Text", ds, "Spares.Invoice Received", False, DataSourceUpdateMode.Never))
Main.VIEW_InvoicePaidDate.DataBindings.Clear()
Main.VIEW_InvoicePaidDate.DataBindings.Add(New Binding("Text", ds, "Spares.Invoice Paid", False, DataSourceUpdateMode.Never))
Main.View_MethodOfPayment.DataBindings.Clear()
Main.View_MethodOfPayment.DataBindings.Add(New Binding("Text", ds, "Spares.Method Of Payment", False, DataSourceUpdateMode.Never))
Main.VIEW_DateReceived.DataBindings.Clear()
Main.VIEW_DateReceived.DataBindings.Add(New Binding("Text", ds, "Spares.Date Item Received", False, DataSourceUpdateMode.Never))
Main.VIEW_AdditionalComments.DataBindings.Clear()
Main.VIEW_AdditionalComments.DataBindings.Add(New Binding("Text", ds, "Spares.Comments", False, DataSourceUpdateMode.Never))
DataGridView_Setup.BindingUpdates() 'CALL BELOW SUB HERE
Main.VIEW_Title.Text = "View / Update Received Spares"
End Sub
Public Sub BindingUpdates()
Dim curr As New DataGridViewRow
curr = Main.DataGridView1.CurrentRow '**THIS LINE FAILS TO GET THE CURRENT ROW, HOWEVER, IF RUN WITHOUT A NEW THREAD, IT WORKS FINE??**
Main.VIEW_Ref.Text = curr.Cells("ID").Value
Main.VIEW_NameOfPerson.Text = curr.Cells("Name Of Person").Value
Main.VIEW_SAPJobNo.Text = curr.Cells("SAP Job Number").Value
Main.VIEW_Site.Text = curr.Cells("Site Name").Value
Main.VIEW_AssetDesc.Text = curr.Cells("Asset Description").Value
Main.VIEW_SparesSupplier.Text = curr.Cells("Spares Supplier").Value
Main.VIEW_SupplierContactName.Text = curr.Cells("Supplier Contact Name").Value
Main.VIEW_SupplierContactNumber.Text = curr.Cells("Supplier Contact Phone Number").Value
Main.VIEW_SupplierContactEmail.Text = curr.Cells("Supplier Contact Email").Value
Main.VIEW_SparesDesc.Text = curr.Cells("Spares Description").Value
Main.VIEW_PartNumber.Text = curr.Cells("Part Number").Value
Main.VIEW_QuantityToOrder.Text = curr.Cells("Quantity To Order").Value
Main.VIEW_CostEach.Text = "£" + CStr(curr.Cells("Cost").Value)
Main.VIEW_DateRequested.Text = curr.Cells("Request Date").Value
'Handle DBNULL From now on
If IsDBNull(curr.Cells("Date Ordered").Value) = True Or _
IsNothing(curr.Cells("Date Ordered").Value) = True Or _
curr.Cells("Date Ordered").Value = "" Or _
curr.Cells("Date Ordered").Value = "Not Ordered Yet" Then
With Main.VIEW_DateOrdered
.Text = "Not Ordered Yet"
.BackColor = Color.LightPink
End With
Else
With Main.VIEW_DateOrdered
.Text = curr.Cells("Date Ordered").Value
.BackColor = Color.White
End With
End If
If IsDBNull(curr.Cells("Ordered By").Value) = True Or _
IsNothing(curr.Cells("Ordered By").Value) = True Or _
curr.Cells("Ordered By").Value = "" Or _
curr.Cells("Ordered By").Value = "Not Ordered Yet" Then
With Main.VIEW_OrderedBy
.Text = "Not Ordered Yet"
.BackColor = Color.LightPink
End With
Else
With Main.VIEW_OrderedBy
.Text = curr.Cells("Ordered By").Value
.BackColor = Color.White
End With
End If
If IsDBNull(curr.Cells("Invoice Received").Value) = True Or _
IsNothing(curr.Cells("Invoice Received").Value) = True Or _
curr.Cells("Invoice Received").Value = "" Or _
curr.Cells("Invoice Received").Value = "No Invoice" Then
With Main.VIEW_InvoiceReceivedDate
.Text = "No Invoice"
.BackColor = Color.LightPink
End With
Else
With Main.VIEW_InvoiceReceivedDate
.Text = curr.Cells("Invoice Received").Value
.BackColor = Color.White
End With
End If
If IsDBNull(curr.Cells("Invoice Paid").Value) = True Or _
IsNothing(curr.Cells("Invoice Paid").Value) = True Or _
curr.Cells("Invoice Paid").Value = "" Or _
curr.Cells("Invoice Paid").Value = "Not Paid" Then
With Main.VIEW_InvoicePaidDate
.Text = "Not Paid"
.BackColor = Color.LightPink
End With
Else
With Main.VIEW_InvoicePaidDate
.Text = curr.Cells("Invoice Paid").Value
.BackColor = Color.White
End With
End If
If IsDBNull(curr.Cells("Method Of Payment").Value) = True Or _
IsNothing(curr.Cells("Method Of Payment").Value) = True Or _
curr.Cells("Method Of Payment").Value = "" Or _
curr.Cells("Method Of Payment").Value = "Not Paid" Then
With Main.View_MethodOfPayment
.Text = "Not Paid"
.BackColor = Color.LightPink
End With
Else
With Main.View_MethodOfPayment
.Text = curr.Cells("Method Of Payment").Value
.BackColor = Color.White
End With
End If
If IsDBNull(curr.Cells("Date Item Received").Value) = True Or _
IsNothing(curr.Cells("Date Item Received").Value) = True Or _
curr.Cells("Date Item Received").Value = "" Or _
curr.Cells("Date Item Received").Value = "Not Received" Then
With Main.VIEW_DateReceived
.Text = "Not Received"
.BackColor = Color.LightPink
End With
Else
With Main.VIEW_DateReceived
.Text = curr.Cells("Date Item Received").Value
.BackColor = Color.White
End With
End If
If IsDBNull(curr.Cells("Comments").Value) = True Or _
IsNothing(curr.Cells("Comments").Value) = True Or _
curr.Cells("Comments").Value = "" Or _
curr.Cells("Comments").Value = "No Comments Added" Then
With Main.VIEW_AdditionalComments
.Text = "No Comments Added"
'.BackColor = Color.LightPink
End With
Else
With Main.VIEW_AdditionalComments
.Text = curr.Cells("Comments").Value
'.BackColor = Color.White
End With
End If
End Sub
End Module
As stated in the code above, the error appears to be that the new thread can't access information from the form?
Thanks.
You need to run the Connecting Sub-Routine as a new Thread. A common Example for Thread would be this:
First create your new thread:
Now make a call from your function to the thread
Now your connecting Sub-Routine
Now your App won´t hang up.
At the same time you could start a Timer, let it after 30 Seconds, close the connection, now the Thread and then display your Error.