Payment method. Help me, am I doing it wrong?-CodePudding

This is a post I made earlier, but I have made a few changes. Currently, what I want to do is to include a payment method(i.e credit card payment). I've created a database that holds dummy card details, I have linked this database to the program and created a function called input to perform the payment transaction with the card. I called it in the dbread function but it doesn't seem to work. Kindly help me figure out what it is I am doing wrong.

 Imports MySql.Data.MySqlClient

Public Class Form1
    'Create MySQL objects.
    Private reader As MySqlDataReader
    Private command As New MySqlCommand
    Private myConnectionString As String
    Private sql As String
    Private dbinput, dbinput2, balance As Double
    Dim fees As Double

    Private Sub dbread()
        'Create a connection object.
        myConnectionString = "server=localhost;" _
              & "uid=root;" _
              & "pwd=password;" _
              & "database=dummystudents"

        Try

            'open the connection
            Dim conn As New MySql.Data.MySqlClient.MySqlConnection(myConnectionString)
            conn.Open()

            'create a command object.
            sql = "SELECT fees,idstudents FROM students WHERE idstudents= " & txtStudentID.Text
            command = New MySqlCommand(sql, conn)
            reader = command.ExecuteReader()

            'fetching data from database
            reader.Read()
            dbinput = dbinput & reader.GetString(0)
            dbinput2 = dbinput2 & reader.GetString(1)

            Call input()
            'outputting result
            txtAmountpaid.Text = fees.ToString("C2")
            balance = CDbl(dbinput - fees)
            txtBalance.Text = balance.ToString("C2")

            'Close connection
            conn.Close()

        Catch ex As MySql.Data.MySqlClient.MySqlException
            MessageBox.Show("Your student ID doesn't exist.")
        End Try
    End Sub
    Private Sub input()
        myConnectionString = "server=localhost" _
            & "uid=root" _
            & "pwd=password" _
            & "database=dummycard"

        Try
            Dim conn As New MySql.Data.MySqlClient.MySqlConnection(myConnectionString)
            conn.Open()

            sql = "SELECT cardID,amount,pin FROM card WHERE cardID=" & txtCardID.Text
            command = New MySqlCommand(sql, conn)
            command.ExecuteReader()

            reader.Read()
            Dim appcard As Integer = appcard & reader.GetString(0)
            Dim appamount As Decimal = appamount & reader.GetString(1)
            Dim apppin As Integer = apppin & reader.GetString(2)

            Dim cardID As Integer = InputBox("Enter Card number")
            If appcard = cardID Then
                Dim pin As Integer = InputBox("Enter pin")
                If apppin = pin Then
                    'calculating fees
                    If dbinput2 = txtStudentID.Text Then
                        If rbntwentyfive.Checked = True Then
                            fees = dbinput * 0.25
                        ElseIf rbnfifty.Checked = True Then
                            fees = dbinput * 0.5
                        ElseIf rbnsixty.Checked = True Then
                            fees = dbinput * 0.6
                        Else
                            fees = dbinput * 1
                        End If

                    End If
                    MessageBox.Show("Transaction Successful")
                End If
            End If
        Catch ex As Exception
            MessageBox.Show("Connection faulty")
        End Try
    End Sub


    Private Sub dbupdate()
        'Create a connection object.
        myConnectionString = "server=localhost;" _
              & "uid=root;" _
              & "pwd=password;" _
              & "database=dummystudents"

        Try
            'open the connection
            Dim conn As New MySql.Data.MySqlClient.MySqlConnection(myConnectionString)
            conn.Open()

            'decimal variable
            Dim dbvalue As Decimal = Decimal.Parse(balance).ToString("#,###.00")

            'create a command object.
            sql = "UPDATE students SET fees = " & dbvalue & "WHERE idstudents = " & txtStudentID.Text
            command = New MySqlCommand(sql, conn)
            command.ExecuteNonQuery()
            'Close connection
            conn.Close()

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


    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        dbread()
        dbupdate()
    End Sub
End Class

CodePudding user response：

Are you using a debugger? I mean really using a debugger? Put a breakpoint after you build the sql and inspect the actual sql. Does it look correct?

UPDATE students SET fees = 'txtBalanceText'WHERE idstudents = 'txtStudentIDText

You may notice it's missing a closing single quote after student id. Also, I wonder what types fees and idstudents are in your database. If they are numeric, which they should be, then you don't use single quotes at all. If they are text then you do need the quotes. Fix the quotes. You can add another single quote like this

sql = "UPDATE students SET fees = '" & txtBalance.Text & "' WHERE idstudents = '" & txtStudentID.Text & "'"

The above code will just fix the missing quote.

However,

Here is how you can improve your code. For one, use Using blocks to create and dispose the connection and command. These objects may have unmanaged memory behind the scenes and you should always either call obj.Dispose() or let the Using block handle it automatically. Also, this sample adds your parameters which effectively avoids the aforementioned sql injection risk

Dim myConnectionString = "server=localhost;" &
    "uid=root;" &
    "pwd=password;" &
    "database=dummystudents"
Using conn As New MySqlConnection(myConnectionString)
    conn.Open()
    Dim sql = "UPDATE students SET fees = @fees WHERE idstudents = @studentID"
    Using com As New MySqlCommand(sql, conn)
        com.Parameters.AddWithValue("@fees", txtBalance.Text)
        com.Parameters.AddWithValue("@studentID", txtStudentID.Text)
        com.ExecuteNonQuery()
    End Using
End Using

Depending on the field types, adding the parameters will either include or exclude the single quotes so you don't need to worry about that anymore - an added bonus.