SQL Client CommandTimeout being ignored -- vb.net data

vb.net data : SQL Client CommandTimeout being ignored

jwgoerlich NO[at]SPAM gmail.com
11/28/2006 1:21:11 PM

Hello group,

I have a simple Vb.net application that reads from a SQL 2005 database.
Sometimes when I execute a query and fill a data table, a "Timeout
expired" exception is thrown.

The strange thing is that I have set the CommandTimeout to 10000. This
should be more than enough time, given that I can execute the query
from the Management Studio in under two minutes. So, I must be missing
something -- either in programming or understanding.

Could someone take a look at the query and code below and give me a
hand?

J Wolfgang Goerlich

The SQL query is:

SELECT Top 1 TimeWritten From Windows_Event_Log Where KeyComputer=57
And KeyEventLog=1 Order by TimeWritten Desc

The Vb.net code is:

Dim dt as new DataTable
Dim sqlAdapter As New System.Data.SqlClient.SqlDataAdapter(sqlQuery,
sqlConn)
sqlAdapter.SelectCommand.CommandTimeout = 10000
sqlAdapter.Fill(dt)
sqlAdapter.Dispose()
sqlAdapter = Nothing

The exception generated is:

System.Data.SqlClient.SqlException: Timeout expired.

RobinS
11/28/2006 1:50:01 PM

Put the timeout on the Connection object rather than the command object.

Robin S.
---------------------------
[quoted text, click to view]

jwgoerlich NO[at]SPAM gmail.com
11/28/2006 2:11:09 PM

[quoted text, click to view]

Sounds like a plan. How do I do this? The ConnectionTimeout is
read-only.

J Wolfgang Goerlich

RobinS
11/28/2006 2:50:33 PM

You don't have a connection object. How are you connecting
to the database w/o a connection object? Maybe that's the
problem.

In this example, PTConnectionString is in my settings,
and is a connection string. This fills a datatable,
but you could fill a dataset instead.

Dim dt as DataTable
Using cnn As New SqlConnection(My.Settings.PTConnectionString)
'open the connection
cnn.Open()

'define the command
Dim cmd As New SqlCommand
cmd.Connection = cnn
cmd.CommandText = "SELECT * FROM Customers"

'define the data adapter and fill the data table
Dim da As New SqlDataAdapter(cmd)
dt = New DataTable
da.Fill(dt)
End Using

I don't need to dispose of the adapter or the connection,
because both as in the Using block, and both will be disposed
of after the block is executed. I end up with dt, the datatable.

If you try this and you still need a timeout (you probably
won't), then re-post and I'll see if I can find it.

Robin S.
-----------------------------

[quoted text, click to view]

jwgoerlich NO[at]SPAM gmail.com
11/28/2006 3:14:22 PM

[quoted text, click to view]

sqlConn is my SqlConnection. I did not include in the code snippet, but
it is dim'd and opened earlier.

[quoted text, click to view]

I just tried your example. It still times out. Could you find the
connection timeout, please?

Thank you,

J Wolfgang Goerlich

RobinS
11/28/2006 9:59:31 PM

The ConnectionTimeout property of the SqlConnection
is read-only. (Sigh.)

You have to specify it in the connection string by
adding "ConnectionTimeout = 30" (or however many
seconds you want it to be) to the connection string.

ConnectionString = "Data Source=(local);Database=AdventureWorks;" _
& "Integrated Security=SSPI;Connection Timeout=30;"

The default is 15 seconds. If you set it to 0, it will
wait *forever*. This isn't a good idea. They would have
to shut down the app using the task manager to stop it.

Good luck.
Robin S.
------------------------
[quoted text, click to view]

jwgoerlich NO[at]SPAM gmail.com
11/29/2006 7:10:03 AM

That did the trick! Thank you very much.

J Wolfgang Goerlich

[quoted text, click to view]

William (Bill) Vaughn
11/29/2006 8:41:11 AM

Huh?
The Connection timeout determines how long the provider waits for a free
Connection--not how long to wait for a command to execute. Are you getting
Connection timeouts or Command timeouts? If changing the Connection timeout
fixed the problem it's the former.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
-----------------------------------------------------------------------------------------------------------------------

[quoted text, click to view]

jwgoerlich NO[at]SPAM gmail.com
11/29/2006 12:12:09 PM

Hello,

[quoted text, click to view]

That was my understanding as well. Still, it _does_ seem to work.

[quoted text, click to view]

Not sure. The exception is "System.Data.SqlClient.SqlException: Timeout
expired." I presume it is the command timeout, as the query itself
takes a couple of minutes.

Here is my test code:

Dim SqlConnectString As String = _
"Data Source=" & Trim(txtSqlServer.Text) & ";" & _
"Initial Catalog=" & Trim(txtDatabase.Text) & ";" & _
"Integrated Security=SSPI;" & _
"Connection Timeout=1000;"

Dim ta As Date = Now
Dim tb As Date
Dim r As Integer

Dim dt As DataTable
Using cnn As New SqlConnection(SqlConnectString)

'open the connection
cnn.Open()
MsgBox("ConnectionTimeout = " & cnn.ConnectionTimeout)

'define the command
Dim cmd As New SqlCommand
cmd.Connection = cnn
cmd.CommandText = "SELECT Top 1 TimeWritten From
Windows_Event_Log Where KeyComputer=57 And KeyEventLog=1 Order by
TimeWritten Desc "

'define the data adapter
Dim da As New SqlDataAdapter(cmd)
da.SelectCommand.CommandTimeout = cnn.ConnectionTimeout
MsgBox("CommandTimeout = " &
da.SelectCommand.CommandTimeout)

'fill the data table
dt = New DataTable
Try
da.Fill(dt)
r = dt.Rows.Count
Catch ex As Exception
MsgBox(ex.ToString)
r = -1
End Try

End Using

tb = Now
MsgBox( _
ta.TimeOfDay.ToString & vbCrLf & _
tb.TimeOfDay.ToString & vbCrLf & _
DateDiff(DateInterval.Second, ta, tb).ToString & " seconds" & vbCrLf
& _
r.ToString & " rows.")

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about vb.net data

home · blog · groups

Questions? Comments? Contact the dn