Declarative SQL problem

I can't figure out what is wrong with the code below.

The first line prints out correctly when I am logged in

If I substitute the actual username in the SQL, the Repeater works fine, printing out the item from the db.
But using
WHERE USERNAME='<%= User.Identity.Name%>'
in the SelectCommand returns no rows.

Can anyone help?

Thanks,

Michael

<h3>News for <%=User.Identity.Name%></h3>

<asp:SqlDataSourceID="SqlDataSource2"runat="server"ConnectionString="<%$ ConnectionStrings:NEWWEB %>"ProviderName="<%$ ConnectionStrings:NEWWEB.ProviderName %>"SelectCommand="SELECT USERNAME FROM ASPNET_MEMBERSHIP WHERE USERNAME='<%= User.Identity.Name%>' "></asp:SqlDataSource><asp:RepeaterID="FirmAlertsRepeater"runat="server"DataSourceID="SqlDataSource2">

<ItemTemplate><b><%# DataBinder.Eval(Container.DataItem, "USERNAME")%></b>—<br/> </ItemTemplate>

</asp:Repeater>

YOu should use an expression for username. I wrote a blog post on how to do that here. Basically, you need to an expression builder to your app_code directory. It's very straight forward.

http://peterkellner.net/2006/09/18/expressionbuilderidentity/

HTH's.

|||

Interesting way of handling it, however, this is what I do:

Inside global.aspx.vb, add:

Protected

Sub Application_PreRequestHandlerExecute(ByVal senderAsObject,ByVal eAs System.EventArgs)If User.Identity.IsAuthenticatedAndAlso Context.SessionIsNotNothingAndAlso Session("Username")IsNothingThen

Session("Username")=user.Identity.Name

end if

end sub

Then just create a session parameter using the Username session property for your SqlDatasource.

|||

Hello Peter,

Thanks, I tried that and it works.

But I am at a loss as to why that is neccessary. Can you point me to anything that explains why the variable can't be used as is?

Michael

|||It's a databinding issue. <%#, that is expression generate code at runtime that has to be executed as an event. Expression are done at compile time so the code is their already. HTH's.|||

There are many reasons why.

First you used ASP syntax to try and insert a value into a server control property. That won't work because the server control property is initialized long before your expression is evaluated.

You COULD use a databinding syntax <%# %> instead, however, the server control must support databinding, and the property you are databinding must support being databound.

Lastly, if you were going to use databinding (assuming the SqlDatasource's SelectCommand property was databindable, which it isn't), you must surround the attribute with single quotes, not double quotes or the databinding expression won't work.

|||

Thanks Peter and Motley,

So much to learn.

So there is no general Eval function that asp.net uses to make sure the variable is resolved before compiling? We have to build our own?

Michael

|||

I tried Peter's method first. It worked very nicely.

But since I am a contractor, and someone else will have to try to understand it later, I switched over to Motley's method - re-done in C#:

In the declarative code:

<

SelectParameters><asp:SessionParameterName="USERNAME"SessionField="USERNAME"DefaultValue=""/></SelectParameters>

In the cs:

protected

void Page_PreInit(object sender,EventArgs e)

{

string strUsername = HttpContext.Current.User.Identity.Name;

Session[

"username"] = strUsername;

}

Debugging SQL Server statements in code

How can I view the raw SQL code that ASP.NET sends to SQL Server when using parameters?
The code below doesn't display the actual SQL statement, but instead:System.Data.SqlClient.SqlCommand.

string SQL = "INSERT INTO [BLAH] VALUES (@.startDate)";
SqlConnection mySqlConn = new SqlConnection(ConfigurationSettings.AppSettings["DSN"]);
SqlCommand mySqlCmd = new SqlCommand(SQL, mySqlConn);
mySqlCmd.Parameters.Add(new SqlParameter("@.startDate", SqlDbType.DateTime));
mySqlCmd.Parameters["@.startDate"].Value = Request.Form["start_date"];
lblSQLDebug.Text = mySqlCmd.ToString();
TIA.
Use SQL Profiler. It allows you to see all the SQL sent to the SQL Server. Very useful tool.|||Hi Douglas.
Following your suggestion, I setup the profiler. Unfortunately, itdoesn't receive my query before I receive the same ambigious error fromASP.NET.
Is it possible to gather the dynamic string assembled and sent to the database before it's actually executed?

|||If the query does not make it into SQL Profiler, then the problem is not a SQL Server problem. Whatexactlyis the error you are getting?|||

String was not recognized as a valid DateTime.

mySqlCmd.Parameters.Add(new SqlParameter("@.startDate", SqlDbType.DateTime));
mySqlCmd.Parameters["@.startDate"].Value = Request.Form["start_date"];
I'm using a third party component to generate the date via a pop-up calendar.
The line # of the error points here:
mySqlCmd.ExecuteNonQuery();
which seems to indicate that the SQL Query being sent to SQL Server isbad. I can't fix it until I know what the actual value of @.startDate isthat is being sent to the database server.
Thanks for helping me out.

|||This doesn't answer myquestion, but did help me debug. I enabled trace="true" and it allowedme to see how the control was submitting the values (different thanexpected).
I'm just surprised that ASP.NET doesn't have a method like RawQuerythat you could use to display the SQL before it's sent to the databaseprogramatically.
E.g., mySqlCmd.RawQuery.ToString();
I appreciate your support Douglas.
|||ASP.NET does not have such a facility. SQL Server does, but again, if the info does not get to SQL Server, of course it will not show up in the Profiler. You can, of course, debug the application and inspect values as they are added to the Parameters collection, or examine the entire Parameters collection in the debugger.