Database insert only works with integers, not strings

Question

I'm attempting to insert into my database but this code only works when the variables are integers, any reason for this? Could using parameters fix this?

Public Class Floshcods

Private CardCodes As Integer
Private KeyWord As String
Private Definition As String
Public Sub New(ByVal CC As Integer)
    CardCodes = CC
    KeyWord = InputBox("Enter keyword")
    Definition = InputBox("Enter definiton")
End Sub
Public Function GetCardCodes() As Integer
    Return CardCodes
End Function
Public Function GetKeyWord() As String
    Return KeyWord
End Function
Public Function GetDefinition() As String
    Return Definition
End Function
End Class
================================================================
sub new()
Dim numb
        Dim cn = ConnectDB()
        Dim cmd As New MySqlCommand
        Dim sql = "SELECT * FROM " & Tabelname & " ORDER by CardCode desc limit 1"
        cmd.Connection = cn
        cmd.CommandText = sql
        cn.Open()
        Dim result As MySqlDataReader = cmd.ExecuteReader
        result.Read()
        Try
            numb = result.GetString(0)
        Catch ex As Exception
            MessageBox.Show("The selected table has no previous cards")
            numb = 0
        End Try
        cn.Close()
        Dim cardcode As Integer = numb + 1
        Dim p As New Floshcods(cardcode)
        Me.Controls("words" & 0).Text = p.GetKeyWord
        Me.Controls("words" & 1).Text = p.GetDefinition
        sql = "insert into " & Tabelname & " (CardCode,Keyword,Definition) VALUES (" & cardcode & ", " & p.GetKeyWord & ", " & p.GetDefinition & ");"
        cmd.CommandText = sql
        Try
            cn.Open()
            cmd.ExecuteNonQuery()
            cn.Close()
        Catch ex As Exception
            MessageBox.Show("failed to add card")
        End Try
end

If you need more of my code to understand, please ask.

The error I get is MySql.Data.MySqlClient.MySqlException: 'Unknown column 'test' in 'field list'.

score 1 · Accepted Answer · answered Mar 22 '20 at 18:54

Yes, the problem would be solved if you used SQL parameters for the values in the SQL query.

There are a few other changes that you might like to make to your code too:

It is usual to use properties rather than functions for values in a class.
Avoid Try..Catch where possible, e.g. if you can check for a value instead of having an error thrown, do so.
Some classes in .NET need to have .Dispose() called on their instances to tell it to clean up "unmanaged resources". That can be done automatically with the Using statement.
Option Strict On is useful to make sure that variable types are correct. You will also want Option Explict On. Option Infer On saves some keystrokes for when variable declarations have types obvious to the compiler.

So I suggest that you use:

Public Class Floshcods

    Public Property CardCode As Integer
    Public Property Keyword As String
    Public Property Definition As String

    Public Sub New(ByVal cardCode As Integer)
        Me.CardCode = cardCode
        Me.Keyword = InputBox("Enter keyword")
        Me.Definition = InputBox("Enter definiton")
    End Sub

End Class

and

Dim latestCardCode As Integer = 0

Dim sql = "SELECT CardCode FROM `" & Tabelname & "` ORDER BY CardCode DESC LIMIT 1;"

Using cn As New MySqlConnection("your connection string"),
 cmd As New MySqlCommand(sql, cn)
    cn.Open()
    Dim rdr = cmd.ExecuteReader()

    If rdr.HasRows Then
        latestCardCode = rdr.GetInt32(0)
    Else
        MessageBox.Show("The selected table has no previous cards.")
    End If

End Using

Dim cardcode = latestCardCode + 1
Dim p As New Floshcods(cardcode)
Me.Controls("words0").Text = p.Keyword
Me.Controls("words1").Text = p.Definition

sql = "INSERT INTO `" & Tabelname & "` (CardCode,Keyword,Definition) VALUES (@CardCode, @Keyword, @Definition);"

Using cn As New MySqlConnection("your connection string"),
 cmd As New MySqlCommand(sql, cn)
    cmd.Parameters.Add(New MySqlParameter With {.ParameterName = "@CardCode", .MySqlDbType = MySqlDbType.Int32, .Value = cardcode})
    cmd.Parameters.Add(New MySqlParameter With {.ParameterName = "@Keyword", .MySqlDbType = MySqlDbType.VarChar, .Size = 64, .Value = p.Keyword})
    cmd.Parameters.Add(New MySqlParameter With {.ParameterName = "@Definition", .MySqlDbType = MySqlDbType.VarChar, .Size = 99, .Value = p.Definition})

    cn.Open()
    Dim nRowsAdded As Integer = cmd.ExecuteNonQuery()
    cn.Close()

    If nRowsAdded = 0 Then
        MessageBox.Show("Failed to add card.")
    End If

End Using

You will need to change the .MySqlDbType and .Size to match how they are declared in the database.

I put backticks around the table name to escape it in case a reserved word is used or the name has spaces in it.

score 0 · Answer 2 · answered Mar 22 '20 at 23:32

I changed the Functions to Properties in the Floshcods class and moved the InputBoxes to the User Interface code.

Public Class Floshcods

    Public Property CardCodes As Integer
    Public Property KeyWord As String
    Public Property Definition As String

    Public Sub New(ByVal CC As Integer)
        CardCodes = CC
    End Sub

End Class

I pulled out the data access code to separate methods so it will be easy to refactor to a data access layer. Also a method should be doing a single job and your Sub New was doing too much.

Using...End Using blocks ensure that your database objects are closed and disposed even if there is an error.

Always use parameters to avoid Sql injection. Datatypes are guesses. Check your database for the actual types.

Sub New()
    Dim TableName = "SomeTableName"
    Dim numb = GetMaxCarCode(TableName)
    Dim cardcode As Integer = numb + 1
    Dim p As New Floshcods(cardcode)
    p.KeyWord = InputBox("Enter keyword")
    p.Definition = InputBox("Enter definiton")
    Controls("words0").Text = p.KeyWord
    Controls("words1").Text = p.Definition
    InsertFloshcods(TableName, p)
End Sub

Private Function GetMaxCarCode(Tablename As String) As Integer
    Dim numb As Integer
    Using cn As New MySqlConnection("Your connection string."),
            cmd As New MySqlCommand($"Select MAX(CardCode) From {Tablename};", cn)
        cn.Open()
        numb = CInt(cmd.ExecuteScalar)
    End Using
    Return numb
End Function

Private Sub InsertFloshcods(TableName As String, f As Floshcods)
    Using cn As New MySqlConnection("your connection String"),
            cmd As New MySqlCommand($"Insert Into {TableName} (CardCode, Keyword, Definition) Values (@Code, @Keyword, @Def);", cn)
        With cmd.Parameters
            .Add("@Code", MySqlDbType.Int32).Value = f.CardCodes
            .Add("@Keyword", MySqlDbType.VarChar).Value = f.KeyWord
            .Add("@Def", MySqlDbType.VarChar).Value = f.Definition
        End With
        cn.Open()
        cmd.ExecuteNonQuery()
    End Using
End Sub

Database insert only works with integers, not strings

2 Answers2

Linked