passing parameters to sql sp

[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

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

input string format error is returned, indicating, if I
understand correctly, a parameter mismatch.

 

[Greg Burns]

The disadvantage is the overhead of making two trips to the database.

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

Greg