User:Pluke/database

Imports System.Data.OleDb
Module dbConnector
    Dim con As New OleDbConnection
    Dim dbProvider As String
    Dim dbSource As String
    Dim ds As New DataSet
    Dim da As OleDb.OleDbDataAdapter
    Dim sql As String

    Sub connect() 'makes a connection
        con.Close()
        dbProvider = "PROVIDER=Microsoft.ACE.OLEDB.12.0;"
        dbSource = "Data Source = E:\SMaths\SMathsDB.accdb"

        con.ConnectionString = dbProvider & dbSource

        con.Open()
        'MsgBox("DB is now open")

        'con.Close()
        'MsgBox("DB is now closed")

    End Sub

    Sub reset() 'resets the connection
        ds.Reset()
    End Sub

    Sub close() 'closes the connection
        con.Close()
    End Sub

    Function SQLselect(ByVal sqlString) 'selects table from database
        sql = sqlString
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "Results")
        Return ds
    End Function

    Sub SQLinsert(ByVal sql) 'inserts data into database
        Dim da As New OleDbCommand(sql, con)
        da.ExecuteNonQuery()

    End Sub

    Sub SQLdelete(ByVal sql)
        Dim da As New OleDbCommand

        da.Connection = con
        da.CommandType = CommandType.Text
        da.CommandText = sql
        da.ExecuteNonQuery()

    End Sub

    Sub SQLupdate(ByVal sql)
        Dim da As New OleDbCommand(sql, con)
        da.ExecuteNonQuery()
    End Sub

End Module

Public Class Form1
    Dim db As New dbConnector
    Dim ds As New DataSet

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        db.connect()

    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        ds = db.SQLselect("SELECT * FROM tblUsers WHERE ID = 1 AND Password = '123456'")
        MsgBox(ds.Tables("Results").Rows(0).Item(3))

    End Sub
End Class