I have 2 Tables , OrderDetails and Requests In my LINQ to SQL dbml file. OrderDetailsID is a foreign key in Requests Table. I want to write the query to get the sum of UnitCost from OrderDetails based on OrderId. And If there is a row in Requests Table for each OrderDetailsID, and the Requests.Last.RequestType="Refund" I want to reduce the total refund amount from the main sum otherwise If there is no row based on OrderDetailsID, add to sum. Here is the way I implement that. I am looking to prevent using "For each".
iRefund = (From od1 In dc.OrderDetails _ Where od1.OrderID =1 _ Select od1.UnitCost).Sum Dim objOrderDetails = (From od1 In dc.OrderDetails _ Where od1.OrderID =1 _ Select od1) For Each OrderDetail As ORM.Entities.OrderDetail In objOrderDetails If Not OrderDetail.Requests Is Nothing Then IF OrderDetail.Requests.Last.RequestType="Refund" Then iRefund -= OrderDetail.UnitCost End If End If Next
I need to know how can I select a gridview row based on a query string data. In my case, the query string will contain the id that exist in the Table related to that grid. Is there a way to do this from the code behind? In other words, if I click a link with that specific query string, can I open the page and tell my grid to select a specific row based on the table row id?
Heres the situation: I need update a column from my table (T1) from two other columns in a different table (T2).
My constraints are:
That I have to match the first 4 letters of a column in T2 to one column in T1 I have to identify that the first letter in a column in T1 corresponds to one letter in the middle of a string in a column in T2 For instance:
My Table (T1):
Order Type Combined Place 0090 0001 YYXX 1YY 0091 1001 YYXX YYY 0092 1002 XXXX 2XX
Table 2 (T2):
Order Value 00900001YY XX 00911001YY XX 00921002XX XX
The Combined column in T1 is what i'm trying to complete. The T1.Place column contains the first character that I want to check for.
If it's a "1" then I want to make sure the 5th letter in T2.Order is a "0" If its a "Y" then I want to look for a "1001" If its a "2" then I want to make sure the 8th letter is a "2" in T2.Order If all that matches then I was the last two letters in T2.Order + T2.Value to be combined and put into the appropiate spot in T1.Combined
Here's what I have:
[Code]....
I know it's a little complex, but i'm really stuck on it and any help would be greatly appreciated.
i have been having trouble trying to find out what is wrong with my sql select query... i wanted to select a sessionID by comparing the tourID and dates. the dates datatype at the database is datetime and the value of itemSessionDate is in string. i tried to convert itemSessionDate to date and dateAndTime but it gave me mm/dd/yyyy while the date in my database is dd/mm/yyyy hh:mm:ss. so i thought i can try using the itemSessionDate string as it has the same value as the one return from the database. but it keeps on goin to the if not reader.hasRows and quit the statement
* 100 AS PERCENTAGE FROM support.SRFILE INNER JOIN BSFLBWF AS BSFLBWF_1 ON BSFLBWF_1.Incidentx = SRFILE.SRONUMBER WHERE (BSFLBWF_1.Closex IS NULL) AND (BSFLBWF_1.DedTypex = 'School' OR BSFLBWF_1.DedTypex = 'LAN-MLE') UNION ALL
The rest of the query continues as the above albeit selecting the same data from different tables and works fine.
The issue I have is that I want to Select some more data (another SELECT query) but with a different Where clause e.g. WHERE (BSFLBWF_1.KPIx NOT LIKE 'KPI%' OR BSFLBWF_1.KPIx NOT LIKE 'Proactive%') AND (BSFLBWF_1.Availabilityx LIKE '')
The problem is that this WHERE clause returns data which the above in bold has null values for (and thus doesnt work). Im still selecting the same data its just the WHERE Clause has changed. Is it possible to run such a query? If so, can it be done via a sub query?
Id - Date - Type 1 - 2011-03-01 - 1 2 - 2011-03-02 - 0 3 - 2011-03-03 - 0 4 - 2011-03-04 - (-1) 5 - 2011-03-04 - 0
Can I somehow select the records between a record having type 1 and type -1 without using a cursor,for example the records with ids 1-4 in this example?
I have an SQL 2005 db with a table named "industry_suppliers."
I've created a query to enable someone to search this table using 3 parameters.
The results need to be grouped by "product_description" (which is essentially a category). When displayed on an .aspx page, for example, the results need to look like this:
Apples
Acme Apple Company Bradbury Orchards Oranges A1 Citrus Company Belfast Growers, Inc Bradbury Orchards
Notice how "Bradbury Orchards" is repeated under both categories. This is the result I want, EXCEPT in cases where someone wants to filter the search results by category. Let's say someone wants to search just for apple suppliers. The problem I'm having is that any record that appears under apples will also get listed again under any other categories associated with that record. So, searching for apple suppliers, the results are this:
Apples Acme Apple Company Bradbury Orchards Oranges Bradbury Orchards
I'm having trouble figuring out how to revise my query to avoid this problem.
I have partitioned a table and it has been partitioned according to an account id. ie also rows that have accountid =1 will be in A partition and all rows that have accountid = 2 will be in B partition. so now if i execute a query with where condition accountid =1 sql server will search only in A partition. Suppose if i add another condition to it ie where accountid=1 and place="aaa" will this query search only A partition or the entire table?
I have stored procedure which selects records from an SQL table based on a bunch of user-input parameters.Just discovered that if a record in the table has null values in some of the columns (I haven't figured out which ones yet), then the SELECT is not returning the record (even if it satisfies all the parameters).The SELECT statement is supposed to allow for nulls, and I've been over it a bunch of times and am not sure what I'm doing wrong.Or do I need to get rid of all the null values in the SQL table, and prevent new ones from being introduced?
I have an asp.net site which includes a "search" page that queries an SQL table, then redirects the user to a "results" page where the results of the query are displayed. The parameters for the query are assembled into a string which is the SELECT statement. I've been asked to add additional functionality that requires a secondary search which takes place on the "results" page. How can I pass the parameters from the "search" page to the "results" page so that I can access those parameters in the code behind? Here's the first page code behind (I inherited most of this from another developer):
[Code]....
And the second page--it would be on line 51 that I would need to insert the parameters from the search above:
I have inherited a very old SQL Server Database for a live site, but have been asked to add a new table for invoice recording to it. Fairly easy to do for any new record added from now on, however I run into some issues with selecting older records.I need to find a way to select from 3 tables, see if a record exists in the new table and if not insert a new record to it.My stored procedue so far is (The new table is [Invoicing]:
in my table data are stored many times ...I want select the only one data from the repeated data
select ven.Id,ven.Vendor_Name from Purchase_Requsition pur,Item_master itm,Approved_Vendors ven where itm.Item_Id=pur.Item_masterItem_Id and ven.Id=itm.Approved_VendorsId and pur.Request_StatusId=1 and pur.StatusId<>4
ID VENDOR_NAME 1 Ranbaxy 1 Ranbaxy 1 Ranbaxy 2 cipla
Is there a way to select data from multiple tables other than the UNION ALL statement. Addtionally I need to select data from a different database.
My current statement is as follows (there are alot more fields but I have shortened it ):
SELECT server.dbo.SRFILE.SR_GROUP, server.dbo.SRFILE.SROWNERSHIP, server.dbo.SRFILE.SRCONMAE, BSFLBWF_1.Incidentx, BSFLBWF_1.Severityx FROM server.dbo.SRFILE INNER JOIN BSFLBWF AS BSFLBWF_1 ON BSFLBWF_1.Incidentx = server.dbo.SRFILE.SRONUMBER WHERE (BSFLBWF_1.Closex IS NULL).........
