The parameterized query '(@Physical_Address_Street varchar(50),@Physical_Address_Local va' expects the parameter '@Physical_Address_Street'

Question

I am very very new to sql server and I am creating a webservice and with the code below I am getting the error above and I am wondering what it is I am doing wrong?

<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

I am looking to just pull values for the columns listed in the query straight from the database. I need all the address info for display in a android app. This would be a read only situation.

Andrew Morton · Accepted Answer · 2013-04-18T18:38:40.757

The column names in your database probably don't start with spaces, so...

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")

But you can make it easier to read by taking advantage of VB.NET XML literals:

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)

Please note that I explicity named the columns to select. This is to make sure that the columns you want back are the columns you get and in the order you want them.

As to the expected parameter error message, you have not assigned a value to any of the parameters. You can do that like this:

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

or like this:

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

EDIT: as you don't need all those parameters,

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>

and don't do all the command.Parameters.Add(...).

Ok So on the parameter do I declare phyAddrStree as a empty string? — yams, Apr 18 '13 at 18:15
@MarkBasler If you want to have the option of not entering a value for some fields, see the link in Dillie-O's answer. — Andrew Morton, Apr 18 '13 at 18:20
Well the values are just whatever gets pulled from the database right? — yams, Apr 18 '13 at 18:24
@MarkBasler Please would you explain the exact intent of the code. — Andrew Morton, Apr 18 '13 at 18:27
I am looking to just pull values for the columns listed in the query straight from the database. I need all the address info for display in a android app. This would be a read only situation. — yams, Apr 18 '13 at 18:31
@MarkBasler So, is the WHERE clause redundant? If you don't need to filter the results on anything, you don't need a WHERE clause (although I suspect you may actually want the `WHERE Location_Type = 1` part). — Andrew Morton, Apr 18 '13 at 18:33
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/28468/discussion-between-mark-basler-and-andrew-morton) — yams, Apr 18 '13 at 18:42

score 1 · Answer 2 · edited May 23 '17 at 11:43

Looking at your query above, it appears you want to display a dynamic set of properties (sometimes Street, sometimes Zip code, etc.) for a fixed location type.

Parameterized queries within SQL Server are designed to work with input parameters, not with columns you want to see in the results.

If you are truly looking to have a dynamic column set in the results, then check out this question on how to do this.

If instead, you are looking for the user to input the address, zip, etc. for the query, then you have your query slightly backwards. It should look like this:

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
    )

Note: You'll want to clean up your formatting above for VB.Net, I just did it this way for more readability. There are also other questions out there if you want to make those fields optionally searchable

Ok so I added the Query above and I got the error "System.Data.SqlClient.SqlException: The parameterized query '(@Physical_Address_Street varchar(50),@Physical_Address_Local va' expects the parameter '@Physical_Address_Street', which was not supplied. " — yams, Apr 18 '13 at 17:22

The parameterized query '(@Physical_Address_Street varchar(50),@Physical_Address_Local va' expects the parameter '@Physical_Address_Street'

2 Answers2