عندي مشكلة عند وصول عدد الفواتير 100 الف ومافوق يبدى البرنامج بلبطى في في تحت شاشة الفواتير وعملية الحفظ خاصة اذا كان في القريد اكثر من سطر
البرنامج دوت نت وقواعد البيانات sql2008
هذا كود الحفظ
Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
Dim tr As SqlTransaction
Try
If MainClass.IsTrial And ProcCode = -1 Then
Dim daCount As New SqlDataAdapter("select id from inv where proc_type=1", conn1)
Dim dtCount As New DataTable
daCount.Fill(dtCount)
If dtCount.Rows.Count >= 50 Then
MessageBox.Show("لقد انتهت مدتك لاستخدام البرنامج , يرجى الاتصال بفريق الدعم لمعرفة كيفية الشراء", "", MessageBoxButtons.OK, MessageBoxIcon.Information)
ISTRialEnd = True
Exit Sub
End If
End If
txtNo.Focus()
If cmbSafe.SelectedIndex = -1 Then
MessageBox.Show("يجب اختيار الصندوق", "", MessageBoxButtons.OK, MessageBoxIcon.Information)
cmbSafe.Focus()
Exit Sub
End If
If cmbStock.SelectedIndex = -1 Then
MessageBox.Show("يجب اختيار الخزنة", "", MessageBoxButtons.OK, MessageBoxIcon.Information)
cmbStock.Focus()
Exit Sub
End If
If dgvItems.Rows.Count = 0 Then
MessageBox.Show("يجب ادخال أصناف للفاتورة", "", MessageBoxButtons.OK, MessageBoxIcon.Information)
cmbCurrency.Focus()
Exit Sub
End If
Dim CustAcc As Integer = 0
Dim StockAcc As Integer = 0
Dim _AvgArr(dgvItems.Rows.Count) As Double
For z = 0 To dgvItems.Rows.Count - 1
_AvgArr(z) = CalcAvgCost(GetCurrencyNo("" & dgvItems.Rows(z).Cells(2).Value))
If _AvgArr(z) = -1 Then
MessageBox.Show("مشكلة في جلب المتوسط لايمكن الحفظ الان، حاول مرة أخرى", "", MessageBoxButtons.OK, MessageBoxIcon.Information)
Exit Sub
End If
Next
Dim daCustAcc As New SqlDataAdapter("select Code from Accounts_Index where type=2 and AName='" & cmbClient.Text & "'", conn)
Dim dtCustAcc As New DataTable
daCustAcc.Fill(dtCustAcc)
If dtCustAcc.Rows.Count > 0 Then
CustAcc = Val("" & dtCustAcc.Rows(0)(0))
End If
Dim daStockAcc As New SqlDataAdapter("select Code from Accounts_Index where type=2 and acc_branch=" & MainClass.BranchNo & " and AName='" & cmbStock.Text & "'", conn)
Dim dtStockAcc As New DataTable
daStockAcc.Fill(dtStockAcc)
If dtStockAcc.Rows.Count > 0 Then
StockAcc = Val("" & dtStockAcc.Rows(0)(0))
End If
Dim CondBranch As String = ""
If MainClass.BranchNo <> -1 Then
CondBranch = " where branch=" & MainClass.BranchNo
End If
If conn.State <> ConnectionState.Open Then conn.Open()
tr = conn.BeginTransaction()
Dim cmd As New SqlCommand
Dim purch_res_id As Integer = 0
Dim sale_res_id As Integer = 0
If ProcCode = -1 Then
If Val(txtTotPurch.Text) <> 0 Then
cmd = New SqlCommand("select max(id) from Restrictions " & CondBranch, conn, tr)
purch_res_id = Val("" & cmd.ExecuteScalar) + 1
End If
If Val(txtTotSale.Text) <> 0 And purch_res_id <> 0 Then
sale_res_id = purch_res_id + 1
ElseIf Val(txtTotSale.Text) <> 0 Then
cmd = New SqlCommand("select max(id) from Restrictions" & CondBranch, conn, tr)
sale_res_id = Val("" & cmd.ExecuteScalar) + 1
End If
Else
purch_res_id = RestPurchCode
sale_res_id = RestSaleCode
cmd = New SqlCommand("delete from Restrictions where id= " & purch_res_id, conn, tr)
cmd.ExecuteNonQuery()
cmd = New SqlCommand("delete from Restrictions where id= " & sale_res_id, conn, tr)
cmd.ExecuteNonQuery()
cmd = New SqlCommand("delete from Restrictions_Sub where res_id= " & purch_res_id, conn, tr)
cmd.ExecuteNonQuery()
cmd = New SqlCommand("delete from Restrictions_Sub where res_id= " & sale_res_id, conn, tr)
cmd.ExecuteNonQuery()
If purch_res_id = 0 And Val(txtTotPurch.Text) <> 0 Then
cmd = New SqlCommand("select max(id) from Restrictions " & CondBranch, conn, tr)
purch_res_id = Val("" & cmd.ExecuteScalar) + 1
End If
If sale_res_id = 0 And Val(txtTotSale.Text) <> 0 Then
cmd = New SqlCommand("select max(id) from Restrictions " & CondBranch, conn, tr)
sale_res_id = Val("" & cmd.ExecuteScalar) + 1
End If
End If
If cmbProcType.SelectedIndex = 1 Then
ProcAvgZeroOnDeleteReturn()
End If
If ProcCode = -1 Then
If cmbProcType.SelectedIndex <> 1 Then
cmd = New SqlCommand("insert into Inv(proc_type,id,date,inv_type,safe,stock,cust_id,sales_emp,tot_purch,tot_sale,tot_net,purch_rest_id,sale_rest_id,branch,IS_Deleted)values" & _
"(@proc_type,@id,@date,@inv_type,@safe,@stock,@cust_id,@sales_emp,@tot_purch,@tot_sale,@tot_net,@purch_rest_id,@sale_rest_id,@branch,@IS_Deleted)", conn, tr)
Else
cmd = New SqlCommand("insert into Inv(proc_type,id,date,inv_type,safe,stock,cust_id,sales_emp,tot_purch,tot_sale,tot_net,purch_rest_id,sale_rest_id,InvId_Return,branch,IS_Deleted)values" & _
"(@proc_type,@id,@date,@inv_type,@safe,@stock,@cust_id,@sales_emp,@tot_purch,@tot_sale,@tot_net,@purch_rest_id,@sale_rest_id,@InvId_Return,@branch,@IS_Deleted)", conn, tr)
End If
LoadInvNo()
Code = Val(txtNo.Text)
Else
If cmbProcType.SelectedIndex <> 1 Then
cmd = New SqlCommand("update Inv set proc_type=@proc_type, date=@date ,inv_type=@inv_type ,safe=@safe ,stock=@stock ," & _
"cust_id=@cust_id ,sales_emp=@sales_emp, tot_purch=@tot_purch ,tot_sale=@tot_sale ,tot_net=@tot_net, IS_Deleted=@IS_Deleted" & _
" where proc_id=" & ProcCode, conn, tr)
Else
cmd = New SqlCommand("update Inv set proc_type=@proc_type, date=@date ,inv_type=@inv_type ,safe=@safe ,stock=@stock ," & _
"cust_id=@cust_id ,sales_emp=@sales_emp, tot_purch=@tot_purch ,tot_sale=@tot_sale ,tot_net=@tot_net,InvId_Return=@InvId_Return, IS_Deleted=@IS_Deleted" & _
" where proc_id=" & ProcCode, conn, tr)
End If
End If
cmd.Parameters.Add("@proc_type", SqlDbType.Int).Value = cmbProcType.SelectedIndex + 1
cmd.Parameters.Add("@id", SqlDbType.Int).Value = Code
If Code = -1 Then
cmd.Parameters.Add("@date", SqlDbType.DateTime).Value = DateTime.Now
Else
cmd.Parameters.Add("@date", SqlDbType.DateTime).Value = txtDate.Value
End If
cmd.Parameters.Add("@inv_type", SqlDbType.Int).Value = cmbType.SelectedIndex + 1
cmd.Parameters.Add("@safe", SqlDbType.Int).Value = cmbSafe.SelectedValue
cmd.Parameters.Add("@stock", SqlDbType.Int).Value = cmbStock.SelectedValue
cmd.Parameters.Add("@cust_id", SqlDbType.Int).Value = cmbClient.SelectedValue
cmd.Parameters.Add("@sales_emp", SqlDbType.Int).Value = MainClass.EmpNo
cmd.Parameters.Add("@tot_purch", SqlDbType.Float).Value = txtTotPurch.Text
cmd.Parameters.Add("@tot_sale", SqlDbType.Float).Value = txtTotSale.Text
cmd.Parameters.Add("@tot_net", SqlDbType.Float).Value = txtDiff.Text.Replace("-", "")
cmd.Parameters.Add("@purch_rest_id", SqlDbType.Int).Value = purch_res_id
cmd.Parameters.Add("@sale_rest_id", SqlDbType.Int).Value = sale_res_id
cmd.Parameters.Add("@InvId_Return", SqlDbType.Int).Value = Val(txtSrcInvNo.Text)
cmd.Parameters.Add("@branch", SqlDbType.Int).Value = MainClass.BranchNo
cmd.Parameters.Add("@IS_Deleted", SqlDbType.Bit).Value = 0
cmd.ExecuteNonQuery()
If ProcCode <> -1 Then
cmd = New SqlCommand("delete from Inv_Sub where proc_id= " & ProcCode, conn, tr)
cmd.ExecuteNonQuery()
End If
If ProcCode = -1 Then
cmd = New SqlCommand("select max(proc_id) from Inv", conn, tr)
ProcCode = Val("" & cmd.ExecuteScalar)
End If
For i = 0 To dgvItems.Rows.Count - 1
cmd = New SqlCommand("insert into Inv_Sub(proc_id,proc_type,currency_from,val,exchange_price,currency_to,avg)" & _
"values(@proc_id,@proc_type,@currency_from,@val,@exchange_price,@currency_to,@avg)", conn, tr)
cmd.Parameters.Add("@proc_id", SqlDbType.Int).Value = ProcCode
Dim dgvCellProc As DataGridViewComboBoxCell = CType(dgvItems.Rows(i).Cells(1), DataGridViewCell)
If dgvCellProc.Value = "شراء" Then
cmd.Parameters.Add("@proc_type", SqlDbType.Int).Value = 1
Else
cmd.Parameters.Add("@proc_type", SqlDbType.Int).Value = 2
End If
cmd.Parameters.Add("@currency_from", SqlDbType.Int).Value = GetCurrencyNo("" & dgvItems.Rows(i).Cells(2).Value)
cmd.Parameters.Add("@val", SqlDbType.Float).Value = dgvItems.Rows(i).Cells(3).Value
cmd.Parameters.Add("@exchange_price", SqlDbType.Float).Value = dgvItems.Rows(i).Cells(4).Value
cmd.Parameters.Add("@currency_to", SqlDbType.Int).Value = GetCurrencyNo("" & dgvItems.Rows(i).Cells(5).Value)
cmd.Parameters.Add("@avg", SqlDbType.Float).Value = _AvgArr(i)
cmd.ExecuteNonQuery()
Next
' --------------------- تسجيل القيد -----------------------------------------------------------------
If cmbProcType.SelectedIndex = 0 Then
If Val(txtTotPurch.Text) <> 0 Then
cmd = New SqlCommand("insert into Restrictions(id,date,doc_no,type,state,notes,branch,IS_Deleted)" & _
"values(@id,@date,@doc_no,@type,@state,@notes,@branch,@IS_Deleted)", conn, tr)
cmd.Parameters.Add("@id", SqlDbType.Int).Value = purch_res_id
cmd.Parameters.Add("@date", SqlDbType.DateTime).Value = txtDate.Value
cmd.Parameters.Add("@doc_no", SqlDbType.Int).Value = Code
cmd.Parameters.Add("@type", SqlDbType.Int).Value = 1
cmd.Parameters.Add("@state", SqlDbType.Int).Value = 1
cmd.Parameters.Add("@notes", SqlDbType.NVarChar).Value = "فاتورة شراء " & cmbType.Text & " رقم:" & Code & " خاصة العميل:" & cmbClient.Text
cmd.Parameters.Add("@branch", SqlDbType.Int).Value = MainClass.BranchNo
cmd.Parameters.Add("@IS_Deleted", SqlDbType.Bit).Value = 0
cmd.ExecuteNonQuery()
cmd = New SqlCommand("insert into Restrictions_Sub(res_id,dept,credit,acc_no,notes,branch)" & _
"values(@res_id,@dept,@credit,@acc_no,@notes,@branch)", conn, tr)
cmd.Parameters.Add("@res_id", SqlDbType.Int).Value = purch_res_id
cmd.Parameters.Add("@dept", SqlDbType.Float).Value = txtTotPurch.Text
cmd.Parameters.Add("@credit", SqlDbType.Float).Value = 0
cmd.Parameters.Add("@acc_no", SqlDbType.Int).Value = 3410001
cmd.Parameters.Add("@notes", SqlDbType.NVarChar).Value = "فاتورة شراء " & cmbType.Text & " رقم:" & Code & " خاصة العميل:" & cmbClient.Text
cmd.Parameters.Add("@branch", SqlDbType.Int).Value = MainClass.BranchNo
cmd.ExecuteNonQuery()
cmd = New SqlCommand("insert into Restrictions_Sub(res_id,dept,credit,acc_no,notes,branch)" & _
"values(@res_id,@dept,@credit,@acc_no,@notes,@branch)", conn, tr)
cmd.Parameters.Add("@res_id", SqlDbType.Int).Value = purch_res_id
cmd.Parameters.Add("@dept", SqlDbType.Float).Value = 0
cmd.Parameters.Add("@credit", SqlDbType.Float).Value = txtTotPurch.Text
cmd.Parameters.Add("@acc_no", SqlDbType.Int).Value = CustAcc
cmd.Parameters.Add("@notes", SqlDbType.NVarChar).Value = "فاتورة شراء " & cmbType.Text & " رقم:" & Code & " خاصة العميل:" & cmbClient.Text
cmd.Parameters.Add("@branch", SqlDbType.Int).Value = MainClass.BranchNo
cmd.ExecuteNonQuery()
If cmbType.SelectedIndex = 1 Then
cmd = New SqlCommand("insert into Restrictions_Sub(res_id,dept,credit,acc_no,notes,branch)" & _
"values(@res_id,@dept,@credit,@acc_no,@notes,@branch)", conn, tr)
cmd.Parameters.Add("@res_id", SqlDbType.Int).Value = purch_res_id
cmd.Parameters.Add("@dept", SqlDbType.Float).Value = txtTotPurch.Text
cmd.Parameters.Add("@credit", SqlDbType.Float).Value = 0
cmd.Parameters.Add("@acc_no", SqlDbType.Int).Value = CustAcc
cmd.Parameters.Add("@notes", SqlDbType.NVarChar).Value = "فاتورة شراء " & cmbType.Text & " رقم:" & Code & " خاصة العميل:" & cmbClient.Text
cmd.Parameters.Add("@branch", SqlDbType.Int).Value = MainClass.BranchNo
cmd.ExecuteNonQuery()
cmd = New SqlCommand("insert into Restrictions_Sub(res_id,dept,credit,acc_no,notes,branch)" & _
"values(@res_id,@dept,@credit,@acc_no,@notes,@branch)", conn, tr)
cmd.Parameters.Add("@res_id", SqlDbType.Int).Value = purch_res_id
cmd.Parameters.Add("@dept", SqlDbType.Float).Value = 0
cmd.Parameters.Add("@credit", SqlDbType.Float).Value = txtTotPurch.Text
cmd.Parameters.Add("@acc_no", SqlDbType.Int).Value = StockAcc
cmd.Parameters.Add("@notes", SqlDbType.NVarChar).Value = "فاتورة شراء " & cmbType.Text & " رقم:" & Code & " خاصة العميل:" & cmbClient.Text
cmd.Parameters.Add("@branch", SqlDbType.Int).Value = MainClass.BranchNo
cmd.ExecuteNonQuery()
End If
End If
If Val(txtTotSale.Text) <> 0 Then
cmd = New SqlCommand("insert into Restrictions(id,date,doc_no,type,state,notes,branch,IS_Deleted)" & _
"values(@id,@date,@doc_no,@type,@state,@notes,@branch,@IS_Deleted)", conn, tr)
cmd.Parameters.Add("@id", SqlDbType.Int).Value = sale_res_id
cmd.Parameters.Add("@date", SqlDbType.DateTime).Value = txtDate.Value
cmd.Parameters.Add("@doc_no", SqlDbType.Int).Value = Code
cmd.Parameters.Add("@type", SqlDbType.Int).Value = 2
cmd.Parameters.Add("@state", SqlDbType.Int).Value = 1
cmd.Parameters.Add("@notes", SqlDbType.NVarChar).Value = "فاتورة بيع " & cmbType.Text & " رقم:" & Code & " خاصة العميل:" & cmbClient.Text
cmd.Parameters.Add("@branch", SqlDbType.Int).Value = MainClass.BranchNo
cmd.Parameters.Add("@IS_Deleted", SqlDbType.Bit).Value = 0
cmd.ExecuteNonQuery()
cmd = New SqlCommand("insert into Restrictions_Sub(res_id,dept,credit,acc_no,notes,branch)" & _
"values(@res_id,@dept,@credit,@acc_no,@notes,@branch)", conn, tr)
cmd.Parameters.Add("@res_id", SqlDbType.Int).Value = sale_res_id
cmd.Parameters.Add("@dept", SqlDbType.Float).Value = txtTotSale.Text
cmd.Parameters.Add("@credit", SqlDbType.Float).Value = 0
cmd.Parameters.Add("@acc_no", SqlDbType.Int).Value = CustAcc
cmd.Parameters.Add("@notes", SqlDbType.NVarChar).Value = "فاتورة بيع " & cmbType.Text & " رقم:" & Code & " خاصة العميل:" & cmbClient.Text
cmd.Parameters.Add("@branch", SqlDbType.Int).Value = MainClass.BranchNo
cmd.ExecuteNonQuery()
cmd = New SqlCommand("insert into Restrictions_Sub(res_id,dept,credit,acc_no,notes,branch)" & _
"values(@res_id,@dept,@credit,@acc_no,@notes,@branch)", conn, tr)
cmd.Parameters.Add("@res_id", SqlDbType.Int).Value = sale_res_id
cmd.Parameters.Add("@dept", SqlDbType.Float).Value = 0
cmd.Parameters.Add("@credit", SqlDbType.Float).Value = txtTotSale.Text
cmd.Parameters.Add("@acc_no", SqlDbType.Int).Value = 4110001
cmd.Parameters.Add("@notes", SqlDbType.NVarChar).Value = "فاتورة بيع " & cmbType.Text & " رقم:" & Code & " خاصة العميل:" & cmbClient.Text
cmd.Parameters.Add("@branch", SqlDbType.Int).Value = MainClass.BranchNo
cmd.ExecuteNonQuery()
If cmbType.SelectedIndex = 1 Then
cmd = New SqlCommand("insert into Restrictions_Sub(res_id,dept,credit,acc_no,notes,branch)" & _
"values(@res_id,@dept,@credit,@acc_no,@notes,@branch)", conn, tr)
cmd.Parameters.Add("@res_id", SqlDbType.Int).Value = sale_res_id
cmd.Parameters.Add("@dept", SqlDbType.Float).Value = txtTotSale.Text
cmd.Parameters.Add("@credit", SqlDbType.Float).Value = 0
cmd.Parameters.Add("@acc_no", SqlDbType.Int).Value = StockAcc
cmd.Parameters.Add("@notes", SqlDbType.NVarChar).Value = "فاتورة بيع " & cmbType.Text & " رقم:" & Code & " خاصة العميل:" & cmbClient.Text
cmd.Parameters.Add("@branch", SqlDbType.Int).Value = MainClass.BranchNo
cmd.ExecuteNonQuery()
cmd = New SqlCommand("insert into Restrictions_Sub(res_id,dept,credit,acc_no,notes,branch)" & _
"values(@res_id,@dept,@credit,@acc_no,@notes,@branch)", conn, tr)
cmd.Parameters.Add("@res_id", SqlDbType.Int).Value = sale_res_id
cmd.Parameters.Add("@dept", SqlDbType.Float).Value = 0
cmd.Parameters.Add("@credit", SqlDbType.Float).Value = txtTotSale.Text
cmd.Parameters.Add("@acc_no", SqlDbType.Int).Value = CustAcc
cmd.Parameters.Add("@notes", SqlDbType.NVarChar).Value = "فاتورة بيع " & cmbType.Text & " رقم:" & Code & " خاصة العميل:" & cmbClient.Text
cmd.Parameters.Add("@branch", SqlDbType.Int).Value = MainClass.BranchNo
cmd.ExecuteNonQuery()
End If
tr.Commit()
dgvSrch.Rows.Clear()
For i = 0 To dgvItems.Rows.Count - 1
If MainClass.CalcStock(GetCurrencyNo("" & dgvItems.Rows(i).Cells(2).Value)) = 0 Then
'cmd = New SqlCommand("delete from AvgZero where curr=" & GetCurrencyNo("" & dgvItems.Rows(i).Cells(2).Value), conn)
'cmd.ExecuteNonQuery()
cmd = New SqlCommand("insert into AvgZero(curr,date)" & _
"values(@curr,@date)", conn)
cmd.Parameters.Add("@date", SqlDbType.DateTime).Value = DateTime.Now
cmd.Parameters.Add("@curr", SqlDbType.Int).Value = GetCurrencyNo("" & dgvItems.Rows(i).Cells(2).Value)
cmd.ExecuteNonQuery()
End If
Next
Try
Catch ex As Exception
End Try
'MessageBox.Show("تم الحفظ", "", MessageBoxButtons.OK, MessageBoxIcon.Information)
txtCurrency1.Text = ""
txtCurrency2.Text = ""
cmbClient.SelectedIndex = -1
cmbCurrency.SelectedIndex = -1
txtVal1.Text = ""
txtExchangeVal.Text = ""
txtVal2.Text = ""
dgvItems.Rows.Clear()
txtCapital.Text = ""
txtTotPurch.Text = ""
txtTotSale.Text = ""
txtDiff.Text = ""
LoadInvNo()
CLR()
cmbCurrency.Focus()
Catch ex As Exception
tr.Rollback()
Dim msg As String = "خطأ أثناء الحفظ"
msg += Environment.NewLine & "تفاصيل الخطأ: " & ex.Message
MessageBox.Show(msg, "", MessageBoxButtons.OK, MessageBoxIcon.Error)
Finally
If conn.State <> ConnectionState.Closed Then conn.Close()
End Try
End Sub