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
Re: VB + Update MySQL table 80 80

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.

Re: VB + Update MySQL table 80 80

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.

Re: VB + Update MySQL table 80 80

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.")
Re: VB + Update MySQL table 80 80

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()
Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.