Problems calling a Stored Procedure from VB.NET
As you can probably ve开发者_StackOverflowry soon see, I am a complete newbie at VB.NET, and I am having some trouble getting output from a stored procedure in SQL Server 2005.
This is the code I use
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 the error;
An SqlParameter with ParameterName '@PlaceID' is not contained by this SqlParameterCollection.
Does anyone see what I'm doing wrong / have any suggestions how I can fix it? Code examples would be very helpful and any help is much appreciated.
You aren't actually adding the parameter to the cmd.Parameters collection:
cmd.Parameters.Add(par)
Alternatively, just add the parameter without instantiating the Parameter object explicitly:
cmd.Parameters.Add("@PlaceID", SqlDbType.Int)
cmd.Parameters("@PlaceID").Value = 3906
Also, I'd follow the principle of using a variable as close to the declaration as possible and reorganize things this way:
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
精彩评论