0

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.

vivek padelkar
  • 155
  • 1
  • 3
  • 28
  • Please don't use string concatenation to create queries because it makes your code vulnerable to SQL injection attacks, as well as SQL syntax errors. Use SqlParameters. [How can I avoid SQL injection attacks?](https://stackoverflow.com/questions/2200256/how-can-i-avoid-sql-injection-attacks) – Richardissimo Feb 07 '19 at 06:53
  • `cmd` is a type which is IDisposable, so should be in a `Using` block. The way you are doing `sqlconn` implies that you are trying to keep it alive, which is a bad idea, connection pooling means it works very efficiently to open a new connection for each thing that you're doing - and the SqlConnection type is also IDisposable, so should also be in a `Using` block. – Richardissimo Feb 07 '19 at 10:35

1 Answers1

2

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

AngryHacker
  • 56,860
  • 95
  • 305
  • 561
  • Are you sure? The stack trace says it's coming from `SqlCommand.ExecuteNonQuery()`... – Richardissimo Feb 07 '19 at 06:57
  • @Richardissimo `at System.Data.SqlClient.SqlConnection.OnError` – AngryHacker Feb 07 '19 at 06:59
  • @Richardissimo Plus there is absolutely nothing in the query (horrible coding style notwithstanding) that would cause the SQL server to timeout. – AngryHacker Feb 07 '19 at 07:00
  • Fair enough. I would have expected an error to occur when they opened the connection if there was something wrong there; but the code doesn't even show whether the Command is in a `using` block, let alone the connection (which should be as well), so we don't have much to go on. I think you've probably nailed it. – Richardissimo Feb 07 '19 at 07:04
  • @Richardissimo Right, it's hard to reconstruct all the missing code. – AngryHacker Feb 07 '19 at 07:09
  • So guys what should i do now or you need some more info about it. – vivek padelkar Feb 07 '19 at 07:21
  • @vivekpadelkar Show how you are creating SqlConnection object and connecting to the database. Show how you are constructing the SqlCommand object. – AngryHacker Feb 07 '19 at 07:25
  • @AngryHacker I have edited the question please check whether you can get any hint from it. thanks – vivek padelkar Feb 07 '19 at 07:28
  • @vivekpadelkar So 2 things. After the 1st line (e.g. sqlconn.Open()), examine the .State property. Then, at the end, copy the contents of `strSql` to the clipboard. Open Sql Server Management Studio and run this command manually. See what you get. – AngryHacker Feb 07 '19 at 07:35
  • @AngryHacker i have run this command in my laptop this command run successfully but in the client site im getting this error so i took client DB to test and this working fine too. i dont know why this not working in client site. – vivek padelkar Feb 07 '19 at 08:09
  • So you are saying "it works on your machine"? Put a lot of debugging statements then take it to the client site again. My guess the connection string is incorrect at the client site. – AngryHacker Feb 07 '19 at 08:37