[Help]SQL & VB.net[Solved]

Bombsaway707 · 2011-02-08T21:32:30.000Z

Ok guys so i was wondering how i to use SQL with VB.net? Like i want to create a program that will keep track of the # of users currently using the program...like i need to know how many are currently running the program + their computer names if possible... Im making a multi-tool and would like to see how many users+ their computer name are running it. I would much appreciate a fully explained way to set up a SQL server + how to integrate the number of users and their PC names into my program . I've been looking around forever with no success -Cheers

[Help]SQL & VB.net[Solved] - MPGH

Alright, if you have a MySQL database, you can connect to it through vb. You need the MySql.Data.dll to reference to your project for starters. After that you need to build the connection string to your database, like so:

Code:

Dim ConnectStr As String = String.Concat("server=", "yourserverIP", ";user id=", "databaseusername", ";password=", "databasepassword", ";database=DatabaseName")

Then you need to set up the connection to the database:

Code:

Private SQLConnection As New MySqlConnection(ConnectStr)

Then you'll need the sub to actually tell it to connect:

Code:

    Private Sub TryConnect()
        Try
            SQLConnection.Open()
        Catch ex As MySQLException
            If MessageBox.Show("An error occurred when trying to connect to the database, would you like to try again?", "Database error", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) = Windows.Forms.DialogResult.Yes Then
                TryConnect()
            Else
                Application.Exit()
            End If
        End Try
    End Sub

So now (hopefully) you're connected to the database.

You need to obtain some sort of unique computer key at this stage which can be used to find the database entry at a later date so let's just assume you found the motherboardID.

Now you need to determine whether the current computer already holds a row in the database:

Code:

Dim rowCount As Integer = 0
Dim MotherBoardID As String = "function to return motherboardID"
Dim cmdStr As String = "SELECT COUNT(*) FROM databasetablename WHERE MotherBoardID='" & MotherBoardID & "'"
Dim cmd As New MySqlCommand(cmdStr, SQLConnection)
Using mD As MySqlDataReader = cmd.ExecuteReader
   mD.Read
   rowCount = Integer.Parse(mD.Item(0).ToString)
End Using

Will finish this later, really needa go now

Alright, edit.

Now you have the rowcount of this particular MotherboardID. If the count is > 0 then you want to simply set the "Online" column to 1. If the count is 0 you want to create a new row with the motherboardID and the "online" value of 1

Say the count is 1.

Code:

Dim UpdateStr As String = "UPDATE yourdatatablename SET Online='1' WHERE MotherBoardID='" & MotherboardID & "'"
Dim UpdateCMD As New MySQLCommand(UpdateStr, SQLConnection)
UpdateCMD.ExecuteNonQuery

Or if the count is 0

Code:

Dim NewRowStr As String = "INSERT INTO datatablename (Online,MotherBoardID) VALUES ('1','" & MotherboardID & "')"
Dim NewRowCMD As New MySQLCommand(UpdateStr, SQLConnection)
NewRowCMD.ExecuteNonQuery

Now making it a bit less messy:

Code:

Dim qryStr As String = String.Empty
If rowCount > 0 Then
   qryStr = "UPDATE datatablename SET Online='1' WHERE MotherBoardID='" & motherboardID & "'"
Else
   qryStr = "INSERT INTO datatablename(Online,MotherBoardID) VALUES('1','" & motherboardID & "')"
End If
Dim newCMD as New MySQlCommand(qryStr, SQLConnection)

Then all you need to do is do another count function to find the members online!

Code:

Dim cmdStr As String = "SELECT COUNT(*) FROM JasonsTable WHERE Online='1'"
Dim OnlineCount As Integer = 0
Dim cmd As New MySqlCommand(cmdStr, SQLConnection)
Using mD As MySqlDataReader = cmd.ExecuteReader
    mD.Read()
    OnlineCount = Integer.Parse(mD.Item(0).ToString)
End Using

Originally Posted by Jason

Code:

Dim ConnectStr As String = String.Concat("server=", "yourserverIP", ";user id=", "databaseusername", ";password=", "databasepassword", ";database=DatabaseName")

Then you need to set up the connection to the database:

Code:

Private SQLConnection As New MySqlConnection(ConnectStr)

Then you'll need the sub to actually tell it to connect:

Code:

    Private Sub TryConnect()
        Try
            SQLConnection.Open()
        Catch ex As MySQLException
            If MessageBox.Show("An error occurred when trying to connect to the database, would you like to try again?", "Database error", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) = Windows.Forms.DialogResult.Yes Then
                TryConnect()
            Else
                Application.Exit()
            End If
        End Try
    End Sub

Code:

Dim rowCount As Integer = 0
Dim MotherBoardID As String = "function to return motherboardID"
Dim cmdStr As String = "SELECT COUNT(*) FROM databasetablename WHERE MotherBoardID='" & MotherBoardID & "'"
Dim cmd As New MySqlCommand(cmdStr, SQLConnection)
Using mD As MySqlDataReader = cmd.ExecuteReader
   mD.Read
   rowCount = Integer.Parse(mD.Item(0).ToString)
End Using

Will finish this later, really needa go now

Code:

Dim UpdateStr As String = "UPDATE yourdatatablename SET Online='1' WHERE MotherBoardID='" & MotherboardID & "'"
Dim UpdateCMD As New MySQLCommand(UpdateStr, SQLConnection)
UpdateCMD.ExecuteNonQuery

Or if the count is 0

Code:

Dim NewRowStr As String = "INSERT INTO datatablename (Online,MotherBoardID) VALUES ('1','" & MotherboardID & "')"
Dim NewRowCMD As New MySQLCommand(UpdateStr, SQLConnection)
NewRowCMD.ExecuteNonQuery

Now making it a bit less messy:

Code:

Dim qryStr As String = String.Empty
If rowCount > 0 Then
   qryStr = "UPDATE datatablename SET Online='1' WHERE MotherBoardID='" & motherboardID & "'"
Else
   qryStr = "INSERT INTO datatablename(Online,MotherBoardID) VALUES('1','" & motherboardID & "')"
End If
Dim newCMD as New MySQlCommand(qryStr, SQLConnection)

Then all you need to do is do another count function to find the members online!

Code:

Dim cmdStr As String = "SELECT COUNT(*) FROM JasonsTable WHERE Online='1'"
Dim OnlineCount As Integer = 0
Dim cmd As New MySqlCommand(cmdStr, SQLConnection)
Using mD As MySqlDataReader = cmd.ExecuteReader
    mD.Read()
    OnlineCount = Integer.Parse(mD.Item(0).ToString)
End Using

It's already solved, he's using my php system now

[Help]SQL & VB.net[Solved]

Post a Reply

Tags for this Thread