Following Code/Process I am Using
Private Function Process_Grid_Data() As BooleanDim rdr As FbDataReaderDim
dicStoredProc As New Dictionary(Of String, String)Dim fbTrans As FbTransaction
= NothingDim strErrorMsg as String=""Dim Is_Update as Boolean=FalseDim
nITEM_IDS As Long = 0Dim nGroup_IDS As Integer = 0Dim nCategory_IDS As Integer
= 0Dim nUnit_IDS As Integer = 0Dim nTax_IDS As Integer = 0Dim nGroup_Added as
Integer=0Public myImportConnection As New FbConnectionTry If
myImportConnection.State = ConnectionState.Closed Then If
OpenImportConnection() = False Then MessageBox.Show("Database
Connection Does not Exist. Check database connection....!!!", "Database
Connection", MessageBoxButtons.OK, MessageBoxIcon.Information) Exit
Function End If End If fbTrans =
myImportConnection.BeginTransaction()For iLoop as Integer=0 to
grid.Rows.Count-1 '--------Grid Has 100,000 Rows '---------Sometimes Hang Here
Also--------- rdr = Get_Reader("USP_CHECK_AND_GET_ITEM_EXIST('" &
Mid(Trim(dicFields_Name.Item("SP_ITEM_CODES")), 1, 20) & "','" &
Mid(Trim(dicFields_Name.Item("ITEM_PART_CODES")), 1, 20) & "','" &
Mid(Trim(dicFields_Name.Item("ITEM_NAMES")), 1, 50) & "','" &
Mid(Trim(dicFields_Name.Item("CUSTOM_PART_CODE")), 1, 20) & "','" &
Mid(Trim(dicFields_Name.Item("CUSTOM_PART_NAME")), 1, 50) & "','" &
Mid(Trim(dicFields_Name.Item("SALE_UNIT")), 1, 10) & "','" &
Mid(Trim(dicFields_Name.Item("SALE_SUB_UNIT")), 1, 10) & "','" &
Mid(Trim(dicFields_Name.Item("ITEM_LOCAL_TAX")), 1, 20) & "','" &
Mid(Trim(dicFields_Name.Item("ITEM_GROUP")), 1, 50) & "','" &
Mid(Trim(dicFields_Name.Item("ITEM_CATEGORY_ID")), 1, 50) & "')", fbTrans, ,
myImportConnection) While rdr.Read nITEM_IDS=Val(rdr("ITEM_IDS") & "")
nGroup_IDS=Val(rdr("GROUP_IDS") & "") nCategory_IDS=Val(rdr("CATEGORY_IDS") &
"") nUnit_IDS=Val(rdr("UNIT_IDS") & "") nTax_IDS=Val(rdr("TAX_IDS") & "") End
While rdr.Close()
If nITEM_IDS > 0 Then Is_Update = True Else Is_Update = False End If
IF nGroupd_IDS=0 Then If
Create_Group(Mid(Trim(dicFields_Name.Item("ITEM_GROUP")), 1,
50),myImportConnection)=True Then 'nGroup_Added=nGroup_Added+1 End If End If IF
nCategory_IDS=0 Then If
Create_Category(Mid(Trim(dicFields_Name.Item("ITEM_CATEGORY")), 1,
50),myImportConnection)=True Then 'Count For Category Added End If End If IF
nUnit_IDS=0 Then If Create_Unit(Mid(Trim(dicFields_Name.Item("SALE_UNIT")), 1,
10),myImportConnection)=True Then 'Count For Unit Added End If End If IF
nTax_IDS=0 Then If Create_Tax(Mid(Trim(dicFields_Name.Item("ITEM_LOCAL_TAX")),
1, 20),myImportConnection)=True Then 'Count For Tax Added End If End If If
Is_Update=True Then If
Update_Item_Master(nItem_IDS,Item_Name,Rate,bla,bla,bla,myImportConnection)=True
Then 'Count For Item Updated Else 'Error_Message='Why not updated...!!!' End
Else If Insert_Item_Master(Item_Name,Rate,bla,bla,bla,myImportConnection)=True
Then 'Count For Item Inserted Else 'Error_Message='Why not updated...!!!' End
EndNext fbTrans.Commit()return trueCatch ex as Exception strErrorMsg =
strErrorMsg & ex.ToString GoTo errMSGFinally
End Tryerrr: If Not fbTrans Is Nothing Then fbTrans.Rollback()
End If If strErrorMsg <> "" Then MessageBox.Show(strErrorMsg,
lblInvoice_Caption.Text, MessageBoxButtons.OK, MessageBoxIcon.Error) End
If Return FalseEnd Function
Public Function
Update_Item_Master(Var1,Var2,Var3,Bla,Bla,Bla,myImportConnection) 'Update
Statement return trueEnd Function
Public Function
Update_Item_Master(Var1,Var2,Var3,Bla,Bla,Bla,myImportConnection)Try 'Insert
Statement PrePare_Column_Parameter_And_Values Using fCommand As
FbCommand = myImportConnection.CreateCommand fCommand.Cancel()
fCommand.CommandText = "Insert Into M_ITEM(" &
strColumns.ToString.TrimEnd(",") & ") Values(" &
strValues.ToString.TrimEnd(",") & ")" fCommand.Connection =
myImportConnection fCommand.CommandType = CommandType.Text
fCommand.Transaction = fbTrans myResult =
fCommand.BeginExecuteNonQuery(Nothing, Nothing) 'While Not
myResult.IsCompleted 'Me.Text = lblInvoice_Caption.Text &
"[Processing Row No." & Row_No.ToString() & " - Adding In Item Master]"
'End While 'iError =
fCommand.EndExecuteNonQuery(myResult) iError = fCommand.ExecuteNonQuery()
'-------Hang In THis Line-------- fCommand.Parameters.Clear()
End Using Return True Catch ex as Exception return FalseFinallyEnd TryEnd
Function==========CONNECTION================Public Function
OpenImportConnection(Optional ByVal ByPass_Message As Boolean = False) As
Boolean Try If myImportConnection.State =
ConnectionState.Closed Then Dim fbConn As New
FbConnectionStringBuilder() fbConn.Password = Password
fbConn.UserID = UserID fbConn.Pooling = False
fbConn.Role = "sysadmin"
fbConn.MaxPoolSize = 20 If
Is_Local_Connection = True Then fbConn.Database =
strLocalDatabase Else fbConn.Database =
Database_Path End If
fbConn.DataSource = DB_Server
fbConn.ConnectionLifeTime = 30 fbConn.ConnectionTimeout = 30
fbConn.DbCachePages = 100 'fbConn.ContextConnection =
True fbConn.Charset = "UTF8" fbConn.ServerType =
ServerType fbConn.Dialect = 3
'fbConn.IsolationLevel = IsolationLevel.RepeatableRead
If Val(strPort) = 0 Then strPort = 3050
End If fbConn.Port = strPort
myImportConnection.ConnectionString = fbConn.ToString
If fbConn IsNot Nothing Then fbConn = Nothing
End If myImportConnection.Open()
End If Return True Catch ex As Exception
If ByPass_Message = False Then MessageBox.Show("Path is not
valid...Please,check data direcotry setting...!" & vbCrLf & vbCrLf & _
ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End If Return False End Try End Function
On Tuesday, February 24, 2015 12:59 PM, "Svein Erling Tysvær
[email protected] [firebird-support]"
<[email protected]> wrote:
Sukhen wrote:
>>>update never fails but it fails in insert query. loop run in a single
>>>connection with a new datarows in same query.
I answered:
>>I think it is about time to show us some code (not all). Could it be that
>>another transaction not visible to your current
>>transaction has inserted a row that creates a lock conflict with your insert
>>and that your code repeatedly tries (and fails)
>>to insert? Or that it is a real deadlock, where one transaction first
>>successfully updates record A, then fails on record B,
>>whereas another transaction has successfully updated record B and fails on
>>record A?
>>
>>Typically, there would be no problems on INSERT, whereas UPDATE could create
>>problems. Getting the problem on INSERT, I'd say
>>indicates some kind of lock conflict which means that you have some UNIQUE
>>field or constraint or don't use generators to
>>populate your primary key.
Sukhen answered:
>is it could be any buffersize/memmroy issue. please, suggest if it is how to
>check it in firebird
>please, somebody help me out
I didn't answer because I know nothing about buffersize/memory issues with
Firebird (other than knowing that buffersize/memory very rarely causes problems
with Firebird itself, don't know about the components you use in VB to connect
to Firebird).
Normally, INSERTs would never fail. When they fail, that indicate some
duplicate in a unique index or constraint or primary key (though that ought to
give an error rather than be time consuming). Or it could possibly be an
infinite loop. INSERTs can be slow, depending on complexity. Show us some code,
I'm particularly interested in the INSERT that fails and the table definitions
for the tables that the INSERT refers to.
Set
#yiv5242127232 #yiv5242127232 -- #yiv5242127232ygrp-mkp {border:1px solid
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv5242127232
#yiv5242127232ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv5242127232
#yiv5242127232ygrp-mkp #yiv5242127232hd
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}#yiv5242127232 #yiv5242127232ygrp-mkp #yiv5242127232ads
{margin-bottom:10px;}#yiv5242127232 #yiv5242127232ygrp-mkp .yiv5242127232ad
{padding:0 0;}#yiv5242127232 #yiv5242127232ygrp-mkp .yiv5242127232ad p
{margin:0;}#yiv5242127232 #yiv5242127232ygrp-mkp .yiv5242127232ad a
{color:#0000ff;text-decoration:none;}#yiv5242127232 #yiv5242127232ygrp-sponsor
#yiv5242127232ygrp-lc {font-family:Arial;}#yiv5242127232
#yiv5242127232ygrp-sponsor #yiv5242127232ygrp-lc #yiv5242127232hd {margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv5242127232
#yiv5242127232ygrp-sponsor #yiv5242127232ygrp-lc .yiv5242127232ad
{margin-bottom:10px;padding:0 0;}#yiv5242127232 #yiv5242127232actions
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv5242127232
#yiv5242127232activity
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv5242127232
#yiv5242127232activity span {font-weight:700;}#yiv5242127232
#yiv5242127232activity span:first-child
{text-transform:uppercase;}#yiv5242127232 #yiv5242127232activity span a
{color:#5085b6;text-decoration:none;}#yiv5242127232 #yiv5242127232activity span
span {color:#ff7900;}#yiv5242127232 #yiv5242127232activity span
.yiv5242127232underline {text-decoration:underline;}#yiv5242127232
.yiv5242127232attach
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}#yiv5242127232 .yiv5242127232attach div a
{text-decoration:none;}#yiv5242127232 .yiv5242127232attach img
{border:none;padding-right:5px;}#yiv5242127232 .yiv5242127232attach label
{display:block;margin-bottom:5px;}#yiv5242127232 .yiv5242127232attach label a
{text-decoration:none;}#yiv5242127232 blockquote {margin:0 0 0
4px;}#yiv5242127232 .yiv5242127232bold
{font-family:Arial;font-size:13px;font-weight:700;}#yiv5242127232
.yiv5242127232bold a {text-decoration:none;}#yiv5242127232 dd.yiv5242127232last
p a {font-family:Verdana;font-weight:700;}#yiv5242127232 dd.yiv5242127232last p
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv5242127232
dd.yiv5242127232last p span.yiv5242127232yshortcuts
{margin-right:0;}#yiv5242127232 div.yiv5242127232attach-table div div a
{text-decoration:none;}#yiv5242127232 div.yiv5242127232attach-table
{width:400px;}#yiv5242127232 div.yiv5242127232file-title a, #yiv5242127232
div.yiv5242127232file-title a:active, #yiv5242127232
div.yiv5242127232file-title a:hover, #yiv5242127232 div.yiv5242127232file-title
a:visited {text-decoration:none;}#yiv5242127232 div.yiv5242127232photo-title a,
#yiv5242127232 div.yiv5242127232photo-title a:active, #yiv5242127232
div.yiv5242127232photo-title a:hover, #yiv5242127232
div.yiv5242127232photo-title a:visited {text-decoration:none;}#yiv5242127232
div#yiv5242127232ygrp-mlmsg #yiv5242127232ygrp-msg p a
span.yiv5242127232yshortcuts
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv5242127232
.yiv5242127232green {color:#628c2a;}#yiv5242127232 .yiv5242127232MsoNormal
{margin:0 0 0 0;}#yiv5242127232 o {font-size:0;}#yiv5242127232
#yiv5242127232photos div {float:left;width:72px;}#yiv5242127232
#yiv5242127232photos div div {border:1px solid
#666666;height:62px;overflow:hidden;width:62px;}#yiv5242127232
#yiv5242127232photos div label
{color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv5242127232
#yiv5242127232reco-category {font-size:77%;}#yiv5242127232
#yiv5242127232reco-desc {font-size:77%;}#yiv5242127232 .yiv5242127232replbq
{margin:4px;}#yiv5242127232 #yiv5242127232ygrp-actbar div a:first-child
{margin-right:2px;padding-right:5px;}#yiv5242127232 #yiv5242127232ygrp-mlmsg
{font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv5242127232
#yiv5242127232ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv5242127232
#yiv5242127232ygrp-mlmsg select, #yiv5242127232 input, #yiv5242127232 textarea
{font:99% Arial, Helvetica, clean, sans-serif;}#yiv5242127232
#yiv5242127232ygrp-mlmsg pre, #yiv5242127232 code {font:115%
monospace;}#yiv5242127232 #yiv5242127232ygrp-mlmsg *
{line-height:1.22em;}#yiv5242127232 #yiv5242127232ygrp-mlmsg #yiv5242127232logo
{padding-bottom:10px;}#yiv5242127232 #yiv5242127232ygrp-msg p a
{font-family:Verdana;}#yiv5242127232 #yiv5242127232ygrp-msg
p#yiv5242127232attach-count span {color:#1E66AE;font-weight:700;}#yiv5242127232
#yiv5242127232ygrp-reco #yiv5242127232reco-head
{color:#ff7900;font-weight:700;}#yiv5242127232 #yiv5242127232ygrp-reco
{margin-bottom:20px;padding:0px;}#yiv5242127232 #yiv5242127232ygrp-sponsor
#yiv5242127232ov li a {font-size:130%;text-decoration:none;}#yiv5242127232
#yiv5242127232ygrp-sponsor #yiv5242127232ov li
{font-size:77%;list-style-type:square;padding:6px 0;}#yiv5242127232
#yiv5242127232ygrp-sponsor #yiv5242127232ov ul {margin:0;padding:0 0 0
8px;}#yiv5242127232 #yiv5242127232ygrp-text
{font-family:Georgia;}#yiv5242127232 #yiv5242127232ygrp-text p {margin:0 0 1em
0;}#yiv5242127232 #yiv5242127232ygrp-text tt {font-size:120%;}#yiv5242127232
#yiv5242127232ygrp-vital ul li:last-child {border-right:none
!important;}#yiv5242127232
