Deduct from Quantity using datagridview

[wilsonchama]

Hi Guys,

I have a Microsoft Access database in which my VB.Net 2008 application is posting the data. It is an inventory management application.
I have managed to accomplish a lot of things with your help.
And Now, I have reached a point where all my ideas have been exhausted, I do not even know how to start to code the next fucntion.

I am looking for suggestions that will help me to make the vb.net appliction deduct quantity in the table field called (proQuantity) based on the items listed in the dtagridview.

The user is selecting items using the combobox based on their IDs and listing them in a datagridview (which is in this case a shoping cart) with a button click. The datagridview has columns (pID, proName,proprice). When all the items the customer wants to buy are listed in the datagridview, the User will then click a button to complete the sale, and this should deduct the items in the datagridview from the table field called (proQuantity) based on the ID of each product in the datagridview.

Is what I am trying to do possible? and If it is, help with possible suggestions.

[Santanu.Das]

Please post how far you did. Which can a way to give you proper solution for your problem.

You can rau a loop through the DGV's row count and deduct the quantity value into the Button Click event. The codes should be like

 Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        For Each RW As DataGridViewRow In DGV.Rows
            'Pick the Product Id from Each row
            'and Deduct the Quantity from Database
        Next
 End Sub

Hope it can give you a way to solve your problem.

[wilsonchama]

Hi Shark,
I got to try the idea out, but I seem to have run of ideas again.
This is what I have done so far, with the help of the few examples i have looked at. I instead used the "For Isold As Integer" and not the "for each".

the code looks like this

For Isold As Integer = 0 To DataGridView1.Rows.Count - 1

            Dim Inveupdate As String = "UPDATE tblProducts SET proQuantity= proQuantity-" & DataGridView1.Rows(Isold).Cells(0).Value & "'"

Whe I try to run the program, it is not doing anything, and there is no error either. Could it be that I have not done it right or am I missing the whole concept?

[wilsonchama]

Hi Sharks,

I've tried to work out something from the ideas you gave me and the ones I've gathered. So far I've come up with the following code.

 Private Sub updatestock()

        For Each Isold As DataGridViewRow In DataGridView1.Rows

            Dim pID As String
            Dim Inveupdate As New OleDb.OleDbCommand
            pID = Isold.Cells(0).Value.ToString()

            Inveupdate.CommandText = "UPDATE tblProducts SET proQuantity = proQuantity - 1 WHERE pID = ?"
            Inveupdate.CommandType = CommandType.Text
            Inveupdate.Connection = myConnToAccess
            Inveupdate.ExecuteNonQuery()

        Next
        myConnToAccess.Close()
    End Sub

But I am getting an error message saying "No value given for one or more required parameters" on "Inveupdate.ExecuteNonQuery()" line. But I happen not to know why the error is occuring.

Any more suggestions?

The screenshot to give you more understanding about what I am talking is shown below.

The datagridview is acting as a shopping Cart. And so, when the user selects and adds the items to the shopping cart and the button "complete sale" is clicked, the program should update the proQuantity field in tblProducts, rfor each of the product appearing in the shoping cart.

Thanks in advance.

[Santanu.Das]

Firstly, I am describing here about the difference of loops. There are no difference in working procedure, only in structure. In your loops you tried to access each row by their Rowindex value. And in other loop you tried to accessed the row directly through a DataGridViewRow object. Finaly they give you the same result.

Secondly, in the SQL query you have used a parameter but you have never declared the parameter. So it has given you an exception. Your code should be

Private Sub updatestock()
        For Each Isold As DataGridViewRow In DataGridView1.Rows

            Dim Inveupdate As New OleDb.OleDbCommand

            Inveupdate.CommandText = "UPDATE tblProducts SET proQuantity = proQuantity - 1 WHERE pID = ?"

            'Declare the Parameter and asign value to it.
            'If you have multiple parameters to set
            'They must set with their respective serials.

            Inveupdate.Parameters.AddWithVAlue("@pID",Isold.Cells(0).Value.ToString())

            Inveupdate.CommandType = CommandType.Text

            Inveupdate.Connection = myConnToAccess

            Inveupdate.ExecuteNonQuery()

            'Clear the Parameters and dispose the Command Object.
            Inveupdate.Parameters.Clear()
            Inveupdate.Dispose()
        Next
        myConnToAccess.Close()
End Sub

Hope it can help you. Don't use needless variables.

[wilsonchama]

Hi sharks,
I implemented the code exactly as you guided me. the full code after adding the try/catch now looks like this.

Private Sub UpdateStock()
        For Each Isold As DataGridViewRow In dgdCart.Rows


            Dim Inveupdate As New OleDb.OleDbCommand

            Try
                Inveupdate.CommandText = "UPDATE tblProducts SET Pqty = Pqty - 1 WHERE pID = ?"

                Inveupdate.Parameters.AddWithValue("@pID", Isold.Cells(0).Value.ToString())

                Inveupdate.CommandType = CommandType.Text
                Inveupdate.Connection = myConnToAccess
                Inveupdate.ExecuteNonQuery()

                Inveupdate.Parameters.Clear()
                Inveupdate.Dispose()
            Catch ex As Exception
                MessageBox.Show(ex.Message)

            End Try
        Next


    End Sub

The first time I implemented it, the code worked with VB.Net Express Edition. But when I shifted the to Vb.net Professional (visual studio 2008 Professional) the code is producing an error saying "Object reference not set to an instance of an object"

What could be the cause of the problem?

[Santanu.Das]

for which object the exception occured? Here you have three objects, DataGridViewRow, OleDb.OleDbCommand and OleDb.OleDbConnection.
Remove the Try....End Try from loop. Make the loop within Try.

[wilsonchama]

Hi Shark, thanks for your reply.
I tried to change the try/catch position.

and also tried to set the break points even though I am not sure whether I was it right. The code now looks like this

Private Sub UpdateStock()
        Try
            For Each Isold As DataGridViewRow In dgdCart.Rows
                Dim pID As String
                Dim Inveupdate As New OleDb.OleDbCommand

                pID = Isold.Cells(0).Value.ToString()

                Inveupdate.CommandText = "UPDATE tblProducts SET Pqty = Pqty - 1 WHERE pID = ?"

                Inveupdate.Parameters.AddWithValue("@pID", Isold.Cells(0).Value.ToString())

                Inveupdate.CommandType = CommandType.Text
                Inveupdate.Connection = myConnToAccess
                Inveupdate.ExecuteNonQuery()

                Inveupdate.Parameters.Clear()
                Inveupdate.Dispose()



            Next
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

During debuging, the variable Inveupdate, pID are returning nothing.

I am not sure whether this is what may be causing the error.

Any more insights?

[wilsonchama]

I am sorry to bother you Shark,

I again made a few changes, this time to the datagridview controls on the form. I changed one of its properties. I simply disabled the Enable adding property of the datagridview. This made tha program to successfully deduct from the inventory as per my desire.

Thanks you so much for your kind help.
I will live to remember you as I continue learning to program in VB.net. It has now become my breakfast.