Update DB Using VB 6.0
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 6 of 6

Thread: Update DB Using VB 6.0

Hybrid View

  1. #1
    Join Date
    Feb 2013
    Posts
    34

    Update DB Using VB 6.0

    Hello Guys I need Your Help I have DB and in my database theres a table called Product and Dailyrecord and this is the fields of my table name Product.. (BarCode,ProductName,Quantity,Price) and this is the field of my table name Dailyrecord(Barcode, ProductName, Quantity, Price, SubTotal) this is my code for adding data to list view
    Code:
    Private Sub save()
    ListView1.ListItems.Add , , lblrp.Caption
    ListView1.ListItems.Item(ListView1.ListItems.Count).ListSubItems.Add , , txtCode.Text
    ListView1.ListItems.Item(ListView1.ListItems.Count).ListSubItems.Add , , txtProductName.Text
    ListView1.ListItems.Item(ListView1.ListItems.Count).ListSubItems.Add , , txtQty.Text
    ListView1.ListItems.Item(ListView1.ListItems.Count).ListSubItems.Add , , txtPrice.Text
    ListView1.ListItems.Item(ListView1.ListItems.Count).ListSubItems.Add , , lbltotal.Caption
    ListView1.ListItems.Item(ListView1.ListItems.Count).ListSubItems.Add , , lbldate.Caption
    End Sub
    and this is my code for saving data to may dailyrecord
    Code:
    Private Sub savetodailyrecord()
    Dim X   As Integer
      Dim SQLString As String
    
      For X = 1 To ListView1.ListItems.Count
        SQLString = "Insert Into Dailyrecord (Receipt, BarCode, ProductName, Quantity, Price, TotalPrice, purchase_date) Values ('" & ListView1.ListItems(X).Text & "', '" & ListView1.ListItems(X).SubItems(1) & "','" & ListView1.ListItems(X).SubItems(2) & "','" & ListView1.ListItems(X).SubItems(3) & "','" & ListView1.ListItems(X).SubItems(4) & "','" & ListView1.ListItems(X).SubItems(5) & "'," & ListView1.ListItems(X).SubItems(6) & ")"
        acd.Execute SQLString
      Next
    
      Call Purchase
      ListView1.ListItems.clear
    
    
    End Sub
    and this is what i need to know if i save the data to dailyrecord, the Quantity from dailyrecord is Subtract to the Quantity in Product.. for example in My Listview Quantity You Buy the product name Sardines And in My Stock i have 20 Sardines then You Buy 5 sardines.. and 20-5 = 15 and My Stock Updated to 15 how do i code like this the Output?
    this is my code to do that but nothings Happen
    Code:
    Private Sub Purchase()
    Dim Counter As Integer
    Dim a As Double
    Dim supplycounter As Integer ' Goods
    
      For Counter = 1 To ListView1.ListItems.Count
      
      If rsProduct.EOF = True Then
      
      If rsProduct.Fields("BarCode") = ListView1.SelectedItem.SubItems(1) Then
      a = rsProduct.Fields("Quantity")
      Exit Sub
      
      Counter = a - ListView1.SelectedItem.SubItems(3)
      End If
      
    Next
      
    Call clear
               
    End If
    End Sub
    help me guys

  2. #2
    Join Date
    Jul 2005
    Posts
    1,074

    Re: Update DB Using VB 6.0

    Try executing an sql UPDATE statement
    Code:
    UPDATE table_name
    SET column1=value, column2=value2,...
    WHERE some_column=some_value
    I would Update the table Product inside the For-Next loop where you are saving to table Dailyrecord
    Code:
    For X = 1 To ListView1.ListItems.Count
        SQLString = "Insert Into Dailyrecord (Receipt, BarCode, ProductName, Quantity, Price, TotalPrice, purchase_date) Values ('" & ListView1.ListItems(X).Text & "', '" & ListView1.ListItems(X).SubItems(1) & "','" & ListView1.ListItems(X).SubItems(2) & "','" & ListView1.ListItems(X).SubItems(3) & "','" & ListView1.ListItems(X).SubItems(4) & "','" & ListView1.ListItems(X).SubItems(5) & "'," & ListView1.ListItems(X).SubItems(6) & ")"
        acd.Execute SQLString
        '
        SQLString = "UPDATE Product SET Quantity=Quantity - " & ListView1.ListItems(X).Subitems(3) & " WHERE BarCode=" & ListView1.ListItems(X).Subitems(1)
        acd.Execute SQLString
    Next X
    If you want to only update the selected rows then enclose the instructions in an If-End If
    Last edited by jggtz; February 9th, 2013 at 09:23 PM.
    JG


    ... If your problem is fixed don't forget to mark your threads as resolved using the Thread Tools menu ...

  3. #3
    Join Date
    Feb 2013
    Posts
    34

    Re: Update DB Using VB 6.0

    Thanks..

    Is this my code is correct? when my Quantity is 0 then i want to make sure if My Stock is 0 then Msgbox pop-up say.. Not Enough Stock..
    Code:
    Private Sub inputqty()
    Dim num As String
    If txtCode.Text = "" Or txtProductName.Text = "" Then
        MsgBox "Fill all the Boxes"
        Else
            MsgBox ""
         num = InputBox("Enter number of items : ", "Number of items", 1)
         
         Do Until rsProduct.EOF
        
      If txtCode.Text = rsProduct!BarCode Then
         
      
      
            If num >= Val(rsProduct!Quantity) Then
                    MsgBox "not enough Stocks"
    
                    Exit Sub
                    ElseIf num <= Val(rsProduct!Quantity) Then
                        Exit Do
                    End If
                    
                    
                    
                    Else
                    
                    rsProduct.MoveNext
                    
                    End If
                   Loop
         
         
         
         
    If num = "" Then
     
        Else
        
        If num >= 1 Then
        txtQty.Text = num
        lbltotalamount.Caption = Val(lbltotalamount.Caption) + Val(txtPrice.Text) * Val(txtQty.Text)
        lbltotal.Caption = Val(txtQty.Text) * Val(txtPrice.Text)
    '
    
    
        Call save
        Call clear
        Else
        MsgBox "invalid input"
        End If
        End If
        End If
    End Sub
    I have 20 stocks of sardines and i want to buy 20 and 0 left in my quantity. and I buy Again 5 why my Quantity become -5? instead of MSGBOX "Not Enough Stock" Help Me Guys.. Thanks in Advance

  4. #4
    Join Date
    Jul 2005
    Posts
    1,074

    Re: Update DB Using VB 6.0

    Code:
    Private Sub inputqty()
    
      Dim Mynum As String
    
      If txtCode.Text = "" Or txtProductName.Text = "" Then
        MsgBox "Fill all the Boxes"
      Else
        Mynum = InputBox("Enter number of items : ", "Number of items", 1)
        If IsNumeric(Mynum) = False Then
          MsgBox "Enter a number"
          Exit Sub
        End If
        If rsProduct.EOF = False or rsProduct.BOF = False Then rsProduct.MoveFirst
        Do Until rsProduct.EOF
          If txtCode.Text = rsProduct!BarCode Then
            If Val(Mynum) >= Val(rsProduct!Quantity) Then
              MsgBox "not enough Stocks"
              Exit Sub
            ElseIf Val(Mynum) <= Val(rsProduct!Quantity) Then
              Exit Do
            End If
          End If
          rsProduct.MoveNext
        Loop
        
        If Val(Mynum) >= 1 Then
          txtQty.Text = Mynum
          lbltotalamount.Caption = Val(lbltotalamount.Caption) + Val(txtPrice.Text) * Val(txtQty.Text)
          lbltotal.Caption = Val(txtQty.Text) * Val(txtPrice.Text)
          Call save
          Call clear
        End If
      End If
    
    End Sub
    JG


    ... If your problem is fixed don't forget to mark your threads as resolved using the Thread Tools menu ...

  5. #5
    Join Date
    Feb 2013
    Posts
    34

    Re: Update DB Using VB 6.0

    Thank You So Much Sir.. You Give My Problem Solve.. I Appreciate it,,

    What Should I do to make a Backup files in my Data in Database?

    I dont know how to start with that.. Can you help me Sir.. I am Making a Simple POS System for My Project sir.. And My Instructor want to see in my Project had a backup file.. What Should I do Sir?

    Thanks in Adavance

  6. #6
    Join Date
    Jul 2005
    Posts
    1,074

    Re: Update DB Using VB 6.0

    If your database is MS Access,
    one option is to use the File System Object FSO and the CopyFolder method
    Here's a FSO Tutorial
    http://www.vb6.us/tutorials/using-fs...tem-object-vb6

    If your database is in a SQL Server then you could use DMO
    Here's an example
    http://www.freevbcode.com/ShowCode.asp?ID=2579
    JG


    ... If your problem is fixed don't forget to mark your threads as resolved using the Thread Tools menu ...

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center