heysoul_sisypus

DB input

Jan 26th, 2020
542
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Imports System.Data.OleDb
  2. Public Class Form1
  3.  
  4.     Dim conn As New OleDbConnection
  5.     Dim myqry As String = Nothing
  6.     Dim mycmd As New OleDbCommand
  7.     Dim mydr As OleDbDataReader
  8.  
  9.     Sub ConnToDB()
  10.         Try
  11.             With conn
  12.                 If .State = ConnectionState.Open Then .Close()
  13.                 .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Database6.accdb"
  14.                 .Open()
  15.             End With
  16.         Catch ex As Exception
  17.             MessageBox.Show("Unable to connect", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
  18.             Application.Exit()
  19.         End Try
  20.     End Sub
  21.  
  22.     Sub FillListview()
  23.         LV.Items.Clear()
  24.         myqry = "SELECT * from Table1 ORDER BY id ASC"
  25.         mycmd = New OleDbCommand(myqry, conn)
  26.         mydr = mycmd.ExecuteReader
  27.  
  28.         While mydr.Read
  29.             With LV
  30.                 .Items.Add(mydr("id"))
  31.                 With .Items(.Items.Count - 1).SubItems
  32.                     .Add(mydr("dbf1"))
  33.                     .Add(mydr("dbf2"))
  34.                     .Add(mydr("dbf3"))
  35.                     .Add(mydr("dbf4"))
  36.                     .Add(mydr("dbf5"))
  37.                     .Add(mydr("dbf6"))
  38.                     .Add(mydr("dbf7"))
  39.                     .Add(mydr("dbf8"))
  40.                 End With
  41.             End With
  42.         End While
  43.     End Sub
  44.  
  45.     Sub LVsettings()
  46.         With LV.Columns
  47.             .Add("ID", 50, HorizontalAlignment.Left)
  48.             .Add("First Name", 100, HorizontalAlignment.Left)
  49.             .Add("Middle Name", 100, HorizontalAlignment.Left)
  50.             .Add("Last Name", 100, HorizontalAlignment.Left)
  51.             .Add("Age", 100, HorizontalAlignment.Left)
  52.             .Add("Gender", 100, HorizontalAlignment.Left)
  53.             .Add("Year Level", 100, HorizontalAlignment.Left)
  54.             .Add("Section", 100, HorizontalAlignment.Left)
  55.             .Add("Strand", 100, HorizontalAlignment.Left)
  56.         End With
  57.     End Sub
  58.  
  59.     Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
  60.  
  61.         Call ConnToDB()
  62.         Call FillListview()
  63.         Call LVsettings()
  64.  
  65.     End Sub
  66.  
  67.     Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
  68.         myqry = "INSERT INTO Table1(dbf1,dbf2,dbf3,dbf4,dbf5,dbf6,dbf7,dbf8) "
  69.         myqry = myqry + "VALUES('" & txt1.Text & "','" & txt2.Text & "','" & txt3.Text & "','" & txt4.Text & "','" & txt5.Text & "',
  70.        '" & txt6.Text & "','" & txt7.Text & "','" & txt8.Text & "')"
  71.         mycmd = New OleDbCommand
  72.         With mycmd
  73.             .CommandText = myqry
  74.             .Connection = conn
  75.             .ExecuteNonQuery()
  76.         End With
  77.         Call FillListview()
  78.     End Sub
  79. End Class
RAW Paste Data