MySQL Query Browser Error Data truncated for column

Question

This is my code and I dont know why I'm getting this Error in my visual studio 2013 and my data base is MySQL Query Browser:

"Additional information: ERROR [HY000] [MySQL][ODBC 3.51 Driver][mysqld-5.1.34-community]Data truncated for column 'userid' at row 1"

If a = "New" Then Dim sqlstring As String sqlstring = "INSERT into users(username, userid, usertype, remarks)values('" & TextBox1.Text & "','" & TextBox2.Text & "','" & ComboBox1.Text & "','" & TextBox4.Text & "')" cmd = New Odbc.OdbcCommand(sqlstring, cnn) cmd.ExecuteNonQuery() reset() disable() btn3() End If

The issue is most likely that the value you passed for that column is too long for the column definition. Apart from that, NEVER use string concatenation to insert values into SQL code like that. ALWAYS use parameters. — jmcilhinney
– jmcilhinney, Commented Jan 19, 2018 at 2:07
Also, Odbc should be your last choice for an ADO.NET provider. If you're using MySQL then, if you can, you should download Connector/Net from the MySQL web site and use that. MySqlClient is a MySQL-specific provider, just as SqlClient is specific to SQL Server. You can also download some MySQL-specific tools for VS that support EF and such. — jmcilhinney
– jmcilhinney, Commented Jan 19, 2018 at 2:09

John Woo · Accepted Answer · 2018-01-19 02:46:56Z

This is because the total length of the characters you are passing exceeded the length defined by column userid. Aside from that, mysql has its own managed provider called MySqlClient and this is the one you should be using.

A much better way to good practice programming is to paramaterized your query. Example below is at least your good starting point:

Dim connectionString As String = "..your connection string here..."
Using SQLConnection As New MySqlConnection(connectionString)
   Using sqlCommand As New MySqlCommand()
      sqlCommand.CommandText = "INSERT into users(username, userid, usertype, remarks) VALUES (@username, @userid, @usertype, @remarks)"
      sqlCommand.Connection = SQLConnection
      sqlCommand.CommandType = CommandType.Text
      sqlCommand.Parameters.AddWithValue("@username", TextBox1.Text)
      sqlCommand.Parameters.AddWithValue("@userid", TextBox2.Text)
      sqlCommand.Parameters.AddWithValue("@usertype", ComboBox1.Text)
      sqlCommand.Parameters.AddWithValue("@remarks", TextBox4.Text)
      SQLConnection.Open()
      sqlCommand.ExecuteNonQuery()
   End Using
End Using

Collectives™ on Stack Overflow

MySQL Query Browser Error Data truncated for column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related