I have following tables.
Items
ItemId
ItemName
GroupId
CatId
1
Apple
5
6
2
Banana
5
6
3
Blackberry
5
6
4
Grapes
5
6
5
Guavas
5
6
6
Beans fresh
5
7
7
Bitter Gourd
5
7
8................
I'm trying to implement a recipe search page that allows users to specify search criteria by selecting checkboxes that match desired recipe attributes. All the data is stored in a database uing tables with 1 - many relationships. Each recipe can have multiple attributes from any of 5 attribute tables. I want to find all the recipes that match the user-selected criteria and display them in a grid. For example, the user may want a breakfast (attribute 1) recipe that is made with fruit and milk (attributes 2 and 3), is low-fat and high fiber (attributes 4 and 5), and takes less than 15 minutes to prepare (attribute 6). The query for this example might look something like:
SELECT distinct recipes.name, recipes.difficulty, recipes.preptime, recipes.cooktime
FROM recipes, recipeMeals, recipeDishes, recipeFoodGrps, recipeNutrients, recipeFixTimes
WHERE recipes.id = recipeMeals.recipeId and recipes.id = recipeDishes.recipeId and recipes.id = recipeFoodGrps.recipeId and recipes.id = recipeNutrients.recipeId and recipes.id = recipeFixTimes.recipeId and recipeMeals.mealId in (1) and
recipeDishes.dishId in (5) and recipeFoodGrps.fdGrpId in (1, 6) and recipeNutrients.nutrAttsId in (2, 3) and recipeFixTimes.fixTimeId in (1)
I' can generate the SQL but can't figure out how to bind it to a grid control. No could I see how to get any of the data controls to build the query for me. Is there a way to say "execute this query and display the results in a GridView"?
I am looking to display the value of a column and divite it by 1.175and display only the result do you know how to adjust the value of a column through a select statement in SQL?
View 7 RepliesAm I going to be able to query (using LINQ to SQL) a database field with a type like the following "public System.Nullable<System.DateTime> Spouse_DOB"?
I'm trying to use the following code: [Code]....
and I get the error "'System.Nullable<System.DateTime>' does not contain a definition for 'Month' and no extension method 'Month' accepting a first argument of type 'System.Nullable<System.DateTime>' could be found (are you missing a using directive or an assembly reference?)"
Is it because this DateTime column allows a NULL value (since everyone isn't married in my database)?
Error 1 'System.Nullable<System.DateTime>' does not contain a definition for 'Month' and no extension method 'Month' accepting a first argument of type 'System.Nullable<System.DateTime>' could be found (are you missing a using directive or an assembly reference?)
C:Working WebsitesGrannys Clan Address BookDefault.aspx.cs
38 26
C:Working WebsitesGrannys Clan Addres
I am trying to do a lookup in a table with a value stored in an xml column.
I tried this several ways but I can't get it to work.
Here is some code:
[Code]....
i have the following select staement which deosnt work, can anyone advise me whats wrong with it:
string _name = ddl_Name.SelectedValue.ToString();
string cmd =
"SELECT ID, Telephone FROM User_List WHERE Name = '' + _name;
i keep getting an invalid column name error? and i know the syntax next to _name isnt correct. how are the single quotes meant to go?
I have tried to query a table with an xml column by using XQuery and I can't get the Select to work.
All my knowledge/understanding of the subject comes from this MSDN article:
[URL]
so I am probably overlooking something minor:
I tried this query Select col1, Phone.Query('element CellPhone {I am not sure about what goes here} I tried {data('Phone/cellPhone} from aTable Where Phone.Exist('/Phone/cellPhone/text()[contains.,"412-8977"]') = 1 This syntax is definitely not fun.
How do i generate another computed column that will contail the result of GrandTotal/Total,
where GrandTotal is another value comes from computed column and the Total would be the sum of the Total within SchedularID.
And thats what i have done so far: [Code]....
Consider a fairly complex business object. For the sake of discussion, let's think about a "ClassRoom" object - that will have some attributes which occur once, such as "capacity" and "area". It will also have some repeating attributes - perhaps "desks" which will itself be a collection of desk objects. The desk object will in turn have attributes such as "width" and "height".My example is more complex, but that should show where I am coming from. The business object, as you would expect, exposes methods to get the attributes in and out. There is also logic included to perform the underlying database updates. Of course, the necessary business validation is also in there.
In terms of binding to the ASP page, I will need to bind the attributes of "ClassRoom" to a set of fields on the page (using one method on the object). I will also want to bind the desks collection to a Repeater or GridView (using another method on the object).Presumably, I need to find a way to use a single instance of the ObjectDataSource so that the updates to the two (or more) disparate sets of data can be kept in step. I suspect that if I simply put two data sources on the page, I will get two instances and therefore no correlation between them.This can't be a unique problem, but I'm struggling to find any useful information.
I am using this code to find titles depending on user criteria:
[Code]....
The search is non-english charachters, and it has some problems. when you looks for "Marker" and in database you have this title: "Markers" it won't find it..... This search just not good enough and I am looking to replace it urgently....
I have two business objects mapped via LINQ to tables. One of the objects contains an instance of the other object and share a key as an identifier.I want to retrieve Class A and within class A all of Class B's values as well. What I am doing is this:
[Code]....
[Code]....
This seems a little clunky, as in slow. Is there a better way to get the Class B (User) information without making a query every time?
I want to have a paging mechanism. Its a bit hard to show all data and tables for this query but this is the current resulset.
As you can see Id has several duplicate entries (EventId as well but thats as its supposed to be):
Rownum EventID Id UserId
1 22 10 03088B17-36B2-425C-BD08-13E63AF07254
2 22 10 03088B17-36B2-425C-BD08-13E63AF07254
3 22 11 39E49E5A-7A42-43B6-882C-B22EE4C0922A
4 22 11 39E49E5A-7A42-43B6-882C-B22EE4C0922A
5 22 12 15C405BB-BD9A-4C6A-BF21-A95763FAD613
6 22 12 15C405BB-BD9A-4C6A-BF21-A95763FAD613
7 22 13 15C405BB-BD9A-4C6A-BF21-A95763FAD613
8 22 13 15C405BB-BD9A-4C6A-BF21-A95763FAD613
9 22 15 00000000-0000-0000-0000-000000000000
10 22 15 00000000-0000-0000-0000-000000000000
Now, I want to have paging and I'm doing that like this:
[code]...
In thew above query, when I leave out seltion of the RowNum column (so instead having: "SELECT distinct eventid,id,userid FROM") only 3 records are shown when I have a maximum pagesize of 5 passed to the @maximumRows variable.
What can I do to have exactly 5 records shown?
I am very new to entity, sql, c#, and asp.net so this might be something easily fixed. I am attempting to display all the inactive products stored in my table called products in a datagrid.
var productQuery = from b in solutionContext.Version
where b.Product.Name == search && b.Product.ActiveNumber > b.VersionNumber
select new Product
{
Name = b.Product.Name,
Description = b.Product.Description,
ID = b.ID,
LastNumber = b.Product.LastNumber,
MiddleNumber = b.Product.MiddleNumber,
RSTATE = b.RSTATE,
ActiveNumber = b.Product.ActiveNumber,
LastModified = b.Product.LastModified,
ParentID = b.Product.ParentID,
ProductType = b.Product.ProductType
};
ProductsGrid.DataSource = productQuery;
ProductsGrid.DataBind();
I am getting this error: $exception {"The entity or complex type 'SolutionsModel.Product' cannot be constructed in a LINQ to Entities query."} System.Exception {System.NotSupportedException}
I have written a web project using Visual Web developer 2008 and SQL 2005. I need to create a complex query and bind it to a repeater.
I have 3 tables
Table 1 - Department
Dept_Id (int) primary key
Dept_Name (vchar)
Include_Unavailable (bit)
Table 2 - Employee
Employ_Id (int) primary key
Dept_Id (int) foreign key
Employ_Name (vchar)
Table 3 - Schedule
Sched_Id (int) primary key
Employ_Id (int) foreign key
Date (datetime)
My query is as follows:
SELECT Department.Dept_Name, Employee.Employ_Name, Schedule.Date
FROM Department, Employee, Schedule
WHERE ??
In my query I want all departments and employees listed who work on a particular date. In addition, if the Department.Include_Unavailable is 'true' I want to create a row with the department and employees who work in that department even if they are not schuled to work. I will eventually list these employees as 'unavailable' but do not want to list all unavailable employees as unavailable. Only employees that work in departments that have Department.Include_Unavailable set to 'true'
1. I have a GridView on my page and it uses sqldatasource with parameterized query. What I want to do is, on page load (where nothing has been selected so no parameter supplied), I want it to query everything (something like SELECT * FROM [this_table]) but since my SelectCommand is something like
SELECT * FROM [this_table] WHERE [this_column] = @someParameters AND [that_column] = @someParameters.
Can I play around with default value to achieve something like that but how ? Now, when the page loads, it doesn't show anything (No Gridview).
2. On my page, I made something like (username, gender, address, and more) and one single search button. That means, no single control enable auto postback. What I am trying to accomplish is building dynamic query
(if username specifed -> SELECT * FROM [this_table] WHERE [username] LIKE @username).
If both username and gender are specified (SELECT * FROM [this_table] WHERE [username] LIKE @username AND [gender] = @gender) and you know the rest. How can I do this using GridView and SqlDataSource ? To my knowledge, I can only specify one SELECT statement in a sqldatasource.
[code]...
This query works perfectly on the query analyser.
But when configuring the Table adapter ,I try executing the query and i get 0 rows affected.
What could I be getting wrong in this case.
NB:Existing GalleryID has been supplied.
By right clicking on my database i created a query in server explorer. But where are this query stored can't find them back. I should aspect that their is a folder query's like there is a folder tables but this isn't the case.
View 10 Repliesselect Groupid,GroupName,onorusername from palgroup where groupid in (select distinct Groupid
View 5 RepliesI've got a query such as
Dim MediaQuery =
From m
In dB.DOWNLOADS _Where m.ID = id _Select
which returns a record from the database. One of the fields in the record is a FK to another table. I need to read this value to be able to set a dropdown based on the ID but I can't work out how to access it. For a standard record I can just do the following txtTitle.Text = MediaQuery.FirstOrDefault().TITLE
However with the foreign key it doesn't work like that. I've tried drpGroup.SelectedIndex = MediaQuery.FirstOrDefault().DOWNLOAD_GROUPS.ID where DOWNLOAD_GROUPS is the FK field but it returns Object reference not set to an instance of an object. If you're simply wanting to read some values from a single db record in the entitiy framework and one is a foreign key how should I go about getting the value?
I'm currectly tryin to access a specific value in my database in a specific column. I'm tryin to use the primary key of the table to access the actual value in the row of that ID.
The current code:
[Code]....
Just iterates through the table and looking for any row with the boolean 'checkin' value is True, then it takes the 'id' value of that row and stores it into an array. Is there a way that I can access a another entry, and only that entry, in the table with the 'id' stored in the array?
Like if my data base contained and int, primarykey, `id` value, a boolean, `checkedin` value, and a `time` value, is there a way to access, lets say, the row with `id` equalling 3 and and the checkIn value from that row?
I'm trying to retreive a column value from gridview5 to use it in a query to display resuls in gridview, Also how do you hide a column in the grid?
Where (p.Link_ID Like GridView5.?
[code]....
i have a datatable with several columns and rows. now i want to copy the last column fully (all rows) and create the new column with that values.
it means last column values sholud be moved to new column (now this is the last column).
i want to add a column on to the end of the gridview that runs an sql query based on that ID i.e! select * from thistable where id = (0). so basicaly i want to bind a sql query as a column!! how would i do this?
View 5 RepliesI want to know how to count items/products using stored procedure in Asp.net C#.Example : I am using repeater control to display Categories.
Categories-------------Shoes : 2Paint : 7Skirts : 204Abaya : 1Burka : 6-------------------------------
I have Written stored procedure "GetItemCount" but how to call it in c# code i do not know. It is working fine Query showing result as per my desire.
SELECT id, category, ( SELECT COUNT(id) FROM entry_table WHERE category.id = entry_table.cat_id) as cnt FROM category
Check screen-shot [URL] .....
-------------------<asp:HyperLink ID="HyperLink2">
<%#DataBinder.Eval(Container,"DataItem.Category")%>Â Â Â ( <%# count here %> )</asp:HyperLink>
Scenario:
I am creating a custom gridview control which has a custom CheckBoxTemplateField column (deriving from TemplateField class). This template field column has custom Checkboxheadertemplate (implementing ITemplate) and CheckboxItemTemplate (also implementing ITemplate). In InstantiateIn method of both templates (header as well as Item template), I am adding a checkbox control which has Autopostback = true.
My requirement is:
I want to mark all the values in the binding column of datasource if checkbox in headertemplate is checked. I dont want to mark only rows visible on grid. I WANT TO MARK ALL ROWS IN DATASOURCE. I want to do this in _CheckedChanged event of checkbox in header template.
Problem I am facing: When I check/uncheck the checkbox in header, it postbacks. so in OnCheckedChanged event, gridview's datasource is null. Secondly, in any event of gridview, I could access only those rows of datasource for which corresponding rows are visible in gridview through Gridviewrow.DataItem property. But I want to set it for all rows in datasource.