I have the following scenario;
Dim cmd as New SQLCommand
cmd.Connection = myopenconnection
cmd.CommandText = "usp_getdata"
cmd.Parameters.AddWithValue("@Year", 2008)
cmd.CommandType = StoredProcedure
Dim reader = cmd.ExecuteReader
The application get stuck and keep waiting for a response when the above is excuted. I have tryed to execute the SQL command from SQL Management studio and it works fine and on another copy of the database.
Instead of calling SqlCommand.ExecuteReader(), call SqlCommand.ExecuteNonQuery().
As per this MSDN reference:
ExecuteReader
Executes commands that return rows. For increased performance, ExecuteReader invokes commands using the Transact-SQL sp_executesql system stored procedure. Therefore, ExecuteReader might not have the effect that you want if used to execute commands such as Transact-SQL SET statements.
ExecuteNonQuery
Executes commands such as Transact-SQL INSERT, DELETE, UPDATE, and SET statements.
EDIT:
Or if what you're trying to do is return a scalar value, you can use SqlCommand.ExecuteScalar(). But if you're looking to get data, you need to utilize a SqlDataReader object, like so:
Dim queryString As String = "usp_getdata"
Using connection As New SqlConnection(connectionString)
Dim command As New SqlCommand(queryString, connection)
command.CommandType = CommandType.StoredProcedure
connection.Open()
Dim reader As SqlDataReader = command.ExecuteReader()
' Call Read before accessing data.
While reader.Read()
Console.WriteLine(String.Format("{0}, {1}", _
reader(0), reader(1)))
End While
' Call Close when done reading.
reader.Close()
End Using
usp_getdate implies that ExecuteReader is the right choice.
Very slow/not working code;
Dim cmd As New SQLCommand
cmd.Connection = myopenconnection
cmd.CommandText = "usp_getdata"
cmd.Parameters.AddWithValue("@Year", 2008)
cmd.CommandType = StoredProcedure
Dim reader = cmd.ExecuteReader
The problem was solved by modifiying the above code to;
Dim cmd As New SQLCommand
cmd.Connection = myopenconnection
cmd.Parameters.AddWithValue("@Year", 2008)
cmd.CommandText = "Exec usp_getdata @Year With Recompile"
cmd.CommandType = Text
Dim reader = cmd.ExecuteReader
readervariable.