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)
cmd2.Parameters.AddWithValue("@Quantity", quantity)cmd2.Parameters.AddWithValue("@ProductID", productId)
cmd2.Parameters.AddWithValue("@ShopCartID", ShopCartID)
View 1 Replies
ADVERTISEMENT
Mar 16, 2007
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
any help would be great.
View 5 Replies
View Related
May 16, 2008
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
View 22 Replies
View Related
Aug 28, 2007
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
View 5 Replies
View Related
Jan 24, 2001
For an existing table containing data, how would you add a primary key field, that contains a value for each row?
(Under Access I'd just add a new field and set it to type AutoNumber, but you can't do that in SQL Server)
View 1 Replies
View Related
Jan 15, 2002
Hi Everybody,
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?.
Tks in Advance,
Sam
View 1 Replies
View Related
May 12, 2008
Hi
Is there anyway to perform a addition on two items in a COUNT query?
For example if i use the followig query:
SELECT
Region
,COUNT(*)
FROM Regions
GROUP BY Regions
It would give me the following results:
Region1 22
Region2 33
Region3 21
Region4 4
Region5 9
Region6 61
However I would want it display the the combined totals Region1 and Region2 together so it would give the figure of 55.
Is this possible?
Thanks
View 6 Replies
View Related
Dec 6, 2006
i have the idea that 23+null=null
now following is my case with 2 columns as:
salary bonus
1 null
2 5
3 null
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)
am a beginner
help appreciated
cheers
View 5 Replies
View Related
Mar 22, 2007
hi guys,
I have got something like this,
A S D
Bi Ar 23
Bi Ar 12
As Ar 52
As Ar 11
Ap Jo 24
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,
A S D
Bi Ar 35
As Ar 63
Ap Jo 24
View 15 Replies
View Related
Jan 7, 2008
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
View 6 Replies
View Related
May 12, 2008
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" />
</InsertParameters>
</asp:SqlDataSource>
View 3 Replies
View Related
Nov 14, 2000
I need to up my license limit on a SQL box from 1 to 2 user connections allowed. Where do I do this?
View 1 Replies
View Related
Dec 1, 2007
Hi,
I have several INT columns in a table that I need to update.
For example, in column 'aa' I need to add 2 to all of the values in that column.
I'm using Query Analyzer - what update statement should I write?
View 3 Replies
View Related
Dec 10, 2013
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.
View 1 Replies
View Related
Nov 8, 2007
Hi,
is there a way to perform an addition to the value of a report parameter?
so like Param Value + 1 = 6
where Param Value would be 5?
View 11 Replies
View Related
Feb 2, 2008
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??
View 4 Replies
View Related
May 9, 2006
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
View 3 Replies
View Related
Sep 6, 2007
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 :)
View 7 Replies
View Related
Nov 29, 2004
Dear all participants,
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?
What is impact on replication?
Let me know step by step.
Thanks in advance.
Thanks R.Mall
View 1 Replies
View Related
Jan 22, 2005
Can any help me addition of article in runnning merge replication in MSSQL SERVER 2000 with SP3.
Any step by step help is realy great for me.
Thanks
R.Mall
View 1 Replies
View Related
Apr 3, 2004
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.
many thanks.
View 2 Replies
View Related
Sep 29, 2015
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.
[URL]
View 0 Replies
View Related
May 29, 2015
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.
View 6 Replies
View Related
Mar 26, 2007
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 '%[!-)]%';
View 2 Replies
View Related
Sep 15, 2001
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.
View 1 Replies
View Related
Jul 20, 2005
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!
View 1 Replies
View Related
Mar 1, 2007
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
View 2 Replies
View Related
Mar 30, 2006
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
View 2 Replies
View Related
Oct 29, 2007
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?
View 3 Replies
View Related
Nov 19, 2004
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
Dave
View 6 Replies
View Related
Jul 10, 2006
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;
// Add the parameters.
cmd.Parameters.AddWithValue("@event_title", txtEditTitle.Text);
cmd.Parameters.AddWithValue("@event_date", txtEditDate.Text);
cmd.Parameters.AddWithValue("@event_time", txtEditTime.Text);
cmd.Parameters.AddWithValue("@event_location", txtEditLocation.Text);
cmd.Parameters.AddWithValue("@event_description", txtEditDescription.Text);
cmd.Parameters.AddWithValue("@eid", eid);
// Try to open database and execute the update.
try
{
con.Open();
int updated = cmd.ExecuteNonQuery();
}
catch (Exception err)
{
Response.Write(err.Message);
}
finally
{
con.Close();
Response.Redirect("./?msg=eus");
}
View 11 Replies
View Related
Feb 19, 2007
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);
command.Parameters.Add(new SqlParameter("@areaId", areaId));
myConnection.Open();
command.ExecuteNonQuery();
myConnection.Close();
} Thanks for any help you can give, cheers, Mark.
View 8 Replies
View Related
Jul 19, 2007
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)
View 33 Replies
View Related
