...

View Full Version : Procedure or function expects parameter which was not



GuitReal
06-27-2011, 07:51 PM
I am calling a stored procedure to delete a record:


Protected Sub deleteThisOfficer(ByVal sender As Object, ByVal e As EventArgs)
Dim sqlConn As SqlConnection = New SqlConnection(strConnString)
sqlConn.Open()
Dim sqlText As String = "ILETSB.PTB.uspDeleteIndividual"
Dim sqlCmd As New SqlCommand(sqlText)
sqlCmd.CommandType = Data.CommandType.StoredProcedure
sqlCmd.Parameters.AddWithValue("IndId", Request.QueryString("oid"))
sqlCmd.Parameters.AddWithValue("UserId", hfUserId.Value)
sqlCmd.Connection = sqlConn
sqlCmd.ExecuteNonQuery()
End Sub

The procedure deletes the record and executes in SQL Server Mgmt Console with no errors. But when run from a ASP.Net page I get the error:
Procedure or function 'uspDeleteIndividual' expects parameter '@IndId', which was not supplied.

Since the record is being deleted and the procedure places a copy of the deleted record in an audit table along with the ID of the person deleting the record, I know the parameters are being passed. Anybody know what is going on?

alykins
06-28-2011, 05:02 PM
i don't know what lang you are using but here is a snippet demo of a C# that calls a stored SQL procedure... you can maybe adapt to ur use... i think you are literally missing the "@" symbol... look at my parameteres and how it's called


SqlConnection conn = new SqlConnection(path);

/* set up call for stored procedure */
SqlCommand doit = new SqlCommand("usp_insert_tbl_monster", conn);
doit.CommandType = CommandType.StoredProcedure;
// parameter settings
doit.Parameters.Add(new SqlParameter ("@mName", name));
doit.Parameters.Add(new SqlParameter("@mType", type));
doit.Parameters.Add(new SqlParameter("@mFriendly", friend));
/* end call params */

conn.Open();
doit.Connection = conn;
doit.ExecuteNonQuery();
conn.Close();

GuitReal
06-28-2011, 08:28 PM
Thanks for responding, this is a very frustrating problem. I have wasted a few hours on it already. I am using VS2010, the page is in ASP.Net with VB. I suspect the error has to do with the parameters being passed (an int for an ID and character string for login ID). I tried using the method you are describing first, but VS told me that method was outdated and to use AddWithValue method instead. It doesn't seem like they worked out the bugs on it.

By the way, including the "@" sign on the parameters had no effect. The SQL stored proc ran fine, moved records to archive, recorded the delete operation and deleted the records I wanted to remove. Then the browser shot up the same message about the procedure needing the parameter passed to it (that was obviously passed since it archived the data then deleted the records). And as I stated in the first post, the stored proc executes inside of SQL Server with no issues, it is only when the function is called from the web page that the error pops up.

alykins
06-28-2011, 08:38 PM
the method i posted is C#... u need to do the same thing but with VB ( i build on VS2010 as well woo woo :D ) anyways check here (http://msdn.microsoft.com/en-us/library/yy6y35y8(v=vs.71).aspx) for MSDN code syntax's and snippets then apply accordingly. Sorry I can;t be of further help, I only know C# my boss knows VB and he made me learn C# to make sure we had all bases covered

AndrewCollins
08-10-2011, 10:18 PM
Yes,
To call a stored procedure, set the CommandType of the Command object to StoredProcedure. Once the CommandType is set to StoredProcedure, you can use the Parameters collection to define parameters, as in the following example.
;)

micheldb
08-24-2011, 08:53 AM
Andrew can you please give me the example as you stated here "as in the following example." ?

Thanks in advance!

Klayer
08-26-2011, 12:03 AM
@alykins,
I tired to use the sample you gave on my SQL and it seems to work fine :thumbsup:

Kevin Richards
08-30-2011, 04:22 AM
I wouldn't mind seeing that example also Andrew :)

Par1s
08-31-2011, 03:13 PM
Are you sure your Request.QueryString is being populated? That will be the first place to look for me, as the SP is expecting a value there.

Jenifer_S
09-07-2011, 04:01 PM
Is issue solved? Since according to my analysis I too doubt your Request.QueryString is not getting populated. Further it may be silly on my part to ask but is the parameter hfUserId.Value has been initiated.

dhape
09-18-2011, 04:07 PM
I would check my application code and see what value you are setting @IndId to. I suspect it is null and there in lies the problem.

Jenifer_S
09-19-2011, 08:47 AM
I've checked the scenario, the issue is not for setting the values null atleast. So you can leave that scenario and look further

arlinetis
10-04-2011, 09:47 AM
I checked my guide and found where I do mention that the scripts to load the Adventureworks database will launch the "SQL Server Management Console". Again, this is just referring to SSMS (but perhaps I should change that to avoid any confusion).

SSMS is the main component of SQL Server 2005...so you're saying SSMS isn't installed on your machine after installing SQL Server? Try going to Start | All Programs | Microsoft SQL Server 2005 | SQL Server Management Studio.

(http://www.eblogz.net)

alykins
10-04-2011, 04:29 PM
The OP has not come back and may not come back... Let's stop digging this thread up asking him/her if the issue is still outstanding since there will not likely be any replies

chukcha
11-05-2011, 05:31 AM
Did you double check that you are getting parameter from Request.QueryString("oid") ?

jassi.singh
11-09-2011, 06:32 AM
I am calling a stored procedure to delete a record:


Protected Sub deleteThisOfficer(ByVal sender As Object, ByVal e As EventArgs)
Dim sqlConn As SqlConnection = New SqlConnection(strConnString)
sqlConn.Open()
Dim sqlText As String = "ILETSB.PTB.uspDeleteIndividual"
Dim sqlCmd As New SqlCommand(sqlText)
sqlCmd.CommandType = Data.CommandType.StoredProcedure
sqlCmd.Parameters.AddWithValue("IndId", Request.QueryString("oid"))
sqlCmd.Parameters.AddWithValue("UserId", hfUserId.Value)
sqlCmd.Connection = sqlConn
sqlCmd.ExecuteNonQuery()
End Sub

The procedure deletes the record and executes in SQL Server Mgmt Console with no errors. But when run from a ASP.Net page I get the error:
Procedure or function 'uspDeleteIndividual' expects parameter '@IndId', which was not supplied.

Since the record is being deleted and the procedure places a copy of the deleted record in an audit table along with the ID of the person deleting the record, I know the parameters are being passed. Anybody know what is going on?

Hello,

Try this

string sqlCommand = "[test]";
DbCommand dbCommandWrapper = db.GetStoredProcCommand(sqlCommand);

// Add in parameters
db.AddInParameter(dbCommandWrapper, "@Id", DbType.Int16, QuoteId);
DataTable dt = db.ExecuteDataSet(dbCommandWrapper).Tables[0];



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum