CODEWITHSUNDEEP
sql-servervb.net
vivek padelkar
vivek padelkar

Reputation: 313

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding in VB.Net

I have a simple SQL Query that is getting fired in DB which is in Server Side while executing query sometimes it shows an error message :

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

My query is :

If sqlconn.State = ConnectionState.Closed Then sqlconn.Open()
Txn = sqlconn.BeginTransaction

cmd = sqlconn.CreateCommand
cmd.Transaction = Txn
    strSql = "insert into " & tableName _
    & " (RepSerNo,ProductType,ProductID,ProductName,Qty,GrpQty,IDSNo,CubicalNo,BatchNo,BalanceId," _
    & "VernierID,UserID,UserName,PrDate,PrTime,Side,Unit,DecimalPoint," _
    & "WgmtModeNo,InstruID,Nom,PosTol,NegTol,NomEmpty,PosTolEmpty,NegTolEmpty," _
    & "NomNet,PosTolNet,NegTolNet,RepoLabel1,RepoLabel2,RepoLabel3,RepoLabel4,RepoLabel5," _
    & "RepoLabel6,RepoLabel7,RepoLabel8,RepoLabel9,RepoLabel10,RepoLabel11, " _
    & "RepoLabel12,RepoLabel13,RepoLabel14,RepoLabel15,RepoLabel16,RepoLabel17," _
    & "RepoLabel18,RepoLabel19,RepoLabel20)" _
    & " values(" _
    & RepBatchSrNo & "," & ProductType(cno) & "," & Chr(39) & ProductId(cno) & Chr(39) & "," & Chr(39) & Product_Name(cno) & Chr(39) & "," & Qty(cno) & "," & GrpQty(cno) & "," & IDSNo(cno) & "," & CubicNo(cno) & "," & Chr(39) & BatchNo(cno) & Chr(39) & "," & Chr(39) & balanceIdforTabCap(cno) & Chr(39) & "," _
    & Chr(39) & VernierId(cno) & Chr(39) & "," & Chr(39) & UserId(cno) & Chr(39) & "," & Chr(39) & UserName(cno) & Chr(39) & "," & DT & "," & Chr(39) & TM & Chr(39) & "," & Chr(39) & SIDE(cno) & Chr(39) & "," & Chr(39) & unit(cno) & Chr(39) & "," & DecimalPoint(cno) & "," _
    & wgmtmodeNo & "," & instruID(cno) & "," & NomValLmt(cno, 2) & "," & UppValPer(cno, 2) & "," & LwrValPer(cno, 2) & "," & NomValLmt(cno, 3) & "," & UppValPer(cno, 3) & "," & LwrValPer(cno, 3) & "," _
    & NomValLmt(cno, 0) & "," & UppValPer(cno, 0) & "," & LwrValPer(cno, 0) & "," & Chr(39) & Trim$(ReportLabels(cno, 1)) & Chr(39) & "," & Chr(39) & Trim$(ReportLabels(cno, 2)) & Chr(39) & "," & Chr(39) & Trim$(ReportLabels(cno, 3)) & Chr(39) & "," & Chr(39) & Trim$(ReportLabels(cno, 4)) & Chr(39) & "," & Chr(39) & Trim$(ReportLabels(cno, 5)) & Chr(39) & "," _
    & Chr(39) & Trim$(ReportLabels(cno, 6)) & Chr(39) & "," & Chr(39) & Trim$(ReportLabels(cno, 7)) & Chr(39) & "," & Chr(39) & Trim$(ReportLabels(cno, 8)) & Chr(39) & "," & Chr(39) & Trim$(ReportLabels(cno, 9)) & Chr(39) & "," & Chr(39) & Trim$(ReportLabels(cno, 10)) & Chr(39) & "," & Chr(39) & Trim$(ReportLabels(cno, 11)) & Chr(39) & "," _
    & Chr(39) & Trim$(ReportLabels(cno, 12)) & Chr(39) & "," & Chr(39) & Trim$(ReportLabels(cno, 13)) & Chr(39) & "," & Chr(39) & Trim$(ReportLabels(cno, 14)) & Chr(39) & "," & Chr(39) & Trim$(ReportLabels(cno, 15)) & Chr(39) & "," & Chr(39) & Trim$(ReportLabels(cno, 16)) & Chr(39) & "," & Chr(39) & Trim$(ReportLabels(cno, 17)) & Chr(39) & "," _
    & Chr(39) & Trim$(ReportLabels(cno, 18)) & Chr(39) & "," & Chr(39) & Trim$(ReportLabels(cno, 19)) & Chr(39) & "," & Chr(39) & Trim$(ReportLabels(cno, 20)) & Chr(39) & ")"

    addToLogAudit("from savedata_new() for master:" & strSql)

    cmd.CommandText = strSql
    cmd.CommandTimeout = 0 'already included command timeout to 0 still its not working
    res = cmd.ExecuteNonQuery()

I have already Included Cmd.Commandtimout=0 still I'm Getting same error message Your help will be appreciated, Thanks in advance.

Upvotes: 0

Views: 579

Answers (1)

AngryHacker
AngryHacker

Reputation: 61596

The timeout you are getting is on the connection, not on your query.

Upvotes: 2

Related Questions