MSSQL expects parameter error, but it is supplied

[itamar82]

I have a SP running on mssql server 2005. When I try to execute the SP through asp I receive back an error that the parameter @City is not supplied, although I clearly do supply it.

Here is my code:

SP:

CREATE PROCEDURE [dbo].[AddResort]
@Id int output,
@Name varchar(100),
@Comments text = NULL,
@Street varchar(100) = NULL,
@Street2 varchar(100) = NULL,
@City varchar(100),
@State varchar(2),
@Zip varchar(11) = NULL
AS
BEGIN
SET NOCOUNT ON;

INSERT INTO resorts (name, comments) VALUES (@Name, @Comments);
SELECT @Id = SCOPE_IDENTITY( );
INSERT INTO resort_address (resort_id, street1, street2, city, state, zip) VALUES
(@Id, @Street, @Street2, @City, @State, @Zip);

SET NOCOUNT OFF;
END

/*************** *************** *************** *************** *************/

Here is the relevant code of the webpage:

Tracer.Add ("[Resort] Save - Inserting New Resort")

' insert into database
set conn = Server.CreateOb ject("ADODB.Con nection")
conn.Open application("db con")

set cmd = Server.CreateOb ject("ADODB.com mand")

cmd.ActiveConne ction = conn
cmd.CommandType = 4
cmd.CommandText = "AddResort"

on error resume next

cmd.Parameters. Append cmd.CreateParam eter("@Id", 3 , 2)
cmd.Parameters. Append cmd.CreateParam eter("@Name",20 0, 1, 100, m_cName)
if (not isempty(m_cComm ents) or len(m_cComments ) > 0) then
cmd.Parameters. Append cmd.CreateParam eter("@Comments ", 200, 1, len(m_cComments )+1, m_cComments)
end if

if (len(m_cStreet) <> 0) then
cmd.Parameters. Append cmd.CreateParam eter("@Street", 200, 1, 100, m_cStreet)
end if

if (len(m_cStreet2 ) <> 0) then
cmd.Parameters. Append cmd.CreateParam eter("@Street2" , 200, 1, 100, m_cStreet2)
end if


cmd.Parameters. Append cmd.CreateParam eter("@City", 200, 1, 100, m_cCity)
cmd.Parameters. Append cmd.CreateParam eter("@State", 200, 1, 2, m_cState)

if (len(m_cZip) <> 0) then
cmd.Parameters. Append cmd.CreateParam eter("@Zip", 200, 1, 11, m_cZip)
end if

dim param
for each param in cmd.Parameters
' prints all parameters correctly!!!!!
Tracer.Add ("Added parameter " & param.Name & ": " & param.Value)

next

cmd.Execute()

m_nId = cmd.Parameters( "@Id").Valu e

if (err.number = 0) then
Tracer.Add ("[Resort] Save - Saved")
m_bIsDirty = false
m_bIsNew = false
Save = true
else
Tracer.Add ("[Resort] Save - Insert Error: " & err.number & "; Desc: " & err.description )
Save = false
end if

on error goto 0

set cmd = nothing
conn.Close: set conn = nothing