Procedure or function spuserdata has too many arguments spec

aditya immadi

13y3.7kLearn iOS Programming

Hai all, just look at my code::

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

///

/// Summary description for DataClass

///

public class DataClass

{

public DataClass()

{

}

///

/// return rows depend on position

/// if you need 10th to 20th you need to pass start=10 and end=20

///

/// database start position of one row

/// database end position of one row

///

public string GetAjaxContent(int start, int end)

{

string result = string.Empty;

//adding sp params with values in Dictionary entry.

Dictionary keyValPair = new Dictionary();

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(@"

{0}

{1}

{2}

", 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;

}

///

/// function to bind data on page load

///

public DataTable FirstTenRecords()

{

Dictionary keyValPair = new Dictionary();

keyValPair.Add("@start", 0);

keyValPair.Add("@next", 10);

DBHelper DBHelper = new DBHelper();

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

return dataTable;

}

///

/// return sqlconnection string formweb.config file

///

public class Provider

{

public static SqlConnection GetConnection()

{

// return new SqlConnection(ConfigurationManager.AppSettings["conn"]);

return new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["conn"].ConnectionString);

}

///

/// Data layer

///

public class DBHelper

{

public DBHelper()

{

}

public DataTable GetTable(string SPName, Dictionary SPParamWithValues)

{

DataTable dataTable = new DataTable();

//SqlCommand cmd;

//SqlDataAdapter adapter;

//SqlConnection conn;

SqlConnection conn = new SqlConnection();

SqlCommand cmd = new SqlCommand();

SqlDataAdapter adapter = new SqlDataAdapter();

conn = Provider.GetConnection();

conn.Open();

//open DB connection

cmd = new SqlCommand();

cmd.CommandType = CommandType.StoredProcedure;

cmd.Connection = conn;

cmd.CommandText = SPName;

foreach (KeyValuePair paramValue in SPParamWithValues)

{

cmd.Parameters.AddWithValue(paramValue.Key, paramValue.Value);

}

adapter = new SqlDataAdapter(cmd);

adapter.Fill(dataTable);---Procedure or function spuserdata has too many arguments specified.

return dataTable;

}

here is my stored procedure ,as for i know this error is because when we are specifying more parameters in your c# code than our stored procedure accepts..but i dont understand what are the extra parameters which i add
and here is my stored proc ::

create proc spuserdata

@id int

select decription,url from tb_userdata inner join tb_userlogin on tb_userdata.uidfromtb1=tb_userlogin.id

where tb_userlogin.id=@id

how an i resolve this
Thnaks and Regards

9 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jaganathan Bantheswaran13y ago
Hi,

You need to pass int value to your SP. But the SessionID is alphanumeric value, so conversion is failing.

Change the SP's @id parameter to nvarchar. or if the @id should be int, then pass correct int to SP.
aditya immadi13y ago
Hai sir, i changed as you say,then after i have error like convertion failed to convert nvarchar to int ,then i changed like below now it saying string is not in correct fomate.i have done corectly?? then why i'm getting this..??

string id = System.Web.HttpContext.Current.Session.SessionID; as
// int id = Convert.ToInt32(System.Web.HttpContext.Current.Session.SessionID);

Thanks and Regards
Jaganathan Bantheswaran13y ago
Hi,

Try in this way

create procedure spuserdata(
@id int)
AS
Begin
select decription,url from tb_userdata inner join tb_userlogin on tb_userdata.uidfromtb1=tb_userlogin.id
where tb_userlogin.id=@id
End
aditya immadi13y ago
Hai Jaganathan

i tried it but it says the same old error

Thanks and Regards
Jaganathan Bantheswaran13y ago
using(var con =Provider.GetConnection() )

{
using (var comd = new SqlCommand(spuserdata, con))
{
cmd.CommandType = CommandType.StoredProcedure;

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

}
using (var da = new SqlDataAdapter(comd))

{
da.Fill(table);
}
}

Before passing comd to SqlDataAdapter, add the parameters and try.
aditya immadi13y ago
Hai Jaganathan ,Thanks for you help

i just cahnges my code like this
using(var con =Provider.GetConnection() )
using (var comd = new SqlCommand(spuserdata, con)) using (var da = new SqlDataAdapter(comd))

{
cmd.CommandType = CommandType.StoredProcedure;

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

}

da.Fill(table);
}
it works fine but now it says expects @id which was not supplied .actuallly i wote a simle procedure like this
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 thoght it was fine.what is wrong with my procedure .can you please help me

and i'm getting curent user id using this
string id = System.Web.HttpContext.Current.Session.SessionID;

Thanks and Regarrds
Jaganathan Bantheswaran13y ago
vzvzvz using(var comd = new SqlCommand(spuserdata, conn)) using(var da = new SqlDataAdapter(comd))
{
cmd.CommandType = CommandType.StoredProcedure;

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

da.Fill(table);....The ConnectionString property has not been initialized.
}

The above code will work...

You have used the empty cmd object instead of comd.
aditya immadi13y ago
Hi Jaganathan ,i tried like this but i got error ike The ConnectionString property has not been initialized.i think wverything is fine how can i resolve this

SqlConnection conn = new SqlConnection();
SqlCommand cmd = new SqlCommand("conn");
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, conn)) using(var da = new SqlDataAdapter(cmd))
{
cmd.CommandType = CommandType.StoredProcedure;

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

da.Fill(table);....The ConnectionString property has not been initialized.
}

return table;

}

Thanks and Regards

Jaganathan Bantheswaran13y ago

Try this,

DataTable table = new DataTable(); using(var con = new SqlConnection(Provider.GetConnection()) using(var cmd = new SqlCommand(SPName, con)) using(var da = new SqlDataAdapter(cmd)) {    cmd.CommandType = CommandType.StoredProcedure;

   foreach(var keyValue in SPParamWithValues)
{
        cmd.Parameters.Add(keyValue.Key, SqlDbType.Integer).Value = keyValue.Value;
}
       da.Fill(table); }

return table;

}