SQL Server Timeout - Second time in loop

[Panda]

Hi All,

Any help greatly appreciated before I finish pulling the rest of my
hair out.

I'm making a program that allows a user to record their hours. There
are five rows (monday to friday). When a user clicks submit the
programme checks if the row has got user data. If it does it checks if
that day already has data held against it and then does an update or
insert accordingly.

It is all working apart from when the program comes to check the
second day it times out on the command. That command run in query
analyser works fine. The function that carries out the check is

Function CheckInsUpd(ByV al userID As Integer, ByVal inputDate As
String) As Integer
Try

Dim sqlCheckConn As New SqlConnection(s trConn)
Dim strcheckSQL As String = "select count(detailID) from
flexiDetail where userID = @uID and flexiDate = @fDate"
sqlCheckConn.Op en()
Dim checkCmd1 As New SqlCommand(strc heckSQL, sqlCheckConn)
checkCmd1.Param eters.AddWithVa lue("@uID", userID)
checkCmd1.Param eters.AddWithVa lue("@fDate", inputDate)

CheckInsUpd = checkCmd1.Execu teScalar

sqlCheckConn.Cl ose()

Catch ex As Exception
MsgBox(ex.ToStr ing)
End Try
End Function

Any line wrapping is down to the browser...

I'm guessing this is a problem with my program structure as I'm
totally self taught so apologies if this is a noob error. I've scanned
the newsgroups but can't see anything applicable...

Paul

[Panda]

Re: SQL Server Timeout - Second time in loop

Ah... queries worked fine Friday... but not now...

If I query the whole table e.g. select userID from flexidetail then
the query runs almost immediately if I add a where clause then it
takes forever - or at least I've given up after a minute.

I guess I need to repost in a SQL group.

[Panda]

Program causing blocking

On 21 Apr, 10:37, Panda <paul.dam...@gm ail.comwrote:

Ah... queries worked fine Friday... but not now...
>
If I query the whole table e.g. select userID from flexidetail then
the query runs almost immediately if I add a where clause then it
takes forever - or at least I've given up after a minute.
>
I guess I need to repost in a SQL group.

And in putting together my post on a SQL group I've spotted that the
program is blocking resource on the SQL Server. Thought I'd put my
program flow here while I test...

Main body runs

Dim sqlAddConn As New SqlConnection(s trConn)
Dim sqlCheckRespons e As Integer
Dim sqlTrans As SqlTransaction

Try

sqlAddConn.Open ()
sqlTrans = sqlAddConn.Begi nTransaction()

If (amArr1.Value <"01-01-1900 08:00:00" Or amDep1.Value
<"01-01-1900 00:00:00") Or (pmArr1.Value <"01-01-1900 00:00:00"
And pmDep1.Value <"01-01-1900 00:00:00") Then
'monday
sqlCheckRespons e = flexi_actions.C heckInsUpd(user ID,
txtDate1.Text)
If sqlCheckRespons e = 0 Then
Dim sqlMonday As New SqlCommand
sqlMonday = sqlAddConn.Crea teCommand
sqlMonday.Comma ndText = strInsertSQL
sqlMonday.Param eters.AddWithVa lue("@uID", userID)
sqlMonday.Param eters.AddWithVa lue("@fDate",
txtDate1.Text)
sqlMonday.Param eters.AddWithVa lue("@amA",
amArr1.Value)
sqlMonday.Param eters.AddWithVa lue("@amD",
amDep1.Value)
sqlMonday.Param eters.AddWithVa lue("@pmA",
pmArr1.Value)
sqlMonday.Param eters.AddWithVa lue("@pmD",
pmDep1.Value)
sqlMonday.Param eters.AddWithVa lue("@extra",
txtExtra1.Text)
sqlMonday.Param eters.AddWithVa lue("@extraR",
txtExtraRsn1.Te xt)
sqlMonday.Trans action = sqlTrans
sqlMonday.Execu teNonQuery()
ElseIf sqlCheckRespons e = 1 Then
Dim sqlMonday As New SqlCommand
sqlMonday = sqlAddConn.Crea teCommand
sqlMonday.Comma ndText = strUpdateSQL
sqlMonday.Param eters.AddWithVa lue("@uID", userID)
sqlMonday.Param eters.AddWithVa lue("@fDate",
txtDate1.Text)
sqlMonday.Param eters.AddWithVa lue("@amA",
amArr1.Value)
sqlMonday.Param eters.AddWithVa lue("@amD",
amDep1.Value)
sqlMonday.Param eters.AddWithVa lue("@pmA",
pmArr1.Value)
sqlMonday.Param eters.AddWithVa lue("@pmD",
pmDep1.Value)
sqlMonday.Param eters.AddWithVa lue("@extra",
txtExtra1.Text)
sqlMonday.Param eters.AddWithVa lue("@extraR",
txtExtraRsn1.Te xt)
sqlMonday.Trans action = sqlTrans
sqlMonday.Execu teNonQuery()
Else
MsgBox("more than 1 result when putting Monday
flexi details!")
End If

End If
sqlCheckRespons e = 2 ' to make sure we don't use the last
value
If (amArr2.Value <"01-01-1900 08:00:00" Or amDep2.Value
<"01-01-1900 00:00:00") Or (pmArr2.Value <"01-01-1900 00:00:00"
And pmDep2.Value <"01-01-1900 00:00:00") Then
'Tuesday

sqlCheckRespons e = flexi_actions.C heckInsUpd(user ID,
txtDate2.Text)
If sqlCheckRespons e = 0 Then
Dim sqlTuesday As New SqlCommand
sqlTuesday = sqlAddConn.Crea teCommand
sqlTuesday.Comm andText = strInsertSQL
sqlTuesday.Para meters.AddWithV alue("@uID", userID)
sqlTuesday.Para meters.AddWithV alue("@fDate",
txtDate2.Text)
sqlTuesday.Para meters.AddWithV alue("@amA",
amArr2.Value)
sqlTuesday.Para meters.AddWithV alue("@amD",
amDep2.Value)
sqlTuesday.Para meters.AddWithV alue("@pmA",
pmArr2.Value)
sqlTuesday.Para meters.AddWithV alue("@pmD",
pmDep2.Value)
sqlTuesday.Para meters.AddWithV alue("@extra",
txtExtra2.Text)
sqlTuesday.Para meters.AddWithV alue("@extraR",
txtExtraRsn2.Te xt)
sqlTuesday.Tran saction = sqlTrans
sqlTuesday.Exec uteNonQuery()
ElseIf sqlCheckRespons e = 1 Then

etc etc for 5 days

the function called is as above...

I'll start to Google and test now but any advice appreciated.

[cfps.Christian]

Re: SQL Server Timeout - Second time in loop

While I'm not entirely sure what your problem is or will end up being,
but I would attempt to open a singular connection and use it to query
within a loop. I see in this method that you're opening the
connection every time it starts, this is fine for a single usage but
for a loop it could become very taxing on the server. Normally the
code I write has a single Connection for every query and then when I'm
done close it out.