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

问题描述

我有这段代码可以从VB中的文本框中的数据更新SQL数据库.我需要使用参数，以防文本中包含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()

这是我la脚的尝试，目的是根据我在网络上看到的内容设置参数，我对此不太了解.

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屋！