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.
