Home › Forum › Programming › Visual Basic Programming › [Help]SQL & VB.net[Solved]

[Help]SQL & VB.net[Solved]

Posts 1–15 of 15 · Page 1 of 1

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

Why don't you use the PHP system I released? You only need a free online database.

Functions are very easy 2 use.

Originally Posted by Blubb1337

Why don't you use the PHP system I released? You only need a free online database.

Functions are very easy 2 use.

Because it limits you unless you know mySQL with vb.

and it is connected through php.

will it work with xamp

yes it will, But i wouldnt suggest Running it on your Local Computer if you plan on using it for an app because it's not up 99.99% such as websevers.

well that may be true for most but the up tim eon my pc as of right now is like 4months with 3 restarts in that time lol so yea plus i was going to run it off a dedicated server aswell for a backup

Well if you need webhosting hit me up on msn, Immortal-@live.com,,,

i have packages as cheap as 2$ a month

Originally Posted by Bombsaway707

I'd create a table with two columns:
_____________________
Online | Computer Name |
-------------------------------------|

In the online column put either a 0 or 1 (change it to 1 when the program is loaded and back to 0 on formclosing)

Then, a simple SQL query to determine each person with the Online value of "1" at any time will be enough to get you from a -> b.

If you require code examples, just say.

Using the SQL ADO provider, that is provided free with your Visual Studio installation.

Originally Posted by mnpeepno2

Because it limits you unless you know mySQL with vb.

and it is connected through php.

The PHP files will work using localhost, the mysql connector won't.

PHP files are faster.

The system contains everything he needs.

Originally Posted by Jason

Yeah i've never done anything like this using mysql so i'm really lost haha, code examples would be appreciated

Originally Posted by Blubb1337

The PHP files will work using localhost, the mysql connector won't.

PHP files are faster.

The system contains everything he needs.

I'd use your's if u can explain it to me lol

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

Originally Posted by Blubb1337

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

Why didn't you stop me then

Originally Posted by Jason

Why didn't you stop me then

Because I went to sleep after helping him :P.

Posts 1–15 of 15 · Page 1 of 1