none
some of rows missing the calculation why? RRS feed

  • Question

  • HI members 

    i have table "ARTSTOCK' in database

    when i execute my code all rows work perfectly when it reach some row don't 

    i looked a lot for a solution with no result

    so please help

      Dim dll As New SqlCommand("select AR_Ref,Qte from DL_Ligne_Table where Numero='" & numero & "' order by numseq ", cn)
                    Dim att As New SqlDataAdapter(dll)
                    Dim arttab As New DataTable
                    att.Fill(arttab)
    
                    For k = 0 To arttab.Rows.Count - 1
    
                        Dim art As New SqlCommand("select AS_QteCom from F_artstock where ar_ref='" & arttab.Rows(k).Item(0) & "' and DE_NO='1'", cn)
                        Dim Qte As Double = art.ExecuteScalar
                        Dim totQte As Double
    
                        totQte = Qte - CDbl(arttab.Rows(k).Item(1))
    
                        If totQte < 0 Then
    
                            Dim comart As New SqlCommand("update F_artstock set AS_QteCom='0' where ar_ref='" & arttab.Rows(k).Item(0) & "' and DE_NO='1'", cn)
                            comart.ExecuteNonQuery()
                        Else
    
                            Dim comart As New SqlCommand("update F_artstock set AS_QteCom='" & totQte & "' where ar_ref='" & arttab.Rows(k).Item(0) & "' and DE_NO='1'", cn)
                            comart.ExecuteNonQuery()
    
    
                        End If
    
    
    
    
    
                    Next

    the code execute like

     when i enter a table with 4 column 

    i make the 3rd column as qte

    it take the qte exist in artstock and make it minus the entered one

    after all thing work unless in some rows don't

    please help 

    Wednesday, November 20, 2019 1:26 PM

Answers

  • Hi,

    According to my understanding, do you want the following results?

    Complete the corresponding specific calculation in the second data table and then update the data table.

    If so, you can try my simple version of the code.

    Before:

    After:

    Code:

    Imports System.Data.SqlClient
    
    Public Class Form1
        Public constr As String = "Data Source = (localdb)\MSSQLLocalDB; Integrated Security = True ;AttachDbFileName= D:\Local Database\191121.mdf"
        Public cn As SqlConnection
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim num As Integer = 1
            Using cn = New SqlConnection(constr)
                cn.Open()
                Dim cmd As New SqlCommand("select article,existing_Qte1,existing_Qte2 from DL_Ligne_Table where num='" & num & "'", cn)
                Dim sda As New SqlDataAdapter(cmd)
                Dim dt As New DataTable
                sda.Fill(dt)
    
                For k = 0 To dt.Rows.Count - 1
                    Dim cmd1 As New SqlCommand("select entered_Qte from F_artstock where article='" & dt.Rows(k).Item(0) & "' and num='" & num & "'", cn)
                    Dim entered_Qte As Double = cmd1.ExecuteScalar
                    MsgBox(entered_Qte)
    
                    'minused_Qte
                    Dim minused_Qte As Double
                    minused_Qte = CDbl(dt.Rows(k).Item(2)) - entered_Qte
                    If minused_Qte < 0 Then
                        Dim cmd2 As New SqlCommand("update F_artstock set minused_Qte='0' where article='" & dt.Rows(k).Item(0) & "' and num='" & num & "'", cn)
                        cmd2.ExecuteNonQuery()
                    Else
                        Dim cmd2 As New SqlCommand("update F_artstock set minused_Qte='" & minused_Qte & "' where article='" & dt.Rows(k).Item(0) & "' and num='" & num & "'", cn)
                        cmd2.ExecuteNonQuery()
                    End If
    
                    'added_Qte
                    Dim added_Qte As Double
                    added_Qte = CDbl(dt.Rows(k).Item(1)) + entered_Qte
                    Dim cmd3 As New SqlCommand("update F_artstock set added_Qte='" & added_Qte & "' where article='" & dt.Rows(k).Item(0) & "' and num='" & num & "'", cn)
                    cmd3.ExecuteNonQuery()
                Next
            End Using
            MsgBox("Successful!")
        End Sub
    End Class
    

    Hope it be helpful.

    Best Regards,

    Julie


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Houssem12 Friday, November 29, 2019 7:50 AM
    Thursday, November 21, 2019 7:27 AM
    Moderator

