[RESOLVED] Filter GridView using Environment.UserName

On my Page_Load I have scripted so I pull through the username as shown below.

 

Public Class VolunteerActivities_tbl

    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Dim loginN As String

        loginN = Environment.UserName

    End Sub

End Class

Now I want to filter the GridView which holds various users entries… so I thought by using a WHERE clause within my SqlDataSource would filter the current user of the system.

 

Below is the script for the SqlDataSource?

 

            ConnectionString="<%$ ConnectionStrings:NCRSDBConnectionString %>" 
        SelectCommand="SELECT ID, Chaplain, CONVERT(VARCHAR(12),Date, 105) Date , Standard, Significant, Critical, Religious, CONVERT(VARCHAR(12),getDate, 105) getDate FROM [AR_20130620_ChaplaincyActivity] where userName = loginN order by date desc">
    
    

I think that easier will be if you move Sqlconnection and select query to Page_Load function of codebehind. 

Example: http://www.dotnetfunda.com/articles/article1594-how-to-populate-gridview-from-code-behind.aspx

Regards

Krzysztof

thanks for the link, it looks a lot more difficult than I thought, any assistance, in helping me writing the script

static string connsql = ConfigurationManager.ConnectionStrings["NCRSDBConnectionString"].ConnectionString;
SqlConnection conn = new SqlConnection(connsql);

 protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            string loginN = Environment.UserName;

            try
            {
               conn.Open();
               string select = "SELECT ID, Chaplain, CONVERT(VARCHAR(12),Date, 105) Date , Standard,
               Significant, Critical, Religious, CONVERT(VARCHAR(12),getDate, 105) getDate FROM
               [AR_20130620_ChaplaincyActivity] where userName = " + loginN + " order by date desc";
               SqlCommand cmd = new SqlCommand(select, conn);
               SqlDataAdapter da = new SqlDataAdapter(cmd);
               DataTable dt = new DataTable();
               da.Fill(dt);
               
               GridView1.DataSource = dt;
               GridView1.DataBind();
                }
               catch(){}
               finally
               {
                   conn.Close();
               }
           
        }
  }

I hope that I have not made mistake. Please "translate" it for VB, because I don't know VB.

hello my friend, i get the following error message when converting to VB using the online converters

CONVERSION ERROR: Code could not be converted. Details:

-- line 1 col 8: invalid TypeDecl

Please check for any errors in the original code and try again.

kszymaniak

string select = "SELECT ID, Chaplain, CONVERT(VARCHAR(12),Date, 105) Date , Standard,
               Significant, Critical, Religious, CONVERT(VARCHAR(12),getDate, 105) getDate FROM
               [AR_20130620_ChaplaincyActivity] where userName = " + loginN + " order by date desc";

because loginN is a string, it should be surrounded with single quotes:

".... where userName = '" + loginN + "' order by date desc"

However, this code is vulnarable to sql injections:

http://www.mikesdotnetting.com/Article/113/Preventing-SQL-Injection-in-ASP.NET

So you should use parameters instead

               string select = "SELECT ID, Chaplain, CONVERT(VARCHAR(12),Date, 105) Date , Standard,
               Significant, Critical, Religious, CONVERT(VARCHAR(12),getDate, 105) getDate FROM
               [AR_20130620_ChaplaincyActivity] where userName = @loginN order by date desc";
               SqlCommand cmd = new SqlCommand(select, conn);
               cmd.Parameters.AddWithValue("loginN", loginN);

in VB:

using conn as new SqlConnection(ConfigurationManager.ConnectionStrings("NCRSDBConnectionString").ConnectionString
  using cmd as New SqlCommand("SELECT ID, Chaplain, CONVERT(VARCHAR(12),Date, 105) Date , Standard,
               Significant, Critical, Religious, CONVERT(VARCHAR(12),getDate, 105) getDate FROM
               [AR_20130620_ChaplaincyActivity] where userName = @loginN order by date desc", conn)
    cmd.Parameters.AddWithValue("loginN", loginN)
    Try
      conn.Open
      GridView1.DataSource = cmd.ExecuteReader()
      GridView1.DataBind
    Catch e As Exception
    End Try
  End using
End using

But you could do this also by creating a custom Parameter

http://weblogs.asp.net/anasghanem/archive/2008/10/15/creating-custom-parameters-for-your-data-sources.aspx

in VB

Namespace CustomControls
    Public Class UserParameter
        Inherits System.Web.UI.WebControls.Parameter

        Protected Overrides Function Evaluate(context As HttpContext, control As System.Web.UI.Control) As Object
            Return context.User.Identity.Name
        End Function

    End Class
End Namespace

Your datasource will look like:

            ConnectionString="<%$ ConnectionStrings:NCRSDBConnectionString %>" 
        SelectCommand="SELECT ID, Chaplain, CONVERT(VARCHAR(12),Date, 105) Date , Standard, Significant, Critical, Religious, CONVERT(VARCHAR(12),getDate, 105) getDate FROM [AR_20130620_ChaplaincyActivity] where userName = @loginN order by date desc">
      
        
      
    

 

sam233

-- line 1 col 8: invalid TypeDecl  

It is not all text of error. Please paste all this line. 

Also, can you paste all this code from codebehind?

Rgs

sam233

loginN = Environment.UserName

By the way, I don't think this is what you want. Environment.UserName will return the account your ASP.NET application is running under.

I think you want

User.Identity.Name

 

hans_v

Namespace CustomControls
Public Class
UserParameter Inherits
System.Web.UI.WebControls.Parameter
Protected Overrides
Function Evaluate(context
As HttpContext, control
As System.Web.UI.Control)
As Object
Return context.User.Identity.Name
End Function
End Class
End Namespace

Im going to use the VB code as shown above, but do i place this in a Page_Load?

sam233

Im going to use the VB code as shown above, but do i place this in a Page_Load?

No, read the article very carefully!

"The custom parameter can be placed in App_Code folder..."

So you need to create a UserParameter.vb file in the App_Code folder!

Also, don't forget to register the control. The easiest way is in web.config