CardinalGaming

CRUD EXAMPLE

Nov 26th, 2021
761
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Imports MySql.Data.MySqlClient
  2. Imports System.Data
  3.  
  4. Public Class Form1
  5.     Dim con As MySqlConnection
  6.     Dim cmd As New MySqlCommand
  7.     Dim dataadapter As New MySqlDataAdapter
  8.     Dim datatable As New DataTable
  9.  
  10.  
  11.     Private Sub Query(ByVal data As String)
  12.         Try
  13.             If con.State = ConnectionState.Closed Then
  14.                 con.Open()
  15.             End If
  16.  
  17.             cmd.Connection = con
  18.             cmd.CommandText = data
  19.             cmd.ExecuteNonQuery()
  20.  
  21.             TextBox1.Clear()
  22.             TextBox2.Clear()
  23.             TextBox3.Clear()
  24.  
  25.  
  26.             DataLoad()
  27.             con.Close()
  28.  
  29.         Catch ex As Exception
  30.             MessageBox.Show("Error: " & ex.Message.ToString)
  31.         Finally
  32.             con.Dispose()
  33.         End Try
  34.     End Sub
  35.  
  36.  
  37.     Private Sub DataLoad(Optional ByVal data As String = "SELECT * FROM user")
  38.         con = New MySqlConnection()
  39.         con.ConnectionString = "server=127.0.0.1; userid=root; password=12345; database= crudsample"
  40.  
  41.         Try
  42.  
  43.             datatable.Clear()
  44.  
  45.             If con.State = ConnectionState.Closed Then
  46.                 con.Open()
  47.             End If
  48.  
  49.             cmd.CommandText = data
  50.             cmd.Connection = con
  51.             dataadapter.SelectCommand = cmd
  52.             dataadapter.Fill(datatable)
  53.  
  54.             With DataGridView1
  55.                 .DataSource = datatable
  56.                 .Columns(0).HeaderText = "ID"
  57.                 .Columns(1).HeaderText = "First Name"
  58.                 .Columns(2).HeaderText = "Middle Name"
  59.                 .Columns(3).HeaderText = "Last Name"
  60.                 .Columns(0).Width = 50
  61.                 .Columns(1).Width = 150
  62.                 .Columns(2).Width = 150
  63.                 .Columns(3).Width = 150
  64.             End With
  65.  
  66.             con.Close()
  67.  
  68.  
  69.         Catch ex As Exception
  70.             MessageBox.Show("Error" & ex.Message.ToString)
  71.  
  72.         Finally
  73.             con.Dispose()
  74.         End Try
  75.     End Sub
  76.  
  77.     Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
  78.         DataLoad("SELECT id, fname, mname, lname FROM user")
  79.     End Sub
  80.  
  81.     Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
  82.         Query("INSERT INTO USER(fname, mname, lname) VALUES ('" & TextBox1.Text & "','" & TextBox2.Text & "','" & TextBox3.Text & "')")
  83.     End Sub
  84.  
  85.     Private Sub DataGridView1_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellClick
  86.         TextBox1.Text = DataGridView1.CurrentRow.Cells(1).Value.ToString
  87.         TextBox2.Text = DataGridView1.CurrentRow.Cells(2).Value.ToString
  88.         TextBox3.Text = DataGridView1.CurrentRow.Cells(3).Value.ToString
  89.  
  90.     End Sub
  91.  
  92.     Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
  93.         Query("UPDATE USER SET fname='" & TextBox1.Text & "', mname='" & TextBox2.Text & "', lname='" & TextBox3.Text & "' WHERE id=" & DataGridView1.CurrentRow.Cells(0).Value.ToString)
  94.     End Sub
  95. End Class
  96.  
RAW Paste Data