ASP.NET - How to display data of the selected table in a view.

Asked By Sameer Khan on 16-Apr-12 01:21 AM
Hi,
I have a View which has a dropdownlist.This dropdownlist populates the names of the tables that are present in my database. When the user selects the particular table from the dropdownlist and clicks search then the record of that table should get filled in the same view.I want a query in Linq or in Sql or in Entity SQL that can get the records of the selected table.
Below is what i have tried...but nothing working.
string str = "SELECT VALUE emp FROM " + TableName + " AS emp Where emp.Delete_Flag=false";
var objmodel = db.CreateQuery<object>(str).ToList();
ViewBag.model = objmodel;

In the above code im getting exception as The query syntax is not valid. Near keyword 'AS', line 1, column 53.
Please guide what can i do to get the required result
Danasegarane Arunachalam replied to Sameer Khan on 16-Apr-12 01:28 AM
The syntax is correct.

You are trying to create the alias for the entire table which is causing the error

The exact syntax is


string str = "SELECT VALUE emp FROM " + TableName + " Where emp.Delete_Flag=false";


And you could use the string.format method to do things better for your


string str = String.Format("SELECT VALUE emp FROM {0}  Where emp.Delete_Flag=false",TableName");

Hope this helps
Anoop S replied to Danasegarane Arunachalam on 16-Apr-12 01:52 AM
If you want to pass tablename as a parameter  the use SP like this way

CREATE PROCEDURE Dynamic_SP

    @Table_Name sysname

AS

BEGIN

    SET NOCOUNT ON;

    DECLARE @DynamicSQL nvarchar(4000)

    SET @DynamicSQL = 'SELECT * FROM ' + @Table_Name

    EXECUTE sp_executesql @DynamicSQL

END

GO

Sameer Khan replied to Danasegarane Arunachalam on 16-Apr-12 02:05 AM
thanks for ur reply...
but wen i remove the alias name im getting error

'emp.Delete_Flag' could not be resolved in the current scope or context. Make sure that all referenced variables are in scope, that required schemas are loaded, and that namespaces are referenced correctly. Near member access expression, line 1, column 83.
Danasegarane Arunachalam replied to Sameer Khan on 16-Apr-12 02:08 AM
I doubt the table alias is causing the issue.

Try this one

string str = "SELECT VALUE emp FROM " + TableName + " Where Delete_Flag=false";


One more question does the table has the column name Delete_Flag ?

Did you check the database column definition ?
Sameer Khan replied to Danasegarane Arunachalam on 16-Apr-12 02:20 AM
yes all the tables in my database are having the Delete_Flag column...
when i run the query with the alias name its giving different error (the one i posted in the last post)...
Somesh Yadav replied to Sameer Khan on 16-Apr-12 02:29 AM

YOu strongly typed your view with a model of type list. However you pass a single class that contains two lists. Thus please correct the @model directive of your view. Moreover, once you get data with linq, before putting them in a VieModel transform them into lists:

(....linq statemnent.....).ToList()

otherwise they are IQueryables, and IQueryables may cause problems when put in a View.

About the two data....simple put them into two different html tables :) if this make sense in your application.

If you want to align them someway, displaying them on the same htmlTable you have two merge couples of objects in the two lists into single objects containing properties form both objects