Doing Addition In Update Query Using Value From Txtbox
Jun 21, 2008
@quantity is the number that the user has input in the txtbox. I want to use this value to ADD together with the current quantity value in the shopcartitem table. I do not know how, my current query only replaces the old quantity with the new .. can someone help me? strSql = "UPDATE ShopCartItem sci SET sci.Quantity" & _
"WHERE sci.ProductID=@ProductID AND sci.ShopCartID=@ShopCartID"Dim cmd2 As New SqlCommand(strSql, conn)
Hi I have a query, what I would like to do is create a column that takes the results in two coulms and add them together: Col A Col B Col C Row1 1 1 2 Row2 2 3 5 Here is the query declare @t table( player_name varchar(100), BuyIn int, TopUp int, ReBuy int, Winnings int, Events int, Test int) INSERT INTO @t (player_name, TopUp) SELECT Player_name, SUM([Top-ups]) AS TOPUPS FROM (SELECT Event_data.Transaction_type, Players.Player_name, Events.Top_up, Event_data.Transaction_value, Events.Top_up * Event_data.Transaction_value AS [Top-ups] FROM Event_data INNER JOIN Events ON Event_data.Event_id = Events.id INNER JOIN Players ON Event_data.Player_id = Players.Player_id WHERE (Event_data.Transaction_type = 2)) AS Topups GROUP BY player_name INSERT INTO @t (player_name, ReBuy ) SELECT Player_name, SUM([Re-buys]) AS REBUYS FROM (SELECT Event_data.Transaction_value, Players.Player_name, Events.Rebuys, Event_data.Transaction_value * Events.Rebuys AS [Re-buys] FROM Event_data INNER JOIN Events ON Event_data.Event_id = Events.id INNER JOIN Players ON Event_data.Player_id = Players.Player_id WHERE (Event_data.Transaction_type = 3)) AS REBUYS GROUP BY Player_name Insert into @t (player_name, BuyIn) SELECT dbo.Players.Player_name, SUM(dbo.Events.Buy_in) AS BuyIn FROM dbo.Players INNER JOIN dbo.Event_data ON dbo.Players.Player_id = dbo.Event_data.Player_id INNER JOIN dbo.Events ON dbo.Event_data.Event_id = dbo.Events.id GROUP BY dbo.Players.Player_name, dbo.Event_data.Transaction_type HAVING (dbo.Event_data.Transaction_type = 1) ORDER BY SUM(dbo.Events.Buy_in) DESC
Insert into @t (player_name, Winnings) SELECT dbo.Players.Player_name, SUM(dbo.Event_data.Transaction_value) AS Winnings FROM dbo.Players INNER JOIN dbo.Event_data ON dbo.Players.Player_id = dbo.Event_data.Player_id GROUP BY dbo.Players.Player_name, dbo.Event_data.Transaction_type HAVING (dbo.Event_data.Transaction_type = 1) insert into @t (player_name, Events) SELECT dbo.Players.Player_name, COUNT(dbo.Event_data.Place) AS Expr1 FROM dbo.Players INNER JOIN dbo.Event_data ON dbo.Players.Player_id = dbo.Event_data.Player_id INNER JOIN dbo.Events ON dbo.Event_data.Event_id = dbo.Events.id GROUP BY dbo.Players.Player_name HAVING (NOT (COUNT(dbo.Event_data.Place) IS NULL)) insert into @t (player_name, test) select player_name, ((TopUp) + (Rebuy)) as Test from @t SELECT player_name, min (BuyIn) as BuyIn, min(TopUp) as TopUps, min(ReBuy)as ReBuy, min(Winnings) as Winnings, min(Events) as Events, min(test) as test FROM @t GROUP BY player_name ORDER BY BuyIn DESC --ORDER BY TOPUPS DESC
END
THis is where I attempt to add the coloms but I get a null result
insert into @t (player_name, test) select player_name, ((TopUp) + (Rebuy)) as Test from @t
On my Home page I have a gridview that has Four columns: CustomerID, Product, Quantity, then a link (which appears on each row..called View). When a user clicks View on a particular row it will then send that particular CustomerID to the next page (CustomerProfile.aspx). On the CustomerProfile.aspx page I have a textbox where the Customer Name should appear. How do I go about displaying the customer name in the textbox based on the CustomerID passed to the customerprofile.aspx page? Do I need to call a stored procedure? Do i need to call the Customer Table in my code? (i'm using C#) Thanks!! Ur help is very much appreciated! Side Note: i am passing the CustomerID by a SESSION variable called customerID ~~~>this is in my pageLoad method labelCustomerID.Text = Session["customerID"].ToString(); but i'm wanting to have the Customer Name in a textbox based on the above ^^^ session variable. My textbox is called txtCustomerName.Text *** Customer Table***CustomerIDCustomerNameCustomerAddress ^^^wanting to call the customer name and put it in the customer name text box based on the particular customerID
How can i make a sum (concatenation) of strings of one column in a table. for example i have a table like this field1 field2 1 abc 1 bcd 2 sdf 2 sdd I want to get these strings added group by field 1 Thanks
I hv executed the following query in the Query Qnalyzer (Ofcourse I am Using SQL 2000 Enterprise Edition) and Surprisingly I am getting the Output as follows,
query:- select '5' + 10
Output:- 15
Can anybody please tell me, why it is happening like this? OR Did I miss any Configuration Parameter in the Server Settings?.
i have to find the total salary(i.e. salary+bonus)
this is how i managed to do it:
select empname,(salary + commission) as 'total salary' from emp where commission is not null union select empname,salary from emp where commission is null
now my question is,wht if the case now is different as below:
salary bonus hra 1 null 6 2 5 null 3 null null
now how do i calculate total salary(i.e. salary+bonus+hra)
I Have 3 columns. First 2 columns are Text and the third one is Number. I need to see if any duplicate values are there in first 2 columns. If so then i need to add the values of 3 columns and make it one. So the output should look like this,
I am trying one of the examples from the .NET Framework 2.0 Web-Based Client Development and I'm having a problem authenticating. I'm running everything on my local computer (IIS, SQL 2005 STANDARD ADDITION and Visual Studio). While in Visual Studio (in other words, debug mode) when I run the example, I get the following error: Cannot open database "pubs" requested by the login. The login failed.Login failed for user 'DPJ-6E95AFFA416ASPNET'. This could be more of a SQL question that a ASP.NET question, but I'm not sure. I'm running under the local administrator account, but this seems to indicate that ASP is using it's own credentials to access SQL. This is my first time using these tools, and I'm guessing this is a fairly well know issue. I was looking for a "User Admin" function in SQL and could not find it, so I'm a bit lost here. Can anyone help please. Thanks
I have a form set-up and am trying to add multiple items in my checkbox to the database; however, the only thing added to the database is the first item checked. Here is the code and any help will be greatly appreciated. Thank you!:<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConConnectionString %>" InsertCommand="INSERT INTO Consultations(FullName, Email, Business, BusinessInfo, Services, Comments) VALUES (@FullName, @Email, @Business, @BusinessInfo, @Services, @Comments)" SelectCommand="SELECT Consultations.* FROM Consultations"> <InsertParameters> <asp:ControlParameter Name="FullName" Type="String" ControlId="fullname" PropertyName="Text" /> <asp:ControlParameter Name="Email" Type="String" ControlId="emailTextBox" PropertyName="Text" /> <asp:ControlParameter Name="Business" Type="String" ControlId="bisDropDownList" PropertyName="SelectedValue" /> <asp:ControlParameter Name="BusinessInfo" Type="String" ControlId="businessTextBox" PropertyName="Text" /> <asp:ControlParameter Name="Services" Type="String" ControlID="checkboxlist" PropertyName="SelectedValue" /> <asp:ControlParameter Name="Comments" Type="String" ControlID="commentsTextBox" PropertyName="Text" />
I've two databases with different structures, i want to add a record with it's data from the first database into the second database in the place i define, this action must happen automatically as soon as the record is created in the first database.
Are there any differences between the Developer Addition and Enterprise Addition of 64 Bit SQL Server 05 that would prevent me from testing full capabilities of my production Enterprise Addition in a QA environment utilizing Developer Addition? Any known restrictions on number of DB connections, memory, CPUs etc??
How can I check for Null for the amounts if no records are returned in either select. Basically it errors out if one or both of the Amounts return no records. I need to do some sort of IF statement to set one of the amounts or both amounts to zero in those cases so it doesn't error out on me
SELECT (Coalesce(pd1_Amount, 0) + Coalesce(PD2_Amount, 0)) as Amount FROM (
SELECT pd.Amount as pd1_Amount FROM Master m (NOLOCK) LEFT JOIN dbo.pdc pd ON pd.number = m.number INNER JOIN dbo.Customer c ON c.Customer = m.Customer
WHERE pd.Active = 1 AND pd.Entered BETWEEN DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate) + 1, @FirstDayMonthDate) AND DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate), DATEADD(MONTH, 1, @FirstDayMonthDate)) AND pd.Entered <> '1900-01-01 00:00:00.000' AND pd.Deposit BETWEEN DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate) + 1, @FirstDayMonthDate) AND DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate), DATEADD(MONTH, 1, @FirstDayMonthDate)) --AND pd.Deposit IS NOT NULL --AND pd.OnHold IS NULL AND c.customer <> '9999999'
UNION
SELECT pdd.Amount as PD2_Amount FROM Master m (NOLOCK) LEFT JOIN dbo.pdcdeleted pdd ON pdd.number = m.number INNER JOIN dbo.Customer c ON c.Customer = m.Customer
WHERE pdd.Entered BETWEEN DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate) + 1, @FirstDayMonthDate) AND DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate), DATEADD(MONTH, 1, @FirstDayMonthDate)) AND pdd.Entered <> '1900-01-01 00:00:00.000' AND pdd.Deposit BETWEEN DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate) + 1, @FirstDayMonthDate) AND DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate), DATEADD(MONTH, 1, @FirstDayMonthDate)) --AND pdd.Deposit IS NOT NULL --AND pdd.OnHold IS NULL AND c.customer <> '9999999' ) as PDC_Main
Hi, I was wondering if you can help. In my vb.net form I am running a query to insert data from one database table to another. However what I need to do is to be able is to add the id of a record I have just created to this insert into sql command. I have managed to use @@ identity to get the id of my first sql insert statement but I am wondering how I can use it in the second insert into and select statement. At the moment my sql statement just copies exactly what is in the select statement. I can't figure out how to add the @@identity value to my second sql statement. My second sql statement is a follows: sql2 = "INSERT INTO ProjectDeliveData(ProjId,ProjDeliveId,RoleId,MeasurementId,ProjDeliveValue, ProjDeliveComments,ProjDeliveYear,FinDataTypeId, FinFileId, ProjDeliveMonthFrom, ResourceId,ProjDeliveDateAdded)" & " select ProjId,ProjDeliveId,RoleId,MeasurementId,ProjDeliveValue, ProjDeliveComments,ProjDeliveYear,FinDataTypeId, FinFileId, ProjDeliveMonthFrom, ResourceId,ProjDeliveDateAdded from ProjectDeliveData where FinFileId=" & strActualFileId I hope you can help Cheers Mark :)
I manage complete online system for two locations, connected via dialup lease line. I have to used merge replication for the same data should be at both locations. It is working fine, I don’t like disturbe it but current requirement is addition of two column in one table which one as also use in merge replication.
How I can add aforesaid columns in table x, column name c1 numeric , c2 character?
SQL Server allows addition of two "datetime" values. For example let d1='April 10,2004' and d2='April 11, 2004'. Now when I execute d1+d2 I get 2108-07-20 00:00:00.000. On what logic is this calculation done.
I am actually looking to derive the same result in Oracle. Hence wanting to know the funda behind this.
My goal is to be able to include test information in the change request that will make the operations manager confident that the change can proceed. Right now all I can think of is to add the index in dev and staging and compare some before and after stats. But I am concerned that an index change might also affect other operations besides SELECTs - such as updates, transactions, or scheduled jobs.
We are using SSRS 2012. Before we were with 2005. Both the versions have the problem. When a change made to parameters (like adding additional parameter to existing report or changing default value of parameter) using visual studio on developers machine and re-deploy to server, additional parameter or changes to parameter is not showing up. So, we are first deleting the report from server and then re-deploying. This works fine. But we realized that we are losing history of the report (like data related to this report run from execution log). We need the history of report. It is very important to top management to see the details.When I searched in forums, users say that it still exist in 2012 and 2014 and every one talks about deleting and re-deploying report. I would like to know if there is any other alternate route to redeploy additional parameter and not lose history.
Hi! Select gets all records that contains illegal chars... Ok, to replace '[' { and some other chars I will make AND '% .. %' and place other intervals, that is not the problem.The problem is: How to replace not allowed chars ( ! @ # $ % ^ & * ( ) etc. ) with '_' ?I have seen that there is a function REPLACE, but can't figure out how to use it. 1 SELECT user_username 2 FROM users 3 WHERE user_username LIKE '%[!-)]%';
I'm looking for a query that can "batch" update one table from another. For example, say there are fields on both tables like this: KeyField Value1 Value2 Value3 The two tables will match on "KeyField". I would like to write one SQL query that will update the "Value" fields in Table1 with the data from Table2 when there is a match.
Hi there,I'm a little stuck and would like some helpI need to create an update trigger which will run an update query onanother table.However, What I need to do is update the other table with the changedrecord value from the table which has the trigger.Can someone please show me how this is done please??I can write both queries, but am unsure as to how to get the value ofthe changed record for use in my trigger???Please helpM3ckon*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
Can I roll back certain query(insert/update) execution in one page if query (insert/update) in other page execution fails in asp.net.( I am using sqlserver 2000 as back end) scenario In a webpage1, I have insert query into master table and Page2 I have insert query to store data in sub table. I need to rollback the insert command execution for sub table ,if insert command to master table in web page1 is failed. (Query in webpage2 executes first, then only the query in webpage1) Can I use System. Transaction to solve this? Thanks in advance
Hello, I have the following query in Access 2000 that I need to convertto SQL 2000:UPDATE tblShoes, tblBoxesSET tblShoes.Laces1 = NullWHERE (((tblShoes.ShoesID)=Int([tblBoxes].[ShoesID])) AND((tblBoxes.Code8)="A" Or (tblBoxes.Code8)="B"))WITH OWNERACCESS OPTION;The ShoesID in the tblShoes table is an autonumber, however the recordsin the tblBoxes have the ShoesID converted to text.This query runs ok in Access, but when I try to run it in the SQLServer 2000 Query Analizer I get errors because of the comma in the"UPDATE tblShoes, tblBoxes" part. I only need to update the tblShoesfield named Laces1 to NULL for every record matching the ones in thetblBoxes that are marked with an "A" or an "B" in the tblBoxes.Code8field.Any help would be greatly appreciated.JR
I have an SSIS package (TransAgentMaster) that I recently modified to include a call to a child package via the file system. The child package creates a text file. When I run the package in dev studio then the child package/text file is produced.
I then imported the TransAgentMaster as a stored packagesfilesystem package into SQL SSIS and executed the package. The child package produced the text file.
I then ran the SQL Server Agent to see if the child package would work and it did not generate the text file. Thus after updating a SSIS package importing the package into SSIS the job that calls the package will not call the child package. Please not that the TransAgentMaster package calls 7 children packages €¦ just not my new one.
Any thoughts why the agent will not run the child newly crated childe package?
I have an update query running which to just now has been running for 22 hours running on two tables 1 a lookuptable that has just been created within the batch the other a denormalised table for doing data analysis on
the query thats causing teh problem is
--//////////////////////////////////// this is the one thats running
Print 'Update Provider 04-05 EmAdmsCount12mths : ' + CAST(GETDATE() AS varchar) GO Update Provider_APC_2004_05 set EmAdmsCount12mths = (Select COUNT(*)-1 from Combined_Admissions where ((Combined_Admissions.NHSNumber = Provider_APC_2004_05.NHSNumber) or (Combined_Admissions.PASNUMBER = Provider_APC_2004_05.PDDISTNO)) and (Combined_Admissions.AdmDate BETWEEN DateAdd(yyyy,-1,Provider_APC_2004_05.AdmDate) AND Provider_APC_2004_05.AdmDate) AND Combined_Admissions.AdmMethod like 'Emergency%')-- and -- CA.NHSorPrivate = 'NHS')) FROM Provider_APC_2004_05, Combined_Admissions
any help in improving speed would be most welcome as there are 3 more of these updates to run right after this one and the analysis tables are almost double the size of this one
This is my first website done in ASP.NET and SQL Server 2005. I haven't ran into any major problems, until I tried to run an update on some rows. I've searched everywhere online, only to be stuck at the end of the day. I will post the code I have for the button that triggers it. I'll be crossing my fingers! int eid = Int32.Parse(Request.QueryString["id"]); string updateSQL = "UPDATE Events SET event_title = @event_title, event_date = @event_date, "; updateSQL += "event_time = @event_time, event_location = @event_location, event_description = @event_description WHERE (eid = @eid)";
SqlConnection con = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand(updateSQL, con); cmd.CommandType = CommandType.Text;
Hi everyone!I have a update query which all looks good and it looks like it executes, though it does not effect the database for some unknown reason. (No error messages).protected void Button1_Click(object sender, EventArgs e) { int areaId = 0;
if (Request.QueryString["doc_area_id"] != null) { areaId = Convert.ToInt32(Request.QueryString["doc_area_id"]); }
SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["CPS_docshareConnectionString"].ConnectionString);
SqlCommand command = new SqlCommand("UPDATE document_area SET doc_area_name = '" + AreaText.Text + "' WHERE doc_area_id = @areaId", myConnection);
I am using the update query listed below in my DetailsView. The issue i am having is the ManagerDecision value which gets populated in a DropDownList in EditTemplate, doesnt seem to update correctly. If I choose 'Approved' from the DropDownList, the result for Status_Type ends up being 'Closed' and not 'Open'. Could someone please Help. Thanks!! UPDATE ServiceRequest_Table SET ManagerDecision = @ManagerDecision, Comments = @Comments, Priority_Name = @Priority_Name, Status_Type = CASE WHEN (ManagerDecision = 'Approved') THEN 'Open' ELSE 'Closed' END, ManagerDateTime = CASE WHEN (Status_Type = 'Closed') THEN '' END, Closed_Date = CASE WHEN (Status_Type = 'Open') THEN '' END WHERE (ServiceRequest_ID = @ServiceRequest_ID)
