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

ryanfielderAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

guru_samiCommented:
You set the SelectCommand in Code-behind like:

srcAddRemove.SelectCommand = "SELECT * FROM exam WHERE examid NOT IN (SELECT examid FROM " + YOURVARIABLETABLE + " WHERE userid = @UserName AND marketcenter = @MarketCenter) AND examid IN (SELECT examid FROM examtomarketcenter WHERE marketcenter = @marketcenter)"
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ryanfielderAuthor Commented:
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.
0
guru_samiCommented:
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
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Gagan_JauraCommented:
Instead of the control parameters you can use the data selecting event. Use the below code
<script runat="server">
protected void _data_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
e.Command.Parameters["ExamCompleted"].Value = "xyz";
e.Command.Parameters["UserName"].Value = "xyz";
e.Command.Parameters["MarketCenter"].Value = "xyz";

}
</script>
<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" OnSelecting="_data_Selecting">
<SelectParameters>
<asp:parameter Name="ExamCompleted" DefaultValue="xoxo" />
<asp:parameter Name="UserName" DefaultValue="xoxo" />
<asp:parameter Name="MarketCenter" DefaultValue="xoxo" />
</SelectParameters>

0
ryanfielderAuthor Commented:
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?
0
ryanfielderAuthor Commented:
i need to call this subroutine obviously.  duh.  how would i call it properly in my page_load event?  
0
guru_samiCommented:
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.
0
ryanfielderAuthor Commented:
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?
0
guru_samiCommented:
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...
0
ryanfielderAuthor Commented:
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?
0
guru_samiCommented:
yes that is correct....
0
ryanfielderAuthor Commented:
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()

0
guru_samiCommented:
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 ...?
0
ryanfielderAuthor Commented:
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.
0
guru_samiCommented:
--->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..
0
ryanfielderAuthor Commented:
and once again codebehind is the only way to achieve this?  
0
guru_samiCommented:
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...
0
Gagan_JauraCommented:
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.
0
ryanfielderAuthor Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Editors IDEs

From novice to tech pro — start learning today.