Problems calling a stored procedure from VB.NET

Question

As you'll probably see very soon, I'm a complete newbie to VB.NET, and I'm having trouble getting results from a stored procedure in SQL Server 2005.

This is the code I'm using.

    Dim con As New SqlConnection
    Dim cmd As New SqlCommand("esp_getDates", con)
    Dim par As New SqlParameter("@PlaceID", SqlDbType.Int, 3906)
    con.ConnectionString = "Data Source=localhost\SQLEXPRESS;Initial Catalog=ProgramDB;User ID=test;Password=test"
    con.Open()
    cmd.CommandType = CommandType.StoredProcedure
    cmd.Parameters("@PlaceID").Direction = ParameterDirection.Output
    cmd.ExecuteNonQuery()
    con.Close()

I get an error;

The SqlParameter parameter with the Name parameter '@PlaceID' is not contained in this SqlParameterCollection.

Does anyone see what I'm doing wrong / have suggestions, how can I fix this? Code examples would be very helpful, and any help would be greatly appreciated.

Answer 1

In fact, you are not adding a parameter to the cmd.Parameters collection:

cmd.Parameters.Add(par)

Parameter:

cmd.Parameters.Add("@PlaceID", SqlDbType.Int)
cmd.Parameters("@PlaceID").Value = 3906

, :

Dim con As New SqlConnection("Data Source=localhost\SQLEXPRESS;Initial Catalog=ProgramDB;User ID=test;Password=test")
con.Open()

Dim cmd As New SqlCommand("esp_getDates", con)

Try
    cmd.CommandType = CommandType.StoredProcedure
    cmd.Parameters.Add("@PlaceID", SqlDbType.Int)
    cmd.Parameters("@PlaceID").Value = 3906
    cmd.ExecuteNonQuery()

Finally
    If cmd IsNot Nothing Then cmd.Dispose()
    If cn IsNot Nothing AndAlso cn.State <> ConnectionState.Closed Then cn.Close()
End Try