Click here to Skip to main content
15,068,678 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Gooday
I have a school management system i developed in VB.Net for our school and it currently uses the SQLCE driver. I want to migrate to MYSQL ADMIN to save student files directly on the cloud. Does anyone know a working code that i can use for this purpose. Thanks in advance

What I have tried:

Imports MySql.Data.MySqlClient
Imports System.IO
Imports System.Drawing.Icon
Imports System.Data.SqlServerCe
Public Class Form1
    Private objConn As SqlCeConnection = New SqlCeConnection()
    Private strSqlConn As String = "Data Source=" & CStr(My.Computer.FileSystem.CurrentDirectory & "\Vicap.sdf") & ";Persist Security Info=False;Password=Mak0pa1011"
    Private strQuery_AllAttachments As String = "select id, fileName, fileSize, extension from tblAttachments order by id"
    Private strQuery_GetAttachmentById As String = "select * from tblAttachments where id = @attachId"
    Private strQuery_AllAttachments_AllFields As String = "select * from tblAttachments"

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Try
            objConn.ConnectionString = strSqlConn
            FillDataGrid(Me.DataGridView1, strQuery_AllAttachments)
            Call rfrsh()
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical, "Vicap Code Bug Detected")
        End Try

    End Sub
    Private Sub ConnectToDb()
        Try
            objConn.ConnectionString = strSqlConn
            objConn.Open()
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical, "Vicap Code Bug Detected")
        End Try
    End Sub
    Private Sub FillDataGrid(ByVal objGrid As DataGridView, ByVal strQuery As String)
        Try
            Dim tbl1 As DataTable = New DataTable()
            Dim adapter1 As SqlCeDataAdapter = New SqlCeDataAdapter()
            Dim cmd1 As SqlCeCommand = New SqlCeCommand()
            cmd1.Connection = objConn
            cmd1.CommandText = strQuery
            adapter1.MissingSchemaAction = MissingSchemaAction.AddWithKey
            adapter1.SelectCommand = cmd1
            adapter1.Fill(tbl1)
            objGrid.DataSource = tbl1
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical, "Vicap Code Bug Detected")
        End Try

    End Sub
    Private Sub CreateAttachment(ByVal strFile As String)
        Try
            If OpenFileDialog1.FileName <> Nothing Then
                Dim objAdapter As SqlCeDataAdapter = New SqlCeDataAdapter(strQuery_AllAttachments_AllFields, objConn)
                objAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
                Dim objCmdBuilder As SqlCeCommandBuilder = New SqlCeCommandBuilder(objAdapter)
                Dim objTable As DataTable = New DataTable()
                Dim objFileStream As FileStream = New FileStream(strFile, FileMode.Open, FileAccess.Read)
                Dim intLength As Integer = Convert.ToInt32(objFileStream.Length)
                Dim objData As Byte()
                objData = New Byte(intLength - 1) {}
                Dim objRow As DataRow
                Dim strPath As String() = strFile.Split(Convert.ToChar("\"))
                objAdapter.Fill(objTable)
                objFileStream.Read(objData, 0, intLength)
                objFileStream.Close()
                objRow = objTable.NewRow()
                Dim a As Integer = Me.DataGridView1.Rows.Count
                Dim ext As New IO.FileInfo(OpenFileDialog1.SafeFileName)
                Dim ext2 As String = ext.Extension
                objRow("id") = a + 1
                objRow("fileName") = strPath(strPath.Length - 1)
                objRow("fileSize") = "" & CStr(Math.Round((intLength / 1000000), 4)) & ""
                objRow("attachment") = objData
                objRow("extension") = ext2
                objTable.Rows.Add(objRow)
                objAdapter.Update(objTable)
            End If
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical, "Vicap Code Bug Detected")
        End Try
    End Sub

    Private Sub SaveAttachment(ByVal objSfd As SaveFileDialog, ByVal objGrid As DataGridView)
        Try
            Dim a As Integer = DataGridView1.CurrentRow.Index
            Dim strId As String = objGrid.Rows(a).Cells(0).Value.ToString()
            If Not String.IsNullOrEmpty(strId) Then
                Dim sqlCmd As SqlCeCommand = New SqlCeCommand(strQuery_GetAttachmentById, objConn)
                sqlCmd.Parameters.AddWithValue("@attachId", strId)
                Dim objAdapter As SqlCeDataAdapter = New SqlCeDataAdapter(sqlCmd)
                Dim objTable As DataTable = New DataTable()
                Dim objRow As DataRow
                objAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
                Dim sqlCmdBuilder As SqlCeCommandBuilder = New SqlCeCommandBuilder(objAdapter)
                objAdapter.Fill(objTable)
                objRow = objTable.Rows(0)
                Dim objData As Byte()
                objData = CType(objRow("attachment"), Byte())
                Dim strFileToSave As String = objSfd.FileName
                Dim objFileStream As FileStream = New FileStream(strFileToSave, FileMode.Create, FileAccess.Write)
                objFileStream.Write(objData, 0, objData.Length)
                objFileStream.Close()
            End If
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical, "Vicap Code Bug Detected")
        End Try
    End Sub

    Dim stat As String

    Public Sub updt()
        Try
            objConn.Close()
            Dim ra As Integer
            Dim cmd1 As SqlCeCommand = New SqlCeCommand()
            objConn.Open()
            Dim a As Integer = Me.DataGridView1.Rows.Count - 1
            For i = 0 To a
                Call rfrsh()
                If Me.DataGridView1.Rows(i).Cells(1).Value = OpenFileDialog1.SafeFileName Then
                    stat = "exists"
                End If
            Next
            If stat = "exists" Then
                MsgBox("File cannot be added because it is already stored in the Cloud", MsgBoxStyle.Critical, "VICAP Cloud System")
            Else
                CreateAttachment(OpenFileDialog1.FileName)
                FillDataGrid(Me.DataGridView1, strQuery_AllAttachments)
            End If
            FillDataGrid(Me.DataGridView1, strQuery_AllAttachments)
            objConn.Close()
            stat = Nothing
            Call rfrsh()
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical, "Vicap Code Bug Detected")
        End Try
    End Sub
    Public Sub rfrsh()
        Try
            objConn.Close()
            Dim ra As Integer
            Dim cmd1 As SqlCeCommand = New SqlCeCommand()
            objConn.Open()
            Dim a As Integer = Me.DataGridView1.Rows.Count - 1
            For i = 0 To a
                Dim strId As String = Me.DataGridView1.Rows(i).Cells(0).Value
                cmd1 = New SqlCeCommand("update tblAttachments set id='' where id='" & CStr(strId) & "'", objConn)
                strId = Me.DataGridView1.Rows(i).Cells(1).Value
                cmd1 = New SqlCeCommand("update tblAttachments set id='" & CStr(i + 1) & "' where filename='" & CStr(strId) & "'", objConn)
                ra = cmd1.ExecuteNonQuery()
            Next
            FillDataGrid(Me.DataGridView1, strQuery_AllAttachments)
            objConn.Close()
            Dim b As Decimal
            For i = 0 To a
                b += Me.DataGridView1.Rows(i).Cells(2).Value
                ToolStripProgressBar1.Value = b
                If b > 4000 Then
                    ToolStripButton1.Enabled = False
                    MsgBox("Storage Capacity Full", MsgBoxStyle.Critical, "VICAP Cloud System")
                    ToolStripLabel2.Text = "Storage Capacity Full"
                End If
                If b < 0 Then
                    ToolStripButton1.Enabled = True
                End If
                ToolStripLabel2.Text = "" & CStr(ToolStripProgressBar1.Value) & " of 4000mb"
            Next
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical, "Vicap Code Bug Detected")
        End Try
    End Sub

    Private Sub ToolStripButton1_Click(sender As Object, e As EventArgs) Handles ToolStripButton1.Click
        Try
            Me.Cursor = Cursors.WaitCursor
            OpenFileDialog1.FileName = ""
            OpenFileDialog1.ShowDialog()
            Call updt()
            Me.Cursor = Cursors.Default
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical, "Vicap Code Bug Detected")
        End Try
    End Sub

    Private Sub ToolStripButton2_Click(sender As Object, e As EventArgs) Handles ToolStripButton2.Click
        Try
            Dim a As Integer = DataGridView1.CurrentRow.Index
            Dim fln As String = Me.DataGridView1.Rows(a).Cells(1).Value.ToString()
            Dim ext As String = Me.DataGridView1.Rows(a).Cells(3).Value.ToString()
            SaveFileDialog1.DefaultExt = ext
            SaveFileDialog1.FileName = "" & CStr(fln) & "" & CStr(ext) & ""
            SaveFileDialog1.ShowDialog()
            SaveAttachment(SaveFileDialog1, Me.DataGridView1)
            FillDataGrid(Me.DataGridView1, strQuery_AllAttachments)
            Call rfrsh()
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical, "Vicap Code Bug Detected")
        End Try
    End Sub

    Private Sub ToolStripButton3_Click(sender As Object, e As EventArgs) Handles ToolStripButton3.Click
        Try
            objConn.Close()
            Dim a As Integer = DataGridView1.CurrentRow.Index
            Dim strId As String = Me.DataGridView1.Rows(a).Cells(0).Value.ToString()
            Dim ra As Integer
            Dim cmd1 As SqlCeCommand = New SqlCeCommand()
            objConn.Open()
            cmd1 = New SqlCeCommand("Delete from tblAttachments where id='" & CStr(strId) & "'", objConn)
            'since no value is returned we use ExecuteNonQuery
            ra = cmd1.ExecuteNonQuery()
            MessageBox.Show("Selected File has been deleted from VICAP Cloud ")
            FillDataGrid(Me.DataGridView1, strQuery_AllAttachments)
            objConn.Close()
            Call rfrsh()
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical, "Vicap Code Bug Detected")
        End Try
    End Sub

    Private Sub ToolStripButton4_Click(sender As Object, e As EventArgs) Handles ToolStripButton4.Click
        Try
            Dim ttlchar As Integer = ToolStripTextBox1.Text.Length
            Dim stdnt1 As String = ToolStripTextBox1.Text.Substring(0, ttlchar)
            Dim a As Integer = Me.DataGridView1.Rows.Count - 1
            Do While (a >= 0)
                a = (a - 1)
                For Each dr As DataGridViewRow In Me.DataGridView1.Rows
                    If dr.Cells(1).Value.Substring(0, ttlchar) <> stdnt1 Then
                        Me.DataGridView1.Rows.Remove(dr)
                    End If
                Next
            Loop
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical, "Vicap Code Bug Detected")
        End Try
    End Sub

    Private Sub ToolStripButton5_Click(sender As Object, e As EventArgs) Handles ToolStripButton5.Click
        Try
            Call rfrsh()
            Call rfrsh()
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical, "Vicap Code Bug Detected")
        End Try
    End Sub

    Private Sub ToolStripTextBox1_TextChanged(sender As Object, e As EventArgs) Handles ToolStripTextBox1.TextChanged
        Dim ttlchar As Integer = ToolStripTextBox1.Text.Length
        Dim stdnt1 As String = ToolStripTextBox1.Text.Substring(0, ttlchar)
        Dim a As Integer = Me.DataGridView1.Rows.Count - 1
        Do While (a >= 0)
            a = (a - 1)
            For Each dr As DataGridViewRow In Me.DataGridView1.Rows
                If UCase(dr.Cells(1).Value.Substring(0, ttlchar)) <> UCase(stdnt1) Then
                    Me.DataGridView1.Rows.Remove(dr)
                End If
            Next
        Loop
    End Sub
End Class
Posted
Updated 16-Apr-21 8:33am
Comments
Richard MacCutchan 16-Apr-21 5:19am
   
You cannot expect "working" code that will exactly match your project. You need to get a copy of MySQL and migrate your database and your code to use it.

1 solution

You'll need to change your SQL statements to match MySql syntax and then change your Connection String.
   

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