.
Public connectionString As String = "Server=192.168.1.10; User Id=UserAccountName; Password=SuperSolidPassword; Database=YourDatabase"
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
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
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
Function TestSQL(connectionString As String) As Boolean
Dim SQLConnection As New MySqlConnection(connectionString)
Dim sqlCommand As New MySqlCommand
sqlCommand.Connection = SQLConnection
Debug.Print(SQLConnection.State.ToString)
Try
SQLConnection.Open()
Catch ex As Exception
Return False
Finally
SQLConnection.Close()
End Try
Return True
End Function