Mysql Register Button insert into

[keujh]

Hey, I'm new to the forums and well I've managed to make a login user form that uses mysql database.

now i wanted to make a register form as well, but i can't seem to figure out how i do that.

so, can anyone help me out?

here's my login form.

My.Computer.Audio.Play(My.Resources.button1, AudioPlayMode.Background)
        Dim conn As MySqlConnection
        'connect to DB
        conn = New MySqlConnection()
        conn.ConnectionString = "server=127.0.0.1; user id=root; password=test; database=users"
        'see if connection failed.
        Try
            conn.Open()
        Catch myerror As MySqlException
            MessageBox.Show("Error Connecting to Database")
            Close()
        End Try
        'sql query
        Dim myAdapter As New MySqlDataAdapter

        Dim sqlquery = "SELECT username, password FROM accounts Where username='" & TextBox1.Text & "' and password='" & TextBox2.Text & "'"
        Dim myCommand As New MySqlCommand()
        myCommand.Connection = conn
        myCommand.CommandText = sqlquery
        'start query
        myAdapter.SelectCommand = myCommand
        Dim myData As MySqlDataReader
        myData = myCommand.ExecuteReader()
        'if nothing is entered into the textboxes
        If TextBox1.Text = "" Then
            MsgBox("Please Enter your Username")
            My.Computer.Audio.Play(My.Resources.button1, AudioPlayMode.Background)
        End If
        If TextBox2.Text = "" Then
            My.Computer.Audio.Play(My.Resources.button1, AudioPlayMode.Background)
            MsgBox("Please Enter your Password")
        Else
        End If

        'see if user exits.
        If myData.HasRows = 0 Then
            MessageBox.Show("The information that has been entered appears to be invalid.", "Vbphysics", MessageBoxButtons.OK, MessageBoxIcon.Error)
            My.Computer.Audio.Play(My.Resources.button1, AudioPlayMode.Background)
        Else
            Dim form2 = New Form2
            form2.Show()
            Me.Visible = False
        End If
    End Sub

[Jenner]

Try something like this for your login form. As you've written it, I can hack your program by typing in one line of text into TextBox1.

        Dim validuser As Boolean

        'no matter what you do, I play this sound, so just play it here.
        My.Computer.Audio.Play(My.Resources.button1, AudioPlayMode.Background)

        'if nothing is entered into the textboxes
        'check this before you do anything with the database!
        If TextBox1.Text.Equals(String.Empty) Then
            MessageBox.Show("Please Enter your Username")
            Exit Sub
        End If
        If TextBox2.Text.Equals(String.Empty) Then
            MessageBox.Show("Please Enter your Password")
            Exit Sub
        Else
        End If

        'by using the "Using" structure, my connection is cleaned up nicely when done.
        Using conn As New MySqlConnection("server=127.0.0.1; user id=root; password=test; database=users")
            'see if connection failed.
            Try
                conn.Open()
            Catch myerror As MySqlException
                MessageBox.Show("Error Connecting to Database")
                Me.Close()
                Exit Sub
            End Try
            'sql query

            Dim sqlquery = "SELECT username, password FROM accounts Where username=?USER and password=?PASS; "
            Dim myCommand As New MySqlCommand(sqlquery, conn)
            'if you don't use parameters, anyone can hack the system with ease!  no more annoying quote problems either!
            myCommand.Parameters.AddWithValue("USER", TextBox1.Text)
            myCommand.Parameters.AddWithValue("PASS", TextBox2.Text)

            Dim myData As MySqlDataReader = myCommand.ExecuteReader()

            'see if user exits.
            If Not myData.HasRows Then
                MessageBox.Show("The information that has been entered appears to be invalid.", "Vbphysics", MessageBoxButtons.OK, MessageBoxIcon.Error)
                validuser = False
            Else
                Dim f As New Form2
                f.Show()
            End If

            'cleanup my junk.
            myData.Close()
            myCommand.Dispose()
            conn.Close()
        End Using
        If validuser Then
            Me.Close()
        End If
    End Sub

[jmcilhinney]

So, what are you actually saying? That you don't know how to insert data into a database, or that you don't know how to gather the data to insert?

[keujh]

So, what are you actually saying? That you don't know how to insert data into a database, or that you don't know how to gather the data to insert?

Both of those, the only thing i can do with mysql is work with login forms, providing i get help to code it.

I'm a slow leaner, due to my intelligence.

Thanks for the help guys, really appreciate it.

[Suterusu]

This is a samply of the code I used:

Private Sub addUserToDatabase(ByVal user1 As String, ByVal password1 As String, ByVal admin1 As Boolean, ByVal server1 As Integer)
        Try
            Dim sqlCommand As String = "INSERT INTO `userdatabase`.`users` (`user_id`, `user_name`, `user_password`, `server_id`, `admin_flag`) VALUES (NULL, '" & user1 & "', '" & password1 & "', '" & server1 & "', '" & admin1 & "')"
            sqlInsertData(sqlCommand)
        Catch ex As Exception
        End Try
        
 
    End Sub

Then: for the actual inserting of data, the important part is the myCommand.ExecuteNonQuery()

Private Sub sqlInsertData(ByVal cmd As String)
        Dim conn As New MySqlConnection
        Dim myCommand As New MySqlCommand
        conn.ConnectionString = "server=" & txtServer & ";" _
            & "user id=" & txtUsername & ";" _
           & "password=" & txtPassword & ";" _
        & "database=" & txtDatabase
        myCommand.Connection = conn
        myCommand.CommandText = cmd
        Try
            conn.Open()
            myCommand.ExecuteNonQuery()
        Catch myerror As MySqlException
            MsgBox("There was an error updating the database: " & myerror.Message)
        End Try
        conn.Close()
    End Sub

As stated above though you should use the "Using, End Using", it is something I will make changes to on my own application.



Some sample code I used for getting user data from the database:

Public Sub readData()
        Dim dataReader As DataTableReader
        Dim getUserData As String = "SELECT `users`.`user_name`'User Name', `users`.`user_password`'Password', `users`.`admin_flag` 'Admin' FROM `users` WHERE ( `users`.`server_id` = '" & serverId & "')"
        runSqlQuery(getUserData)
        dataReader = myData.CreateDataReader
        Do While dataReader.Read()
            Dim name As String = CStr(dataReader(0))
            Dim passwd As String = CStr(dataReader(1))
            Dim admin As Boolean = CBool(dataReader(2))
            Try
                'do stuff with your data here
            Catch ex As Exception
            End Try
        Loop

This SQL query code:

Private Sub runSqlQuery(ByVal cmd As String)
        Try
            conn.ConnectionString = "server=" & txtServer & ";" _
          & "user id=" & txtUsername & ";" _
         & "password=" & txtPassword & ";" _
      & "database=" & txtDatabase & ";" _
      & "Encrypt=true;"
            conn.Open()
        Catch myerror As MySqlException
        End Try
        myAdapter.SelectCommand = myCommand
        myCommand.Connection = conn
        myCommand.CommandText = cmd
        myAdapter.Fill(myData)
        conn.Close()
        conn.Dispose()
    End Sub

[keujh]

This is nice and i appreciate it but it's not very explanatory.

So something else, would be highly appreciated.

[Suterusu]

What parts don't you understand? <.<