0

When trying to call and run a stored procedure stored in sql server management studio in vb.net, I receive an error saying that my stored procedure is not set to an instance of an object. I have tested to see that my stored procedure executes in sql, have a sub in my database class that runs a stored procedure(with no parameters), and called the procedure and passed it with the stored procedure name.

Here is my code for the two subs in my database class:

Public Sub RunProcedure(ByVal strName As String)

    ' CREATES INSTANCES OF THE CONNECTION AND COMMAND OBJECT
    Dim objConnection As New SqlConnection(mstrConnection)
    ' Tell SQL server the name of the stored procedure you will be executing
    Dim mdbDataAdapter As New SqlDataAdapter(strName, objConnection)
    Try
        ' SETS THE COMMAND TYPE TO "STORED PROCEDURE"
        mdbDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure
        ' clear dataset
        Me.mDatasetCustomer.Clear()
        ' OPEN CONNECTION AND FILL DATASET
        mdbDataAdapter.Fill(mDatasetCustomer, "tblCustomers")
        ' copy dataset to dataview
        mMyView.Table = mDatasetCustomer.Tables("tblCustomers")
    Catch ex As Exception
        Throw New Exception("stored procedure is " & strName.ToString & " error is " & ex.Message)
    End Try
End Sub

Public Sub GetAllUsingSP()
    ' purpose: get all customer records with stored procedure
    ' inputs:  none directly
    ' returns: all customer records
    RunProcedure("usp_Customers_Get_All")
End Sub

Here is the code in my actual web form where I call the sub:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        'get all customers from db
        DB.GetAllUsingSP()
        'set up gridview
        gvCustomers.DataSource = DB.CustDataset.Tables("tblCustomers")
        'bind data
        gvCustomers.DataBind()
    End Sub

If someone can help me understand what the problem is, I'd greatly appreciate it!

marc_s
  • 675,133
  • 158
  • 1,253
  • 1,388
Joyce Wang
  • 11
  • 1
  • 1
    When you throw a `new` exception you're essentially throwing away all the good exception details. Remove that and just throw exception. Can you debug your code and tell us where this is happening? – JonH Mar 16 '15 at 16:40
  • where are you setting up the store procedure name in the sql command? – pedrommuller Mar 16 '15 at 16:41
  • @JonH - the error is occuring in this line: Throw New Exception("stored procedure is " & strName.ToString & " error is " & ex.Message) – Joyce Wang Mar 16 '15 at 16:53
  • What is tblCustomers is that the name of your stored procedure? Where in your code do you execute the sproc? – JonH Mar 16 '15 at 16:55
  • @jack.the.ripper- Not sure this is what you're asking, but I'm creating/ setting up the stored procedure name under stored procedures in sqlserver – Joyce Wang Mar 16 '15 at 16:57
  • Here's an example of how I call a sproc: `using (SqlConnection conMyData = new SqlConnection(connString)) { using (SqlCommand dbCommand = new SqlCommand("mySproc")) { dbCommand.Connection = conMyData; dbCommand.CommandType = CommandType.StoredProcedure; dbCommand.Parameters.Add("@ChecklistSubTypeID", SqlDbType.Int).Value = checklistSubTypeID; SqlDataAdapter dbAdapter = new SqlDataAdapter {SelectCommand = dbCommand};` – JonH Mar 16 '15 at 16:57
  • `resultsDataSet = new DataSet(); dbAdapter.Fill(resultsDataSet); } }` – JonH Mar 16 '15 at 16:57
  • Sorry had to post it in two comments due to its size. You need to be able to pass a connection to the sproc (your db connection), the name of the sproc, and then either fill a dataset or `ExecuteNonQuery` – JonH Mar 16 '15 at 16:58

0 Answers0