MySql Database INSERT, DELETE, SELECT, REPLACE

.

Public connectionString As String = "Server=192.168.1.10; User Id=UserAccountName; Password=SuperSolidPassword; Database=YourDatabase"

INSERT Example (paramaterized)

Function funAddData(dDateTime As DateTimePicker, sAmount As String, sNotes As String, sCategory As String, bDeleted As Boolean, sBank As String, bCleared As Boolean, sTransID As String) As Integer

        Dim SQLConnection As New MySqlConnection(connectionString)

        Dim sqlCommand As New MySqlCommand

        sqlCommand.Connection = SQLConnection

        If sBank.Length = 0 Then

            MsgBox("Bank is Blank")

            Return -1

        End If

        Debug.Print(SQLConnection.State.ToString)

        SQLConnection.Open()

        ' Try

        Debug.Print("amount=" & sAmount.ToString)

        Debug.Print(dDateTime.Value)

        sqlCommand.CommandText = "INSERT INTO entries(amount,bDeleted,category,dDateTime,notes,iBank,bCleared,TransID)VALUES(?amount,?bDeleted,?category,?dDateTime,?notes,?bank,?cleared,?TransID); SELECT LAST_INSERT_ID();"

        sqlCommand.CommandType = CommandType.Text

        'amount,bDeleted,category,dDateTime,notes

        sqlCommand.Parameters.AddWithValue("?amount", sAmount.ToString)

        sqlCommand.Parameters.AddWithValue("?bDeleted", bDeleted)

        sqlCommand.Parameters.AddWithValue("?category", sCategory.ToString)

        sqlCommand.Parameters.AddWithValue("?dDateTime", dDateTime.Value()) '("yyyy-MM-dd 00:00:00")) '

        sqlCommand.Parameters.AddWithValue("?notes", sNotes.ToString)

        sqlCommand.Parameters.AddWithValue("?bank", sBank.ToString)

        sqlCommand.Parameters.AddWithValue("?cleared", IIf(bCleared, 1, 0))

        sqlCommand.Parameters.AddWithValue("?TransID", sTransID.ToString)

        'sqlCommand.ExecuteNonQuery()

        Dim cmd_result As Integer = CInt(sqlCommand.ExecuteScalar())

        SQLConnection.Close()

        Return cmd_result

        'Dim insert_coupon_query As String = ("INSERT INTO qa_discountcoupons (status_code) VALUES (5); SELECT LAST_INSERT_ID()")

        'Dim cmd_query As New MySqlCommand(insert_coupon_query, objConn)

        'Dim cmd_result As Integer = CInt(cmd_query.ExecuteScalar())

        'MsgBox(cmd_result)

        'Catch ex As Exception

        '  MsgBox("Error occured: Could not insert record " & ex.Message.ToString)

        '   Return False

        '   End Try

    End Function

SELECT example

Function funGetData(bIncludeScheduled As Boolean) As Boolean

                sqlQuery = "SELECT * FROM entries WHERE iBank='" & Form1.cmbBank1.SelectedItem.Text & "' and dDateTime > DATE_ADD(Now(), INTERVAL " & -1 * IIf(Form1.MaskedTextBox1.Text.ToString.Length = 0, 0, Form1.MaskedTextBox1.Text.ToString) & " DAY) OR ( iBank='" & Form1.cmbBank1.SelectedItem.Text & "' AND  LEFT(category,2)='s:' AND category <> 's:Credit Limit') ORDER BY " & Form1.ComboBox1.Text.ToString & ""

            Debug.Print(sqlQuery)

            Dim SQLConnection As New MySqlConnection(connectionString)

            Dim sqlCommand As New MySqlCommand

            sqlCommand.Connection = SQLConnection

            SQLConnection.Open()

            Debug.Print(SQLConnection.State.ToString)

            'Try

            sqlCommand.CommandText = sqlQuery

            sqlCommand.CommandType = CommandType.Text

            Dim sqlReader As MySqlDataReader = sqlCommand.ExecuteReader()

            While sqlReader.Read()

                Debug.Print(funMoolaIt(sqlReader("id").ToString, sqlReader("dDateTime").ToString, sqlReader("amount").ToString, sqlReader("notes").ToString, sqlReader("category").ToString, False, -1, sqlReader("bCleared")))

            End While

            sqlReader.Close()

            SQLConnection.Close()

            Debug.Print(SQLConnection.State.ToString)

        End If

    End Function

DELETE and REPLACE INTO  example

Function UpdateInsertBankDebt(deleteMe As Boolean, sBank As String) As Integer 'return number of rows

        Dim sqlQuery As String ' = " iBank='" & Form1.cmbBank.Text.ToString & "' " 'ORDER BY " & Form1.ComboBox1.Text & " DESC"

        If deleteMe Then

            sqlQuery = "DELETE FROM DebtAccounts WHERE DebtAccountName='" & sBank & "'"

        Else

            sqlQuery = "REPLACE into DebtAccounts SET DebtAccountName='" & sBank & "'"

        End If

        Dim SQLConnection As New MySqlConnection(connectionString)

        Dim sqlCommand As New MySqlCommand

        sqlCommand.Connection = SQLConnection

           SQLConnection.Open()

        Debug.Print(SQLConnection.State.ToString)

        'Try

        sqlCommand.CommandText = sqlQuery

        sqlCommand.CommandType = CommandType.Text

        Dim sqlReader As MySqlDataReader = sqlCommand.ExecuteScalar()

        SQLConnection.Close()

        Debug.Print(SQLConnection.State.ToString)

    End Function

TestSQL (MySql)