|

|

Creating Database using Navicat 1. Open Navicat (Start>>All Programs>>Premium Soft>>NavicatMYSQL. 2. Create new connection by clicking Connection button. 3. Under the General TAB, * Connection Name = MySQLServerConnection * Host Name/IP Address = localhost * Port = 3306 * User Name = root * Password = admin * Click OK button 4. Double click the newly created connection on the left pane to activate it. 5. Create a new database named lastname_firstname_dbgrading. 6. Add the following fields. * Studentid => varchar(10) => pk * Studentname => varchar(50) * quiz => float(3,2) * exam =>float(3,2) * project => float(3,2)

|

|

Creating a simple vb.net application to connect to mysql server. 1. Open Visual Studio 2008 and create a new project under VB.Net Windows form application. 2. Select a location where you want to save your application. (name your application as course_lastname_firstname) – (example: bsit_balauag_jr). 3. Set the following properties of the form. * Name => lastname_firstname_frm_server_login – (example: balauag_jr_frm_server_login). * Text => Connect to Server * StartUpPosition => CenterScreen * MaximizeBox => False * MinimizeBox => False * FormatBorderStyle => FixedSingle * Size => 417, 195

4. Add 3 textbox control, 3 labels, and 2 button on your form.

5. Set the properties of the first textbox. * Name => lastname_firstname_txt_server_name (example: balauag_jr_txt_server_name) * Text => Empty 6. Set the properties of the second textbox. * Name => lastname_firstname_txt_user_name (example: balauag_jr_txt_user_name) * Text => Empty 7. Set the properties of the third textbox. * Name => lastname_firstname_txt_password (example: balauag_jr_txt_password) * Text => Empty * PasswordChar => *

8. Set the properties of the first button. * Name => lastname_firstname_btn_connect (example: balauag_btn_connect) * Text => &Connect 9. Set the properties of the second button. * Name => lastname_firstname_btn_exit (example: balauag_jr_btn_exit) * Text => &Exit 10. Click Project on the menu bar, and then click “Add Module”, click the “Add” button. * Change the module’s name to lastname_firstname_functions.vb (example: balauag_jr_functions.vb) 11. Click Project on the menu bar, click “Add References”. 12. The Add References dialog box will appear, select .NET tab, browse MySQL.Data, and then click the “Ok” button. 13. Open lastname_firstname_functions.vb on the solution explorer, add the following lines
-------------------------------------------------
Imports MySql.Data.MySqlClient

Module lastname_firstname_functions Public conn As New MySqlConnection Public comm As New MySqlCommand
-------------------------------------------------
Public db_reader As MySqlDataReader

Public Function connect_to_server(ByVal server_name As String, ByVal user_name As String, ByVal password As String) As Boolean

Dim is_connected As Boolean = False Try conn.ConnectionString = "Server=" & server_name & "; uid=" & user_name & "; password=" & password & "; database=lastname_firstname_dbgrading" conn.Open() comm.Connection = conn is_connected = True Catch ex As Exception is_connected = False End Try

Return is_connected
-------------------------------------------------
End Function
Public Sub show_info_message(ByVal l_msg As String, ByVal l_title As String)
MessageBox.Show(l_msg, l_title, MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1)

End Sub
-------------------------------------------------
End Module

14. Open lastname_firstname_frm_server_login.vb on the solution explorer, add the following lines
-------------------------------------------------
Public Class lastname_firstname_frm_server_login

Private Sub lastname_firstname_btn_connect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lastname_firstname_btn_connect.Click Dim server_name As String = lastname_firstname_txt_server_name.Text.Trim Dim user_name As String = lastname_firstname_txt_user_name.Text.Trim.Trim Dim password As String = lastname_firstname_txt_password.Text.Trim If connect_to_server(server_name, user_name, password) Then Me.Hide() show_info_message("Success in connecting to server.", "Connect") lastname_firstname_frm_main.Show()

Else show_info_message("Unable to connect to server.", "Connect")

End If
-------------------------------------------------
End Sub

Private Sub lastname_firstname_btn_exit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lastname_firstname_btn_exit.Click Application.Exit() End Sub
-------------------------------------------------
End Class

15. Click Project on the menu bar, and then click “Add New Item”, select “MDI Parent Form” on the list. 16. Name your MDI Parent Form as lastname_firstname_frm_main.vb, and then click “Add” button. 17. Select lastname_firstname_frm_main.vb on the solution explorer, double-click any vacant space on the form, press CTRL+A on your keyboard and click the Delete key to remove all the pre-generated codes. Add these lines, your screen should look something like this.

Public Class lastname_firstname_frm_main

-------------------------------------------------
End Class

18. 19. Open lastname_firstname_frm_main.vb on the solution explorer, press CTRL+A on your keyboard and click the Delete key to delete all the controls available on your form. 20. 21. Once done, set the properties of lastname_firstname_frm_main.vb. * Text => Main Window – Your Whole Name (example: Main Window – Jr G. Balauag). * WindowState => Maximized

22. Add one MenuStrip on the form. Your form should look something like this.

23. Click Project on the menu bar, click “Add Windows Form”, name the form as lastname_firstname_frm_record.vb (example: balauag_jr_frm_record). 24. Add six labels, six textboxes, three buttons, and one listview control on your form. * TextBox1 – change its name to txt_studentnumber * TextBox2 – change its name to txt_studentname * TextBox3 – change its name to txt_quiz * Textbox4 – change its name to txt_exam * TextBox5 – change its name to txt_project * TextBox6 – change its name to txt_search * Button1 – change its name to btn_save * Button2- change its name to btn_update * Button3 – change its name to btn_delete * Button1 – change its text to &Save * Button2- change its text to &Update Selected * Button3 – change its text to &Delete Selected * ListView1 * Name => lv_records * Gridlines =>true * View => Details * FullRowSelect => True

25. Select lastname_firstname_frm_main.vb from the solution explorer. Double click any vacant area inside the form and add the following lines.

Public Class lastname_firstname_frm_main

Private Sub RecordScoreToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RecordScoreToolStripMenuItem.Click Form2.MdiParent = Me Form2.Show()
-------------------------------------------------
End Sub

Private Sub ExitToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ExitToolStripMenuItem.Click Application.Exit() End Sub
-------------------------------------------------
End Class 26. Select lastname_firstname_frm_record.vb from the solution explorer. Double click any vacant area inside the form and add the following lines.

Public Class lastname_firstname_frm_record
-------------------------------------------------
Private f_studentnumber As String = ""

Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load load_record(False)
-------------------------------------------------
End Sub

Private Sub load_record(ByVal is_search As Boolean) lv_records.Columns.Clear() lv_records.Items.Clear() txt_studentnumber.Clear() txt_studentname.Clear() txt_quiz.Text = "0" txt_project.Text = "0" txt_exam.Text = "0" lv_records.Columns.Add("#") lv_records.Columns.Add("Student Number") lv_records.Columns.Add("Student Name") lv_records.Columns.Add("Quiz") lv_records.Columns.Add("Exam") lv_records.Columns.Add("Project") Dim i As Integer = 0 Dim condition As String = "" If is_search = True Then condition = "WHERE studentid='" & txt_search.Text.Trim & "'" End If Dim query As String = "SELECT * FROM tbl_scores " & condition comm.CommandText = query

db_reader = comm.ExecuteReader

While db_reader.Read i += 1 Dim item1 As New ListViewItem(i & ".") item1.SubItems.Add(db_reader(0).ToString) item1.SubItems.Add(db_reader(1).ToString) item1.SubItems.Add(db_reader(2).ToString) item1.SubItems.Add(db_reader(3).ToString) item1.SubItems.Add(db_reader(4).ToString)

lv_records.Items.AddRange(New ListViewItem() {item1}) End While

db_reader.Close() db_reader = Nothing

------------------------------------------------- End Sub

Private Sub btn_save_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_save.Click Dim studentnumber As String = txt_studentnumber.Text.Trim Dim studenname As String = txt_studentname.Text.Trim Dim quiz As Double = Double.Parse(txt_quiz.Text.Trim) Dim project As Double = Double.Parse(txt_project.Text.Trim) Dim exam As Double = Double.Parse(txt_exam.Text.Trim)

Dim query As String = ""

query = "INSERT INTO tbl_scores " _ & "VALUES(" _ & "'" & studentnumber & "','" _ & studenname & "'," _ & quiz & "," _ & project & "," _ & exam & ")"

If exec_query(query) Then show_info_message("Record has been successfully saved.", "Save") load_record(False) Else show_info_message("Unable to save record.", "Save") End If
-------------------------------------------------
End Sub

Private Function exec_query(ByVal query As String) As Boolean Dim is_query_ok = False

comm.CommandText = query Try Dim affected_rows As Integer = comm.ExecuteNonQuery

If affected_rows > 0 Then is_query_ok = True Else is_query_ok = False End If Catch ex As Exception is_query_ok = False End Try

Return is_query_ok
-------------------------------------------------
End Function

Private Sub lv_records_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lv_records.SelectedIndexChanged If lv_records.Items.Count > 0 And lv_records.SelectedItems.Count > 0 Then f_studentnumber = lv_records.Items(lv_records.FocusedItem.Index).SubItems(1).Text txt_studentnumber.Text = lv_records.Items(lv_records.FocusedItem.Index).SubItems(1).Text txt_studentname.Text = lv_records.Items(lv_records.FocusedItem.Index).SubItems(2).Text txt_quiz.Text = lv_records.Items(lv_records.FocusedItem.Index).SubItems(3).Text txt_project.Text = lv_records.Items(lv_records.FocusedItem.Index).SubItems(4).Text txt_exam.Text = lv_records.Items(lv_records.FocusedItem.Index).SubItems(5).Text End If
-------------------------------------------------
End Sub

Private Sub btn_update_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_update.Click If f_studentnumber = String.Empty Then show_info_message("Please select student to be deleted from the list.", "Delete") Else Dim studentnumber As String = txt_studentnumber.Text.Trim Dim studenname As String = txt_studentname.Text.Trim Dim quiz As Double = Double.Parse(txt_quiz.Text.Trim) Dim project As Double = Double.Parse(txt_project.Text.Trim) Dim exam As Double = Double.Parse(txt_exam.Text.Trim)

Dim query As String = ""

query = "UPDATE tbl_scores " _ & "SET studentid='" _ & studentnumber & "',studentname='" _ & studenname & "',quiz=" _ & quiz & ",project=" _ & project & ",exam=" _ & exam & " WHERE studentid='" & f_studentnumber & "'"

If exec_query(query) Then show_info_message("Record has been successfully updated.", "update") load_record(False) Else show_info_message("Unable to update record.", "Save") End If End If

------------------------------------------------- End Sub

Private Sub btn_delete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_delete.Click If f_studentnumber = String.Empty Then show_info_message("Please select student to be deleted from the list.", "Delete") Else If MessageBox.Show("Are you sure that you want to delete this record?", "Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) = Windows.Forms.DialogResult.Yes Then Dim query As String = ""

query = "DELETE FROM tbl_scores WHERE studentid='" & f_studentnumber & "'"

If exec_query(query) Then show_info_message("Record has been successfully deleted.", "Delete")

load_record(False) Else show_info_message("Unable to delete record.", "Delete") End If End If End If
-------------------------------------------------
End Sub

Private Sub txt_search_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txt_search.TextChanged If txt_search.Text.Trim <> String.Empty Then load_record(True) End If End Sub
End Class

-------------------------------------------------
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
-------------------------------------------------
Dim username As String = TextBox1.Text.Trim.Trim
-------------------------------------------------
Dim password As String = TextBox2.Text.Trim
-------------------------------------------------

------------------------------------------------- If connect_to_server(username, password) Then
-------------------------------------------------
Me.Hide()
-------------------------------------------------
show_info_message("Success in connecting to server.", "Connect")
-------------------------------------------------

------------------------------------------------- Else
-------------------------------------------------
show_info_message("Unable to connect to server.", "Connect")
-------------------------------------------------

------------------------------------------------- End If
-------------------------------------------------
End Sub
-------------------------------------------------

-------------------------------------------------

------------------------------------------------- Private Sub lastname_firstname_btn_exit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
-------------------------------------------------
Application.Exit()
-------------------------------------------------
End Sub
-------------------------------------------------
End Class
-------------------------------------------------