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(ByVal userID As Integer, ByVal inputDate As
String) As Integer
Try

Dim sqlCheckConn As New SqlConnection(strConn)
Dim strcheckSQL As String = "select count(detailID) from
flexiDetail where userID = @uID and flexiDate = @fDate"
sqlCheckConn.Open()
Dim checkCmd1 As New SqlCommand(strcheckSQL, sqlCheckConn)
checkCmd1.Parameters.AddWithValue("@uID", userID)
checkCmd1.Parameters.AddWithValue("@fDate", inputDate)

CheckInsUpd = checkCmd1.ExecuteScalar

sqlCheckConn.Close()

Catch ex As Exception
MsgBox(ex.ToString)
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]

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]

On 21 Apr, 10:37, Panda <[e-mail]:

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(strConn)
Dim sqlCheckResponse As Integer
Dim sqlTrans As SqlTransaction

Try

sqlAddConn.Open()
sqlTrans = sqlAddConn.BeginTransaction()

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
sqlCheckResponse = flexi_actions.CheckInsUpd(userID,
txtDate1.Text)
If sqlCheckResponse = 0 Then
Dim sqlMonday As New SqlCommand
sqlMonday = sqlAddConn.CreateCommand
sqlMonday.CommandText = strInsertSQL
sqlMonday.Parameters.AddWithValue("@uID", userID)
sqlMonday.Parameters.AddWithValue("@fDate",
txtDate1.Text)
sqlMonday.Parameters.AddWithValue("@amA",
amArr1.Value)
sqlMonday.Parameters.AddWithValue("@amD",
amDep1.Value)
sqlMonday.Parameters.AddWithValue("@pmA",
pmArr1.Value)
sqlMonday.Parameters.AddWithValue("@pmD",
pmDep1.Value)
sqlMonday.Parameters.AddWithValue("@extra",
txtExtra1.Text)
sqlMonday.Parameters.AddWithValue("@extraR",
txtExtraRsn1.Text)
sqlMonday.Transaction = sqlTrans
sqlMonday.ExecuteNonQuery()
ElseIf sqlCheckResponse = 1 Then
Dim sqlMonday As New SqlCommand
sqlMonday = sqlAddConn.CreateCommand
sqlMonday.CommandText = strUpdateSQL
sqlMonday.Parameters.AddWithValue("@uID", userID)
sqlMonday.Parameters.AddWithValue("@fDate",
txtDate1.Text)
sqlMonday.Parameters.AddWithValue("@amA",
amArr1.Value)
sqlMonday.Parameters.AddWithValue("@amD",
amDep1.Value)
sqlMonday.Parameters.AddWithValue("@pmA",
pmArr1.Value)
sqlMonday.Parameters.AddWithValue("@pmD",
pmDep1.Value)
sqlMonday.Parameters.AddWithValue("@extra",
txtExtra1.Text)
sqlMonday.Parameters.AddWithValue("@extraR",
txtExtraRsn1.Text)
sqlMonday.Transaction = sqlTrans
sqlMonday.ExecuteNonQuery()
Else
MsgBox("more than 1 result when putting Monday
flexi details!")
End If

End If
sqlCheckResponse = 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

sqlCheckResponse = flexi_actions.CheckInsUpd(userID,
txtDate2.Text)
If sqlCheckResponse = 0 Then
Dim sqlTuesday As New SqlCommand
sqlTuesday = sqlAddConn.CreateCommand
sqlTuesday.CommandText = strInsertSQL
sqlTuesday.Parameters.AddWithValue("@uID", userID)
sqlTuesday.Parameters.AddWithValue("@fDate",
txtDate2.Text)
sqlTuesday.Parameters.AddWithValue("@amA",
amArr2.Value)
sqlTuesday.Parameters.AddWithValue("@amD",
amDep2.Value)
sqlTuesday.Parameters.AddWithValue("@pmA",
pmArr2.Value)
sqlTuesday.Parameters.AddWithValue("@pmD",
pmDep2.Value)
sqlTuesday.Parameters.AddWithValue("@extra",
txtExtra2.Text)
sqlTuesday.Parameters.AddWithValue("@extraR",
txtExtraRsn2.Text)
sqlTuesday.Transaction = sqlTrans
sqlTuesday.ExecuteNonQuery()
ElseIf sqlCheckResponse = 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]

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.