Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Retrieve SP Params through VB.NET

Posted on 2006-06-17
6
471 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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VB.NET - Error - Object not set in Module 4 39
Winform Module - What is the ASP.Net equiv 2 28
vb.net class 3 21
Need syntax help building hyperlink 2 25
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

860 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