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, Dec 04 '18 at 08: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, Dec 04 '18 at 09:07
you cannot use the username as primary key. primary should auto increment when you adding a new value. — parkway, Dec 04 '18 at 09:20
@parkway actually you can if you want to, but extra code is needed to ensure that the values are definitely unique. Anything can be a primary key, but of course not everything can be an auto_increment field. — ADyson, Dec 04 '18 at 09:23
Yes, sir, I got your point, but "Admin11" is different from "admin11" the difference is the other one is in uppercase "A" and the other one is in lowercase "a" is it possible to insert that ? — Mark Nartea, Dec 04 '18 at 09:28
That doesn't answer my question. What error do you get? Saying "I get an error" is no good if you don't tell us exactly what it is. — ADyson, Dec 04 '18 at 09:30
there is two errors for one (Input string was not in a correct format) it highlights the count As Integer = Convert.ToInt32(cmd.ExecuteScalar()). and the second one is duplicate entry for key primary @ADyson — Mark Nartea, Dec 04 '18 at 09:33
`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, Dec 04 '18 at 09:34
The second error is likely because mysql varchar fields are case-insensitive by default. your WHERE BINARY workaround will change the output of that one query, but it doesn't change what the field will actually accept. This has been discussed before and solutions proposed - see https://stackoverflow.com/questions/6448825/sql-unique-varchar-case-sensitivity-question — ADyson, Dec 04 '18 at 09:35
Possible duplicate of [SQL unique varchar case sensitivity question](https://stackoverflow.com/questions/6448825/sql-unique-varchar-case-sensitivity-question) — ADyson, Dec 04 '18 at 09:37
@ADyson yes it can but by doing that need to do extra validation for the code. make sure the database structure is in correct way at early stage so that wont face difficulty at the end of the project we built. — parkway, Dec 05 '18 at 00:44
Thanks for help pro devs, I already fix it. thank you very much!. :) especially to Sir, ADyson & Sir parkway. — Mark Nartea, Dec 05 '18 at 05:45

score 2 · Answer 1 · edited Dec 05 '18 at 09:16

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 05 '18 at 09:16

ADyson

57,178
14
51
63

answered Dec 05 '18 at 05:59

Mark Nartea

67
8

Ali Hosseini Nezhad · Answer 2 · 2018-12-05T13:52:36.183

1

MySQL Reserved Words

Change your query:

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

edited Dec 05 '18 at 13:52

answered Dec 04 '18 at 08:57

Ali Hosseini Nezhad

304
1
10

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()) – Mark Nartea Dec 04 '18 at 09:22
1

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. – ADyson Dec 04 '18 at 09:29
1

@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. – Mary Dec 04 '18 at 17:48
1

@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): https://dev.mysql.com/doc/refman/8.0/en/keywords.html – ADyson Dec 05 '18 at 09:19

VB.Net MySQL How to insert values to the database

2 Answers2

Linked