Visual basic Create Users in database access

Question

I'm doing a program and I'm in a part that I need to create users in the database in access. But after entering the data and clicking the create user button it gives me an error. If you add users to the database it works but if you try to add it by the program's the result is a syntax error

Else
    Try
        Dim myconnection As OleDbConnection
        Dim constring As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\Software_Simples\Software\DataBase.accdb"
        myconnection = New OleDbConnection(constring)
        myconnection.Open()
        Dim sqlQry As String

        'sqlQry = "INSERT INTO Usernames(Username, Password) VALUES(" & TextBox2.Text & "," & TextBox3.Text & ")"
        sqlQry = "INSERT INTO Usernames(ID_User, Username, Password) VALUES(Null,TextBox2.Text,TextBox3.Text )"

        Dim cmd As New OleDbCommand(sqlQry, myconnection)
        cmd.ExecuteNonQuery()

        myconnection.Close()
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
    'Fechar
    Me.Close()
    MessageBox.Show("Utilizador criado com suecesso: " & username_create, "Aviso", MessageBoxButtons.OK)
End If

Yes that's the correct way to post code. Now back to your problem. As I have said. Password is a reserved keyword. If you use it from ADO.NET you need to put it between square brackets. — Steve
– Steve, Commented Feb 15, 2017 at 15:28
You can write the code, I'm not quite sure what you're saying. — Jorge Pereira
– Jorge Pereira, Commented Feb 15, 2017 at 15:30
If the field is an AutoNumber then do not write anything for it (no fieldname no null value) simply ignore it. — Steve
– Steve, Commented Feb 15, 2017 at 15:35

Steve · Accepted Answer · 2017-02-15 15:45:12Z

1

There are at least three problems in your code:
1) Password is a reserved keyword. Needs to be between square brackets
2) String values when passed to a database need to be between single quotes, but.. see point 3
3) Use parameters instead of string concatenation (what if password contains a single quote?)
4) Dispose the disposable object like the connection and the command.

So rewriting it

Dim constring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\Software_Simples\Software\DataBase.accdb"
Dim sqlQry = "INSERT INTO Usernames(Username, [Password]) VALUES(@name, @pwd)"
Try
    Using myconnection = New OleDbConnection(constring)
    Using cmd As New OleDbCommand(sqlQry, myconnection)
        myconnection.Open()
        cmd.Parameters.Add("@name", OleDbType.VarWChar).Value = TextBox2.Text
        cmd.Parameters.Add("@pwd", OleDbType.VarWChar).Value = TextBox3.Text
        cmd.ExecuteNonQuery()
    End Using
    End Using
Catch ex As Exception
    MessageBox.Show(ex.Message)
End Try

edited Feb 15, 2017 at 15:45

answered Feb 15, 2017 at 15:40

Steve

217k22 gold badges242 silver badges296 bronze badges

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

4 Comments

Jorge Pereira Over a year ago

Do not miss Dim sqlQry as string

Steve Over a year ago

Corrected. Really there is no need to as String when the compiler can see from the context the type of the variable to be assigned. (But Dim yes)

Jorge Pereira Over a year ago

i got more How do I make an if to see if the user I created already exists and if it does not give error msg

Steve Over a year ago

@JorgePereira please one problem at time. If questions here help to solve your problem please accept one then post a new question with the new details

Gustav · Accepted Answer · 2017-02-15 15:29:47Z

0

It should read:

sqlQry = "INSERT INTO Usernames (ID_User, Username, [Password]) VALUES (Null,'" + TextBox2.Text + "','" + TextBox3.Text + "')"

answered Feb 15, 2017 at 15:29

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

1 Comment

Gustav Over a year ago

I've seen similar but not a duplicate.

Collectives™ on Stack Overflow

Visual basic Create Users in database access

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related