CODEWITHSUNDEEP
mysqlvb.net
MooseXD
MooseXD

Reputation: 1

VB.net, MySQL column count doesn't match value count row 1

I'm trying to insert only; name, surname, date of birth, gender and password information. however, I read elsewhere that the reason this isn't working is because the storage type im using is innoDB and that I need to fill in the remaining columns in that table which is employee_id, account level and username. my table looks like this:

1 employee_id int(11) AUTO_INCREMENT

2 account_level int(2)

3 name varchar(20) latin1_swedish_ci

4 surname varchar(20) latin1_swedish_ci

5 username varchar(20) latin1_swedish_ci

6 gender varchar(20) latin1_swedish_ci

7 date_of_birth date

8 password varchar(20) latin1_swedish_ci

My aim is to have every new employee_id to increment by 1, account_level set to 1 by default, and username to be made from surname & first letter of forename & their employee_id

My code:

Private Sub save_btn_Click(sender As Object, e As EventArgs) Handles save_btn.Click
    MysqlConn = New MySqlConnection
    MysqlConn.ConnectionString = "Server =localhost; userid = root; password =;Database = furnituredb; pooling=false;"
    Dim reader As MySqlDataReader

    Try
        MysqlConn.Open()
        Dim Query As String
        Query = "insert into furnituredb.employee_profiles (name, surname, username, gender, date_of_birth, password) values ('" & name_txtbox.Text & surname_txtbox.Text & gender_txtbox.Text & dob_txtbox.Text & password_txtbox.Text & "')"
        command = New MySqlCommand(Query, MysqlConn)
        reader = command.ExecuteReader


        MessageBox.Show("Data saved")
        MysqlConn.Close()

    Catch ex As MySqlException
        MessageBox.Show(ex.Message)
    Finally
        MysqlConn.Dispose()
    End Try

End Sub

I'm new to this so when you answer please explain thoroughly. thank you!

Upvotes: 0

Views: 123

Answers (1)

jmcilhinney
jmcilhinney

Reputation: 54457

You have specified six columns to insert into:

"...(name, surname, username, gender, date_of_birth, password)..."

but you have only provided a single value:

"...('" & name_txtbox.Text & surname_txtbox.Text & gender_txtbox.Text & dob_txtbox.Text & password_txtbox.Text & "')"

Even if you were to provide the values from each control separately, there's still only five controls specified there. You need to provide six distinct values. Doing it your dodgy way, that could look like this:

"...('" & name_txtbox.Text & "', '" & surname_txtbox.Text & "', '" & username_txtbox.Text & "', '" & gender_txtbox.Text & "', '" & dob_txtbox.Text & "', '" & password_txtbox.Text & "')"

or, slightly less dodgy:

String.Format("...('{0}', '{1}', '{2}', '{3}', '{4}', '{5}')", name_txtbox.Text, surname_txtbox.Text, username_txtbox.Text, gender_txtbox.Text, dob_txtbox.Text, password_txtbox.Text)

or:

$"...('{name_txtbox.Text}', '{surname_txtbox.Text}', '{username_txtbox.Text}', '{gender_txtbox.Text}', '{dob_txtbox.Text}', '{password_txtbox.Text}')"

That's a bad way to build SQL code though. You should learn how to use parameters to insert values into SQL code. There are many, MANY places you can learn why and how, including here.

Upvotes: 1

Related Questions