Results 1 to 19 of 19

Thread: Timeout error when form load

Hybrid View

  1. #1

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    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

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    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.

  3. #3
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    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

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    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.

  5. #5

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Re: Timeout error when form load

    hi, I dont see CommandTimeout property:

    Attachment 156073

  6. #6

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Re: Timeout error when form load

    hi, I dont see CommandTimeout property:

    Attachment 156073

  7. #7
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    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:
    1. tblContextTableAdapter1.Adapter.SelectCommand.CommandTimeout = 60

  8. #8

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    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?

  9. #9
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Timeout error when form load

    Quote Originally Posted by schoemr View Post
    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.

  10. #10

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    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

  11. #11
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    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.

  12. #12
    Addicted Member
    Join Date
    Mar 2017
    Location
    Netherlands
    Posts
    136

    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

    Code:
    wait(2)
    in seconds you want to wait

  13. #13
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Timeout error when form load

    Quote Originally Posted by jmcilhinney View Post
    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.


    Quote Originally Posted by zubenubie View Post
    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

    Code:
    wait(2)
    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  14. #14
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Timeout error when form load

    Quote Originally Posted by techgnome View Post
    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.
    Quote Originally Posted by techgnome View Post
    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:
    1. Namespace TestDataSetTableAdapters
    2.  
    3.     Partial Public Class Table_1TableAdapter
    4.  
    5.         Public Property SelectCommandTimeout() As Integer
    6.             Get
    7.                 Return CommandCollection.First().CommandTimeout
    8.             End Get
    9.             Set
    10.                 Array.ForEach(CommandCollection, Sub(cmd) cmd.CommandTimeout = value)
    11.             End Set
    12.         End Property
    13.     End Class
    14.  
    15. 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:
    1. For Each cmd In CommandCollection
    2.     cmd.CommandTimeout = value
    3. Next

  15. #15

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    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

  16. #16
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Timeout error when form load

    Quote Originally Posted by schoemr View Post
    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.

  17. #17

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    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...

  18. #18
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    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.

  19. #19

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    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
  •  



Click Here to Expand Forum to Full Width