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!