[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3009
  • Last Modified:

Timeout on SqlDataAdapter Fill to DataTable

I have a query that when run via sql server management studio, it takes 30 seconds. But when I run it within my source code to populate a DataTable, it errors with a timeout.

Here is my code that uses the query to populate the DataTable. It errors right after the Fill:

      Public Function GetDataTable(ByVal sql As String) As DataTable

         Dim con As New SqlConnection(Name.GetConnectionString)
         Dim adapter As New SqlDataAdapter()
         adapter.SelectCommand = New SqlCommand(sql, con)
         Dim myDataTable As New DataTable()
         con.Open()
         Try
            adapter.Fill(myDataTable)
         Finally
            adapter.Dispose()
            con.Close()
         End Try
         Return myDataTable

      End Function

Open in new window


Is there something wrong with my code above that would cause the same query that runs successfully in SSMS to fail with a web project? It goes straight to the Finally block.

thanks.
0
Starr Duskk
Asked:
Starr Duskk
  • 2
1 Solution
 
Fernando SotoRetiredCommented:
Hi BobCSD;

Try increasing the CommandTimeout property of the SqlCommand object to see if that helps. Modify your code as shown below.

Dim adapter As New SqlDataAdapter()
Dim cmd As New SqlCommand(sql, con)
' Increase the timeout to a value greater then 30 which is the default. 
' This value is in seconds
cmd.CommandTimeout = 45
adapter.SelectCommand = cmd
Dim myDataTable As New DataTable()

Open in new window

0
 
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
I did this and it worked too. Is that 6 of one half a dozen of the other, or is the way you just showed me different?

thanks!

         Dim adapter As New SqlDataAdapter()
         adapter.SelectCommand = New SqlCommand(sql, con)
         adapter.SelectCommand.CommandTimeout = 300   ' 3/16/2014
         Dim myDataTable As New DataTable()

Open in new window

0
 
Fernando SotoRetiredCommented:
Yes that is fine as we'll.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now