Hi,

I am developing a web application that use SQL server 2005 DB. Till now, I have made a connection with SQL server, retrieved the data from DB and showing in the GridView.

Now I have two ways of storing the dataset:
1. By storing dataset results in session variable
2. or use sql state management.

In the following code, I have implemented first way.

public partial class _Default : System.Web.UI.Page
{
public string strConn = "server=(local)\\SQLEXPRESS;database=OTC;Integrated Security=SSPI";

// create dataset
DataSet ds;

protected void Page_Load(object sender, EventArgs e)
{


// Build SQL String
string SQLstring = "Select * FROM FileHistory";

// Call and Build Grid
// BindGrid(SQLDBcontectString, SQLstring, DataGrid);
if (Page.IsPostBack == false)
{
BindGrid(strConn, SQLstring, GridView1);
//GridView1.PageIndex = (int)Session["PageIndex"];

}
else
{
BindGrid2(ds);
//GridView1.PageIndex = (int)Session["PageIndex"];
}

this.Button1.Text = Resources.Resource.button;
this.Menu1.Items[0].Text = Resources.Resource.menu1;
//this.TextBox1.Text = Session["textboxtext"].ToString();

}

private void BindGrid(string DBconnectString, string sqlCommand,
System.Web.UI.WebControls.GridView DGrid)
// Load intial page from database
// binds to datagrid
{
// create data connection
SqlConnection conn = new SqlConnection(DBconnectString);

// Call SQL from db
SqlCommand command = new SqlCommand(sqlCommand, conn);

// create data adapter
SqlDataAdapter adapter = new SqlDataAdapter(command);

// fill dataset
ds = new DataSet();
if (ds != null && Page.IsPostBack == false)
{
adapter.Fill(ds);

// fill and bind data to Datagrid
DGrid.DataSource = ds;
DGrid.DataBind();
Session["filedataset"] = ds;
Session["PageIndex"] = GridView1.PageIndex;
}
// Close Connection
conn.Close();
}

private void BindGrid2(DataSet ds)
{
// fill and bind data to Datagrid
ds = (System.Data.DataSet)Session["filedataset"];
GridView1.DataSource = ds;
GridView1.DataBind();
}
}

But it can be an scalability issue on server side.

So I have tried with second way (SQL state management) but it is giving the error:

Cannot open database "ASPState" requested by the login. The login failed.
Login failed for user 'test'.

Web.config is as below:
<sessionState mode="SQLServer"
sqlConnectionString="data source=VIKAS\SQLEXPRESS;user id=test;password=test1234;Trusted_Connection=False"
cookieless="false" timeout="20" />

Please suggest, what i am missing? (it can be a very simple issue, as i am new to web applications)

Thanks In Advance
Vikas Kohli