none
Receipt not saving in VB RRS feed

  • Question

  • Hello, I'm developing a Point-of-Sale system. I'm trying to gather the data from a datagridview and save it into the database. I've run into many problems. There are three tables. One is Inventory, one is ReceiptDetails, and the other is an index table to the receipts that selects the Receipt's ID, date and total. I don't know if I've structured this completely wrong or not, but it is continually throwing errors. Such as a duplicate receiptID, the "Items" field can't be found and the date and time are always wrong.When i run the code with a breakpoint and check all of the steps the data shows up correctly, but when i try to test run it it's as if none of the data is being gathered.

    I've been struggling with this for a week. I would really appreciate any help!

    Private Sub btnCash_Click(sender As Object, e As EventArgs) Handles btnCash.Click
            'Create the connection and  transaction object

            Try
                Dim MyTransaction As System.Data.SqlClient.SqlTransaction
                Dim MyConnection As New System.Data.SqlClient.SqlConnection(POS.My.Settings.POSConn)
                MyConnection.Open()
                MyTransaction = MyConnection.BeginTransaction

                'insert the new receipt
                Dim SQL As String = "insert into WareReceipts (ReceiptDate, ReceiptTotal) values (0, 1)"
                Dim CMD1 As New System.Data.SqlClient.SqlCommand
                CMD1.Connection = MyConnection
                CMD1.Transaction = MyTransaction
                CMD1.CommandText = SQL
                CMD1.Parameters.AddWithValue("0", Now.Date)
                CMD1.Parameters.AddWithValue("1", txtboxTotal.Text)
                CMD1.ExecuteNonQuery()
                CMD1.Dispose()

                'Get the ID for the receipt
                SQL = "SELECT MAX(ReceiptID) AS MAXID FROM WareReceipts"
                Dim CMD2 As New System.Data.SqlClient.SqlCommand
                CMD2.Connection = MyConnection
                CMD2.Transaction = MyTransaction
                CMD2.CommandText = SQL
                Dim ReceiptID As Long = CMD2.ExecuteScalar()
                CMD2.Dispose()

                    ' insert the details of the Receipt
                Dim I As Integer
                For I = 0 To DGV2.Rows.Count - 1

                    ' get the values
                    Dim ItemID As Integer = DGV2.Rows(I).Cells(0).Value
                    Dim Items As Char = DGV2.Rows(I).Cells(1).Value
                    Dim Price As Decimal = DGV2.Rows(I).Cells(2).Value
                    Dim Quantity As Integer = DGV2.Rows(I).Cells(3).Value

                    'next create a commmand
                    Dim CMD3 As New System.Data.SqlClient.SqlCommand
                    SQL = "insert into ReceiptDetails (ReceiptID, ItemID, Items, Price, Quantity) values (0 ,1 ,2 ,3 ,4 )"
                    CMD3.Connection = MyConnection
                    CMD3.Transaction = MyTransaction
                    CMD3.CommandText = SQL
                    CMD3.Parameters.AddWithValue("0", ReceiptID)
                    CMD3.Parameters.AddWithValue("1", ItemID)
                    CMD3.Parameters.AddWithValue("2", Items)
                    CMD3.Parameters.AddWithValue("3", Price)
                    CMD3.Parameters.AddWithValue("4", Quantity)
                    CMD3.ExecuteNonQuery()
                    CMD3.Dispose()
                Next

                'all's well save the changes
                MyTransaction.Commit()

                    'close connection
                MyTransaction.Dispose()
                MyConnection.Close()
                MyConnection.Dispose()

              

                If MyTransaction IsNot Nothing Then
                MyTransaction.Rollback()
                End If
            Catch ex As Exception

                MsgBox(ex.Message, MsgBoxStyle.Critical Or MsgBoxStyle.OkOnly, "Error!")

            End Try
            DGV2.Rows.Clear()
            txtboxTotal.Text = ""
        End Sub
    End Class

    Wednesday, July 2, 2014 4:10 PM

