Passing only values without parameters for Stored Procedure


The Code that discovers stored procedure parameters in the background is the SqlCommandBuilder.DeriveParameters method. You call the method passing in an SqlCommand object as such:


and it will populate the SqlParametersCollection on the command for you. Only thing you need to do is populate the input parameters with their appropriate values and execute the query.

SqlCommandBuilder.DeriveParameters Example

SqlConnection DBConn = New SqlConnection(ConfigurationManager.AppSettings("ConnectionString"))
'Create command
Dim DBCommand As SqlCommand = DBConn.CreateCommand()
DBCommand.CommandType = CommandType.StoredProcedure

' Stored Procedure Name
DBCommand.CommandText = "StoredProcedureName"
DBCommand.CommandType = CommandType.StoredProcedure

Dim parameterValues As Object() = {Field Values to be added}

' Populate command.Parameters Collection.
' Causes Rountrip to Database.
' Initialize Index of parameterValues Array
Dim index As Integer = 0
' Populate the Input Parameters With Values Provided
For Each parameter As SqlParameter In DBCommand.Parameters
If parameter.Direction = ParameterDirection.Input OrElse parameter.Direction = ParameterDirection.InputOutput Then
parameter.Value = parameterValues(index)
index += 1
End If
Dim DBAdapter As New SqlDataAdapter()
Dim DSValue As New DataSet()
DBAdapter.SelectCommand = DBCommand
GridVew1.DataSource = DSValue


No responses found. Be the first to comment...

  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name: