Hi there, I am new to ASP.net. I have VWD Express 2008 and SQL Server 2008 Express installed to create a simple web application for learning purposes. I have used Log in page and I carry the user name to a page where I have my gridview control. In data source configuration I want to able to use the username in where clause. Can someone please help me with this, and show me simply as possible, what I need to do? What and where exactly I need to place the code. Thanks for your help in advance. gsrai31

Hi there,

Are you using a SqlDataSource control? If so, you can do something like this:

1. Configure the data source and create a parameter in the wizard. You can set its type to None as you'll fill in the value later. Your SQL will end up like this:

SELECT WhatEver FROM SOmeTable WHERE UserName = @userName

Inside the SqlDataSource you see the <Parameters> with your parameter.

2. Handle the Selecting event of the SqlDataSource. In code behind, inside the handler, write something like:

e.Command.Parameters(0}.Value = YourUserName

If you're using ASP.NET Membership you can get the name from the MembershipUser which you can get by calling Membership.GetUser() (provided the user is logged on).

Hope this helps,

Imar


---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of Beginning ASP.NET 3.5 : in C# and VB, ASP.NET 2.0 Instant Results and Dreamweaver MX 2004
Want to be my colleague? Then check out this post.

Hi Imar
Many thanks for looking into my request. I am still slighly lost here. Just to clarify couple of things. I am using SqlDataSource control. I am also using ASP.NET Membership so I have ASPNETDB.MDF for user management. Another SQL Server database I am using for my data management purposes is called SPMSDB.MDF. On my log in page I have used the login control, leaving the names etc as default. Login process works fine. Once users has successfully logged in, he is sent to a page called refdata.aspx. This page has a LoginName control and a datagrid. My select statement is meant to look like this -

Select * From [RefData] Where ([SupplierName]) = "UserName"

Following your instruction above, when in Add WHERE Clause window I select Column "SupplierName" Operator = Source: None, the Where Clause added to the statment is [SupplierName]=@SupplierName

So what code I need to add in the code behind, inside the handler? Is there anything else I need to do to make my gridview work?

Sorry about this, hope this all makes sense. Thanks again for your help.
gsrai31

You're pretty much there, if you add the code from my previous post.

Your SqlDataSource should look similar to this: Then in code behind you can have something like: Right before the SqlDataSource talks to the database, it fires the Selecting event. Inside the handler, you grab the user name from the MembershipUser and feed it to the parameters collection (since you only have one, you set it on (0) which is the first parameter).

The SQL statement is then executed with the user's user name in the WHERE clause.

Note that this only works when the user is logged in when accessing this page. Otherwise, GetUser() returns Nothing....

Hope this helps,

Imar


---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of Beginning ASP.NET 3.5 : in C# and VB, ASP.NET 2.0 Instant Results and Dreamweaver MX 2004
Want to be my colleague? Then check out this post.

Hi Imar

Thanks again. Can you please tell me how do I check if my SqlDataSource looks like this -

asp:SqlDataSource ...
SelectCommand="Select * From [RefData] Where ([SupplierName]) = @SupplierName)">
  <SelectParameters>
    <asp:Parameter Name="SupplierName" Type="String" />
  </SelectParameters>
</asp:SqlDataSource>


This is what my select statement looks like in Configure Data Source - SqlDataSource1 window -
SELECT * FROM [RefData] WHERE ([SupplierName] = @SupplierName)

I have used the the following code in the code behind as you suggested -
e.Command.Parameters(0).Value = Membership.GetUser().UserName

When I log in and go to the page refdata.aspx I get an error as shown at the bottom of this message. By the way I have disabled the strong password, so does this mean I am not using ASP.NET Membership? Or is there another reason for this error?
Thanks again for your help with this. gsrai31

Server Error in '/SPMS' Application.
--------------------------------------------------------------------------------

Object reference not set to an instance of an object.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

Hi there,

You can check the code for the SqlDataSource control in markup, where all the other HTML and markup is.

I think the error is caused by the fact the user is not currently logged in. To check that out, set a breakpoint on the line that starts with e.Command.... (hit F9 to do so) then hit F5 to start debugging. Once you hit the line with the breakpoint, look at Membership.GetUser() (you can select that text, right-click it and choose Add Watch. ) If the item is Nothing / Null, you're not logged in.

Hope this helps,

Imar


---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of Beginning ASP.NET 3.5 : in C# and VB, ASP.NET 2.0 Instant Results and Dreamweaver MX 2004
Want to be my colleague? Then check out this post.

Hi Imar,
I built all the pages before viewing in browser and testing the website. It worked fine, did not get the error message. So the error must be due to the fact that user was not logged in as you pointed out.
I am sure I will run into more problems while working on this application. I will be looking for furhter help over the next couple of week. Your help will be much appreciated.
Thank you again. gsrai31

You're welcome...

Imar


---------------------------------------
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Everyone is unique, except for me.
Author of Beginning ASP.NET 3.5 : in C# and VB, ASP.NET 2.0 Instant Results and Dreamweaver MX 2004
Want to be my colleague? Then check out this post.