Executing stored procedure that returns parameters

Executing stored procedure that returns parameters

• BrianMac

  The code you listed looks good. There should be more code tho. At some point you should be passing this command to a DataAdapter so that you can use that to fill a dataset.

  
  

• Hiral

  Hello. What is the exact error you're receiving Just going on appearances I've noticed the following:

  1. There isn't a SQLConnection specified. You can do so in several ways...one might be:

  Dim MyConnection as new SQLConnection(<enter some valid connection string here>)

  TransferCommand.Connection = MyConnection

  2. You haven't specified any parameter information. You can add the above parameters as follows:

  Dim Parameter as SQLParameter

  Parameter = New SqlParameter
  Parameter = TransferCommand.Parameters.Add("@InvalidCount", SqlDbType.Bit)
  Parameter.Direction = ParameterDirection.Output

  Parameter = New SqlParameter
  Parameter = TransferCommand.Parameters.Add("@InputRecords", SqlDbType.Int)
  Parameter.Direction = ParameterDirection.Output

  Parameter = New SqlParameter
  Parameter = TransferCommand.Parameters.Add("@Message", SqlDbType.Varchar)
  Parameter.Direction = ParameterDirection.Output

  3. You then need to execute the command:

  TransferCommand.ExecuteNonQuery

  4. You can query the return parameters as follows:

  Dim InvalidCount as Boolean = TransferCommand.Parameters("@InvalidCount").Value
  Dim InputRecords as Integer = TransferCommand.Parameters("@InputRecords").Value
  Dim Message as String = TransferCommand.Parameters("@Message").Value

   

  
  
  

• GriffinSmith

  Thanks for your help.

  The error is "error BC30456: 'CreateCommand' is not a member of 'String'"

  Here is the complete code:

  Dim stConnection As String

  stConnection = "Data Source=(local);Initial Catalog=Database;Integrated Security=True"

  Dim TransferCommand As SqlCommand = stConnection.CreateCommand()

  TransferCommand.CommandType = CommandType.StoredProcedure

  TransferCommand.CommandText = "spImport"

  'Create return parameter

  Dim paInvalidCount As SqlParameter = TransferCommand.Parameters.Add("@InvalidCount", SqlDbType.SmallInt)

  paInvalidCount.Direction = ParameterDirection.ReturnValue

  Dim paMessage As SqlParameter = TransferCommand.Parameters.Add("@Message", SqlDbType.VarChar)

  paMessage.Direction = ParameterDirection.ReturnValue

  Dim paInputRecords As SqlParameter = TransferCommand.Parameters.Add("@InputRecords", SqlDbType.Bit)

  paInputRecords.Direction = ParameterDirection.ReturnValue

  
  

• Siddharth Ramavat

  Thanks for your help.

  The error is "error BC30456: 'CreateCommand' is not a member of 'String'"

  Here is the complete code:

  Dim stConnection As String

  stConnection = "Data Source=(local);Initial Catalog=Database;Integrated Security=True"

  Dim TransferCommand As SqlCommand = stConnection.CreateCommand()

  TransferCommand.CommandType = CommandType.StoredProcedure

  TransferCommand.CommandText = "spImport"

  'Create return parameter

  Dim paInvalidCount As SqlParameter = TransferCommand.Parameters.Add("@InvalidCount", SqlDbType.SmallInt)

  paInvalidCount.Direction = ParameterDirection.ReturnValue

  Dim paMessage As SqlParameter = TransferCommand.Parameters.Add("@Message", SqlDbType.VarChar)

  paMessage.Direction = ParameterDirection.ReturnValue

  Dim paInputRecords As SqlParameter = TransferCommand.Parameters.Add("@InputRecords", SqlDbType.Bit)

  paInputRecords.Direction = ParameterDirection.ReturnValue

  
  

• taurean

  The problem is exactly as stated. There is no CreateCommand method for a string object. Here's what you need to do:

  Dim stConnection As String

  stConnection = "Data Source=(local);Initial Catalog=Database;Integrated Security=True"

  Dim DbConnection As New SqlConnection(stConnection)

  Dim TransferCommand As SqlCommand = DbConnection.CreateCommand()

  ...

  
  
• Executing stored procedure that returns parameters