Click here to Skip to main content
15,867,488 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
SQL
Is it possible to insert a session variable in an SQLDataSource like so:

="SELECT * FROM Companies WHERE CompanyID = <%session(CurrentCompID)%>"
Posted

1 solution

This is not DataSource, its a SqlCommand.
And because of Object-type of all values that stores in Session this kind of solution is not good.

Try this:

C#
string query = "SELECT * FROM Companies WHERE CompanyID = @companyID";
DataTable dt = new DataTable();
SqlCommand cmd = new SqlCommand(query);
cmd.Parameters.Add(new SqlParameter("@companyID", Session["CurrentCompID"]));
using(SqlConnection conn = new SqlConnection("Here Is Your Connection String"))
{
    cmd.Connection = conn;
    SqlDataAdapter da = new SqlDataAdapter(cmd);

    try
    {
        da.Fill(dt);
    }
    catch(Exception ex)
    {
    }
}


And i recommend to check value in Session for existing, and of course on Type matching.
So when you will know exact type of value in session, then you can use other (more preferable) constructor of SqlParameter class.

C#
if(Session["CurrentCompID"] != null)
{
    int compId;
    if(int.TryParse(Session["CurrentCompID"].ToString(), out compId)
    {
        // And here paste above code, but with changing of SqlParameter initialization
        SqlParameter par = new SqlParameter("@companyID", SqlDbType.Int);
        par.Value = compId;
        cmd.Parameters.Add(par);
    }
}


For Update query it will looks the same:

C#
string query = "UPDATE [Data] SET [Companyname ]= @Companyname, [UpdatedBy] = @updatedBy, [UpdatedDate]= @updatedDate WHERE [CompanyID] = @CompanyID";
SqlCommand cmd = new SqlCommand(query);
cmd.Parameters.Add(new SqlParameter("@companyID", Session["CurrentCompID"]));
// And here add additional parameters
cmd.Parameters.Add(new SqlParameter("@updatedBy", Session["UpdatedBy"]));
cmd.Parameters.Add(new SqlParameter("@updatedDate", Session["UpdatedDate"]));
using(SqlConnection conn = new SqlConnection("Here Is Your Connection String"))
{
    cmd.Connection = conn;

    try
    {
        conn.Open();
        // Because no need in data returning
        cmd.ExecuteNonQuery();
    }
    catch(Exception ex)
    {
    }
}
 
Share this answer
 
v2
Comments
vibsg 2-Jul-13 5:58am    
Hi, I am using this query in sqldatasource
UpdateCommand="UPDATE [Data] SET [Companyname ]= @Companyname ,[UpdatedBy] = "<%session(UpdatedBy)%>" , [UpdatedDate]= <%session(UpdatedBy)%>" WHERE [CompanyID ] = @CompanyID">"

please confirm how do i pass session value in updatecommand?
Denis Shemenko 2-Jul-13 11:15am    
Its no difference in kind of SqlCommand type. See improved solution.
vibsg 2-Jul-13 11:31am    
Hi , I have used the below code on detailview's itemdeleting event as well as itemupdating event..

protected void DetailsView1_ItemDeleting(object sender, DetailsViewDeleteEventArgs e)
{

e.Values["UpdatedBy"] = Session["UpdatedBy"];
e.Values["UpdatedDate"] = DateTime.Now.ToString();


}

protected void DetailsView1_ItemUpdating(object sender, DetailsViewUpdateEventArgs e)
{


e.Values["UpdatedBy"] = Session["UpdatedBy"];
e.Values["UpdatedDate"] = DateTime.Now.ToString();

}

in case of update values are saving in the database however in case of delete Updatedby and UpdatedDate value is not saving as I am getting must declare scaler variable. Please advice.
vibsg 2-Jul-13 11:38am    
protected void DetailsView1_ItemUpdating(object sender, DetailsViewUpdateEventArgs e)
{


e.NewValues["UpdatedBy"] = Session["UpdatedBy"];
e.NewValues["UpdatedDate"] = DateTime.Now.ToString();

}
Denis Shemenko 2-Jul-13 12:10pm    
Provide code with what you perform Delete operation

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



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