MYSQL CRUD Using Custom Function In VB.Net (Part 3)

MYSQL CRUD Using Custom Function In VB.Net (Part 3)

By   July 23, 2018   No comments:

Step 2: Back - Create Function For Insert, Update And Delete Data

Step 3: Testing The Function in Form1

• Create Database exercise1

Create database on mysql. You can execute query below in your mysql management database :

CREATE DATABASE exercise1; USE exercise1; DROP TABLE IF EXISTS `t_teacher`; CREATE TABLE `t_teacher` ( `id` int(5) NOT NULL AUTO_INCREMENT, `full_name` varchar(20) NOT NULL, `sex` varchar(5) NOT NULL, `address` varchar(220) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

• Design Form1, Please design form like picture below:

• Copy And Paste code below to your form1 code:

Public Class Form1 Dim fullName As String Dim sex As String Dim address As String Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click fullName = TextBox1.Text sex = TextBox2.Text address = TextBox3.Text Dim fieldsArray As String = "full_name,sex,address" 'these are field name on t_teacher (separates by comma) Dim valuesArray As String = "'" & fullName & "','" & sex & "','" & address & "'" 'separetes by comma If bejoInsert("t_teacher", fieldsArray, valuesArray) Then MsgBox("Successfull Inserted", vbInformation) Else MsgBox(errorDescription, vbCritical) End If End Sub End Class

• Run your project by press F5
• You can test input and save data

Read More

MYSQL CRUD Using Custom Function In VB.Net (Part 2)

MYSQL CRUD Using Custom Function In VB.Net (Part 2)

By   July 23, 2018   No comments:

Step 1: Create Function Connection to Database
Step 2: Create Function For Insert, Update And Delete Data

In Step 2 we will create function for insert, update and delete data from database.

• Function For Insert Data

Function bejoInsert(tableName As String, fields As String, values As String) As Boolean bejoDBConnect() Try Dim cmd As New MySqlCommand(String.Format("INSERT INTO " & tableName & " (" & fields & ") VALUES (" & values & ")")) cmd.Connection = conn cmd.ExecuteNonQuery() Return True Catch ex As Exception ErrorDescription = ex.Message Return False End Try End Function

• Function For Update Data

Function bejoUpdate(tableName As String, fields As String, values As String, condition As String) As Boolean bejoDBConnect() Try Dim update As String = "" Dim strsql As String = "" Dim a As Array = Split(fields, ",") Dim b As Array = Split(values, "|") If a.Length <> b.Length Then Return False ErrorDescription = "Fields count doesn't match with values count!" Exit Function End If For i As Integer = 0 To (a.Length - 1) If update = "" Then update = a(i) & "=" & b(i) Else update = update & "," & a(i) & "=" & b(i) End If Next strsql = "UPDATE " & tableName & " SET " & update & " WHERE " & condition 'MsgBox(strsql) Dim cmd As New MySqlCommand(String.Format(strsql)) cmd.Connection = conn cmd.ExecuteNonQuery() Return True Catch ex As Exception ErrorDescription = ex.Message Return False End Try End Function

• Function For Delete Data

Function bejoDelete(tableName As String, condition As String) As Boolean bejoDBConnect() Dim SQLCmd As MySqlCommand Dim DR As MySqlDataReader Try SQLCmd = New MySqlCommand("DELETE FROM " & tableName & " WHERE " & condition, conn) DR = SQLCmd.ExecuteReader Return True DR.Close() Catch ex As Exception errorDescription = ex.Message Return False DR.Close() End Try End Function

Step 3: Next - Testing The Function In Form1

Read More

MYSQL CRUD Using Custom Function In VB.Net

MYSQL CRUD Using Custom Function In VB.Net

By   July 23, 2018   No comments:

In this article I will share my way to Mysql CRUD using vb.net by creating custom functions. Why custom function, because this function I created itself to simplify and shorten the program to CRUD data from the database by calling the function. So we no longer need to type the insert, update and delete commands in the sql query.

Step 1: Create Function For Connect To Database

• Open Visual Basic .Net
• Create New Project
• Add New Module (Right click on Project Name at Solution Explorer - Add - Module)
• Let its name Module1.vb
• Add to Reference : MySql.Data.Dll

Type codes below to Module1:

Imports MySql.Data.MySqlClient Module Module1 Public conn As New MySqlConnection Public errorDescription As String Public server As String = "127.0.0.1" 'based on your server Public username As String = "root" 'default user in mysql Public password As String = "12345678" 'on my server i give this password Public database_name As String = "exercise1" 'for testing i create exercise1 dbname Public Function bejoDBConnect() As String If Not conn Is Nothing Then conn.Close() conn.ConnectionString = String.Format("server={0}; user id={1}; password={2}; database={3}; pooling=false; Convert Zero Datetime=True", server, username, password, database_name) Try conn.Open() Return "OK" Catch ex As Exception errorDescription = ex.Message Return ex.Message End Try End Function End Module1

Step 2: Next - Create Function For Insert, Update And Delete Data

Read More