Question query for getting result ?

raydvard

New member
Joined
Dec 7, 2013
Messages
4
Programming Experience
1-3
Can anyone help me on this please??
I am trying to make a stock exchange system based on mysql database using vb...

So i am stuck on :
my sql table : inProduct, OutProduct and Stock...
inProduct cells > id,prodName, quantity, units, date
OutProcust cells > id,prodName, quantity, units, date
Stock cells > prodName, quantity,units

So i want query to show on listview... with this i have 3 listview of this tables to show data...
now i want query to show Stock in Stock listview that will result like > Quantity of inProduct - Quantity of OutProduct = Stock quantity...
100 - 50 = 50...
So when i add inProduct it will add with Stock,,, and when i add OutProduct it will minus from stock... then show listview...

Please i need this mysql query for vb.net
And please let me if you cannot understand what i am asking for then i will give you codes for the problem...

It will be great pleasure to me if anyone help me on this purpose... Thanks in advance....
 
Hi and welcome to the Forum,

That all sounds like pretty straight forward stuff so what have you tried so far? If there is any particular aspect of this project that you are struggling with then by all means show the code you have tried and explain what you were expecting and what you are actually getting and I am sure that someone here will help guide you through whatever issues you are having.

Cheers,

Ian
 
Having problem with mysql query result

Hi and welcome to the Forum,

That all sounds like pretty straight forward stuff so what have you tried so far? If there is any particular aspect of this project that you are struggling with then by all means show the code you have tried and explain what you were expecting and what you are actually getting and I am sure that someone here will help guide you through whatever issues you are having.

Cheers,

Ian

First Thank you so much for the greetings @IanRyder bro....

Let me clear this problem....

Mysql Database :
Database :
tbl.PNG
inprod Table :
inprodtbl.PNG
outprod Table:
OutProdtbl.PNG
stock Table:
stocktbl.PNG

Okay now i have 3 listview named lvDisplayIn, lvDisplayOut & lvDisplayTot in different tabControls named Total, In & Out ...
So now i want to show how i am showing data in listviews :
lvDisplayIn :
VB.NET:
'Data In Showing    
Public Sub showDataIn()
        conn.Close()
        conn.Open()
        ds = New DataSet
        da = New MySqlDataAdapter("SELECT * FROM inprod", conn)
        da.Fill(ds, "inprod")


        lvDisplayIn.Items.Clear()
        If ds.Tables("inprod").Rows.Count > 0 Then
            For i As Integer = 0 To ds.Tables("inprod").Rows.Count - 1
                With lvDisplayIn.Items.Add(ds.Tables("inprod").Rows(i).Item(0).ToString)
                    .SubItems.Add(ds.Tables("inprod").Rows(i).Item(1).ToString)
                    .SubItems.Add(ds.Tables("inprod").Rows(i).Item(2).ToString + ds.Tables("inprod").Rows(i).Item(3).ToString)
                    .SubItems.Add(ds.Tables("inprod").Rows(i).Item(4).ToString)
                    .SubItems.Add(ds.Tables("inprod").Rows(i).Item(5).ToString)
                End With
            Next
        End If


        tbxProdname.Text = ""
        tbxQuantity.Text = ""
        tbxFromdis.Text = ""
        tbxDate.Value = Now
End Sub

lvDisplayOut :
VB.NET:
'Data Out Showing    
Public Sub showDataOut()


        ds = New DataSet
        da = New MySqlDataAdapter("SELECT * FROM outprod", conn)
        da.Fill(ds, "outprod")


        lvDisplayOut.Items.Clear()
        If ds.Tables("outprod").Rows.Count > 0 Then
            For i As Integer = 0 To ds.Tables("outprod").Rows.Count - 1
                With lvDisplayOut.Items.Add(ds.Tables("outprod").Rows(i).Item(0).ToString)
                    .SubItems.Add(ds.Tables("outprod").Rows(i).Item(1).ToString)
                    .SubItems.Add(ds.Tables("outprod").Rows(i).Item(2).ToString + ds.Tables("outprod").Rows(i).Item(3).ToString)
                    .SubItems.Add(ds.Tables("outprod").Rows(i).Item(4).ToString)
                    .SubItems.Add(ds.Tables("outprod").Rows(i).Item(5).ToString)
                End With
            Next
        End If


        tbxProdname.Text = ""
        tbxQuantity.Text = ""
        tbxFromdis.Text = ""
        tbxDate.Value = Now
End Sub

lvDisplayTot :
VB.NET:
Now here i want to show results of total products remains in my stock...
I am mentioning that i have 3 columns in lvDisplayTot named Product Name, Quanity & Stock

