passing parameters to sql sp

  • Thread starter Thread starter Jorge
  • Start date Start date
J

Jorge

Hi
I usualy use the following approach to pass parameters.
It has the advantage you don't have to worry about
parameters datatype.

Dim p As New SqlParameter
Dim objCommandBuilder As New SqlCommandBuilder


Me.SqlCommand1.CommandType = CommandType.StoredProcedure
Me.SqlCommand1.CommandText = "proc_name"
Me.SqlCommand1.Parameters.Clear()
objCommandBuilder.DeriveParameters(Me.SqlCommand1)
For Each p In Me.SqlCommand1.Parameters
If p.ParameterName = "@param1" Then p.Value = var1
If p.ParameterName = "@param2" Then p.Value = var2
Next
reader = Me.SqlCommand1.ExecuteReader

Kind Regards
Jorge
-----Original Message-----
I am trying to pass two parameters to a stored
Click to expand...
procedure. In SQL, they are nvarchar 50 in both the table
and as stored procedure variables.
My code is like:

mySQLCommand.parameters.add("@spVarialble1", localString1, _
SQLnvarchar, 50, ParameterDirection.Input)

The SP performs an update and I execute nonquery. An
Click to expand...
input string format error is returned, indicating, if I
understand correctly, a parameter mismatch.
 
The disadvantage is the overhead of making two trips to the database.

This method is usually frowned upon (so I've read)

Greg
 
Back
Top