Solved

Retrieve SP Params through VB.NET

Posted on 2006-06-17
6
470 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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
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…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

776 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