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
Submit an article or tip