Timeout error while executing delete query in SqlCommand

[winman]

Hi,

We have a Vb.net program and we are using SqlCommand to execute some queries.This is the code block

If Trim(Slno) <> "" Then
                    oCmd_Local = New SqlCommand("select * from ErrorReport_analysis where (sl_no = '" & Slno & "' and Product = '" & ProductStr & "') order by id", Connum_Local)
                    rs_local = oCmd_Local.ExecuteReader
                    If rs_local.Read = True Then

                        If Trim(Hostname) <> "" Then
                            oCmd_Local = New SqlCommand("select count(*) from ErrorReport_analysis where (sl_no = '" & Slno & "' and Product = '" & ProductStr & "' and HOSTNAME = '" & Hostname & "')", Connum_Local2)
                            rs_local2 = Val(oCmd_Local.ExecuteScalar.ToString)
                        ElseIf Trim(CompKey) <> "" Then
                            oCmd_Local = New SqlCommand("select count(*) from ErrorReport_analysis where (sl_no = '" & Slno & "' and Product = '" & ProductStr & "' and PC_key like '%-" & CompKey & "-%')", Connum_Local2)
                            rs_local2 = Val(oCmd_Local.ExecuteScalar.ToString)
                        End If

                        If rs_local2 = 1 Then 'if only one row present then delete that row itself
                            oCmd_Local = New SqlCommand("Delete from ErrorReport_analysis where (id = " & rs_local.Item("id").ToString & ")", Connum_Local2)
                            oCmd_Local.CommandTimeout = TimeOutval
                            oCmd_Local.ExecuteNonQuery()
                        Else
                            Do While rs_local.Read = True
                                If Trim(Hostname) <> "" Then
                                    If rs_local.Item("HOSTNAME").ToString = Hostname Then
                                        oCmd_Local = New SqlCommand("Delete from ErrorReport_analysis where (id = " & rs_local.Item("id").ToString & ")", Connum_Local2)
                                        oCmd_Local.CommandTimeout = TimeOutval
                                        oCmd_Local.ExecuteNonQuery()
                                        Exit Do
                                    End If
                                ElseIf Trim(CompKey) <> "" Then
                                    If InStr(rs_local.Item("PC_key").ToString, "'%-" & CompKey & "-%'", CompareMethod.Text) > 0 Then
                                        oCmd_Local = New SqlCommand("Delete from ErrorReport_analysis where (id = " & rs_local.Item("id").ToString & ")", Connum_Local2)
                                        oCmd_Local.CommandTimeout = TimeOutval
                                        oCmd_Local.ExecuteNonQuery()
                                        Exit Do
                                    End If
                                Else
                                    Exit Do
                                End If
                            Loop
                        End If
                    End If
                    rs_local.Close()
                    rs_local = Nothing
                Else
                    oCmd_Local = New SqlCommand("select * from ErrorReport_analysis where ((sl_no ='' or sl_no is null) and PC_key like '%-" & CompKey & "-%' and HOSTNAME = '" & Hostname & "' and Product = '" & ProductStr & "') order by id", Connum_Local)
                    rs_local = oCmd_Local.ExecuteReader
                    If rs_local.Read = True Then
                        oCmd_Local = New SqlCommand("Delete from ErrorReport_analysis where (id = " & rs_local.Item("id").ToString & ")", Connum_Local2)
                        oCmd_Local.CommandTimeout = TimeOutval
                        oCmd_Local.ExecuteNonQuery()
                    End If
                    rs_local.Close()

                    rs_local = Nothing
                End If

            End If

But while executing delete statement some this timeout error coming

I have Executed sp_who2 command in Management studio and find out that there are 4 sessions created by program and a session with select statement is blocking the session with delete statement.Why this is happening?what to do?

Thank you

[techgnome]

It's your Reader... when you use a reader, it gets an exclusive lock... which can then tie up the tables in the query... you get the time out because it waits for the lock to be released so it can update the table... but it's inside the loop of the reader,so it never gets released... and after the designated amount of time (30seconds in the default I believe) it timesout.

how do you get rid of it? You don't use a reader... at least not like you're doing... use the reader to fill a datatable, then loop through the datatable to perform the rest of your commands. and make sure they ALL use the SAME connection.

-tg

[winman]

Thanks for reply

We used 'with (NOLOCK)' and its working fine for most of time but sometime A transport-level error has occurred when receiving results from the server error coming. Is this anything to do with 'with (NOLOCK)'? I checked network connection.It seems to be fine.Why this is happening?

[szlamany]

Using bad code logic that results in an unneeded lock and then using WITH (NOLOCK) to get around it - that's not what TG said to do. Is that all you got from his post? I thought he was quite clear - ONE CONNECTION only - fill a datatable from the reader - then there will be no lock on the DB and your deletes will work.

[techgnome]

err.... yeah, what he said.... I didn't even say anyhting about using NOLOCK hint... all that would do is allow you to read uncommitted transactions... its does NOT bypass the READ LOCK that the reader has...
Use the reader to fill a datatable... then using that SAME CONNECTION... loop through the datatable to perform the rest of your work.

-tg