CODEWITHSUNDEEP
c#.netsql-serverstored-proceduresado.net
user2265321
user2265321

Reputation: 203

Running stored procedures with parameters

I have a method that will get the sql connection and connect. Create a new command, add some parameters and then run a adapter to fill a DataTable. However I'm getting error saying that I have not provided a parameter - even though I clear have.

What do you think is going wrong here?

public static DataTable getStatsPaged(int currentPage, int pageSize, int year, int month, int day, int logType, int itemid, string stat)
{
    using (SqlConnection connection = sqldb.getSqlConnection("db2"))
    {
        connection.Open();

        using (SqlCommand command = new SqlCommand("stp_FidusTrak_"+stat+"_paged", connection))
        {
            command.Parameters.Add(new SqlParameter("@RETURN_VALUE", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, ((System.Byte)(0)), ((System.Byte)(0)), "", DataRowVersion.Current, null));
            command.Parameters.Add(new SqlParameter("@CurrentPage", currentPage));
            command.Parameters.Add(new SqlParameter("@PageSize", pageSize));
            command.Parameters.Add(new SqlParameter("@Year", year));
            command.Parameters.Add(new SqlParameter("@Month", month));
            command.Parameters.Add(new SqlParameter("@Day", day));
            //error is this logType
            //Procedure or function 'stp_FidusTrak_SearchParameterSelect_paged' expects parameter '@LogType', which was not supplied.
            command.Parameters.Add(new SqlParameter("@LogType", -10));
            command.Parameters.Add(new SqlParameter("@itemId", itemid));
            command.Parameters.Add(new SqlParameter("@TotalRecords", SqlDbType.Int, 4, ParameterDirection.Output, false, ((System.Byte)(0)), ((System.Byte)(0)), "", DataRowVersion.Current, null));
            command.Parameters.Add(new SqlParameter("@FirstDate", SqlDbType.SmallDateTime, 4, ParameterDirection.Output, false, ((System.Byte)(0)), ((System.Byte)(0)), "", DataRowVersion.Current, null));

            using (SqlDataAdapter adapter = new SqlDataAdapter())
            {
                adapter.SelectCommand = command;
                DataTable table = new DataTable();
                adapter.Fill(table);

                return table;
            }
        }
    }
}

It will be passed in as a variable normally, but even if I just write it in as -10, it still says not supplied..

Anyone got an idea? Maybe my using stuff is wrong?

thanks.

Upvotes: 3

Views: 535

Answers (4)

The Hungry Dictator
The Hungry Dictator

Reputation: 3484

This line must be inserted in while using SP in your code. 

command.CommandType = CommandType.StoredProcedure;

Upvotes: 1

kaushik0033
kaushik0033

Reputation: 677

Check LogType parameter type which you are passing in Stored procedure and mentioned StoredProcedure type of command object because it takes default Text type

Try below line :-

command.Parameters.Add(new SqlParameter("@LogType", "-10"));

Upvotes: 0

Felice Pollano
Felice Pollano

Reputation: 33252

You should specify the command type, that must be StoredProcedure. See the documentation here: http://msdn.microsoft.com/en-us/library/system.data.common.dbcommand.commandtype.aspx

Upvotes: 3

marc_s
marc_s

Reputation: 754348

You haven't told ADO.NET that this is a stored procedure that you're calling.

Add this line:

using (SqlCommand command = new SqlCommand("stp_FidusTrak_"+stat+"_paged", connection))
{  
    // tell it that it's a stored procedure
    command.CommandType = CommandType.StoredProcedure;

Upvotes: 2

Related Questions