[The-One]

Hey,

I've been working on this a few hours now. I remade my old MySQL connection class. You can do the following things:

• Login
  


• Register a new account
  


• List all the accounts
  

You can for example add a 'modify' account option or a 'delete' account option which UID count is already build in (meaning you should (never tested) never have two same UID's).

[img]http://k.min.us/jdxmsK.png[/img]

Code and Info:

The class file:
Spoiler (Click to View)

Code

Imports MySql.Data.MySqlClient
Public Class MySQL_VB
    Private mysql_con As New MySqlConnection
    Private mysql_adapter As New MySqlDataAdapter
    Private mysql_query As String
    Private mysql_command As New MySqlCommand
    Private mysql_bool As Boolean = False
    Private mysql_count As Integer = 0

    Public Sub SetConnectionData(ByVal ip As String, ByVal username As String, ByVal pw As String, ByVal database As String)
  mysql_con.ConnectionString = "server=" & ip & ";" & "user id=" & username & ";" & "password=" & pw & ";" & "database=" & database
    End Sub

    Public Sub Login_(ByVal user As String, ByVal pass As String)
  Try
    mysql_con.Open()
  Catch ex As MySqlException
    MsgBox("Could not connect to the database...")
  End Try
  mysql_query = "SELECT * FROM Users WHERE Username ='" + Replace(user, " ", "") + "' AND Password='" & Replace(pass, " ", "") & "'"
  mysql_command.Connection = mysql_con
  mysql_command.CommandText = mysql_query

  mysql_adapter.SelectCommand = mysql_command
  Dim mysql_data As MySqlDataReader = mysql_command.ExecuteReader()
  If mysql_data.HasRows Then
    MsgBox("Logon sucessful!")
    mysql_con.Close()
    mysql_bool = True
  Else
    MsgBox("Error! Wrong username/password!")
    mysql_bool = False
  End If
    End Sub

    Public Sub Register_(ByVal user As String, ByVal pass As String, ByVal pass2 As String)
  If pass = pass2 Then
    Try
    mysql_con.Open()
    Catch ex As MySqlException
    MsgBox("No connection to the database!")
    End Try

    Count()

    Dim myAdapter As New MySqlDataAdapter
    Dim mysql_Command As New MySqlCommand
    mysql_Command.Connection = mysql_con
    mysql_Command.CommandText = "SELECT * FROM Users WHERE Username='" + user + "'"

    myAdapter.SelectCommand = mysql_Command
    Dim myData As MySqlDataReader
    myData = mysql_Command.ExecuteReader()

    If myData.HasRows = 0 Then
g:
    mysql_con.Close()
    mysql_con.Open()
    mysql_Command.CommandText = "SELECT * FROM Users WHERE Id='" & mysql_count.ToString & "'"

    myAdapter.SelectCommand = mysql_Command
    Dim myDataX As MySqlDataReader
    myDataX = mysql_Command.ExecuteReader()
    If myDataX.HasRows = 0 Then
    mysql_con.Close()
    mysql_con.Open()
    Dim registerfinal As New MySqlDataAdapter
    mysql_Command.CommandText = "INSERT INTO Users(Id, Username, Password, Date)" _
     & "VALUES('" & mysql_count & "','" & user & "','" & pass & "','" & My.Computer.Clock.GmtTime & "')"
    mysql_Command.ExecuteNonQuery()
    MsgBox("The account with the name: " & user & " was created sucessfully!")
    mysql_con.Close()
    Else
    mysql_count += 1
    GoTo g
    End If
    Else
    MsgBox("This username does already exist")
    End If
  Else
    MsgBox("The passwords did not match!")
  End If
    End Sub

    Public Sub UsersList_(ByVal l As ListView)
  Try
    mysql_con.Open()
  Catch ex As MySqlException
    MsgBox("Could not connect to the database...")
  End Try

  Count()

  Dim myAdapter As New MySqlDataAdapter
  Dim mysql_Command As New MySqlCommand
  mysql_Command.Connection = mysql_con
  mysql_Command.CommandText = "SELECT * FROM Users"

  myAdapter.SelectCommand = mysql_Command
  Dim myData As MySqlDataReader
  myData = mysql_Command.ExecuteReader()

  For i = 0 To mysql_count
    myData.Read()
    Dim lvi As New ListViewItem(New String() {myData.GetString(0), myData.GetString(1), myData.GetString(2), myData.GetString(3)})
    l.Items.Add(lvi)
  Next

  mysql_con.Close()
    End Sub

    Private Sub Count()
  Dim myAdapter As New MySqlDataAdapter
  Dim mysql_Command As New MySqlCommand
  mysql_Command.Connection = mysql_con
  mysql_Command.CommandText = "SELECT * FROM Users"

  myAdapter.SelectCommand = mysql_Command
  Dim myData As MySqlDataReader
  myData = mysql_Command.ExecuteReader()
  Dim L As Integer = -1
g:
  myData.Read()
  If L = myData.GetString(0) Then
  Else
    L = myData.GetString(0)
    GoTo g
  End If

  mysql_count = L
  myData.Close()
    End Sub
End Class

NOTE: to use this you must have MySql.Data.dll included as reference in your project

Usage is now very simple:

List the table:

Code

Dim m As New MySQL_VB
  m.SetConnectionData("88.198.43.24", "username", "password", "testdba")
  m.UsersList_(ListView1)

Register a new account:

Code

Dim m As New MySQL_VB
  m.SetConnectionData("88.198.43.24", "username", "password", "testdba")
  m.Register_(TextBox1.Text, TextBox2.Text, TextBox3.Text)

Check if a login exist or is valid:

Code

Dim m As New MySQL_VB
  m.SetConnectionData("88.198.43.24", "username", "password", "testdba")
  m.Login_(TextBox6.Text, TextBox5.Text)

Downloads:

Whole project (as on the above image YOU MUST DOWNLOAD THE DATABASE FILE BELOW): http://www.mediafire.com/?yu25su89yo79rqb
MySQL.dll (already included on the project): http://www.mediafire.com/?1lk13m1bh8a98p3
Class file: http://www.mediafire.com/?jyu2lunh39jgjjl
Database file: http://www.mediafire.com/?mc8r9c0ob6xp77s

[Apple]

Not to thread jack here but you can do everything with this without the limitation that your library provides =P