CODEWITHSUNDEEP
mysqlvb.net
Mark Nartea
Mark Nartea

Reputation: 67

VB.Net MySQL How to insert values to the database

Pro Devs I'm using VB.Net with MySQL Database and I want to insert values in my DB. Example: I have values in my DB which are Admin11 but when I insert another value admin11 I get an error here's my code. by the way, these two methods are in different classes.

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

here's my code to check if Username exists in the database...

Public Sub checkUsernameIfExist()

    Dim con = New MySqlConnection
    con.ConnectionString = "server=localhost;userid=root;password=alpine;port=3305;database=pos_db;pooling=false;SslMode=none"

    con.Open()
    Dim query As String = "SELECT Username FROM pos_db.tblusers WHERE BINARY Username=@Users"
    Dim cmd As New MySqlCommand(query, con)
    cmd.Parameters.AddWithValue("@Users", frmLogin.txtUser.Text)

    Dim count As Integer = Convert.ToInt32(cmd.ExecuteScalar())

    If count <> 0 Then
        MessageBox.Show("Username is already taken. Please create a unique one!", "System", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        Return
    Else
        insertDataToTblUser.insertToLogin()
    End If
    con.Close()
    con.Dispose()
End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Here's my code in insert values......

Dim con As MySqlConnection

Public Sub insertToLogin()
    con = New MySqlConnection
    con.ConnectionString = "server=localhost;userid=root;password=alpine;port=3305;database=pos_db;pooling=false;SslMode=none"

    con.Open()

    Dim qry As String = "INSERT INTO tblUsers (Username,Password,Level) VALUES (@User,@Pass,@lvl)"
    Dim cmd As New MySqlCommand(qry, con)
    cmd.Parameters.AddWithValue("@User", frmLogin.txtUser.Text)
    cmd.Parameters.AddWithValue("@Pass", frmLogin.txtPass.Text)
    cmd.Parameters.AddWithValue("@lvl", frmLogin.cmbUserlevel.Text)
    cmd.ExecuteNonQuery()

    MessageBox.Show("Sign Up Successful", "System", MessageBoxButtons.OK, MessageBoxIcon.Information)

    con.Close()
    con.Dispose()
End Sub

Please help me thanks a lot.

Upvotes: 0

Views: 1785

Answers (2)

Ali Hosseini Nezhad
Ali Hosseini Nezhad

Reputation: 304

MySQL Reserved Words

Change your query:

Dim qry As String = "INSERT INTO tblUsers (Username,`Password`,`Level`) VALUES (@User,@Pass,@lvl)"

Upvotes: 1

Mark Nartea
Mark Nartea

Reputation: 67

These solves my problem Thanks to all who help me.

CREATE TABLE `pos_db`.`tblForLogin` (
    `Username` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL UNIQUE, 
    `Password` VARCHAR(50) NOT NULL,
    `Level` VARCHAR(50) NOT NULL, 
    PRIMARY KEY(Username)
);

Upvotes: 2

Related Questions