ThisIsABird ThisIsABird - 7 months ago 16
SQL Question

mysql, vb.net - Saving Transaction not working

I don't know what's the real problem since there are no error being reported. So what I want these codes to do is insert a transaction record to the database but there is nothing being returned. Here are the codes that related to this:


MainForm


Private Sub PayButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PayButton.Click
Dim payment As New Payment
payment.Show()
AddHandler payment.PaymentEvent, AddressOf paymentSuccess
payment.PaymentAmount = TransactionTotal
End Sub






Public Sub paymentSuccess(ByVal sender As Object, ByVal e As Payment.PaymentMadeEventArgs)
mydbcon = New MySqlConnection
mydbcon.ConnectionString = "server=localhost;userid=root;password=;database=sdudb"
Dim reader As MySqlDataReader
Try
mydbcon.Open()
Dim Query As String
Query = "select * from inventory"
COMMAND = New MySqlCommand(Query, mydbcon)
reader = COMMAND.ExecuteReader()
While reader.Read
Dim itId As Integer = reader.GetString("itemid")
Dim itName As String = reader.GetString("itemname")
If e.PaymentSuccess = True Then
paymentSuccessQuery(itId, itName)
End If
End While
reader.Close()
mydbcon.Close()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub



Private Sub paymentSuccessQuery(ByVal itemid, ByVal itemname)
mydbcon = New MySqlConnection
mydbcon.ConnectionString = "server=localhost;userid=root;password=;database=sdudb"
Dim reader As MySqlDataReader
Try
mydbcon.Open()
Dim Query As String
Query = "INSERT INTO transaction (itemid, itemname) VALUES('" & itemid & "', '" & itemname & "')"
COMMAND = New MySqlCommand(Query, mydbcon)
reader = COMMAND.ExecuteReader()
If reader.Read Then
MessageBox.Show("Unable to save transaction!")
Else
MessageBox.Show("Transaction Saved!")
End If
reader.Close()
mydbcon.Close()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub


Transactionform


Public Class Payment
Public Delegate Sub PaymentMadeEvent(ByVal sender As Object, ByVal e As PaymentMadeEventArgs)
Public Event PaymentEvent As PaymentMadeEvent

Private _paymentAmount As Decimal
Public Property PaymentAmount As Decimal
Get
Return _paymentAmount
End Get
Set(ByVal value As Decimal)
_paymentAmount = value
AmountBox.Text = String.Format("{0:c}", _paymentAmount)
End Set
End Property

Private Sub PayButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PayButton.Click
Dim total As Decimal = 0

Try
total = Decimal.Parse(AmountBox.Text.TrimStart("₱")) - Decimal.Parse(PaymentBox.Text)
Catch
MessageBox.Show("Error Occured, please enter a valid amount!")
Return
End Try

If (total > 0) Then
AmountBox.Text = total.ToString()
Else
MessageBox.Show("Please give " + String.Format("{0:c}", -total))
RaiseEvent PaymentEvent(Me, New PaymentMadeEventArgs() With {.PaymentSuccess = True})
End If

End Sub

Public Class PaymentMadeEventArgs
Inherits EventArgs
Private _paymentSuccess As Boolean
Public Property PaymentSuccess As Boolean
Get
Return _paymentSuccess
End Get
Set(ByVal value As Boolean)
_paymentSuccess = value
End Set
End Property
End Class
End Class

Answer

ExecuteReader executes the command (the insert) but it has been built to return the rows extracted by a SELECT command.
Calling Read to discover if your INSERT has been successful is meaningless in this context.

You should call ExecuteNonQuery, catch the return value, and if it is not equal to zero, then you have inserted the record.

Private Sub paymentSuccessQuery(ByVal itemid, ByVal itemname)
    Using mydbcon = New MySqlConnection("server=localhost;userid=root;password=;database=sdudb"
    Try
        mydbcon.Open()
        Dim Query As String
        Query = "INSERT INTO transaction (itemid, itemname) " & _ 
                "VALUES(@id, @name)"
        Using COMMAND = New MySqlCommand(Query, mydbcon)
           COMMAND.Parameters.Add("@id", MySqlDbType.VarChar).Value =  itemid 
           COMMAND.Parameters.Add("@name", MySqlDbType.VarChar).Value =  itemname
           Dim rowsAdded = COMMAND.ExecuteNonQuery()
           if rowsAdded = 0 Then
               MessageBox.Show("Unable to save transaction!")
           Else
              MessageBox.Show("Transaction Saved!")
           End If
        End Using
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
    End Using
End Sub

Notice also the I have changed your code to use the appropriate Using statement around the disposable objects like the connection and the command and, of utmost importante, I have changed your query to use a more safe parameterized query approach (not sure about the MySqlDbType for the ID parameter, it seems to be an integer but in your original query you put it between single quotes like a string)

Comments