Click here to Skip to main content
15,890,557 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi all,

Can any one help me what is wrong in my code?

It says expects @id which was not supplied. Actually, I wrote a simple procedure like this
SQL
ALTER proc [dbo].[spuserdata]
@id int 
AS
select decription,url from tb_userdata inner join tb_userlogin on tb_userdata.uidfromtb1=tb_userlogin.id
 where tb_userlogin.id=@id

I thought it was fine. What is wrong with my procedure? Can you please help me?

I'm getting current user id using this
C#
string id = System.Web.HttpContext.Current.Session.SessionID;


Thanks and Regards

C#
public class DataClass
{

    public DataClass()
    {
    }
    /// <summary>
    ///  return rows depend on position
    ///  if you need 10th to 20th you need to pass start=10 and end=20
    /// </summary>
    /// <param name="start">database start position of one row</param>
    /// <param name="next">database end position of one row</param>
    /// <returns></returns>
    public string GetAjaxContent(int start, int end)
    {
        string result = string.Empty;
        //adding sp params with values in Dictionary entry.
        Dictionary<string, object> keyValPair = new Dictionary<string, object>();
        keyValPair.Add("@start", start);
        keyValPair.Add("@next", end);

        DBHelper DBHelper = new DBHelper();
        //passing the Stored Procedure name and keyvalue pair
        DataTable dataTable = DBHelper.GetTable("spuserdata", keyValPair);
        if (dataTable.Rows.Count > 0)
        {
            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                result += string.Format(@"<tr>
                                                        <td>
                                                            <table>
                                                                <tr>
                                                                    <td style='width:50px;'>{0}</td><td style='width:400px;'>{1}</td><td style='width:150px;'>{2}</td>
                                                                </tr>
                                                            </table>
                                                        </td>
                                                   </tr>", dataTable.Rows[i][0].ToString(), dataTable.Rows[i][1].ToString(), dataTable.Rows[i][2].ToString());
            }

        }
        //this string is going to append on Datalist on client.
        return result;
    }
    /// <summary>
    /// function to bind data on page load
    /// </summary>
    /// <returns></returns>
    public DataTable FirstTenRecords()
    {
        Dictionary<string, object> keyValPair = new Dictionary<string, object>();
        keyValPair.Add("@start", 0);
        keyValPair.Add("@next", 10);

        DBHelper DBHelper = new DBHelper();
        DataTable dataTable = DBHelper.GetTable("spuserdata", keyValPair);


        return dataTable;
    }
}

/// <summary>
/// return sqlconnection string formweb.config file
/// </summary>
public class Provider
{
    public static SqlConnection GetConnection()
    {
       // return new SqlConnection(ConfigurationManager.AppSettings["conn"]);
        return new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["conn"].ConnectionString);

    }
}
/// <summary>
/// Data layer
/// </summary>
public class DBHelper
{
    public DBHelper()
    {
    }


    public DataTable GetTable(string spuserdata, Dictionary<string, object> SPParamWithValues)
    {
        string id = System.Web.HttpContext.Current.Session.SessionID;


        SqlConnection conn = new SqlConnection();
        SqlCommand cmd = new SqlCommand("con");
        SqlDataAdapter adapter = new SqlDataAdapter();
        cmd.Connection = conn;

        DataTable table = new DataTable();
        // conn = Provider.GetConnection();

        using (var con = Provider.GetConnection())
        {
            using (var comd = new SqlCommand(spuserdata, con))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@id", id);

                foreach (var keyValue in SPParamWithValues)
                {
                    cmd.Parameters.Add(keyValue.Key, SqlDbType.Int).Value = keyValue.Value;

                }
                using (var da = new SqlDataAdapter(comd))
                {
                    da.Fill(table);
                }
            }

            return table;

        }



    }
}

Thanks and Regards
Posted
Updated 1-Oct-13 8:36am
v2
Comments
Richard C Bishop 1-Oct-13 14:36pm    
When debugging, what value does "id" have?
adityaimmadi 1-Oct-13 14:51pm    
Sir,i have changed my code like this
foreach (var keyValue in SPParamWithValues)
{
cmd.Parameters.Add(keyValue.Key, SqlDbType.Int).Value = keyValue.Value;

}
comd.CommandType = CommandType.StoredProcedure;
comd.Parameters.AddWithValue("@id", id);
using (var da = new SqlDataAdapter(comd))
{
da.Fill(table);
}
}
thenn it says Error converting data type nvarchar to int.in my table id datatype is int ,i spend coulpe of hours for this,can you please helpp me
Thanks and regards
adityaimmadi 1-Oct-13 14:57pm    
the value is encoded sir and type is string...
Thanks and Regards
Mike Meinz 1-Oct-13 14:44pm    
This won't solve your problem but it is not a best practice to start the name of your stored procedure with the letters "sp". All of the stored procedures built into SQL Server by Microsoft start with "sp". Maybe start your name with "stpr" instead.
adityaimmadi 1-Oct-13 14:52pm    
Thanks sir,i'll change it defnetly...

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900