All replies

  • ARTICLE      entered Qte          existing Qte                result

    CA7W0182         8                     0       8                    0    0       (no)

    CA7W7045         6                     0       12                  0     6      (no)

    CA7X2578          10                  40      10                 50    0    (this is correct)

    result = 1st column

    entered Qte + existing Qte (10+40=50)

    result =2nd columnexisting column - entered Qte(10-10=0)

    Wednesday, November 20, 2019 1:37 PM
  • Hi

    Looks more like 7 columns to me.

    Column1 - User enters (Article say CA7W0182)

    Column2 - User enters (say 10)

    Column3- - Calculated as Entered Qty (here = 10)

    Column4 - Value from Data Base (here = 40)

    Column5 - Copied Qty (here = Column3  = 10)

    Column6 - Calculated as Qty (here = Column4 + Column5  =  50)

    Column7 - is completely indeterminate.

    *

    If you ask an understandable question then you will get an understandable answer.


    Regards Les, Livingston, Scotland

    Wednesday, November 20, 2019 2:30 PM
  • thanks for answer

    there 2 table each one have many column

    the 1st i use the qte column and article

    the 2nd the qte(+ to be added) and qte(-to be minused) and  article

    the result like

    take the existing qte and make (+) also take the existing and make (-)

    and make a update query to update the result in artstock db

    its like i make an order for 5 cars i get 3 cars and rest 2 cars 

    this is what the + and - means


    Wednesday, November 20, 2019 2:52 PM
  • Hi,

    According to my understanding, do you want the following results?

    Complete the corresponding specific calculation in the second data table and then update the data table.

    If so, you can try my simple version of the code.

    Before:

    After:

    Code:

    Imports System.Data.SqlClient
    
    Public Class Form1
        Public constr As String = "Data Source = (localdb)\MSSQLLocalDB; Integrated Security = True ;AttachDbFileName= D:\Local Database\191121.mdf"
        Public cn As SqlConnection
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim num As Integer = 1
            Using cn = New SqlConnection(constr)
                cn.Open()
                Dim cmd As New SqlCommand("select article,existing_Qte1,existing_Qte2 from DL_Ligne_Table where num='" & num & "'", cn)
                Dim sda As New SqlDataAdapter(cmd)
                Dim dt As New DataTable
                sda.Fill(dt)
    
                For k = 0 To dt.Rows.Count - 1
                    Dim cmd1 As New SqlCommand("select entered_Qte from F_artstock where article='" & dt.Rows(k).Item(0) & "' and num='" & num & "'", cn)
                    Dim entered_Qte As Double = cmd1.ExecuteScalar
                    MsgBox(entered_Qte)
    
                    'minused_Qte
                    Dim minused_Qte As Double
                    minused_Qte = CDbl(dt.Rows(k).Item(2)) - entered_Qte
                    If minused_Qte < 0 Then
                        Dim cmd2 As New SqlCommand("update F_artstock set minused_Qte='0' where article='" & dt.Rows(k).Item(0) & "' and num='" & num & "'", cn)
                        cmd2.ExecuteNonQuery()
                    Else
                        Dim cmd2 As New SqlCommand("update F_artstock set minused_Qte='" & minused_Qte & "' where article='" & dt.Rows(k).Item(0) & "' and num='" & num & "'", cn)
                        cmd2.ExecuteNonQuery()
                    End If
    
                    'added_Qte
                    Dim added_Qte As Double
                    added_Qte = CDbl(dt.Rows(k).Item(1)) + entered_Qte
                    Dim cmd3 As New SqlCommand("update F_artstock set added_Qte='" & added_Qte & "' where article='" & dt.Rows(k).Item(0) & "' and num='" & num & "'", cn)
                    cmd3.ExecuteNonQuery()
                Next
            End Using
            MsgBox("Successful!")
        End Sub
    End Class
    

    Hope it be helpful.

    Best Regards,

    Julie


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Houssem12 Friday, November 29, 2019 7:50 AM
    Thursday, November 21, 2019 7:27 AM
    Moderator