Solved

Retrieve SP Params through VB.NET

Posted on 2006-06-17
6
469 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

863 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

22 Experts available now in Live!

Get 1:1 Help Now