So now i need these mysql queries:
1. When I add any product into my inprod table it will add also in stock table where product name is same ( by adding method +)
As a example : I am adding prodName: pipes & quantity: 100 ... then next time i am adding prodName: pipes & quantity: 100
Then in stock table it should be added like prodName: pipes & quantity: 200 (100+100=200)

2. When i add any product into my outprod table it will deduct from stock table (by deducting method -)
As a example : I am adding prodName: pipes & quantity: 50 ...then it will deduct from stock where prodName: pipes & quantity : 100
So stock will be like prodName: pipes & quanity: 50 (100-50=50)

3. Then i want to show this table data in my lvDisplayTot

Thats it what i want....

I am using these codes to add a product into inprod Table :
VB.NET:
'Adding System @In Stock    
Private Sub btnIn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnIn.Click
        If tbxProdname.Text = Nothing Or tbxQuantity.Text = Nothing Or tbxFromdis.Text = Nothing Or cmbUnits.Text = Nothing Then
            MsgBox("Please Fill Up All The Details Correctly !!", MsgBoxStyle.Information, "Information")
        Else
            Try
                ds = New DataSet
                da = New MySqlDataAdapter("INSERT INTO inprod (prodName, quantity, units, dfrom, date) VALUES('" & tbxProdname.Text & "', '" & tbxQuantity.Text & "','" & cmbUnits.Text & "','" & tbxFromdis.Text & "','" & tbxDate.Value & "')", conn)                            
                da.Fill(ds, "inprod")
                tmrStatus.Start()
                Me.Cursor = Cursors.WaitCursor


            Catch ex As Exception
                MsgBox(ex.Message)
                Me.Refresh()
            End Try
        End If
End Sub

And adding method into outprod Table:
VB.NET:
'Adding System @Out Stock    
Private Sub btnOut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOut.Click
        If tbxProdname.Text = Nothing Or tbxQuantity.Text = Nothing Or tbxFromdis.Text = Nothing Or cmbUnits.Text = Nothing Then
            MsgBox("Please Fill Up All The Details Correctly !!", MsgBoxStyle.Information, "Information")
        Else
            Try
                ds = New DataSet
                da = New MySqlDataAdapter("INSERT INTO outprod (prodName, quantity,units, dispatchedto, date) VALUES('" & tbxProdname.Text & "', '" & tbxQuantity.Text & "','" & cmbUnits.Text & "','" & tbxFromdis.Text & "','" & tbxDate.Value & "')", conn)
                da.Fill(ds, "outprod")
                tmrStatus.Start()
                Me.Cursor = Cursors.WaitCursor


            Catch ex As Exception
                MsgBox("Productname already exists !!", MsgBoxStyle.Information, "Duplicate Instances Found")
                Me.Refresh()
            End Try
        End If
End Sub

So please help me on this problem... I am searching for this problem for a long time... please help me...
Thanks in advance....
 

Attachments

  • InprodLV.PNG
    InprodLV.PNG
    12 KB · Views: 40
Last edited:
You have two choices here. You can either do this in the database or in the application. There are differing opinions on whether this sort of logic should be stored in the database or the application but, to me, this seems like an ideal scenario for doing it in the database. In SQL Server you have something called a Trigger, which is a bit of SQL code that gets executed when something specific happens in a table. If you were doing this in SQL Server, you could add an Insert Trigger to each of your two traffic tables and, in the trigger, update the stock table as appropriate. I don't use MySQL so I don't know if it has an equivalent but I would expect so, so I suggest that you look for that and, assuming it exists, implement it like I described.
 
You have two choices here. You can either do this in the database or in the application. There are differing opinions on whether this sort of logic should be stored in the database or the application but, to me, this seems like an ideal scenario for doing it in the database. In SQL Server you have something called a Trigger, which is a bit of SQL code that gets executed when something specific happens in a table. If you were doing this in SQL Server, you could add an Insert Trigger to each of your two traffic tables and, in the trigger, update the stock table as appropriate. I don't use MySQL so I don't know if it has an equivalent but I would expect so, so I suggest that you look for that and, assuming it exists, implement it like I described.

Please bro can you give me the detailed description how i use Trigger in vb and what Trigger means??
In Mysql Database there is Trigger Option but i don't know how to use it... please give me an explanation on this...
Trigger :
Capture.PNG

and at an easy method i think i should use just mysql query ti insert and select for my Total Listview :
VB.NET:
Dim sqL As String
sqL = ""    ' <<<<< I need a query for the result :(
conn.Open()
cmd = New MySqlCommand(sqL, conn)
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
 
Back
Top