Ms sql stored procedures return data without output

Question

Hi everyone, I am trying to find examples of returning data from a stored procedure that has no parameters sent to it and does not have a return output parameter. Although it displays data.

How can I get this from my im code using below?

Dim myCommandSQL As New SqlCommand Dim myReaderSQL As SqlDataReader = Nothing Dim intX As Integer = 0 Dim connSql As SqlConnection Try connSql = New SqlConnection("Server=sqlprod;" & _ "Database=ISS3_PROD;" & _ "User ID=xxx;" & _ "Password=xxx;" & _ "Trusted_Connection=False;") connSql.Open() myCommandSQL.CommandType = CommandType.StoredProcedure myCommandSQL.CommandText = "Select_Prod" Dim sqlParReturn1 As System.Data.SqlClient.SqlParameter = myCommandSQL.Parameters.Add("@return_value", SqlDbType.VarChar) sqlParReturn1.Direction = ParameterDirection.Output myCommandSQL.ExecuteNonQuery() MsgBox(sqlParReturn1) connSql.Close() myCommandSQL.Dispose() 

@return_value I just put in there to see what happens, but I got nothing.

Any help would be great!

David

Answer 1

If you assign a parameter to your command, your stored procedure must accept the parameter. In addition, if you specify the direction as Output , then you should mark this parameter as Output in the stored procedure.

If you want the results of the stored procedure to accept no parameters, delete all rows containing sqlParReturn1 . In addition, your team is not a “no-request” - you request data. To get this you have to do it (I also reorganized your code using some best practices):

 Using connSql As SqlConnection = New SqlConnection(...) connSql.Open() Using myCommandSQL As SqlCommand = connSql.CreateCommand() myCommandSQL.CommandType = CommandType.StoredProcedure myCommandSQL.CommandText = "Select_Prod" Using reader As SqlDataReader = myCommandSQL.ExecuteReader() If reader.HasRows Then While reader.Read() // loops through the rows returned End While End If End Using End Using End Using 

Answer 2

The following is the MSDN documentation when reading data using the ADO datareader. I think their example explains this quite well, so I just copied and pasted the example here. Just replace your SQL setup, and then you just need to call ExecuteReader , and then the while loop, which runs while reader.Read finds the rows. Inside the loop, you can access your columns through reader.Get... using ordinals or column names.

 Private Sub HasRows(ByVal connection As SqlConnection) Using connection Dim command As SqlCommand = New SqlCommand( _ "SELECT CategoryID, CategoryName FROM Categories;", _ connection) connection.Open() Dim reader As SqlDataReader = command.ExecuteReader() If reader.HasRows Then Do While reader.Read() Console.WriteLine(reader.GetInt32(0) _ & vbTab & reader.GetString(1)) Loop Else Console.WriteLine("No rows found.") End If reader.Close() End Using End Sub