-
February 9th, 2013, 09:35 PM
#1
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
-
February 9th, 2013, 10:20 PM
#2
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 10:23 PM.
JG
... If your problem is fixed don't forget to mark your threads as resolved using the Thread Tools menu ...
-
February 9th, 2013, 11:12 PM
#3
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
-
February 9th, 2013, 11:26 PM
#4
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 ...
-
February 9th, 2013, 11:51 PM
#5
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
-
February 10th, 2013, 12:27 AM
#6
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|