时间戳

首页 > 解决方案 > 数据库插入仅适用于整数，不适用于字符串

vb.net - 数据库插入仅适用于整数，不适用于字符串

问题描述

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

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

如果您需要更多我的代码来理解，请询问。

我得到的错误是MySql.Data.MySqlClient.MySqlException: 'Unknown column 'test' in 'field list'。

标签： vb.net

解决方案

是的，如果您对 SQL 查询中的值使用 SQL 参数，问题就会得到解决。

您可能还想对代码进行一些其他更改：

  • 通常对类中的值使用属性而不是函数。
  • 尽可能避免 Try..Catch，例如，如果您可以检查一个值而不是抛出一个错误，那么就这样做。
  • .NET 中的某些类需要.Dispose()调用它们的实例来告诉它清理“非托管资源”。这可以通过Using语句自动完成。
  • Option Strict On有助于确保变量类型正确。你也会想要Option Explict On。Option Infer On当变量声明的类型对编译器很明显时，可以节省一些击键。

所以我建议你使用：

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

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

如果使用了保留字或名称中包含空格，我会在表名称周围放置反引号以将其转义。

推荐阅读