none
calling a procedure of a data adapter and retrieve the output parameter value RRS feed

  • Question

  • I try to call a procedure of a data adapter of a dataset that I have created as RIGHT CLICK PROJECT> NEW ITEM > DATASET. I use a store procedure that already exists on the database. The procedure has three parameters: two input and one output. I don't know how to call the function of the data set: I get runtime error: Object reference not set to an instance of an object.

    I am calling the data table function as this:

    Dim contract_id As String = " "

    daContracts.CrtContract(contract_id, dp_product.SelectedItem.Value, txt_desc.Text)

     

    I have test the existing database store procedure by calling it in the usual way:

     Dim conn As SqlConnection = New SqlConnection("Data Source=Server\instance;Initial Catalog=MyDB;Integrated Security=True")

                Dim cmdCrtContract As New SqlCommand("CrtContract", conn)

                cmdCrtContract.CommandType = CommandType.StoredProcedure

                cmdCrtContract.Parameters.Add("@product_id", SqlDbType.Int)

                cmdCrtContract.Parameters.Add("@contract_description", SqlDbType.NVarChar, 50)

                cmdCrtContract.Parameters.Add("@contract_id", SqlDbType.NVarChar, 50)

                cmdCrtContract.Parameters("@contract_id").Direction = ParameterDirection.Output

                cmdCrtContract.Parameters("@product_id").Value = dp_product.SelectedItem.Value

                cmdCrtContract.Parameters("@contract_description").Value = txt_desc.Text

                conn.Open()

                cmdCrtContract.ExecuteNonQuery()

                contract_id = cmdCrtContract.Parameters("@contract_id").Value

                conn.Close()

     

      And the store procedure returns the value of @contract_id.

     

     I have also tested the data adapter function by doing DATASET>CLICK ON DATA ADAPTER PROCEDURE >  PREVIEW DATA > PARAMETERS INPUT INITIALIZED and it is shown the output parameter with the right value. Surprisingly, I have to enter a blank space as input value of the output parameter.

     

    So, how shall I call the data adapter function?

    Peternac

     

    Tuesday, September 14, 2010 1:50 PM

Answers

  • Hi Peternac,

    If you already have a store procedure exists in the database, you just need to drag it from the server explorer window and drop it on the DataSet designer. That will generate a TableAdapter and a query corresponding to the store procedure. Then you can instantiate one that TableAdapter and call the function, something like :

    Dim tableAdapter As New DataSet1TableAdapters.QueriesTableAdapter
    Dim cusID As String = String.Empty
    tableAdapter.GetCustomerID(10249, cusID)
    Console.WriteLine(cusID)


    The error message indates that you seems forget to instantiate an object before using it. Please check your code carefully.

    Best regards,

    Alex Liang

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Peternac Wednesday, September 15, 2010 11:58 AM
    Wednesday, September 15, 2010 2:43 AM
    Moderator

All replies

  • Hi Peternac,

    If you already have a store procedure exists in the database, you just need to drag it from the server explorer window and drop it on the DataSet designer. That will generate a TableAdapter and a query corresponding to the store procedure. Then you can instantiate one that TableAdapter and call the function, something like :

    Dim tableAdapter As New DataSet1TableAdapters.QueriesTableAdapter
    Dim cusID As String = String.Empty
    tableAdapter.GetCustomerID(10249, cusID)
    Console.WriteLine(cusID)


    The error message indates that you seems forget to instantiate an object before using it. Please check your code carefully.

    Best regards,

    Alex Liang

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Peternac Wednesday, September 15, 2010 11:58 AM
    Wednesday, September 15, 2010 2:43 AM
    Moderator
  • I'm a little unclear what your question is ... what do you mean, how should you call it? Either pass the contract_id parameter by reference, or return contract_id from the method. You haven't shown the method's signature, so I have no idea how you've defined it.

    Also, this sometimes makes a difference ... I always define the ParameterDirection as .InputOutput rather than just .Output.

    What problem are you having?


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Wednesday, September 15, 2010 4:49 AM
  • Thanks mate, I follow your advise and I got it. In addition, I have discovered what was wrong in my existing code. I hadn't instantiated the data adapter object inside the New procedure. After that, the store procedure with output parameters does the job from the data adapter I created following your advise and also from my existing data adapter matching the table. Bye!
    Wednesday, September 15, 2010 11:58 AM