SQL Connection timeout while trying to fill a dataset

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Robert Brown
    #1

    SQL Connection timeout while trying to fill a dataset

    Hi All..

    I am experiencing Timeouts after about 30-40 seconds when trying to
    fill a dataset from SQL.

    I have seen a lot of posts mentioning to set the commandtimeout
    property, but how do you do that on an SQLAdapter? I setting a
    connection timeout on the SQL Connector, but that didn't help.

    Here is my code.. Can anyone help???? BTW: I am returning about 1
    million records (hope the dataset can handle it...

    Dim SQLLink As String =
    "server=(local) ;Trusted_Connec tion=Yes;Databa se=FTO;Connecti on
    Timeout=600"

    Dim SQLConn As SqlConnection = New SqlConnection(S QLLink)
    Dim daSQL As SqlDataAdapter

    'Get OrderItem
    sSql = "select * from tbl_OrderItems inner join tbl_OrderLog on
    tbl_OrderItems. Ordernum = tbl_OrderLog.FO rdernum "
    sSql += "where tbl_OrderLog.St artTD <= '" & DateRange1 & "' order by
    tbl_orderlog.Or dernum"

    daSQL = New SqlDataAdapter( sSql, SQLLink)
    daSQL.Fill(dsOr derItems, "t1")


    Thanks,
    Robert
    Tags: None
    • Paul Hatcher
      #2
      Re: SQL Connection timeout while trying to fill a dataset

      Robert

      You can explicitly create and assign the SelectCommand to the DataAdapter
      rather than using SQL in the constructor, or this should work...

      daSQL = New SqlDataAdapter( sSql, SQLLink)
      daSQL.SelectCom mand.CommandTim eout = 120
      daSQL.Fill(dsOr derItems, "t1")

      though I haven't tested it

      Regards

      Paul

      "Robert Brown" <rbrown@edium.c om> wrote in message
      news:bdd1819.04 03032232.2fddd0 fc@posting.goog le.com...[color=blue]
      > Hi All..
      >
      > I am experiencing Timeouts after about 30-40 seconds when trying to
      > fill a dataset from SQL.
      >
      > I have seen a lot of posts mentioning to set the commandtimeout
      > property, but how do you do that on an SQLAdapter? I setting a
      > connection timeout on the SQL Connector, but that didn't help.
      >
      > Here is my code.. Can anyone help???? BTW: I am returning about 1
      > million records (hope the dataset can handle it...
      >
      > Dim SQLLink As String =
      > "server=(local) ;Trusted_Connec tion=Yes;Databa se=FTO;Connecti on
      > Timeout=600"
      >
      > Dim SQLConn As SqlConnection = New SqlConnection(S QLLink)
      > Dim daSQL As SqlDataAdapter
      >
      > 'Get OrderItem
      > sSql = "select * from tbl_OrderItems inner join tbl_OrderLog on
      > tbl_OrderItems. Ordernum = tbl_OrderLog.FO rdernum "
      > sSql += "where tbl_OrderLog.St artTD <= '" & DateRange1 & "' order by
      > tbl_orderlog.Or dernum"
      >
      > daSQL = New SqlDataAdapter( sSql, SQLLink)
      > daSQL.Fill(dsOr derItems, "t1")
      >
      >
      > Thanks,
      > Robert[/color]


        Comment

        • Cor
          #3
          Re: SQL Connection timeout while trying to fill a dataset

          Hi Robert,[color=blue]
          > I am experiencing Timeouts after about 30-40 seconds when trying to
          > fill a dataset from SQL.[/color]

          \\\watch typos not checked
          dim cmd as new sqlcommand(sSql , SQLLink)
          cmd.CommandTime out = 60
          daSQL = New SqlDataAdapter( cmd)[color=blue]
          > daSQL.Fill(dsOr derItems, "t1")[/color]
          ///
          I hope this helps,

          Cor


            Comment

            • Samuel Hon
              #4
              Re: SQL Connection timeout while trying to fill a dataset

              rbrown@edium.co m (Robert Brown) wrote in message news:<bdd1819.0 403032232.2fddd 0fc@posting.goo gle.com>...[color=blue]
              > Hi All..
              >
              > I am experiencing Timeouts after about 30-40 seconds when trying to
              > fill a dataset from SQL.
              >
              > I have seen a lot of posts mentioning to set the commandtimeout
              > property, but how do you do that on an SQLAdapter? I setting a
              > connection timeout on the SQL Connector, but that didn't help.
              >
              > Here is my code.. Can anyone help???? BTW: I am returning about 1
              > million records (hope the dataset can handle it...
              >
              > Dim SQLLink As String =
              > "server=(local) ;Trusted_Connec tion=Yes;Databa se=FTO;Connecti on
              > Timeout=600"
              >
              > Dim SQLConn As SqlConnection = New SqlConnection(S QLLink)
              > Dim daSQL As SqlDataAdapter
              >
              > 'Get OrderItem
              > sSql = "select * from tbl_OrderItems inner join tbl_OrderLog on
              > tbl_OrderItems. Ordernum = tbl_OrderLog.FO rdernum "
              > sSql += "where tbl_OrderLog.St artTD <= '" & DateRange1 & "' order by
              > tbl_orderlog.Or dernum"
              >
              > daSQL = New SqlDataAdapter( sSql, SQLLink)
              > daSQL.Fill(dsOr derItems, "t1")
              >
              >
              > Thanks,
              > Robert[/color]

              Hey there Robert

              Dare I ask why you are retrieving so many records? There is probably
              another way to acheive your goal

              Sam

                Comment

                Previous template Next
                Working...