ASP.NET Include local variable in Oracle Select Statement

jinksto

Hello,

How do I insert a local variable into a select statement when using the Oracle DB connecter in asp.net 2.0

Expand|Select|Wrap|Line Numbers

 
In Code Behind:  I set this the string to be the users id:

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

        Dim strNTUser As String

        Dim iPos As Integer

        strNTUser = RTrim(Request.ServerVariables("LOGON_USER"))

        iPos = Len(strNTUser) - InStr(1, strNTUser, "\", CompareMethod.Binary)

        strNTUser = Right(strNTUser, iPos)

    End Sub
 
In ASP I try to use the value of strNTUser as the where clause condition:

<asp:SqlDataSource ID="oraDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:oraDB %>" ProviderName="<%$ ConnectionStrings:oraDB.ProviderName %>" SelectCommand='SELECT * FROM "MY_ASSOCIATES" WHERE ("USERID" = :USERID)'>

        <SelectParameters>

            <asp:SessionParameter Name="USERID" SessionField="strNTUser" Type="String" />

        </SelectParameters>

When I run this, I get no result. as the USERID doesn't seem to be fetting set. I realize that I'm probably doing something silly with this but I'm rather new with ASP.net and I'm trying to understand wherre I'm going wrong. Thanks for your help.

jinksto

Quote:

Originally Posted by jinksto

Hello,

How do I insert a local variable into a select statement when using the Oracle DB connecter in asp.net 2.0

In Code Behind: I set this the string to be the users id:
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
Dim strNTUser As String
Dim iPos As Integer
strNTUser = RTrim(Request.ServerVariables("LOGON_USER"))
iPos = Len(strNTUser) - InStr(1, strNTUser, "\", CompareMethod.Binary)
strNTUser = Right(strNTUser, iPos)
End Sub

In ASP I try to use the value of strNTUser as the where clause condition:
<asp:SqlDataSource ID="oraDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:oraDB %>" ProviderName="<%$ ConnectionStrings:oraDB.ProviderName %>" SelectCommand='SELECT * FROM "MY_ASSOCIATES" WHERE ("USERID" = :USERID)'>
<SelectParameters>
<asp:SessionParameter Name="USERID" SessionField="strNTUser" Type="String" />
</SelectParameters>

When I run this, I get no result. as the USERID doesn't seem to be fetting set. I realize that I'm probably doing something silly with this but I'm rather new with ASP.net and I'm trying to understand wherre I'm going wrong. Thanks for your help.

I've also tried using other methods. What should I select in the Add WHERE clause dialog for "Source:" in order to do this? Control, Cookie, Form, Profile, Querystring, Session? Lacking solid information I selected "Session" but I think that is probably for Session Variables. Not sure what to put there in order to use a derived variable in the SQL string.

Plater

I am not positive but maybe something like:

Expand|Select|Wrap|Line Numbers

 
<asp:SqlDataSource ID="oraDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:oraDB %>" ProviderName="<%$ ConnectionStrings:oraDB.ProviderName %>" SelectCommand='SELECT * FROM "MY_ASSOCIATES" WHERE ("USERID" = <%=strNTUser  %>)'>

jinksto

hi, thanks for your response. If I do that I get:

ORA-00936: missing expression

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.Data.OracleClient.OracleException: ORA-00936: missing expression

I really only want to inject the value off strNTUser into the SQL string. You would think that this would be a rather easy thing to do but I'm failing to grasp some core concept.

Plater

You could always create the whole object in the code side and not in the asp-page

jinksto

Quote:

Originally Posted by Plater

You could always create the whole object in the code side and not in the asp-page

While I appreciate the elegance, that seems like a long way to go just to add a single value to the parameter string.

jinksto

Quote:

Originally Posted by Plater

I am not positive but maybe something like:

Expand|Select|Wrap|Line Numbers

 
<asp:SqlDataSource ID="oraDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:oraDB %>" ProviderName="<%$ ConnectionStrings:oraDB.ProviderName %>" SelectCommand='SELECT * FROM "MY_ASSOCIATES" WHERE ("USERID" = <%=strNTUser  %>)'>

It seems like the strNTUser variable isn't getting set in the SQL. If I'm setting strNTUser in Page_Load event is it still in context when the ASP portion of the page runs? Here's where I create strNTUser:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
Dim strNTUser As String
Dim iPos As Integer
strNTUser = RTrim(Request.ServerVariables("LOGON_USER"))
iPos = Len(strNTUser) - InStr(1, strNTUser, "\", CompareMethod.Binary)
strNTUser = Right(strNTUser, iPos)
End Sub

And I'm not using it until much later in the ASP

<asp:SqlDataSource ID="oraDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:oraDB %>" ProviderName="<%$ ConnectionStrings:oraDB.ProviderName %>" SelectCommand='SELECT * FROM "MY_ASSOCIATES" WHERE ("USERID" = <%=strNTUser %>)'>

Am I even going in the right direction with this...?

Plater

I think you will need to make that varriable at the very least Page Global in order for the ASP side to use it.

I'm not sure though, I almost never do anything ASP side if I can help it.

jinksto

Quote:

Originally Posted by Plater

I think you will need to make that varriable at the very least Page Global in order for the ASP side to use it.

I'm not sure though, I almost never do anything ASP side if I can help it.

I found the issue, I needed to set the variable in the "selecting" event. In the end I created a function to return the LOGON_USER and called that from the selecting event to set the variable.

So technically, you were correct, the strNTUser was out of context in the place that I was trying to use it.

ASP.NET Include local variable in Oracle Select Statement

re: ASP.NET Include local variable in Oracle Select Statement

re: ASP.NET Include local variable in Oracle Select Statement

re: ASP.NET Include local variable in Oracle Select Statement

re: ASP.NET Include local variable in Oracle Select Statement

re: ASP.NET Include local variable in Oracle Select Statement

re: ASP.NET Include local variable in Oracle Select Statement

re: ASP.NET Include local variable in Oracle Select Statement

re: ASP.NET Include local variable in Oracle Select Statement

Similar .NET Framework bytes

/bytes/development

/bytes/it

/bytes/about