selectcommand with logged in username in gridview datasource

[infinitizon]

Hi all,

I'm sorry to say that my asp.net is not sharp enough.
I've been trying different means to get this done without success.
Here's the latest I did
<asp:GridView ID="gdvMonitorTickets" runat="server" AutoGenerateColumns="False"
DataSourceID="ds_getTicketsForPerson">
<Columns>
<asp:BoundField DataField="Ticket_No" HeaderText="Ticket_No"
SortExpression="Ticket_No" ItemStyle-Width="220" InsertVisible="False"
ReadOnly="True" />
<asp:BoundField DataField="Description" HeaderText="Description"
SortExpression="Description" ReadOnly="True" />
<asp:BoundField DataField="Date" HeaderText="Date" SortExpression="Date" />
<asp:BoundField DataField="TheStatus" HeaderText="TheStatus" ReadOnly="True"
SortExpression="TheStatus" />
<asp:CheckBoxField DataField="status" HeaderText="status"
SortExpression="status" />
</Columns>

</asp:GridView>
<asp:SqlDataSource ID="ds_getTicketsForPerson" runat="server"
ConnectionString="<%$ ConnectionStrings:WebTablesConnectionString %>"
SelectCommand="SELECT [Ticket No] AS Ticket_No, (CASE WHEN LEN([Description])>30 THEN (left([Description],33)+'...') ELSE [Description] END)[Description],[Date/Time Created] AS [Date], (CASE [status] WHEN 0 THEN 'Opened' WHEN 1 THEN 'Closed' END)[TheStatus],[status] FROM [IT Helpdesk Raised Tickets] WHERE (UserId=@userId) GROUP BY [Ticket No],[Description],[Date/Time Created],[status] ORDER BY [Ticket No] DESC">
<SelectParameters>
<asparameter Name='<%# Eval("User.Identity.Name") %>' Type="String" />
</SelectParameters>
</asp:SqlDataSource>

Please what's wrong

____________________
Men put up a strong face just to cover their weaknesses...good!
But a smile makes them live longer....
Which would you choose?

Think about it.

 

[infinitizon]

Thanks all,

I'm not that dull after-all, I was able to push the select parameter to the code behind such that my aspx page looks like this:
<asp:SqlDataSource ID="ds_getTicketsForPerson" runat="server"
ConnectionString="<%$ ConnectionStrings:WebTablesConnectionString %>"
SelectCommand="SELECT [Ticket No] AS Ticket_No, (CASE WHEN LEN([Description])>30 THEN (left([Description],33)+'...') ELSE [Description] END)[Description],[Date/Time Created] AS [Date], (CASE [status] WHEN 0 THEN 'Opened' WHEN 1 THEN 'Closed' END)[TheStatus],(CASE [status] WHEN 0 THEN 'Close' WHEN 1 THEN 'Reopen' END)[lblStatus],[status] FROM [IT Helpdesk Raised Tickets] WHERE (UserId=@userId) GROUP BY [Ticket No],[Description],[Date/Time Created],[status] ORDER BY [Ticket No] DESC">
</asp:SqlDataSource>

and code behind like this:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
ds_getTicketsForPerson.SelectParameters.Add("userId", DbType.String, User.Identity.Name)
End Sub

Cool right.

Thank you thank you, thank you

____________________
Men put up a strong face just to cover their weaknesses...good!
But a smile makes them live longer....
Which would you choose?

Think about it.

 

[jmeckley]

you're using a datasource control
in all seriousness, the data source controls are the worst idea ever. they are very limited in functionality and provide no means of stepping through or debugging the code.

in this situation the problem sticks out.
Name refers to the name of the parameter, not the value.
you would set the default value to the current user's name

<asp:Parameter Name="userId" DefaultValue='<%# Eval("User.Identity.Name") %>' />

the default type is string, so no need to set that.



Jason Meckley
Programmer

faq855-7190
faq732-7259