Reputation: 177
I am trying to pass a parameter to a stored procedure in my application but I don't know what I am doing wrong. I get an error
Procedure or function 'usp_getBorrowerDetails' expects parameter '@BookID', which was not supplied.
while I am passing and I did many things but still didn't find the solution.
This is my code:
IDataReader reader = null;
SqlCommand command = new SqlCommand();
try
{
SqlConnection connection = GetDBConnection();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = Constants.SP_BookBorrowerDetails;
command = new SqlCommand(command.CommandText, connection);
command.Parameters.AddWithValue("@BookID", bookID);
reader = base.ExecuteReader(command);
}
catch (System.Data.SqlClient.SqlException ex)
{
throw new Exception("Oops! Something went wrong.");
}
Below is my stored procedure:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_getBorrowerDetails]
@BookID INT
AS
SELECT
Name, Mobile, ReturnDate
FROM
BorrowerDetails
INNER JOIN
BookDetails ON BookDetails.CurrentBorrowerID = BorrowerDetails.ID
WHERE
BookDetails.BookID = @BookID
GO
If I run any stored procedure that does not requires any parameter, it works fine. Issue is only coming when I am adding parameter.
Upvotes: 2
Views: 1537
Reputation: 787
It's work for me,
connection = new SqlConnection(connectionString);
connection.Open();
SqlCommand com = new SqlCommand("usp_getBorrowerDetails", connection);
com.CommandType = CommandType.StoredProcedure;
com.Parameters.AddWithValue("@BookID", bookID);
IDataReader reader = com.ExecuteReader();
while (reader.Read())
{
}
reader.Close();
connection.Close();
Upvotes: 0
Reputation: 81
I believe you have declared Constants.SP_BookBorrowerDetails as "EXEC [dbo].[usp_getBorrowerDetails]", it doesn't have the expected input parameter "@BookID".
Command.Parameters.AddWithValue which will only accepts the parameter name & value which needs to be passed.But still it would expect the parameter in the Constants.SP_BookBorrowerDetails syntax because while executing the command text the list of parameters available in the "Command.Parameters" needs the mapping parameter which has the same name.
using (SqlConnection connection = new SqlConnection("SQL connection string"))
{
connection.Open();
try
{
SqlCommand command = new SqlCommand("EXEC [dbo].[usp_getBorrowerDetails] @BookID", connection);
command.CommandType = CommandType.StoredProcedure;
command = new SqlCommand(command.CommandText, connection);
command.Parameters.AddWithValue("@BookID", 1);
IDataReader reader = command.ExecuteReader();
}
catch (SqlException ex)
{
throw new Exception("Oops! Something went wrong." + ex.Message);
}
}
Please check above code snippet.
Please revert back if any issue arises.
Upvotes: 0
Reputation: 1064114
One possibility here is that bookID
is null
.
Convert.ToString(null)
returns: null
.
A parameter with a null
value is not attached. You need to use DBNull.Value
for that.
But: there's also no need to make it a string
anyway.
Consider using: (object)bookId ?? DBNull.Value
as the value instead, which should fix both of these issues.
Edit: as noted in a comment, you're also setting the CommandType
too early, and then replacing the command object; setting the CommandType
must be done after you've new
-d the variable.
Note: these are just two of many things that "dapper" would help you with; consider:
var reader = connection.ExecuteReader(
"usp_getBorrowerDetails", new { BookID = bookID },
commandType: CommandType.StoredProcedure);
(although usually I'd advocate dapper's Query<T>
methods instead - they do a lot more for you, too)
Upvotes: 2
Reputation: 16609
You're redefining command
to a brand new SqlCommand
after setting the CommandType
and CommandText
, meaning it will be treated as plain SQL, rather than a stored procedure. Create it once in the appropriate place.
IDataReader reader = null;
SqlCommand command;
try
{
SqlConnection connection = GetDBConnection();
command = new SqlCommand(Constants.SP_BookBorrowerDetails, connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@BookID", bookID);
reader = base.ExecuteReader(command);
}
catch (System.Data.SqlClient.SqlException ex)
{
throw new Exception("Oops! Something went wrong.");
}
As an aside, you should probably also look at not just keeping the connection open, but instead getting the results within the one method, rather than relying on the calling code to manage the connection.
Upvotes: 7
Reputation: 33581
One of the problems is the order of your code. You are setting your command to a new SqlCommand. When this happens the default CommandType is Text.
command = new SqlCommand(command.CommandText, connection);
command.CommandType = CommandType.StoredProcedure;
command.CommandText = Constants.SP_BookBorrowerDetails;
You should first create the command, then set the properties.
Upvotes: 4