Answers

  • This is the finalized code that works great with no errors! It was a matter of entering an 'Exit For' to stop the loop from deleting the values before committing the transaction.

    Private Sub btnCash_Click(sender As Object, e As EventArgs) Handles btnCash.Click
            'Create the connection and  transaction object
            
            Try
    
                Dim MyConnection As New System.Data.SqlClient.SqlConnection(POS.My.Settings.POSConn)
                MyConnection.Open()
    
                Dim MyTransaction As System.Data.SqlClient.SqlTransaction
                MyTransaction = MyConnection.BeginTransaction
                ' insert the details of the Receipt
                Dim CMD1 As New System.Data.SqlClient.SqlCommand
                Dim SQL As String = "insert into WareReceipts (ReceiptDate, ReceiptTotal, Category)" & _
                    "Values (@date, @total, @cat)"
                CMD1.Connection = MyConnection
                CMD1.Transaction = MyTransaction
                CMD1.CommandText = SQL
                CMD1.Parameters.Add("@date", SqlDbType.DateTime)
                CMD1.Parameters.Add("@total", SqlDbType.Money)
                CMD1.Parameters.Add("@cat", SqlDbType.Char)
    
                CMD1.Parameters("@date").Value = Now
                CMD1.Parameters("@total").Value = txtboxTotal.Text
                CMD1.Parameters("@cat").Value = "Cash"
                CMD1.ExecuteNonQuery()
                CMD1.Dispose()
    
                Dim CMD2 As New System.Data.SqlClient.SqlCommand
                Dim SL As String = "select max(ReceiptID) as MAXID from WareReceipts"
                CMD2.Connection = MyConnection
                CMD2.Transaction = MyTransaction
                CMD2.CommandText = SL
                Dim ReceiptID As Long = CMD2.ExecuteScalar
                CMD2.Dispose()
    
    
                Dim I As Integer
                For I = 0 To DGV2.Rows.Count - 1
                    Dim CMD3 As New System.Data.SqlClient.SqlCommand
                    Dim SQ As String = "insert into ReceiptDetails " & _
                        "(ReceiptID, ItemID, Items, Price, Quantity)" & _
                        "values (@RecID, @itID, @item, @prc, @quan)"
                    CMD3.Connection = MyConnection
                    CMD3.Transaction = MyTransaction
                    CMD3.CommandText = SQ
                    CMD3.Parameters.Add("@RecID", SqlDbType.Int)
                    CMD3.Parameters.Add("@itID", SqlDbType.Int)
                    CMD3.Parameters.Add("@item", SqlDbType.Char)
                    CMD3.Parameters.Add("@prc", SqlDbType.Decimal)
                    CMD3.Parameters.Add("@quan", SqlDbType.Int)
    
                    CMD3.Parameters("@RecID").Value = ReceiptID
                    CMD3.Parameters("@itID").Value = DGV2.Rows(I).Cells(0).Value
                    CMD3.Parameters("@item").Value = DGV2.Rows(I).Cells(1).Value
                    CMD3.Parameters("@prc").Value = DGV2.Rows(I).Cells(2).Value
                    CMD3.Parameters("@quan").Value = DGV2.Rows(I).Cells(3).Value
                    CMD3.ExecuteNonQuery()
                    Exit For
                Next
                'all's well save the changes
                MyTransaction.Commit()
    
                'close connection
                MyTransaction.Dispose()
                MyConnection.Close()
                MyConnection.Dispose()
    
            Catch ex As Exception
    
                MsgBox(ex.Message, MsgBoxStyle.Critical Or MsgBoxStyle.OkOnly, "Error!")
    
            End Try
    
            DGV2.Rows.Clear()
            txtboxTotal.Text = ""
        End Sub



    • Marked as answer by ETSwitek Saturday, July 12, 2014 1:42 PM
    • Edited by ETSwitek Saturday, July 12, 2014 1:43 PM
    Saturday, July 12, 2014 1:42 PM

