connection string intialization failed

aditya immadi

Hai all, when i'm developing infinite scolll down i need to use this handler .aspx .it looks like everything is fine to me but connection string is failed i'm providing youo my page and web.config.can any one help me

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"]);

}

///

/// Data layer

///

public class DBHelper

{

public DBHelper()

{ }

SqlConnection conn;

SqlCommand cmd;

SqlDataAdapter adapter;

public DataTable GetTable(string SPName, Dictionary SPParamWithValues)

{

DataTable dataTable = new DataTable();

try

{

conn = Provider.GetConnection();

//open DB connection

conn.Open();

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);

}

finally

{

//close connection string

conn.Close();

}

return dataTable;

}

and my web.config

Thanks and Regards

6 Replies

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

13y ago
The SP expects only one parameter. Based on the error I understood , either you're sending more that one parameter to the sp or no parameters.
So please the check the number of items in the "SPParamWithValues" collection.
aditya immadi13y ago
Hai All,
Thanks for your help.i resolved it,after that a small problem is there at this line in DataClass.cs

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.

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
AS
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
aditya immadi13y ago
no no,DstsClass file is in same project.i put it in app_data folder.. is it ok?? Thanks and Regards
13y ago
Does the DataClass is present in a another project? If yes, add app.config in that project and configure the connectionstring there also.
aditya immadi13y ago
Hai muralidharran Thanks for your help,i'm sharing my handler.aspx

<%@ WebHandler Language="C#" Class="Handler" %>

using System;
using System.Web;

public class Handler : IHttpHandler {

public void ProcessRequest(HttpContext context)
{
string startQstring = context.Request.QueryString["start"];
string nextQstring = context.Request.QueryString["next"];
//null check
if ((!string.IsNullOrWhiteSpace(startQstring)) && (!string.IsNullOrWhiteSpace(nextQstring)))
{
//convert string to int
int start = Convert.ToInt32(startQstring);
int next = Convert.ToInt32(nextQstring);

//setting content type
context.Response.ContentType = "text/plain";
DataClass data = new DataClass();
//writing response
context.Response.Write(data.GetAjaxContent(start, next));
}
}
public bool IsReusable {
get {
return false;
}
}

Thanks and Regards
13y ago
Your connectionstirng looks fine.
I did not see any value added option that you've a separate class just to return the sqlconnection.
You can move the sql connection into DBHelper class itself.

Can you please post your handler.aspx page?
If you class library project and it is referred in your project, make sure, you have added app.config file in your class library project also.

Hope this helps you.