Click here to Skip to main content
15,389,833 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
can some one please help me convert my old project from using oledb connection MS access database to use MySQL database?

the connection files are Module 1 here

VB.NET
Imports System.Data.OleDb
Imports System.Globalization

Module Module1
    Private cn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source = C:\Users\Public\lamavie\database.accdb")

    Public Sub Connect()
        If cn.State = ConnectionState.Closed Then cn.Open()
    End Sub

    Public Function InsertUpdateDelete(ByVal sql As String) As Boolean
        Connect()
        Dim cmd As New OleDbCommand(sql, cn)
        Return cmd.ExecuteNonQuery() > 0
    End Function

    Public Function IsConfirm(ByVal message As String) As Boolean
        Return MessageBox.Show(message, "Confirm ?", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = DialogResult.Yes
    End Function

    Public Function QueryAsDataTable(ByVal sql As String) As DataTable
        Dim da As New OleDbDataAdapter(sql, cn)
        Dim ds As New DataSet
        da.Fill(ds, "result")
        Return ds.Tables("result")
    End Function

    Public Function ConvertDateToString(ByVal dateVal As Date) As String
        Return dateVal.ToString("MM/dd/yyyy", CultureInfo.GetCultureInfo("ar-AE"))
    End Function

End Module


and main form here

VB.NET
Imports System.Globalization

Public Class frmDisplayAppointment
    Private listFlDay As New List(Of FlowLayoutPanel)
    Private currentDate As DateTime = DateTime.Today

    Private Sub frmDisplayAppointment_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        GenerateDayPanel(42)
        DisplayCurrentDate()
    End Sub

    Private Sub AddNewAppointment(ByVal sender As Object, e As EventArgs)
        Dim day As Integer = CType(sender, FlowLayoutPanel).Tag
        If day <> 0 Then
            With frmManageAppointment
                .AppID = 0
                .txtName.Text = ""
                .txtAddress.Text = ""
                .txtComment.Text = ""
                .dtpDate.Value = New Date(currentDate.Year, currentDate.Month, day)
                .phnumber.Text = ""
                .carmodel.Text = ""
                .carnumber.Text = ""
                .ShowDialog()
            End With
            DisplayCurrentDate()
        End If
    End Sub

    Private Sub ShowAppointmentDetail(sender As Object, e As EventArgs)
        Dim appID As Integer = CType(sender, LinkLabel).Tag
        Dim sql As String = $"select * from appointment where ID = {appID}"
        Dim dt As DataTable = QueryAsDataTable(sql)
        If dt.Rows.Count > 0 Then
            Dim row As DataRow = dt.Rows(0)
            With frmManageAppointment
                .AppID = appID
                .txtName.Text = row("ContactName")
                .txtAddress.Text = row("Address")
                .txtComment.Text = row("service")
                .dtpDate.Value = row("AppDate")
                .phnumber.Text = row("phnumber")
                .carmodel.Text = row("carModel")
                .carnumber.Text = row("carNumber")
                .ShowDialog()
            End With
            DisplayCurrentDate()
        End If
    End Sub

    Private Sub AddAppointmentToFlDay(ByVal startDayAtFlNumber As Integer)
        Dim startDate As DateTime = New Date(currentDate.Year, currentDate.Month, 1)
        Dim endDate As DateTime = startDate.AddMonths(1).AddDays(-1)

        Dim sql As String = $"select * from appointment where AppDate between #{ConvertDateToString(startDate)}# and #{ConvertDateToString(endDate)}#"
        Dim dt As DataTable = QueryAsDataTable(sql)

        For Each row As DataRow In dt.Rows
            Dim appDay As DateTime = DateTime.Parse(row("AppDate"))
            Dim link As New LinkLabel
            link.Tag = row("ID")
            link.Name = $"link{row("ID")}"
            link.Text = row("ContactName")
            AddHandler link.Click, AddressOf ShowAppointmentDetail
            listFlDay((appDay.Day - 1) + (startDayAtFlNumber - 1)).Controls.Add(link)
        Next
    End Sub

    Private Function GetFirstDayOfWeekOfCurrentDate() As Integer
        Dim firstDayOfMonth As DateTime = New Date(currentDate.Year, currentDate.Month, 1)
        Return firstDayOfMonth.DayOfWeek + 1
    End Function

    Private Function GetTotalDaysOfCurrentDate() As Integer
        Dim firstDayOfCurrentDate As DateTime = New Date(currentDate.Year, currentDate.Month, 1)
        Return firstDayOfCurrentDate.AddMonths(1).AddDays(-1).Day
    End Function

    Private Sub DisplayCurrentDate()
        lblMonthAndYear.Text = currentDate.ToString("MMMM, yyyy", CultureInfo.GetCultureInfo("ar-AE"))
        Dim firstDayAtFlNumber As Integer = GetFirstDayOfWeekOfCurrentDate()
        Dim totalDay As Integer = GetTotalDaysOfCurrentDate()
        AddLabelDayToFlDay(firstDayAtFlNumber, totalDay)
        AddAppointmentToFlDay(firstDayAtFlNumber)
    End Sub

    Private Sub PrevMonth()
        currentDate = currentDate.AddMonths(-1)
        DisplayCurrentDate()
    End Sub

    Private Sub NextMonth()
        currentDate = currentDate.AddMonths(1)
        DisplayCurrentDate()
    End Sub

    Private Sub Today()
        currentDate = DateTime.Today
        DisplayCurrentDate()
    End Sub

    Private Sub GenerateDayPanel(ByVal totalDays As Integer)
        flDays.Controls.Clear()
        listFlDay.Clear()
        For i As Integer = 1 To totalDays
            Dim fl As New FlowLayoutPanel
            fl.Name = $"flDay{i}"
            fl.Size = New Size(128, 99)
            fl.BackColor = Color.White
            fl.BorderStyle = BorderStyle.FixedSingle
            fl.Cursor = Cursors.Hand
            fl.AutoScroll = True
            AddHandler fl.Click, AddressOf AddNewAppointment
            flDays.Controls.Add(fl)
            listFlDay.Add(fl)
        Next
    End Sub

    Private Sub AddLabelDayToFlDay(ByVal startDayAtFlNumber As Integer, ByVal totalDaysInMonth As Integer)
        For Each fl As FlowLayoutPanel In listFlDay
            fl.Controls.Clear()
            fl.Tag = 0
            fl.BackColor = Color.White
        Next

        For i As Integer = 1 To totalDaysInMonth
            Dim lbl As New Label
            lbl.Name = $"lblDay{i}"
            lbl.AutoSize = False
            lbl.TextAlign = ContentAlignment.MiddleRight
            lbl.Size = New Size(110, 22)
            lbl.Text = i
            lbl.Font = New Font("Microsoft Sans Serif", 12)
            listFlDay((i - 1) + (startDayAtFlNumber - 1)).Tag = i
            listFlDay((i - 1) + (startDayAtFlNumber - 1)).Controls.Add(lbl)

            If New Date(currentDate.Year, currentDate.Month, i) = Date.Today Then
                listFlDay((i - 1) + (startDayAtFlNumber - 1)).BackColor = Color.Aqua
            End If

        Next
    End Sub


What I have tried:

I tried using MySQL connector but I get a lot of errors at module 1 dataset and datatable parts
and I can't get past it to test the edits in the main form
Posted
Updated 19-Jun-22 18:31pm

1 solution

Basically, you've been lucky up to now - that's some very dangerous code you have there. Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'
The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'
Which SQL sees as three separate commands:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'x';
A perfectly valid SELECT
SQL
DROP TABLE MyTable;
A perfectly valid "delete the table" command
SQL
--'
And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

Fixing that will pretty much involve you changing the whole structure of that code, as you can't pass parameter data to your QueryAsDataTable method without adding a whole new mechanism to pass parameter data, and that will apply to most of the other methods in your module as well.

And that means large changes in your Form code throughout your app.

Plus, you are holding a connection open for the duration of your app, which is ok - just - when you are using a single user DB, but a very poor idea in a multiuser one like MySql or Sql Server.; you don't dispose Connection objects when you are fis=nished with them; and so forth.

As a result, I'd have to say that it's not worth the effort to do it: scrap the existing code and start again, doing the job properly instead of mashing your Data Access code into your Presentation code as you have here.
That's up to you: but I suspect you will save yourself a lot of pain in both the short and long terms if you do bite the bullet!
   
Comments
Ahmed Sadek 2022 20-Jun-22 0:42am
   
I am delighted with your answer, and I appreciate your effort.
My skills are still mid-level, and I don't know how to change some of the functions to update it using MySQL like the QueryAsDataTable.
I have updated other parts in the project for inserting, editing, and deleting data from the database to use MySQL, but I can't update the codes I provided.

If you can help me or guide me, I would be very grateful.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900