All replies

  • Hello,

    A few remarks, all tables in a database should have an auto-incrementing key to identify a specific row and that the database handles incrementing values when inserting new rows, this will prevent the duplicate key. Do not retrieve data from a user interface container such as a DataGridView, instead retrieve data via the underlying DataSource i.e. BindingSource, DataSet, DataTable. In regards to command parameters I highly suggest moving away from AddWithValue and use Add especially concerning dates. Don't clear the DataGridView but instead handle this at the DataSource level i.e. MyBindingSource.DataSource = Nothing etc.

    When creating connection and command objects do that with a 'using' statement to ensure objects are properly disposed.

    Going back to auto-incrementing we can do something like this

    Demo for auto-incrementing

    Imports System.Data.SqlClient
    
    Module Module1
        Public AppRoleID As Integer
    
        Public Function demoing(
            ByVal AppID As String,
            ByVal RoleName As String,
            ByVal RoleDescription As String) As Boolean
    
            Dim Success As Boolean
    
            Using cn As New SqlConnection("TODO")
                cn.Open()
                Try
    
                    Dim sql As String =
                        <SQL>
                            INSERT INTO [AppRole] 
                            (
                                RoleName, 
                                RoleDescription, 
                                AppID
                            ) 
                            VALUES
                            ( 
                                @RoleName,
                                @RoleDescription, 
                                @AppID
                            ); 
                            SELECT CAST(scope_identity() AS int);                        
                        </SQL>.Value
    
                    Using cmd As New SqlCommand(sql, cn)
    
                        cmd.Parameters.AddWithValue("@AppID", AppID)
    
                        cmd.Parameters.AddWithValue("@RoleName", RoleName)
                        cmd.Parameters.AddWithValue("@RoleDescription", RoleDescription)
    
                        AppRoleID = CInt(cmd.ExecuteScalar())
    
                        Success = True
                    End Using
                Catch
                    Throw New Exception("Error in ApplicationRole.Create")
                Finally
                    cn.Close()
                End Try
            End Using
    
            Return Success
    
        End Function
    End Module
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Wednesday, July 2, 2014 4:52 PM
  • Thank you for your response. I have to use the values from the datagridview because sometimes they are user inputed. I can't bind the data source because then none of my data shows up correctly with all the variable parameters. The way our business works is that prices and such aren't set in stone. Sometimes they must be changed due to circumstances. Therefore, I need the values from the datagridview because they aren't always the same as the data in the tables. Is there a way to successfully grab the values from the DGV and insert them into another table?

    That's what i'm trying to do, essentially. Is take the values from the datagridview and insert them into another table named ReceiptDetails.

    Monday, July 7, 2014 5:24 PM
  • Now the only problem i'm having is with the values. The error message i'm getting is that the parameters are not being provided. Yet, when i breakpoint and go line by line each value is clearly stated. Something is going wrong when i try to execute the query. =/

    Private Sub btnCash_Click(sender As Object, e As EventArgs) Handles btnCash.Click
            'Create the connection and  transaction object

            Try

                Using MyConnection As New System.Data.SqlClient.SqlConnection(POS.My.Settings.POSConn)
                    MyConnection.Open()

     Dim MyTransaction As System.Data.SqlClient.SqlTransaction
                    MyTransaction = MyConnection.BeginTransaction

    Using CMD3 As New System.Data.SqlClient.SqlCommand
                        Dim SQ As String = "insert into ReceiptDetails " & _
                            "(ReceiptDate, ItemID, Items, Price, Quantity, ReceiptTotal)" & _
                            "values (@date, @itID, @item, @prc, @quan, @total)"
                            CMD3.Connection = MyConnection
                            CMD3.Transaction = MyTransaction
                            CMD3.CommandText = SQ
                        CMD3.Parameters.Add("@date", SqlDbType.Date)
                        CMD3.Parameters.Add("@itID", SqlDbType.Int)
                        CMD3.Parameters.Add("@item", SqlDbType.Char)
                        CMD3.Parameters.Add("@prc", SqlDbType.Decimal)
                        CMD3.Parameters.Add("@quan", SqlDbType.Int)
                        CMD3.Parameters.Add("@total", SqlDbType.Money)

                        Dim I As Integer
                        For I = 0 To DGV2.Rows.Count - 1
                            CMD3.Parameters("@date").Value = Now
                            CMD3.Parameters("@itID").Value = DGV2.Rows(I).Cells(0).Value
                            CMD3.Parameters("@item").Value = DGV2.Rows(I).Cells(1).Value
                            CMD3.Parameters("@prc").Value = DGV2.Rows(I).Cells(2).Value
                            CMD3.Parameters("@quan").Value = DGV2.Rows(I).Cells(3).Value
                            CMD3.Parameters("@total").Value = Val(txtboxTotal.Text)

                            CMD3.ExecuteNonQuery()

                    Next
                    End Using

                    'all's well save the changes
                    MyTransaction.Commit()

    'close connection
                    MyTransaction.Dispose()
                    MyConnection.Close()
                    MyConnection.Dispose()

                    'End Using

                    If MyTransaction IsNot Nothing Then
                        MyTransaction.Rollback()
                    End If
                End Using


            Catch ex As Exception

                MsgBox(ex.Message, MsgBoxStyle.Critical Or MsgBoxStyle.OkOnly, "Error!")

            End Try
    End Sub

    Monday, July 7, 2014 11:22 PM
  • Please provide the entire error message as I see nothing standing out in your code to be an issue.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Monday, July 7, 2014 11:38 PM
  • I know, right?!...That's what i've been saying to myself. I don't understand why it's not working. The full error message is:

    The parameterized query '(@date date, @itID int, @item char(8000), @prc decimal(29,0), @quan' expects the parameter '@itID', which was not supplied.

    Tuesday, July 8, 2014 1:28 PM
  • I know, right?!...That's what i've been saying to myself. I don't understand why it's not working. The full error message is:

    The parameterized query '(@date date, @itID int, @item char(8000), @prc decimal(29,0), @quan' expects the parameter '@itID', which was not supplied.

    One thought is date is a reserve word, I would try changing date in values to say @ReceiptDate


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Tuesday, July 8, 2014 1:49 PM
  • It gives me the same error message. =/
    Tuesday, July 8, 2014 2:34 PM
  • I'm using the SQL database through Azure. Would that have anything to do with it?
    Tuesday, July 8, 2014 2:42 PM
  • One last thing to try, create the INSERT statement without parameters i.e.

            Dim Name As String = "jsmith"
            Dim ActiveCode As String = "XYZ"
            Dim Credits As Integer = 10
            Dim InsertStatement As String =
            <SQL>
    INSERT INTO SomeTable (AppraiserName, ActiveSwitch, Credits) VALUES ('<%= Name %>','<%= ActiveCode %>',<%= Credits %>)
            </SQL>.Value.ToString

    Then output the InsertStatement to the IDE Output Windows via Console.WriteLine

    Console.WriteLine(InsertStatement)

    Output

    INSERT INTO SomeTable (AppraiserName, ActiveSwitch, Credits) VALUES ('jsmith','XYZ',10)
    Run as is above (of course replace the above with your table and fields). If there are still errors then remove one field and value, try again, failed, remove another. If left with one field and it still fails I would say we have ruled out a problem with the command object and must consider an issue pointing to Azure, perhaps a permission issue. I have not done anything with Azure so I have nothing else to suggest.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Tuesday, July 8, 2014 3:51 PM
  • I don't think i did the output correctly. Now it's giving me the error

    "This SqlTransaction has completed; it is no longer usable."

    When i go line by line it seems to be looping and on the second time through the values that appeared the first time show up as "Nothing" now. I wonder if that's where the problem is with it not supplying the parameters.

    Private Sub btnCheck_Click(sender As Object, e As EventArgs) Handles btnCheck.Click
    
    
            Dim MyTransaction As System.Data.SqlClient.SqlTransaction
            Dim MyConnection As New System.Data.SqlClient.SqlConnection(POS.My.Settings.POSConn)
            MyConnection.Open()
    
    
            Try
                MyTransaction = MyConnection.BeginTransaction
    
                Dim I As Integer
                For I = 0 To DGV2.Rows.Count - 1
                    Dim ReceiptDate As String = Now
                    Dim ItemID As Integer = DGV2.Rows(I).Cells(0).Value
                    Dim Items As Char = DGV2.Rows(I).Cells(1).Value
                    Dim Price As Decimal = DGV2.Rows(I).Cells(2).Value
                    Dim Quantity As Integer = DGV2.Rows(I).Cells(3).Value
                    Dim Total As Decimal = Val(txtboxTotal.Text)
                    Dim InsertStatement As String =
                        <SQL>
                            INSERT INTO ReceiptDetails (ReceiptID, ItemID, Items, Price, Quantity, Total) VALUES ('<%= ReceiptDate %>','<%= ItemID %>>','<%= Items %>','<%= Price %>>','<%= Quantity %>>','<%= Total %>>')
                        </SQL>.Value.ToString
    
                    Console.WriteLine(<SQL> INSERT INTO ReceiptDetails(ReceiptID, ItemID, Items, Price, Quantity, Total) VALUES ('Now','DGV2.Rows(I).Cells(0).Value','DGV2.Rows(I).Cells(1).Value','DGV2.Rows(I).Cells(2).Value','DGV2.Rows(I).Cells(3).Value','Val(txtboxTotal.Text)')
                            </SQL>)
    
                Next
                'all's well save the changes
                MyTransaction.Commit()
    
                'close connection
                MyTransaction.Dispose()
                MyConnection.Close()
                MyConnection.Dispose()
    
                'End Using
    
                If MyTransaction IsNot Nothing Then
                    MyTransaction.Rollback()
                End If
    
    
    
            Catch ex As Exception
    
                MsgBox(ex.Message, MsgBoxStyle.Critical Or MsgBoxStyle.OkOnly, "Error!")
    
            End Try
    
            DGV2.Rows.Clear()
            txtboxTotal.Text = ""
        End Sub

    Tuesday, July 8, 2014 4:55 PM
  • Hello,

    Which line is raising the exception?

    In regards to the Console.WriteLine, simply use Console.WriteLine(InsertStatement)


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Tuesday, July 8, 2014 7:17 PM
  • ok. I fixed the console.writeline and it's still showing me errors. I start the process and go line by line and all the values are showing up correctly when i hover the mouse over them. When it gets to the 'Next' it loops back up to 'Dim ReceiptDate'. The it goes back through all the lines, but now all the values that are in the datagridview i.e. (DGV2.Rows(I).Cells(0).Value) are gone and marked as "Nothing".

    When it gets to MyTransaction.Commit() it either gives me the error "The parameterized query '(@date date, @itID int, @item char(8000), @prc decimal(29,0), @quan' expects the parameter '@itID', which was not supplied."

    or

    "This SqlTransaction has completed; it is no longer usable."

    I'm very mystified as to why this isn't working and pulling my hair out, because if i can't get this 6 months of work is for nothing. =/

    I greatly appreciate you taking the time to help me with this.

    Wednesday, July 9, 2014 1:22 PM
  • Hello,

    I am moving your post to another forum to see if we can get others to take a look at this issue.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Wednesday, July 9, 2014 1:47 PM
  • does that mean it's a different link to get to the post? Or will it be on this link and you simply migrated the category?
    Thursday, July 10, 2014 1:43 PM
  • Ok. I fixed the problem of it not saving. Now the data saves into the ReceiptDetails table.

    It's throwing an error that says the connection is not open

    OR

    The same Paramter is missing error

    ...but it still saves the row of data..lol I'm not positive what i did to fix it, but it created another problem (my luck). This is what i have now.

    To fix the saving issue i simple moved 'Next' in front of my.transaction.commit

    Private Sub btnCash_Click(sender As Object, e As EventArgs) Handles btnCash.Click
            'Create the connection and  transaction object
            
                Try
    
                Dim MyConnection As New System.Data.SqlClient.SqlConnection(POS.My.Settings.POSConn)
                MyConnection.Open()
    
                Dim MyTransaction As System.Data.SqlClient.SqlTransaction
                MyTransaction = MyConnection.BeginTransaction
                ' insert the details of the Receipt
                Dim I As Integer
                For I = 0 To DGV2.Rows.Count - 1
    
                    'next create a commmand
                    Dim CMD3 As New System.Data.SqlClient.SqlCommand
                    Dim SQ As String = "insert into ReceiptDetails " & _
                        "(ReceiptDate, ItemID, Items, Price, Quantity, ReceiptTotal)" & _
                        "values (@date, @itID, @item, @prc, @quan, @total)"
                    CMD3.Connection = MyConnection
                    CMD3.Transaction = MyTransaction
                    CMD3.CommandText = SQ
                    CMD3.Parameters.Add("@date", SqlDbType.DateTime)
                    CMD3.Parameters.Add("@itID", SqlDbType.Int)
                    CMD3.Parameters.Add("@item", SqlDbType.Char)
                    CMD3.Parameters.Add("@prc", SqlDbType.Decimal)
                    CMD3.Parameters.Add("@quan", SqlDbType.Int)
                    CMD3.Parameters.Add("@total", SqlDbType.Money)
    
    
                    CMD3.Parameters("@date").Value = Now
                    CMD3.Parameters("@itID").Value = DGV2.Rows(I).Cells(0).Value
                    CMD3.Parameters("@item").Value = DGV2.Rows(I).Cells(1).Value
                    CMD3.Parameters("@prc").Value = DGV2.Rows(I).Cells(2).Value
                    CMD3.Parameters("@quan").Value = DGV2.Rows(I).Cells(3).Value
                    CMD3.Parameters("@total").Value = txtboxTotal.Text
    
                    CMD3.ExecuteNonQuery()
    
                    'all's well save the changes
                    MyTransaction.Commit()
                    'End Using
                    'close connection
                Next
                MyTransaction.Dispose()
                MyConnection.Close()
                MyConnection.Dispose()
    
    
                If MyTransaction IsNot Nothing Then
                    MyTransaction.Rollback()
                End If
                'End Using
    
    
            Catch ex As Exception
    
                MsgBox(ex.Message, MsgBoxStyle.Critical Or MsgBoxStyle.OkOnly, "Error!")
    
            End Try
    
            DGV2.Rows.Clear()
            txtboxTotal.Text = ""
        End Sub

    • Marked as answer by ETSwitek Saturday, July 12, 2014 1:37 PM
    • Unmarked as answer by ETSwitek Saturday, July 12, 2014 1:37 PM
    Thursday, July 10, 2014 6:33 PM
  • This is the finalized code that works great with no errors! It was a matter of entering an 'Exit For' to stop the loop from deleting the values before committing the transaction.

    Private Sub btnCash_Click(sender As Object, e As EventArgs) Handles btnCash.Click
            'Create the connection and  transaction object
            
            Try
    
                Dim MyConnection As New System.Data.SqlClient.SqlConnection(POS.My.Settings.POSConn)
                MyConnection.Open()
    
                Dim MyTransaction As System.Data.SqlClient.SqlTransaction
                MyTransaction = MyConnection.BeginTransaction
                ' insert the details of the Receipt
                Dim CMD1 As New System.Data.SqlClient.SqlCommand
                Dim SQL As String = "insert into WareReceipts (ReceiptDate, ReceiptTotal, Category)" & _
                    "Values (@date, @total, @cat)"
                CMD1.Connection = MyConnection
                CMD1.Transaction = MyTransaction
                CMD1.CommandText = SQL
                CMD1.Parameters.Add("@date", SqlDbType.DateTime)
                CMD1.Parameters.Add("@total", SqlDbType.Money)
                CMD1.Parameters.Add("@cat", SqlDbType.Char)
    
                CMD1.Parameters("@date").Value = Now
                CMD1.Parameters("@total").Value = txtboxTotal.Text
                CMD1.Parameters("@cat").Value = "Cash"
                CMD1.ExecuteNonQuery()
                CMD1.Dispose()
    
                Dim CMD2 As New System.Data.SqlClient.SqlCommand
                Dim SL As String = "select max(ReceiptID) as MAXID from WareReceipts"
                CMD2.Connection = MyConnection
                CMD2.Transaction = MyTransaction
                CMD2.CommandText = SL
                Dim ReceiptID As Long = CMD2.ExecuteScalar
                CMD2.Dispose()
    
    
                Dim I As Integer
                For I = 0 To DGV2.Rows.Count - 1
                    Dim CMD3 As New System.Data.SqlClient.SqlCommand
                    Dim SQ As String = "insert into ReceiptDetails " & _
                        "(ReceiptID, ItemID, Items, Price, Quantity)" & _
                        "values (@RecID, @itID, @item, @prc, @quan)"
                    CMD3.Connection = MyConnection
                    CMD3.Transaction = MyTransaction
                    CMD3.CommandText = SQ
                    CMD3.Parameters.Add("@RecID", SqlDbType.Int)
                    CMD3.Parameters.Add("@itID", SqlDbType.Int)
                    CMD3.Parameters.Add("@item", SqlDbType.Char)
                    CMD3.Parameters.Add("@prc", SqlDbType.Decimal)
                    CMD3.Parameters.Add("@quan", SqlDbType.Int)
    
                    CMD3.Parameters("@RecID").Value = ReceiptID
                    CMD3.Parameters("@itID").Value = DGV2.Rows(I).Cells(0).Value
                    CMD3.Parameters("@item").Value = DGV2.Rows(I).Cells(1).Value
                    CMD3.Parameters("@prc").Value = DGV2.Rows(I).Cells(2).Value
                    CMD3.Parameters("@quan").Value = DGV2.Rows(I).Cells(3).Value
                    CMD3.ExecuteNonQuery()
                    Exit For
                Next
                'all's well save the changes
                MyTransaction.Commit()
    
                'close connection
                MyTransaction.Dispose()
                MyConnection.Close()
                MyConnection.Dispose()
    
            Catch ex As Exception
    
                MsgBox(ex.Message, MsgBoxStyle.Critical Or MsgBoxStyle.OkOnly, "Error!")
    
            End Try
    
            DGV2.Rows.Clear()
            txtboxTotal.Text = ""
        End Sub



    • Marked as answer by ETSwitek Saturday, July 12, 2014 1:42 PM
    • Edited by ETSwitek Saturday, July 12, 2014 1:43 PM
    Saturday, July 12, 2014 1:42 PM