如何使用参数“@"在 VB 中的 SQL 命令中 [英] How to use parameters "@" in an SQL command in VB

问题描述

我有这段代码可以从文本框中的数据更新我的 SQL 数据库，在 VB 中.如果文本包含 tic 标记 ,' 或引号 ," 等，我需要使用参数.这是我所拥有的:

I have this code to update my SQL database from data in a textbox, in VB. I need to use parameters in case the text contains a tic mark ,', or a quote ,", etc. Here is what I have:

dbConn = New SqlConnection("server=.SQLEXPRESS;Integrated Security=SSPI; database=FATP")
    dbConn.Open()

    MyCommand = New SqlCommand("UPDATE SeansMessage SET Message = '" & TicBoxText.Text & _
                                            "'WHERE Number = 1", dbConn)

    MyDataReader = MyCommand.ExecuteReader()
    MyDataReader.Close()
    dbConn.Close()

这是我从网上看到的设置参数的蹩脚尝试，我不太了解.

And this is my lame attempt to set a parameter from what I have seen on the web, which I don't understand all that well.

dbConn = New SqlConnection("server=.SQLEXPRESS;Integrated Security=SSPI; database=FATP")
    dbConn.Open()

    MyCommand = New SqlCommand("UPDATE SeansMessage SET Message = @'" & TicBoxText.Text & _
                                            "'WHERE Number = 1", dbConn)

    MyDataReader = MyCommand.ExecuteReader()
    MyDataReader.Close()
    dbConn.Close()

你是怎么做到的?因为如果我运行代码时文本框中有一个 ' 标记，它就会崩溃.

How do you do this? Cause if there is a ' mark in the textbox when I run the code, it crashes.

推荐答案

避免Bobby Tables，但是你对@参数的理解是不完整的.

You are on the right path to avoiding Bobby Tables, but your understanding of @ parameters is incomplete.

命名参数的行为类似于编程语言中的变量:首先，您在 SQL 命令中使用它们，然后在 VB.NET 或 C# 程序中提供它们的值，如下所示:>

Named parameters behave like variables in a programming language: first, you use them in your SQL command, and then you supply their value in your VB.NET or C# program, like this:

MyCommand = New SqlCommand("UPDATE SeansMessage SET Message = @TicBoxText WHERE Number = 1", dbConn)
MyCommand.Parameters.AddWithValue("@TicBoxText", TicBoxText.Text)

请注意您的命令文本是如何变得独立的:它不再依赖于文本框中文本的值，因此用户无法通过插入他们自己的命令来破坏您的 SQL.@TicBoxText 成为变量名，代表命令文本中的值；对 AddWithValue 的调用提供了该值.之后，您的 ExecuteReader 就可以使用了.

Note how the text of your command became self-contained: it no longer depends on the value of the text from the text box, so the users cannot break your SQL by inserting their own command. @TicBoxText became a name of the variable that stands for the value in the text of the command; the call to AddWithValue supplies the value. After that, your ExecuteReader is ready to go.

这篇关于如何使用参数“@"在 VB 中的 SQL 命令中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！