DataSource Controls :: Combining Two Queries - Count For Each City As Whole
Feb 4, 2010
I know there has to be a way to do this but for some reason I keep getting it wrong. I have one query which pulls all of the cities from a zipcode database like so:
[Code]....
Then I have another which counts the records from a transactions table per city, like so:
[Code]....
Only the zipcode is stored in the transactions table, so the idea here is to get a list off all the distinct cities that reside in the state. Once I have the cities, I want a count for each city as a whole, so I need to go back in and get all of the zipcodes that make up one city, and then count the transactions for those zipcodes. It works using the first query above, looping through them and then executing the second for every pass. But shouldn't there be a way to do it all in one query?
I need to do some performance testing for an ASP.NET MVC application and specifically want to measure how many database queries each HTTP request results in. The data access layer is using LINQ-to-SQL. I'd like to be able to automate the tests so am looking for a good way to be able to do a before and after count of the number of SQL queries. The possibilities I can think of are: A performance monitor counter for the ASP.NET process, A system table in the MSSQL database that can be queried before and after each page request, A property somewhere in the System.Data.SQLClient namespace that tracks the number of queries executed by the process. Has anyone done this before and can perhaps point my down the right path?
Ok I hope I can describe this clearly. This is all demo code nothing in production.Right now I have a stored procedure that is
[Code]....
This will return only one row as ID is uniquethen I have a procedure that gets all the colors that are available for this model number and i use that data in a drop down list, it is like this
[Code]....
this can return multiple rows with different colors.Right now i am opening a connection getting the product data, consuming that to properties of a class, then to get the data from the Colors I open another connection run the procedure with the model parameter being the model returned from the first sproc and bind that to my ddl.I want to do this in one connection and just get two result sets back. So how do I set the parameter of the second procedure to be the model from the first?
I want to insert a record into a log table ONLY if one exact copy already doesn't exist within ten days, what is a good way to do in one stored procedure what I do above in two?
I am trying to create a multi column listbox by combining fields in Linq select statement, as Let Name = Last + First. The problem is how to pad the Last column with spaces as you would in SQL using REPLICATE so when I bind the listbox to the data, it looks like two columns.
SELECT 1000000*QuantityNC/Quantity AS PPM FROM [table1] However I need my WHERE clause to do thisI need the above calculation depending on BusUnit, which is linked to PartNumber, which is in the above table. also it has to be by Company (which is selected from a dropdown). And also based on the current month and year.
PartNumber and CompanyName are in [table1] BusUnitID, PartNumber, SupplierID, are in tblParts BusUnitID and BusUnit are in tblBusUnits
I need to fire sql queries in my website and get the results from the Database. for example: I will give select * from emp in a text box. It shud return the results for me. DO any of u have a sample/links of such application, through which i will be able to implement in my application share me the links/sources/app if u have any.
I get from SQL Server Server Data. I need to use datas form 2 colums of my SQL query for a VB.NET. The VB.NET need 2 values for both colums and produce 1 answer. I what to display the value on the same line instead of the 2 1st values. How can i store the 2 colums wich lot of SQL values on my vb.net script and diplay the result with the other data from the SQL query ?
I'm currently working on a FOREX Live Trading project that I'm implementing.
I already have a database that stores the CURRENCY PAIR, BUY/SELL, PRICE AND QTY.
But I have a huge problem.
Scenario that is:
A person BUYS EUR/USD at a PRICE of 1.29900 @ quantity 100,000. (This will be recorded in the database table)
Then he BUYS EUR/USD at a PRICE of 1.29950 @ quantity 100,000. (This will then be recorded in the database also)
Then he decides to SELL his EUR/USD at a PRICE of 1.31000 @ quantity 200,000 all out. (How can I check from the database if he previously had make a transaction of BUY/SELL, so that I can do my necessary calculations for the P/L Statement)
I am having trouble with queries because they are not returning the data needed, and I am having trouble figuring out why because I only am testing with 2 records.First I want a query to return all records for one customer as follows:
[Code]....
There are two records with the customerID of 1, but it only returns one,next I try to return only one record, the AccountPrimary or customer_contacts.IsPrimary, but when I add " AND customer_contacts.IsPrimary = 'True' "at the end of my query it then returns both records, but only one IsPrimary = True and the Other = False. Is it backwards? I also tried another method and it returned 30 of the same record and I only have 2
I am planning a project for a pharmacy to enter quality control data to generate reports and graphs with Crystal Reports. There are 20 stores, I need to password protect the entrance page, giving each store their own username and password (just one per store is fine), allowing the store to only access that store's data. Each store will have an associated ID in the MSSQL table.I am using the Membership Provider. I know I need to implement user roles for Managers and Stores levels (Managers can run reports for all stores), I wonder if I can set the default store ID for insert/edit templates after that specific store logs in.Basically, I think I need to filter SQL queries based on the store ID associated with a user ID. I have seen a .NET site that, after logging in, you type in a store id into a textbox, and it gives you access only to that stores data. Something like this would work for my situation.Can anyone explain or show me an example for the best method to accomplish this with C#?
I am working on asp.net 3.5 and in MVC using Linq to SQL classes. In my MVC project i am calling stored procedure using linq to sql that conatin query joining two tables.Below is my code in Partial class
[ Function(Name="dbo.GetUserWiseThreadDetail_SP")] [ResultType(typeof(Thread))] [ResultType(typeof(Reply))] public IMultipleResults GetUserWiseThreadDetail_SP([Parameter(DbType="VarChar(30)")] string userID) { ExecuteResult result = this.ExecuteMethodCall(this, (MethodInfo)(MethodInfo.GetCurrentMethod())), userID); return ((IMultipleResults)(result.ReturnValue)); }
In one of the controller i have made one more class
public class MypostIndexData { public IEnumerable<Thread> Thrds { get; set; } public IEnumerable<Reply> Repls { get; set; } public MypostIndexData(IEnumerable<Thread> thrds,IEnumerable<Reply> repls) { this.Thrds=thrds;this.Repls=repls; } }
finally coding of controller action method
ForumDataContext pmforum = new PMForumDataContext(); public ActionResult MyPosts(string userId) { IEnumerable<Thread>thrds; IEnumerable<Reply>repls; IMultipleResults result = pmforum.GetUserWiseThreadDetail_SP(userId); (this satement doesnot give any result,if i execute stored proc in sql it give the results) thrds = result.GetResult<Thread>(); List<Thread> thrdlist = new List<Thread>(thrds); repls = result.GetResult<Reply>(); List<Reply> replylist; if (repls != null) { replylist = new List<Reply>(repls); } return View(new MypostIndexData(thrds,repls));
I am unable to understand why i am not getting any results?
I am accepting query/queries from user (our support team) in a text box where user can enter only one query or multiple queries. I need to display result of all queries entered in the textbox. If it is SELECT statement then result of that statement in grid which is done. If UPDATE/INSERT/DELETE then total number of rows affected which is also done but if only one UPDATE/INSERT/DELETE statement is entered in the textbox.
If user enters 2 UPDATE statements and then a SELECT statement then how can I get number of rows affected for individual UPDATE statements just like SQL Server Qury Analyzer displays messages in its result pane.
e.g.
(6 row(s) affected) -- first UPDATE statement
(4 row(s) affected) -- second UPDATE statement
(16 row(s) affected) -- for SELECT statement (grid will also be displayed along with these messages).
I tried almost everything, SqlDataSource (returns count of first statement only), SqlCommand.ExecuteNonQuery and DataAdapter.Fill (returns count of last statement only).
In general terms, which way will require more server processing power and which way will return faster queries... if there is even a difference. The client's database is still on the dev MS SQL server but is essentially but the client has decided they want to collect two more fields of information. Both fields are going to be collection text data and probably varchar(300) will be sufficient for the column data types. Would it ultimately be more efficient to
Add two additional columns in the main table so the data is entered directly into each record, or Add an additional table (or two) with a foriegn key to the primary table that holds the info for each new field The SELECT statement for the main table could be modified to either add the new columns (a.NewColumn1 and a.NewColumn2) or it could contain nested SELECT statements that pull in the data from the other tables. Custom daisy-chained Insert or Update statements would be no problem and a cascade Delete relationship would take care of any deletions. I'm just trying to figure out which would be better from a performance standpoint?
I have a gridview that pulls data from two tables, CLIENT and PAYMENT. I want to add the edit, delete functions to the gridview for the payment portion of the table, that is to edit or delete the payments but not the client. I've manually enabled the edit, delete functions and input the queries. I've also deleted the textboxes from the templates which draw from the CLIENT table so they cannot be edited. Everything looks good on the surface until I actually go to edit or delete anything, and it has no effect.Here's the structure of the two tables:
UPDATE [PAYMENT] SET [PaymentDate] = @PaymentDate, [PaymentAmount] = @PaymentAmount, [PaymentDetails] = @PaymentDetails WHERE [PaymentNumber] = @original_PaymentNumber AND [CLIENT$ClientID] = @original_CLIENT$ClientID AND [PaymentDate] = @original_PaymentDate AND [PaymentAmount] = @original_PaymentAmount AND (([PaymentDetails] = @original_PaymentDetails) OR ([PaymentDetails] IS NULL AND @original_PaymentDetails IS NULL))
Finally, here's the delete query:
DELETE FROM [PAYMENT] WHERE [PaymentNumber] = @original_PaymentNumber AND [CLIENT$ClientID] = @original_CLIENT$ClientID AND [PaymentDate] = @original_PaymentDate AND [PaymentAmount] = @original_PaymentAmount AND (([PaymentDetails] = @original_PaymentDetails) OR ([PaymentDetails] IS NULL AND @original_PaymentDetails IS NULL))
I have a scenario where I need 2 dropdowns like state and city.City dropdown depands upon on State drop down.Will you please tell me how to achieve it ASP.NET MVC?I found this article but not sure is there any alternate way to do?http://stephenwalther.com/blog/archive/2008/09/07/asp-net-mvc-tip-41-creating-cascading-dropdown-lists-with-ajax.aspx
i want to get Same Row value Count From Table In sqlserver2005
My table Structure Is below
ID Day1 Day2 Day3 Day4 Day5 Day6 Day7 Day8 Day9 Day10 Day11 Day12 Day13................. 5401 NULL NULL NULL NULL NULL H H P P P P P P .................
here i want to get Total No of P values,Is this possible?
I have three tables nameTbl contains the following columns: BarID, Name, Description locTbl contains locID and Location namelocationTbl contains BarID(from the nameTbl) and locID(from the locTbl)
im trying to count the amount of users inside a chatroom, im trying to count a specific row using LINQ to SQL.
Heres the code
DataRowView dataRow = ((DataRowView)e.Item.DataItem); string roomId = dataRow["RoomID"].ToString(); ChatRoomDataContext db = new ChatRoomDataContext(); var query = from c in db.ChatLoggedInUsers where c.RoomID.ToString() == roomId.ToString() select new { }; foreach (var c in query) { }
Where " select new { }; " is, is left blank as i am not too sure what goes there. I tried adding c.UserName.Count() but i got an error. what i need to put into that space to count the amount of records in the UserName column?
