数据库插入仅适用于整数，不适用于字符串 [英] Database insert only works with integers, not strings

问题描述

我正在尝试插入到我的数据库中，但此代码仅在变量为整数时才有效，有什么原因吗?可以使用参数解决这个问题吗?

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.

我得到的错误是MySql.Data.MySqlClient.MySqlException:'字段列表'中的'未知列'测试'.

推荐答案

是的，如果对 SQL 查询中的值使用 SQL 参数，问题将得到解决.

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:

• 通常对类中的值使用属性而不是函数.
• 尽可能避免 Try..Catch，例如如果您可以检查值而不是引发错误，请这样做.
• .NET 中的某些类需要在它们的实例上调用 .Dispose() 以告诉它清理非托管资源".这可以通过 <代码>使用语句.
• Option Strict On 有助于确保变量类型正确.您还需要 Option Explict On.Option Infer On 为当变量声明具有对编译器显而易见的类型时节省了一些按键操作.

• 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.

所以我建议你使用:

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

和

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

您需要更改 .MySqlDbType 和 .Size 以匹配它们在数据库中的声明方式.

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.

这篇关于数据库插入仅适用于整数，不适用于字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！