My Insert statement is not working for some reason and I cannot figure out why. Any help would be highly appriciated.

Imports MySql.Data
Imports MySql.Data.MySqlClient

Public Class frmCreateUser
    Dim connectionstring As String
    Dim dbCon As MySqlConnection
    Dim strQuery As String = ""
    Dim SQLCmd As MySqlCommand
    Dim DR As MySqlDataReader
    Dim mysqladapter As MySqlDataAdapter
    Dim c As New Class1
    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Try
            'dbCon = New MySqlConnection("Server = localhost; Database=test;uid=root;Pwd=")
            dbCon = New MySqlConnection("SERVER=localhost;DATABASE=test;")
            If txtPwd.Text <> txtRetypePwd.Text Then c.StdMsg("Retype password is not matching.") : Exit Sub
            If txtPwd.Text = "" Then c.StdMsg("Password required.") : Exit Sub
            If txtUser.Text = "" Then c.StdMsg("Username required.") : Exit Sub
            If ComboBox1.Text = "" Then c.StdMsg("Usertype required.") : Exit Sub

            'Dim id As Integer = c.maxSelect("id", "logintest")

            strQuery = "INSERT INTO login (username, password) values (" & txtUser.Text & "', '" & txtPwd.Text & "');"


            'strQuery = "INSERT INTO logintest (id, username, password, type) VALUES (" & id & ", '" & txtUser.Text & "','" & txtPwd.Text & "','" & ComboBox1.Text & "');"

            SQLCmd = New MySqlCommand(strQuery, dbCon)

            dbCon.Open()

            c.StdMsg("Record Saved.")
            Me.Hide()
            btnClear_Click(sender, e)
        Catch ex As Exception
            MsgBox("Failure to communicate!" & vbCrLf & vbCrLf & ex.Message)
        End Try



        dbCon.Close()


    End Sub

Try changing

strQuery = "INSERT INTO login (username, password) values (" & txtUser.Text & "', '" & txtPwd.Text & "');"

to

strQuery = "INSERT INTO login (username, [password]) values ('" & txtUser.Text & "', '" & txtPwd.Text & "')"

Looks like you missed the single quote before the first value. You might want to read up on parameterized entry and how to avoid SQL injection attacks. Also, password is likely a reserved word. Note I used [ and ] around password in the rewritten query.

Hello and thank you for the tips. I just wanna get it working properly and step up to optimazing it.

However, I get the message that the record has been saved but I still don't see the user saved into the table.

You set the SQL command and opened the connection to the database but you didn't execute the query.

SQLCmd = New MySqlCommand(strQuery, dbCon)
dbCon.Open()
c.StdMsg("Record Saved.")

Actually thank you very much, it works now.

 S
        strQuery = "INSERT INTO login (username, password) values ('" & txtUser.Text & "', '" & txtPwd.Text & "')"
        'strQuery = "INSERT INTO logintest (id, username, password, type) VALUES (" & id & ", '" & txtUser.Text & "','" & txtPwd.Text & "','" & ComboBox1.Text & "');"

        SQLCmd = New MySqlCommand(strQuery, dbCon)

        dbCon.Open()

        DR = SQLCmd.ExecuteReader


        c.StdMsg("You've created a new user successfully!")
        Me.Hide()
        btnClear_Click(sender, e)
    Catch ex As Exception
        MsgBox("Failure to communicate!" & vbCrLf & vbCrLf & ex.Message)
    End Try


    DR.Close()
    dbCon.Close()

Edited 4 Years Ago by SQLpower

This question has already been answered. Start a new discussion instead.