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?
 
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
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.