-
Feb 8th, 2018, 01:51 AM
#1
Thread Starter
Frenzied Member
Timeout error when form load
Hi, I have dashboard where I click to open other form. This other form then load many records (6 tableAdapers fill) Sometimes network is very slow so what happen is that gives timeout error (also then loads partial records and all code is not executed - after fill then records must be filtered according to userID - so it doesnt get to filter part of code (because of timeout error) and user see records not for her userID)
I understand I must increase cmd.CommandTimeout but I dont know how and where to do this. (apparently the default is 30 seconds) I want records must load no matter how long it takes or no matter how slow network.
Thanks
-
Feb 8th, 2018, 03:00 AM
#2
Re: Timeout error when form load
You modify table adapters in the DataSet designer. Open your DataSet in the designer, select your table adapter and open the Properties window. The SelectCommand, InsertCommand, UpdateCommand and DeleteCommand properties refer to the command objects that get executed when you call Fill and Update. Expand the SelectCommand property and you should see the CommandTimeout property of that object.
-
Feb 8th, 2018, 03:03 AM
#3
Re: Timeout error when form load
Note that that will set the default timeout for a table adapter of that type. That's probably what you want but you can still do it for individual instances in code as well. You can access the .SelectCommand.CommandTimeout property of your table adapter instance in code before you call Fill, if that's appropriate.
-
Feb 8th, 2018, 03:04 AM
#4
Re: Timeout error when form load
I always recommend that anyone using typed DataSets do some reading on the subject, starting here:
https://msdn.microsoft.com/en-us/lib...or=-2147217396
-
Feb 8th, 2018, 03:21 AM
#5
Thread Starter
Frenzied Member
Re: Timeout error when form load
hi, I dont see CommandTimeout property:
Attachment 156073
-
Feb 8th, 2018, 03:22 AM
#6
Thread Starter
Frenzied Member
Re: Timeout error when form load
hi, I dont see CommandTimeout property:
Attachment 156073
-
Feb 8th, 2018, 04:14 AM
#7
Re: Timeout error when form load
Hmmm... I haven't used a typed DataSet for a long time and I don't think I've ever tried to change the CommandTimeout for one. I was actually just assuming that it would be there but I guess that they figure that it's not that important. That means that you will have to set it in code on the individual instance of your table adapter. I was actually a little off in my previous suggestion in that regard. You actually have to access the command via the internal data adapter, e.g.
vb.net Code:
tblContextTableAdapter1.Adapter.SelectCommand.CommandTimeout = 60
-
Feb 8th, 2018, 04:29 AM
#8
Thread Starter
Frenzied Member
Re: Timeout error when form load
Okay that is I find, thank you very much! I can maybe just ask why did you make "tblContextTableAdapter" -> tblContextTableAdapter1 (with "1" at end?
-
Feb 8th, 2018, 04:44 AM
#9
Re: Timeout error when form load
Originally Posted by schoemr
I can maybe just ask why did you make "tblContextTableAdapter" -> tblContextTableAdapter1 (with "1" at end?
Just as if you add a 'Button' instance to your form from the Toolbox it will be named 'Button1' by default, so if you add a 'tblContextTableAdapter' instance to your form it will be named 'tblContextTableAdapter1' by default. 'tblContextTableAdapter' is the type name, i.e. the name of a class, while 'tblContextTableAdapter1' is an example of a name for a variable of that type, i.e. a variable that refers to a 'tblContextTableAdapter' object.
-
Feb 8th, 2018, 04:51 AM
#10
Thread Starter
Frenzied Member
Re: Timeout error when form load
JMC, so this happens:
Private Sub frm_NEW_Context_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Code:
'to prevent timout
Me.TblContextTableAdapter.Adapter.SelectCommand.CommandTimeout = 60
Me.TblOMETableAdapter.Fill(Me.MAINRiskDBDataSet.tblOME)
Me.TblBusinessTableAdapter.Fill(Me.MAINRiskDBDataSet.tblBusiness)
Me.TblAreaOrPlantTableAdapter.Fill(Me.MAINRiskDBDataSet.tblAreaOrPlant)
Me.TblDepartmentTableAdapter.Fill(Me.DepartmentDataSet.tblDepartment)
Me.TblContextTableAdapter.Fill(Me.MAINRiskDBDataSet.tblContext)
Me.TblUsersTableAdapter.Fill(Me.MAINRiskDBDataSet.TblUsers)
'format dgv date column
Me.TblContextDataGridView.Columns(3).DefaultCellStyle.Format = "dd/MM/yyyy"
Attachment 156075
-
Feb 8th, 2018, 05:34 AM
#11
Re: Timeout error when form load
Hmmm... again. It seems that the SelectCommand of the inner data adapter is Nothing by default. I guess that must be because it doesn't know which query will be executed until you call Fill or some FillBy method. I'll have to investigate further whether there's a way to access the command object before calling Fill. I'll get back to you.
-
Feb 8th, 2018, 07:01 AM
#12
Addicted Member
Re: Timeout error when form load
maybe this can help
Code:
Private Sub wait(ByVal seconds As Integer)
For i As Integer = 0 To seconds * 100
System.Threading.Thread.Sleep(10)
Application.DoEvents()
Next
End Sub
And then in load event
in seconds you want to wait
-
Feb 8th, 2018, 08:12 AM
#13
Re: Timeout error when form load
Originally Posted by jmcilhinney
Hmmm... again. It seems that the SelectCommand of the inner data adapter is Nothing by default. I guess that must be because it doesn't know which query will be executed until you call Fill or some FillBy method. I'll have to investigate further whether there's a way to access the command object before calling Fill. I'll get back to you.
It's actually on the CONNECTION object... There should be a ConnectionTimeout and a CommandTimeout ... two different properties with two different effects, so make sure you're setting the right one.
Originally Posted by zubenubie
maybe this can help
Code:
Private Sub wait(ByVal seconds As Integer)
For i As Integer = 0 To seconds * 100
System.Threading.Thread.Sleep(10)
Application.DoEvents()
Next
End Sub
And then in load event
in seconds you want to wait
No... no, don't do this. This is a busy wait and doesn't really solve the problem. It just masks it by sleeping the thread (which stops the thread) ... also the .Fill on the adaptor is a blocking call, so even if you were to call the wait function (which you shouldn't do), by the time it does get called, the .Fill will have either run or failed... so it's just wasting time.
after fill then records must be filtered according to userID - so it doesnt get to filter part of code (because of timeout error) and user see records not for her userID)
While it's not directly related to your problem... I'd like to address this issue... I'm a little surprised that jmc didn't catch on to this and address it as well... but if the user should only see their records, then don't select all of the records in the first place. Include a where clause with a parameter to filter the results right from the beginning.... so instead of getting all of the records and then filtering them down, you get just the records you want right from the start, no filtering.
-tg
-
Feb 8th, 2018, 09:12 AM
#14
Re: Timeout error when form load
Originally Posted by techgnome
It's actually on the CONNECTION object... There should be a ConnectionTimeout and a CommandTimeout ... two different properties with two different effects, so make sure you're setting the right one.
There are indeed two timeouts and one of them is on the connection object but the other is on the command object. As I'm sure you're aware tg, the ConnectionTimeout property of the connection object controls how long the object waits for a connection to the database to succeed before throwing an exception while the CommandTimeout property of the command object controls how long the command waits for a query to succeed before throwing an exception. The first can be set via the connection string, so there should be no issue there. The second can only be set on the command object, as far as I can tell.
Originally Posted by techgnome
While it's not directly related to your problem... I'd like to address this issue... I'm a little surprised that jmc didn't catch on to this and address it as well... but if the user should only see their records, then don't select all of the records in the first place. Include a where clause with a parameter to filter the results right from the beginning.... so instead of getting all of the records and then filtering them down, you get just the records you want right from the start, no filtering.
I did miss that and it might actually solve the issue. As tg says, if you don't want all the data then you shouldn't retrieve all the data. You would add a query with a WHERE clause that filters based on user ID and, because there's less data to retrieve, the default timeout on the command may prove to be enough. The link I provided earlier provides information on how to add queries to a table adapter.
If you really do need to change the timeout for the SelectCommand then it appears that you actually need to modify your table adapter class. As I alluded to earlier, it actually maintains a collection of commands that correspond to the queries for that table adapter and then it uses whichever is appropriate based on the Fill/FillBy method you call. You can only access that collection from inside the class, so you either need to modify the auto-generated code, which is not advisable, or else add a partial class. As an example, my project contains TestDataSet that included Table_1TableAdapter and I added this code in a new code file:
vb.net Code:
Namespace TestDataSetTableAdapters Partial Public Class Table_1TableAdapter Public Property SelectCommandTimeout() As Integer Get Return CommandCollection.First().CommandTimeout End Get Set Array.ForEach(CommandCollection, Sub(cmd) cmd.CommandTimeout = value) End Set End Property End Class End Namespace
You can then get or set that SelectCommandTimeout property on your table adapter in code. Note that setting it will set the timeout for all queries associated with that table adapter, so all Fill/FillBy methods will be affected.
When I add a code file for a partial class, I usually name it "ClassName.partial.vb". Also note that that Array.ForEach call could be replaced with a loop like this:
vb.net Code:
For Each cmd In CommandCollection cmd.CommandTimeout = value Next
-
Feb 9th, 2018, 01:25 AM
#15
Thread Starter
Frenzied Member
Re: Timeout error when form load
Hi, thank you for replies. I was hope to be simple solution.. But seems not. I was just think for connection object default timeout is 15 seconds and for command object default timeout is 30 seconds. So that "30" & "15" must be written "somewhere" and all I shall do is to change that.
For not filtering and get records only for that users i agree is wonderful. What I am do is when user login that UserID is stored in global variable (yes, there better ways but this works for me) So when that form loads I put that userID in textbox1 and am use this code to find only that records in the BindingSource (when form loads - but after fill):
Code:
On Error Resume Next
Dim position As Integer = TblUsersBindingSource.Find("USERID", TextBox1.Text)
If position > -1 Then
TblUsersBindingSource.Position = position
End If
I agree why load 1000 records in first place if only want to see 20. That can also solve this timeout problem. But I not know how to put value of textbox1 on WHERE statement.... I am using same dataset in other forms as well, so to modify that in designer may cause other forms not to work
-
Feb 9th, 2018, 02:05 AM
#16
Re: Timeout error when form load
Originally Posted by schoemr
But I not know how to put value of textbox1 on WHERE statement....
The TextBox is irrelevant and almost certainly pointless. You already have the user Id value in your global variable so just use that directly. As for how to execute a query with a WHERE clause using a table adapter, that seems like something that would be covered in the information I linked to in post #4.
You said back in post #1 that you are filtering data and you seem to be suggesting that the code you just posted is what does that filtering but there's no filtering going on there. All that's doing is making the first matching record the current record. Filtering would mean hiding all the non-matching records. To do that you would set the Filter property of the BindingSource. Of course, if you filter using your query then that becomes a moot point.
-
Feb 9th, 2018, 02:26 AM
#17
Thread Starter
Frenzied Member
Re: Timeout error when form load
You already have the user Id value in your global variable so just use that directly.
I would then think something like:
Code:
SELECT UserID, UserName, LastName
FROM tblUsers
WHERE (UserID = MyGlobalVariableUserID)
But obviously this shall not work..
The link in #4 i read about (thank you) but it does nowhere explain how to use GlobalVariable in WHERE statement...
-
Feb 9th, 2018, 02:44 AM
#18
Re: Timeout error when form load
On the page I linked to, the fourth link that is provided is to this page:
https://msdn.microsoft.com/library/m...v=VS.140).aspx
That page explains how to add parameterised queries to a table adapter. Once you have a query with parameters, you can pass any value you want to them. You might even pass a value from a global variable.
I'm not going to reply to this thread any more because we're very much at the stage now where you have all the information you need but are just not making use of it. We both know where our conversations go once we get there.
-
Feb 9th, 2018, 04:40 AM
#19
Thread Starter
Frenzied Member
Re: Timeout error when form load
Ok, John, thanks for almost help me again. I shall keep this thread open because maybe someone will come along that knows where to set default times for ConnectionTimeout and a CommandTimeout
Takce care
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|