Solved

Retrieve SP Params through VB.NET

Posted on 2006-06-17
6
468 Views
Last Modified: 2008-03-17
Hi,

I want the user to execute SP through VB.NET and see the return results in the grid.

For this I will display all SPs in the combo box. Once the user selects the SP, how do I find out how many parameter values to take input from user? Also after finding the number of params, I also need to validate the user input for each of the param based on its data type. So how do I find:
a. Number of params for that SP
b. Data type of each param.

Regards,
MI
0
Comment
Question by:vj_mi
  • 4
6 Comments
 
LVL 6

Expert Comment

by:manch
ID: 16926395
Code To Fetch Data from SP and display on Grid :

        Dim pObjSQLConnection As SqlConnection      'Connection Object
        Dim psConStr As String                      'Connection String
        Dim pObjSQLCommand As SqlCommand            'Command Object
        Dim pObjSQLAdapter As SqlDataAdapter        'Adapter Object
        Dim pdsDataSet As DataSet                   'DataSet
        Dim pdtData As DataTable                    'Data Table
        Dim pObjSQLCommandBuilder As SqlCommandBuilder

        'Write Connection String Here
        psConStr = ""
        'Instantiate the Connectin Object
        pObjSQLConnection = New SqlConnection(psConStr)

        'Open the connection
        pObjSQLConnection.Open()

        'Command Object Instancing
        pObjSQLCommand = New SqlCommand("sp_test", pObjSQLConnection)
        pObjSQLCommand.CommandType = CommandType.StoredProcedure

        'Instancing the Adapter Object
        pObjSQLAdapter = New SqlDataAdapter(pObjSQLCommand)

        'Instantiate the Command Builder
        pObjSQLCommandBuilder = New SqlCommandBuilder(pObjSQLAdapter)

        'Instantiate the Dataset and Table
        pdsDataSet = New DataSet
        pdtData = New DataTable

        'Use the Adapter to fill the Data Set
        pObjSQLAdapter.Fill(pdsDataSet)

        'U Can Have the Table for ur furthur use
        pdtData = pdsDataSet.Tables(0)

        DataGrid1.DataSource = pdtData

       'Dispose all objects after use
0
 
LVL 7

Expert Comment

by:pradeepsudharsan
ID: 16926403
Use the SqlCommandBuilder and its DeriveParameters method.
0
 
LVL 7

Expert Comment

by:pradeepsudharsan
ID: 16926408
 SqlConnection1.Open()
        SqlCommand2.CommandType = CommandType.StoredProcedure
        SqlCommand2.CommandText = "sp_testProc"
        SqlCommand2.Connection = SqlConnection1

        Dim a As SqlCommandBuilder = New SqlCommandBuilder
        a.DeriveParameters(SqlCommand2)
        cboValues1.DataSource = SqlCommand2.Parameters
        cboValues1.DataBind()
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 6

Assisted Solution

by:Nandakumar Sakthivel
Nandakumar Sakthivel earned 150 total points
ID: 16926458
Hi,
   As per my understanding of the question, i think that you had a drop down box which shows the list of stored procedures on selecting a s tored procedure you need to dynamically bring the parameters and their corresponding data types in the screen
    There is a procedure named "sp_sproc_columns" which will return all the details about the stored procedure .Executing the stored procedure will return nearly 20 columns.Among them returned columns column number 3 will hold the name of the parameter and column number 6 will hold the data type of the parameter.
   You can use a datareader to loop thro' the values

            Dim sqlCmd As SqlCommand
            Dim SqlCon As SqlConnection
            Dim trans As SqlTransaction
            Dim dr As SqlDataReader
            SqlCon = New SqlConnection(<conn string>)
            SqlCon.Open()
            sqlCmd = New SqlCommand
            trans = SqlCon.BeginTransaction
            sqlCmd.CommandTimeout = 0
            sqlCmd.Connection() = SqlCon
            sqlCmd.CommandType = CommandType.StoredProcedure
            sqlCmd.Transaction = trans
            sqlCmd.CommandText = "sp_sproc_columns"
            sqlCmd.Parameters.Add("@procedure_name", SqlDbType.NVarChar)
            sqlCmd.Parameters.Add("@procedure_owner", SqlDbType.NVarChar)
            sqlCmd.Parameters.Add("@procedure_qualifier", SqlDbType.NVarChar)
            sqlCmd.Parameters.Add("@column_name ", SqlDbType.NVarChar)
            sqlCmd.Parameters.Add("@ODBCVer ", SqlDbType.Int)
            dr = sqlCmd.ExecuteReader

            SqlCon.close()
 
