参数化查询'(@Physical_Address_Street varchar(50)，@Physical_Address_Local va‘)需要参数'@Physical_Address_Street’

Question

我是sql server的新手，我正在创建一个new服务，通过下面的代码，我得到了上面的错误，我想知道我做错了什么？

<WebMethod()> _
Public Function GetAddresses(ByVal skip As Integer, ByVal take As Integer) As FuelStop()
    Dim resultList = New List(Of FuelStop)()
    Using sqlCon As New SqlConnection()
        sqlCon.ConnectionString = "Data Source=(local);Initial Catalog=DEV_DB;User ID=*****;Password=**********"
        Dim command As New SqlCommand("SELECT * FROM Gas_Stations WHERE Location_Type = 1 AND [ Physical_Address_Street] = @Physical_Address_Street AND [ Physical_Address_Local] = @Physical_Address_Local AND [Physical_Address_State] = @Physical_Address_State AND [ Physical_Address_Zip] = @Physical_Address_Zip AND [ Phone_Number] = @Phone_Number")
        command.Parameters.Add("@Physical_Address_Street", SqlDbType.VarChar, 50, "Physical_Address_Street")
        command.Parameters.Add("@Physical_Address_Local", SqlDbType.VarChar, 50, "Physical_Address_Local")
        command.Parameters.Add("@Physical_Address_State", SqlDbType.VarChar, 50, "Physical_Address_State")
        command.Parameters.Add("@Physical_Address_Zip", SqlDbType.VarChar, 50, "Physical_Address_Zip")
        command.Parameters.Add("@Phone_Number", SqlDbType.VarChar, 50, "Phone_Number")

        command.Connection = sqlCon
        sqlCon.Open()

        Using reader = command.ExecuteReader()
            While reader.Read()
                Dim addr = New FuelStop()
                addr.Physical_Address_Street = reader.GetString(0)
                addr.Physical_Address_Local = reader.GetString(1)
                addr.Physical_Address_State = reader.GetString(2)
                addr.Physical_Address_Zip = reader.GetString(3)
                addr.Phone_Number = reader.GetString(4)

                resultList.Add(addr)
            End While
        End Using
    End Using
    Return resultList.Skip(skip).Take(take).ToArray()
End Function

我希望直接从数据库中提取查询中列出的列的值。我需要所有的地址信息显示在一个安卓应用程序。这将是一个只读的情况。

Answer 1

数据库中的列名可能不是以空格开头，所以...

Dim command As New SqlCommand("SELECT * FROM Gas_Stations WHERE Location_Type = 1 AND [Physical_Address_Street] = @Physical_Address_Street AND [Physical_Address_Local] = @Physical_Address_Local AND [Physical_Address_State] = @Physical_Address_State AND [Physical_Address_Zip] = @Physical_Address_Zip AND [Phone_Number] = @Phone_Number")

但是，您可以通过利用VB.NET XML文本使其更易于阅读：

Dim sql = <sql>
        SELECT
            [Physical_Address_Street]
            , [Physical_Address_Local]
            , [Physical_Address_State]
            , [Physical_Address_Zip]
            , [Phone_Number]
        FROM Gas_Stations
        WHERE Location_Type = 1
            AND [Physical_Address_Street] = @Physical_Address_Street
            AND [Physical_Address_Local] = @Physical_Address_Local
            AND [Physical_Address_State] = @Physical_Address_State
            AND [Physical_Address_Zip] = @Physical_Address_Zip
            AND [Phone_Number] = @Phone_Number
        </sql>

Dim command As New SqlCommand()
command.CommandText = CStr(sql)

请注意，我显式地命名了要选择的列。这是为了确保您想要返回的列是您获得的列，并且是您想要它们的顺序。

对于预期的参数错误消息，您尚未为任何参数赋值。你可以这样做：

command.Parameters.Add("@Physical_Address_Street", SqlDbType.VarChar, 50).Value = physAddrStreet

或者像这样：

command.Parameters.Add("@Physical_Address_Street", SqlDbType.VarChar, 50)
command.Parameters("@Physical_Address_Street").Value = physAddrStreet

编辑:由于您不需要所有这些参数，

Dim sql = <sql>
        SELECT
            [Physical_Address_Street]
            , [Physical_Address_Local]
            , [Physical_Address_State]
            , [Physical_Address_Zip]
            , [Phone_Number]
        FROM Gas_Stations
        WHERE Location_Type = 1
        </sql>

而且不要做所有的command.Parameters.Add(...)。

Answer 2

查看上面的查询，您似乎希望显示一组动态的属性(有时是街道，有时是邮政编码，等等)。对于固定位置类型。

SQL Server中的参数化查询旨在处理输入参数，而不是要在结果中显示的列。

如果您确实希望在结果中设置动态列，请查看this question了解如何做到这一点。

相反，如果您希望用户输入查询的地址、邮政编码等，那么您的查询就稍微有点倒退了。它应该看起来像这样：

Dim command As New SqlCommand
   ("SELECT * FROM Gas_Stations 
      WHERE Location_Type = 1
            AND
            Physical_Address_Street = @Physical_Address_Street 
            AND 
            Physical_Address_Local = @Physical_Address_Local 
            AND 
            Physical_Address_State = @Physical_Address_State
            AND
            Physical_Address_Zip = @Physical_Address_Zip
            AND 
            Phone_Number = @Phone_Number
    )

注意:您需要为VB.Net清理上面的格式，我这样做只是为了更具可读性。如果您想要使这些字段可供选择地搜索，也可以使用other questions