SQL Server :: SUM In Multiple Queries Of Each Table
Dec 21, 2010
I want to find the sum of each tables fileld. how can do this. i tried the following.but could nt get the correct sum. "SELECT SUM(gb) FROM data20 union all SELECT SUM(gb) FROM data10 union all SELECT SUM(gb) FROM data5 union all SELECT SUM(gb) FROM data6"
how to run multiple queries on a single button click. Actually I want whatever a user write in text box and click the button the text would get inserted in table category and also a table name project should get updated. I am using Sql server 2005 and c#
I am trying to do multiple queries with multiple CheckBoxes. This code worked until just recently, and I didn't make any changes other than adding a single field to my SQL Server DB; field is named 'Crawl'. Now, nothing works! SearchCheckBox.aspx:
[Code]....
SearchCheckBox.aspx.vb
[Code]....
In DesignView, the error reads 'Error Creating control SqlDataSource3' Literal content ('connectionString="Data Source=EXCEL-4J2W8KYNPSQLEXPRESS;Initial Catalog=PubCrawls;Integrated Security=True" ProviderName="System.Data.SqlClient" SelectCommand="SELECT ID, Pub, Street, Neighborhood, City, State, RelativeAddress, Crawl FROM ListofPubs WHERE (Neighborhood = @Neighborhood1) OR (Neighborhood = @Neighborhood2) OR (Neighborhood = @Neighborhood3) _ OR (Neighborhood = @Neighborhood4) OR (Neighborhood = @Neighborhood5) OR (Neighborhood = @Neighborhood6) OR (Neighborhood = @Neighborhood7) OR (Neighborhood = @Neighborhood8) OR (Neighborhood = @Neighborhood9) . . .
goes on like this for a while) is not allowed within a 'System.Web.UI.WebControls.SqlDataSource' This line is highlighted red: connectionString="Data Source=EXCEL-4J2W8KYNPSQLEXPRESS;Initial Catalog=PubCrawls;Integrated Security=True" Finally, there are 19 'Neighborhoods' in my list. There must be a way to loop through the neighborhoods in my SQL statement, right. For Loop or While Loop?
protected void Button2_Click(object sender, EventArgs e) { String a = DropDownList1.SelectedItem.Value; String b = DropDownList3.SelectedItem.Value.PadLeft(3, '0'); String c = TextBox2.Text.PadLeft(5,'0').ToString(); String d = TextBox3.Text.ToString(); String digit = a+ b + c + d; try { myConn = new OdbcConnection("Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=testcase;User=root;Password=root;Option=3;"); myConn.Open(); //** string sql = "select * from testcase.main where reg_no =?"; //** OdbcCommand cmd = new OdbcCommand(sql, myConn); //** cmd.Parameters.AddWithValue("?", digit); MyReader = cmd.ExecuteReader(); //** while (MyReader.Read()) { String f = MyReader["pet_name"].ToString(); String g = MyReader["res_name"].ToString(); Label9.Visible = true; Label9.Text = f; Label10.Visible = true; Label10.Text = "VS"; //Label11.Visible = true; Label11.Text = g; } MyReader.Close(); } catch (Exception e1) { Response.Write(e1.ToString()); } finally { if (MyReader != null && !MyReader.IsClosed) { MyReader.Close(); } if (myConn != null && myConn.State == ConnectionState.Open) { myConn.Close(); } }
I want to add another sql query which is using two tables to fetch the data and than i want to display it in a dropdownlist after fetching data from database. How should i proceed?? Should i create a totally new connection? I tried many different ways like, creating new connection and new reader and entire try and catch block i coded again but on running the website i t was taking very long to load the contents. What i did i modified only this portion(below code) and used the entire try catch again but it dint work.
while (MyReader1.Read()) { String f = MyReader1["ret"].ToString(); DropDownList1.Items.Add(f); }
I am creating entries in a contract table using linq to sql.
One column needs to conditionally be assigned an application number from a range of values.
I'd like to simply increment the max existing value, however it is a multi-user application and I'm concerned about multiple contracts receiving the same app number if they happen too close together.
Is there a way to either force the entire thing to use the same transaction, or to execute both during the same submit?
We are building a front page/dashboard that queries our database for live statistics. The page has 3 update panels, each update panel has a user control that pulls data for it's box. Lets call the user controls UC_NotStarted, UC_Active and UC_Finished.
We have built the queries that return the data and all of them take a while to run (~7 seconds each). So if we let the first page run with one update panel the image spins for ~21 seconds and display everything. We have broken that code into 3 update panels and set the loading to conditional. When we do this we get one box to load every 7 seconds. That's a step in the right direction but they load sequentially (UC_NotStarted is queried waits 7 seconds and displays, then UC_Active goes for 7 seconds, and finally UC_Finished runs for 7 seconds). We are still at 21 seconds but data is being shown every 7 seconds.
We would like all of the data to be shown in 7 seconds since all of the update panels should be fetching the data at the same time. Instead of using LinqToSQL to pull the data I am now leaning towards web services to get it but not sure if that will work.
I have a drop down with several option. I would like that if a certain item is select then a certain query will run. I read a post that azamsharp on 12/23/03 .In that post he suggested to do:
anyone know that can we used select statement to called queries table in database or not?usually people used select statement to called common table in database.
i want to display results from multiple queries in a repeater... is it possible? for example, a user searches for a keyword "test" i search 4 different databases for it, and display results from all 4 in one repeater.
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).
I want to query an SQL table using some Web controls to provide the parameters for filtering the records in the table. Results are displayed in a GridView.
I have a TextBox with an ID of "keyword" where the user may enter one or more keywords. I'd like to search 2 columns for instances of these keywords.
I also have a DropDownList named "category" that lists categories contained in a "category" column of the table.
I have a dataset with a TableAdapter for the table I want to search. Got it working fine with the DropDown List but not sure how to proceed with the TextBox and keywords.
What SQL query should I use? The user may not enter anything into the textbox, they may enter one word, or multiple words.
This is where I am now:
SELECT * FROM tablename WHERE category = @category AND ??
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?
Introducing FOREIGN KEY constraint 'FK_Order_Customers1' on table 'Order' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. Could not create constraint. See previous errors. what is solution,as i want when i delete data from master table ,all dependent (child) data automatically deleted, so i use Cascade.
I've a table where one person teaches multiple classes. It looks like this.
Table1
Id Name Class Subject ---------------------------------------- 2 Sam 12 English 2 Sam 10 Maths 2 Sam 10 English 2 Sam 8 History 2 Sam 12 Economics
Here Sam teaches Class 12 - English & Economics, Class 10 - Maths & English, Class 8 - History
I want to display the result like this -
Id Name Class Subject --------------------------------------------------------------- 2 Sam 12 English, Economics 2 Sam 10 Maths, English 2 Sam 8 History
I would like to use ASP.net, C# and SQL to display a list of games with radio buttons like below (the x is the radio). One team can be selected for each game.
game 1: x team 4 x team 2 game 2: x team 6 x team 1 game 3: x team 5 x team 3
The game list is stored in a table in an SQL database. So far, I can pull all teams into one big RadioButtonList. I cannot figure out how to create multiple RadioButtonList controls from this single table of games. Does anyone know how this can be accomplished - or reference to an example / tutorial that accomplishes something like this?
i need to call multi dropdown for products that retrieve product name from microsoft sql and get the price as the product is been selected from the dropdown for total amount for the customer.Example is for customer to buy 6 product.The client is to enter the number of product purchase and the 6 dropdown appears as he click ok button.And as the clients select the product from the dropdown the price sum up together before saving into database.
I have one SP in sql server which takes the data from user and inserts into three tables. My problem is if multiple users call the same SP in same time it throwing error..(I guess one user at a time can access the table and table will be locked, that is why remaning users getting error)how to handle this situation. it is a web application, so at a time so many people can call the same SP.How can many users insert data in the same table in the same time?
I am trying to create a table dynamically in my code behind. My problem is I have a count of how many controls I want added to each TableRow after which I want to add that TableRow to the table and then start a new row. Here is the code I have no far but it just adds one row and does not move any of the controls to a seperate new row.
public void FillTable(string DB, int? appID, string function, int? rID, string UserId, int ControlsperRow) { OneEvaDataContext datacontext = new OneEvaDataContext(); var results = datacontext.sp_Build_Menu_Field_Names(DB, appID, function, rID); int controlCount = 0;
[Code]....
I would have thought that doing the .Add only when I am finished building that row would work but I guess since I'm using the same TableRow "tr" It is only putting in just one row.
EDIT: in the is context base is my class which looks like this:
public class Dynamic_Search_Table : System.Web.UI.WebControls.Table {
moving some tables & stored procedures from SQL Server 2000, to SQL Server 2005.So far so good, but I've come across a problem with this stored procedure: