VB.Net MySQL How to insert values to the database

Question

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.

how your database looks like? is there is primary key is used in the database? — parkway
– parkway, Commented Dec 4, 2018 at 8:31
Yes, Sir, Username is my primary key. but when I insert another value just like what I illustrate on the top I got an error. — Mark Nartea
– Mark Nartea, Commented Dec 4, 2018 at 9:07
you cannot use the username as primary key. primary should auto increment when you adding a new value. — parkway
– parkway, Commented Dec 4, 2018 at 9:20
count As Integer = Convert.ToInt32(cmd.ExecuteScalar() fails because your query returns a username (i.e. some text), not a number. Changing to SELECT Count(Username) would do the job. — ADyson
– ADyson, Commented Dec 4, 2018 at 9:34
Possible duplicate of SQL unique varchar case sensitivity question — ADyson
– ADyson, Commented Dec 4, 2018 at 9:37

ADyson · Accepted Answer · 2018-12-05 09:16:08Z

2

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)
);

edited Dec 5, 2018 at 9:16

ADyson

62.9k16 gold badges85 silver badges98 bronze badges

answered Dec 5, 2018 at 5:59

Mark Nartea

679 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ali Hosseini Nezhad · Accepted Answer · 2018-12-05 13:52:36Z

1

MySQL Reserved Words

Change your query:

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

edited Dec 5, 2018 at 13:52

answered Dec 4, 2018 at 8:57

Ali Hosseini Nezhad

3041 silver badge10 bronze badges

4 Comments

Mark Nartea Over a year ago

thanks for the help sir. but its not working hehe. this is the error Input string was not in a correct format. it highlights the Dim count As Integer = Convert.ToInt32(cmd.ExecuteScalar())

ADyson Over a year ago

Sorry I don't get the point of this answer. You've put Username, Password and Level in quotes...but none of them appear in the list of reserved words you've linked to.

Mary Over a year ago

@ADyson This answer has added the back tick, which is the escape character in MySql. It is assuming that the error was caused by keywords being used as field names.

ADyson Over a year ago

@Mary I'm not disputing that, but my point was that in the answer there is a link to a list of reserved words, but none of the field names above appear in that list. If someone is going to use some documentation to back up their answer, it would make sense if that documentation actually contains the relevant proof. And it's always better to link to the official documentation anyway (which does contain PASSWORD and LEVEL listed as reserved words): dev.mysql.com/doc/refman/8.0/en/keywords.html

Collectives™ on Stack Overflow

VB.Net MySQL How to insert values to the database

2 Answers 2

Comments

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Linked

Related