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

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]

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.EventArg s)
Dim strNTUser As String
Dim iPos As Integer
strNTUser = RTrim(Request.S erverVariables( "LOGON_USER "))
iPos = Len(strNTUser) - InStr(1, strNTUser, "\", CompareMethod.B inary)
strNTUser = Right(strNTUser , iPos)
End Sub

In ASP I try to use the value of strNTUser as the where clause condition:
<asp:SqlDataSou rce ID="oraDataSour ce" runat="server" ConnectionStrin g="<%$ ConnectionStrin gs:oraDB %>" ProviderName="< %$ ConnectionStrin gs:oraDB.Provid erName %>" SelectCommand=' SELECT * FROM "MY_ASSOCIA TES" WHERE ("USERID" = :USERID)'>
<SelectParamete rs>
<asp:SessionPar ameter Name="USERID" SessionField="s trNTUser" Type="String" />
</SelectParameter s>



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:

[code=asp]
<asp:SqlDataSou rce ID="oraDataSour ce" runat="server" ConnectionStrin g="<%$ ConnectionStrin gs:oraDB %>" ProviderName="< %$ ConnectionStrin gs:oraDB.Provid erName %>" SelectCommand=' SELECT * FROM "MY_ASSOCIA TES" WHERE ("USERID" = <%=strNTUser %>)'>
[/code]

[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.Ora cleClient.Oracl eException: 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]

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]

I am not positive but maybe something like:

[code=asp]
<asp:SqlDataSou rce ID="oraDataSour ce" runat="server" ConnectionStrin g="<%$ ConnectionStrin gs:oraDB %>" ProviderName="< %$ ConnectionStrin gs:oraDB.Provid erName %>" SelectCommand=' SELECT * FROM "MY_ASSOCIA TES" WHERE ("USERID" = <%=strNTUser %>)'>
[/code]

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.EventArg s)
Dim strNTUser As String
Dim iPos As Integer
strNTUser = RTrim(Request.S erverVariables( "LOGON_USER "))
iPos = Len(strNTUser) - InStr(1, strNTUser, "\", CompareMethod.B inary)
strNTUser = Right(strNTUser , iPos)
End Sub

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

<asp:SqlDataSou rce ID="oraDataSour ce" runat="server" ConnectionStrin g="<%$ ConnectionStrin gs:oraDB %>" ProviderName="< %$ ConnectionStrin gs:oraDB.Provid erName %>" SelectCommand=' SELECT * FROM "MY_ASSOCIA TES" 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]

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.