asked on

Using a variable in place of the table name in an SqlDataSource Select Command...

I am using the ASP.NET gridview control with an SqlDataSource. In my select command I need to use a variable in place of the table name because the table that i pull this data from could be changing. I figured the best way to do this would be by defining control paremeters.

So rather than

"SELECT * FROM exam WHERE examid NOT IN (SELECT examid FROM examcompleted WHERE userid = @UserName AND marketcenter = @MarketCenter) AND examid IN (SELECT examid FROM examtomarketcenter WHERE marketcenter = @marketcenter)"
Runat="server"

I would use

"SELECT * FROM exam WHERE examid NOT IN (SELECT examid FROM @ExamCompleted WHERE userid = @UserName AND marketcenter = @MarketCenter) AND examid IN (SELECT examid FROM examtomarketcenter WHERE marketcenter = @marketcenter)"
Runat="server"

I am getting the error Must declare the table variable "@ExamCompleted". when I try to run this...

all relevant code is below

Yes i realize that using a label for my parameters is not best practice and I have also confirmed that the labels text property is displaying the correct text.

<asp:SqlDataSource
id="srcAddRemove"
ConnectionString="<%$ ConnectionStrings:login %>"
SelectCommand = "SELECT * FROM exam WHERE examid NOT IN (SELECT examid FROM @ExamCompleted WHERE userid = @UserName AND marketcenter = @MarketCenter) AND examid IN (SELECT examid FROM examtomarketcenter WHERE marketcenter = @marketcenter)"
Runat="server">
<SelectParameters>
<asp:controlParameter name="UserName" ControlID="lblUsername1" PropertyName="text" />
<asp:controlParameter name="MarketCenter" ControlID="lblMarketCenter1" PropertyName="text" />
<asp:controlParameter name="ExamCompleted" ControlID="lblexamcompleted1" PropertyName="text" />

</SelectParameters>

Open in new window

ASKER CERTIFIED SOLUTION

guru_sami

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ryanfielder

ASKER

Ahh cool. I see. What if I don't have any code-behind pages? Once again I realize not good practice I had to throw this together very quickly and it has been a while since I've programmed.

guru_sami

You need a code-behind or you can do it in same page here is a ref:
http://msdn.microsoft.com/en-us/library/015103yb.aspx

SOLUTION

Gagan_Jaura

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ryanfielder

ASKER

Gagan,

Your code seems to display the page without producing an error however it only pulls data when i am statically putting the needed information in the defaultvalue section. it doesn't appear to be pulling from the e.command.parameters commands in my script.

What do you think?

ryanfielder

ASKER

i need to call this subroutine obviously. duh. how would i call it properly in my page_load event?

guru_sami

I think you want a variable table name and you cannot use it they way you do with parameters...
you have to deal with SelectCommand....which you can get access in page_load...as my initial code...
if you want to use onSelecting event then you need to set e.Command.

ryanfielder

ASKER

From looking at Gagans code it looks like you are dynamically setting the parameter with the e.Command.Parameters["ExamCompleted"].Value = "xyz"; command are you not? I figured I could set this to the value of a cookie or what have you to dynamically get my table name.

your thoughts?

guru_sami

yes, Gagans code is setting parameter VALUES dynamically and those parameters need to be part of e.Command (or SelectCommand).

I didn't get your rest of your question...or now I am not sure what exactly is the issue you are facing...

ryanfielder

ASKER

I have cookies that have the values I need to obtain. My thinking was that I could run this code at page load

e.Command.Parameters("ExamCompleted").Value = "examcompleted" & Request.Cookies("website")("marketcenter")
e.Command.Parameters("UserName").Value = Request.Cookies("website")("username")
e.Command.Parameters("MarketCenter").Value = Request.Cookies("website")("marketcenter")

and that would assign the values to my parameters. is this thinking correct?

guru_sami

yes that is correct....

ryanfielder

ASKER

OK great. I appreciate you sticking with me on this one. Last thing. How would I properly call this subroutine in my page_load? I am getting errors with the variables that i am passing up I assume when I just use subroutine()

guru_sami

no can't (or i should say you shouldn't) call that handler explicitly ....it is called when OnSelecting event is fired....
Instead you set the DefaultValue of SelectParamters like below:

srcAddRemove.SelectParameters("YourParameter").DefaultValue = yourvalue

Or why you want to do that in page_load ...?

ryanfielder

ASKER

I want the value set when the page loads so that I can have the table name dynamically set and used in my gridview. depending on certain criteria these users would call a different table. when would an onselecting event be fired? the only interactivity my users would have in this particular situation would be they are clicking on a hyperlink field in my gridview i don't see any opportunity to use an onselecting event in my situation hence the thought of assigning this variable during page_load.

guru_sami

--->when would an onselecting event be fired?
whenever your GV is databound ...i.e. whenever the datasource wants to fetch data for GV from database..

ryanfielder

ASKER

and once again codebehind is the only way to achieve this?

guru_sami

You cannot do that in markup i.e. html ... you can do it in the same .aspx page as shown in the link I provided earlier....which is called Single Page Model...

Gagan_Jaura

Hey ryan, sorry for the delay, I was out of town. What I will suggest you to define three properties. And assign these properties the values at page load. E.g
this.ExamCompletedProperty = "examcompleted" & Request.Cookies("website")("marketcenter")
this.UserNameProperty = Request.Cookies("website")("username")
this.MarketCenterProperty = Request.Cookies("website")("marketcenter")
and use these properties to fill the parameters in OnSelecting event.
If the above thing doesnt work then you can directly assign the properties to default value. Like
<SelectParameters>
<asp:parameter Name="ExamCompleted" DefaultValue="<% ExamCompletedProperty %>" />
<asp:parameter Name="UserName" DefaultValue="<% UserNameProperty %>" />
<asp:parameter Name="MarketCenter" DefaultValue="<% MarketCenterProperty %>" />
</SelectParameters>
I am not sure about the syntax "<% ExamCompletedProperty %>". But it is something similar. Right now I do not have access to my Visual Studio, otherwise I would have given you the exact syntax.
Please let me know if it works.

ryanfielder

ASKER

Thank you both for your help. I've been out of town myself and just got in two days ago. Been cleaning up messes ever since. Gagan.....this is the message I get when I run your code

BC30451: Name 'this' is not declared.

Is there an import or something I am missing?

Thanks again guys.