You can test the procedure in the query analyser itself

        exec  sp_sproc_columns '<name of the procedure>','<owner>',Null,NUll,2

which will return you all the details abt the sp

Thanks,
Nanda

     
0
 
LVL 7

Expert Comment

by:pradeepsudharsan
ID: 16926504
SqlConnection1.Open()
        SqlCommand2.CommandType = CommandType.StoredProcedure
        SqlCommand2.CommandText = "sp_testProc"
        SqlCommand2.Connection = SqlConnection1
        Dim a As SqlCommandBuilder = New SqlCommandBuilder
        a.DeriveParameters(SqlCommand2)
        'cboValues1.DataSource = SqlCommand2.Parameters
        'cboValues1.DataBind()
        Dim count1 As Integer
        Dim input As New Hashtable
        Dim output As New Hashtable
        Dim inputoutput As New Hashtable
        For count1 = 1 To SqlCommand2.Parameters.Count - 1
            If (SqlCommand2.Parameters.Item(count1).Direction = ParameterDirection.Input) Then
                input.Add(count1, SqlCommand2.Parameters.Item(count1).ToString())
            ElseIf (SqlCommand2.Parameters.Item(count1).Direction = ParameterDirection.Output Or SqlCommand2.Parameters.Item(count1).Direction = ParameterDirection.ReturnValue) Then
                output.Add(count1, SqlCommand2.Parameters.Item(count1).ToString())
            ElseIf (SqlCommand2.Parameters.Item(count1).Direction = ParameterDirection.InputOutput) Then
                inputoutput.Add(count1, SqlCommand2.Parameters.Item(count1).ToString())
            End If
        Next count1
        cboValues1.DataSource = input.Values
        cboValues1.DataBind()
        cboValues2.DataSource = output.Values
        cboValues2.DataBind()
        cboValues.DataSource = inputoutput.Values
        cboValues.DataBind()

0
 
LVL 7

Accepted Solution

by:
pradeepsudharsan earned 350 total points
ID: 16926524
'Consider this only
        'This will display the stored procedure parameters and their data types
        SqlConnection1.Open()
        SqlCommand2.CommandType = CommandType.StoredProcedure
        SqlCommand2.CommandText = "spDOC_DocDiscussionReplyInsert"
        SqlCommand2.Connection = SqlConnection1
        Dim a As SqlCommandBuilder = New SqlCommandBuilder
        a.DeriveParameters(SqlCommand2)
        Dim count1 As Integer
        Dim input As New Hashtable
        Dim output As New Hashtable
        Dim inputoutput As New Hashtable
        For count1 = 1 To SqlCommand2.Parameters.Count - 1
            If (SqlCommand2.Parameters.Item(count1).Direction = ParameterDirection.Input) Then
                input.Add(count1, SqlCommand2.Parameters.Item(count1).ToString() + " " + SqlCommand2.Parameters.Item(count1).SqlDbType.ToString())
            ElseIf (SqlCommand2.Parameters.Item(count1).Direction = ParameterDirection.Output Or SqlCommand2.Parameters.Item(count1).Direction = ParameterDirection.ReturnValue) Then
                output.Add(count1, SqlCommand2.Parameters.Item(count1).ToString() + " " + SqlCommand2.Parameters.Item(count1).SqlDbType.ToString())
            ElseIf (SqlCommand2.Parameters.Item(count1).Direction = ParameterDirection.InputOutput) Then
                inputoutput.Add(count1, SqlCommand2.Parameters.Item(count1).ToString() + " " + SqlCommand2.Parameters.Item(count1).SqlDbType.ToString())
            End If
        Next count1
        cboValues1.DataSource = input.Values
        cboValues1.DataBind()
        cboValues2.DataSource = output.Values
        cboValues2.DataBind()
        cboValues.DataSource = inputoutput.Values
        cboValues.DataBind()
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now