Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C#2.0
Is it possible to insert a session variable in an SQLDataSource like so:
 
="SELECT * FROM Companies WHERE CompanyID = <%session(CurrentCompID)%>"
Posted 1-Jul-13 21:39pm
vibsg428

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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:
 
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.
 
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:
 
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)
    {
    }
}
  Permalink  
v2
Comments
vibsg at 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 at 2-Jul-13 11:15am
   
Its no difference in kind of SqlCommand type. See improved solution.
vibsg at 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 at 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 at 2-Jul-13 12:10pm
   
Provide code with what you perform Delete operation
Denis Shemenko at 3-Jul-13 3:35am
   
At the end all became clear...
So for the future, if you dont want to spend your and others time, read this:
http://www.codeproject.com/Messages/1278604/How-to-get-an-answer-to-your-question.aspx
 
And about SqlDataSource, sorry, i have no idea how to use it, just because we dont use it from long ago :)
vibsg at 3-Jul-13 4:37am
   
Anyone can advice please?
vibsg at 3-Jul-13 3:28am
   
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:AddConnectionStrings %>" SelectCommand="SELECT ID,Name,Address,UpdatedBy,UpdatedDate FROM [Data]where Status=1 and Id=@Id order by Name" DeleteCommand="UPDATE [Data] SET [Status] = '0',[UpdatedBy]= @UpdatedBy,[UpdatedDate] = @UpdatedDate WHERE [ID] = @ID" InsertCommand="INSERT INTO [Data] ([Name], [Address][UpdatedBy],[UpdatedDate]) VALUES (@Name, @Address,@UpdatedBy,@UpdatedDate)" UpdateCommand="UPDATE [Data] SET [Name]= @Name, [Address] = @Address,[UpdatedBy]= @UpdatedBy,[UpdatedDate] = @UpdatedDate WHERE [ID] = @ID" > <SelectParameters> <asp:ControlParameter ControlID="GridView1" Name="ID" PropertyName="SelectedValue" Type="Int32" /> </SelectParameters> <asp:Parameter Name="ID" Type="Int32" /> <asp:Parameter Name="UpdatedBy" Type="String" /> <asp:Parameter Name="UpdatedDate" Type="DateTime" /> <asp:Parameter Name="ID" Type="String" /> <asp:Parameter Name="Name" Type="String" /> <asp:Parameter Name="Address" Type="String" /> <asp:Parameter Name="UpdatedBy" Type="String" /> <asp:Parameter Name="UpdatedDate" Type="DateTime" /> <asp:Parameter Name="ID" Type="int32" /> <asp:Parameter Name="Name" Type="String" /> <asp:Parameter Name="Address" Type= "String" /> <asp:Parameter Name="UpdatedBy" Type="String" /> <asp:Parameter Name="UpdatedDate" Type="DateTime" />

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 575
1 Kornfeld Eliyahu Peter 409
2 Maciej Los 369
3 DamithSL 196
4 OriginalGriff 188
0 OriginalGriff 6,353
1 DamithSL 4,854
2 Maciej Los 4,476
3 Kornfeld Eliyahu Peter 4,058
4 Sergey Alexandrovich Kryukov 3,917


Advertise | Privacy | Mobile
Web01 | 2.8.141220.1 | Last Updated 2 Jul 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100