Time out Waiting for Response from DB

[mbiro]

How do I have my code wait for a response from a database call for a certain length of time before it gives up and moves on? When my code below hits the executequery, it waits for a response. If there is an error on the db side, my code waits indefinitely. I need it to give up after, say, 30 seconds. Any ideas? (This is a connection to an AS400 iSeries, but I assume it wouldn't matter to what I was connecting)

Dim drReader400 As iDB2DataReader

ConnStr400.Open()

Dim cmd1 As iDB2Command = New iDB2Command(ConfigurationSettings.AppSettings("iSeriesLibrary") + ".SP60150EOL", ConnStr400)

cmd1.CommandType = CommandType.StoredProcedure
cmd1.ExecuteNonQuery()

 

[mkohl]

in your connection string add: ";Connect Timeout=30"

Also, I'm kind of new to vb.net but I noticed if I put my database code into a try catch statement it usually throws an error after the default timeout has been reached. So, you might want to put your code in a try catch statement.. and then if it does hang then maybe u can find the problem and fix it or redirect the user.

-mike

 

[mbiro]

The connect timeout tells the script how long to try to establish the connection. In this case, the connection is made and the command is executed, but, due to an error on the db side, nothing is returned. My script just hangs. I have tried connectTimeout and commandtimeout with no luck.

 

[mkohl]

mbiro,

Instead of progragramming around the problem wouldn't it be easier just to fix the problem? You say there is something wrong with the db side, what is wrong with it?

Also, when you stated nothing is returned,and it just hangs. Can't you just check for a dbnull value? example:

If System.DBNull.Value = True Then
'do something here
end if


Just trying to help
-mike

 

[mbiro]

I appreciate the help.

The trouble is that I am connecting to an AS400 and have to have my page respond if something goes wrong with it while I am waiting for a response. I do have my connection in a try catch and the connectiontimeout is set, but the trouble is that the connection doesn’t fail. This means that the connectiontimeout doesn’t apply and the try catch doesn’t trigger. I thought it would be the commandtimeout, but this setting is sent to the AS400 for its own use. I need a way the triggers the try catch after a certain period of time. Right now, it just hangs indefinitely waiting for the return value. Here is what I have (it hangs on the cmd1.ExecuteNonQuery()) :

Try
Dim drReader400 As iDB2DataReader

ConnStr400.Open()
Dim AS400call As Boolean
Dim cmd1 As iDB2Command = New iDB2Command(ConfigurationSettings.AppSettings("iSeriesLibrary") + ".SP60150EOL", ConnStr400)
cmd1.CommandType = CommandType.StoredProcedure
cmd1.CommandTimeout = 30
cmd1.Parameters.Add("iString", iDB2DbType.iDB2Char, 600)
cmd1.Parameters("iString").Direction = ParameterDirection.Input
cmd1.Parameters("iString").Value = iString
cmd1.Parameters.Add("rtn", iDB2DbType.iDB2Char, 184)
cmd1.Parameters("rtn").Direction = ParameterDirection.Output
cmd1.Parameters("rtn").Value = " "
Try
cmd1.ExecuteNonQuery()
AS400call = True
Catch
AS400call = False
End Try
Catch
End Try