Passing a outside variable into a <asp:sqldatasource> tag. ASP.NET 2.0

Question

I'm designing some VB based ASP.NET 2.0, and I am trying to make more use of the various ASP tags that visual studio provides, rather than hand writing everything in the code-behind. I want to pass in an outside variable from the Session to identify who the user is for the query.

<asp:sqldatasource id="DataStores" runat="server" connectionstring="<%$ ConnectionStrings:MY_CONNECTION %>"
          providername="<%$ ConnectionStrings:MY_CONNECTION.ProviderName %>"
          selectcommand="SELECT THING1, THING2 FROM DATA_TABLE WHERE (THING2 IN (SELECT THING2 FROM RELATED_DATA_TABLE WHERE (USERNAME = @user)))"
          onselecting="Data_Stores_Selecting">

          <SelectParameters>
          <asp:parameter name="user" defaultvalue ="" />
          </SelectParameters>

          </asp:sqldatasource>

And on my code behind I have:

Protected Sub Data_Stores_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Handles Data_Stores.Selecting

    e.Command.Parameters("user").Value = Session("userid")
End Sub

Oracle squaks at me with ORA-01036, illegal variable name. Am I declaring the variable wrong in the query? I thought external variables share the same name with a @ prefixed. from what I understand, this should be placing the value I want into the query when it executes the select.

EDIT: Okay, thanks for the advice so far, first error was corrected, I need to use : and not @ for the variable declaration in the query. Now it generates an ORA-01745 invalid host/bind variable name.

EDIT AGAIN: Okay, looks like user was a reserved word. It works now! Thanks for other points of view on this one. I hadn't thought of that approach.

Answer 1

I believe Oracle uses the colon ":", not the at-symbol "@".

---

"user" is probably a reserved word. Change it to "userID", or something similar.

Answer 2

You may want to consider using a SessionParameter instead of just a Parameter and let the SqlDataSource extract the user id directly from the session without any intervention on your part. Also, the example on the page linked above seems to imply that you should use ? instead of @user for parameter replacement for an ODBC connection. I think the parameter replacement would be done by the SqlDataSource and not passed to Oracle, that is it would substitute the actual value of the user id in place of the parameter (properly quoted of course) before sending the query to the database.

<SelectParameters>
  <SessionParameter Name="userID" SessionField="user" DefaultValue="" />
</SelectParameters>

Answer 3

Using ASP.NET's SessionParameter is definitely the way to go here - that's why we have it :)

Using ASP.NET parameters you can easily include in your queries values from static sources, session state, query string, control property values, form post data, cookies, and user profile.

Answer 4

SO JUST TO CLARIFY... THE CORRECT SOLUTION IS....

" providername="<%$ ConnectionStrings:MY_CONNECTION.ProviderName %>" selectcommand="SELECT THING1, THING2 FROM DATA_TABLE WHERE (THING2 IN (SELECT THING2 FROM RELATED_DATA_TABLE WHERE (USERNAME = @user)))" onselecting="NAME_OF_SUB_Selecting">

      <SelectParameters>
      <asp:parameter name="@user1" defaultvalue ="" />
      </SelectParameters>

      </asp:sqldatasource>

Protected Sub NAME_OF_SUB_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Handles Data_Stores.Selecting

e.Command.Parameters("@user1").Value = Membership.GetUser.ProviderUserKey.ToString()

End Sub

Answer 5

can u give me this code in c#