Cannot get Stored Procedure parameters in SMO

Hi ,

I have tried it all and I cannot seem to get the stored Procedure parameters

StoredProcedure sp=new StoredProcedure (mActiveDatabase,spName);

foreach (StoredProcedureParameter spParam in sp.Parameters) {

string fldName = spParam.Name;

}

Now I can see in the Debug Visualizer the spName and other properties

however the parameter.count is 0 even though I know for a fact i have 3 parameters.

Why is not getting them?Bug in SMO or AM I missing something.

Using reflection I get them!!

thanks in advance for your replies.

I modified procedure AfterSelect of smoBrowser.vb in the SMOBrowse sample program as shown below to dump the parameters of the selected stored procedure in the object treeview. It seems to work file:

00129Private Sub AfterSelect(ByVal sender As System.Object, ByVal e As System.Windows.Forms.TreeViewEventArgs)

Handles objectTreeView.AfterSelect

00130Dim oldCursor As Cursor = Me.Cursor

00131Me.Cursor = Cursors.WaitCursor

00132

00133Try

00134Me.propertyGrid1.SelectedObject = e.Node.Tag

00135

00136' *********************************************

00137' * If the node clicked is a stored procedure

00138' * then debug its parameters

00139' *********************************************

00140If TypeOf e.Node.Tag Is StoredProcedure Then

00141Dim p As Parameter

00142Dim sp As StoredProcedure

00143sp = CType(e.Node.Tag, StoredProcedure)

00144

00145

00146For Each p In sp.Parameters

00147Debug.WriteLine(p.Name & " " & p.DataType.ToString)

00148Next

00149

00150End If

00151

00152If TypeOf e.Node.Tag Is IScriptable Then

00153Me.textBox1.Text = GetScriptText(CType(e.Node.Tag, IScriptable))

00154Else

00155Me.textBox1.Text = String.Empty

00156End If

00157

00158Catch ex As SmoException

00159Dim emb As New ExceptionMessageBox(ex)

00160emb.Show(Me)

00161Finally

00162Me.Cursor = oldCursor

00163End Try

00164End Sub

Regards,
Joginder Nahil
www.starprint2000.com

|||

You are a star!!!

You are helping me so much thanks!

One question

To assign an image to the node in smoBrowser did you modify "CreateNode"

and depending on the item you assign the relative picture?Is this how you did it?

Thanks again

|||

Not a problem.

That is exacltly what I did. I took the images from SQL Server Management Studio.

Regards,
Joginder Nahil
www.starprint2000.com