Updating Database Column After Order Has Been Made

Jun 7, 2004

i am building a shopping cart. I want to update the UNIT_IN_STOCK column in database after order have been submitted. i want to subtract the quanity value from the order made from the UNIT_IN_STOCK column in database. how would the sql statement be like?? i tried this but it didnt work. any suggestions??





CREATE PROCEDURE update_Products_By_name


(





@ProductName varchar,


@UnitInStock int





)


AS





UPDATE Products


SET UnitInStock=(UnitInStock-@UnitInStock)


WHERE ProductName = @ProductName


GO

View 1 Replies


ADVERTISEMENT

Display Database Column In Alphabetical Order

Mar 12, 2007

Hello
I know how I can display a list of names in alphebetical order on my website:
Select L as [Last Name]
From  Name_CatEWhere Education = 'yes'Order ByLName ASC
However, to make things a little more orginised I would like to view my database table column in alphabetical order also, but ithie code does not work within my database.
What do I need to change in the following code, to view my database table column in a-z order?
 SELECT LName FROM Name_CatEORDER BY LName ASC
Thanks
Lynn

View 1 Replies View Related

DB Design :: Change Order Of Column In Database Tables

Jul 22, 2015

How to Change Order of Column In Database Tables

View 10 Replies View Related

How Can A Matrix's Row And Column Headers Be Made Floatable?

Dec 25, 2007

when paging down or across in a matrix based report, it would be nice for headings (row and column) to float (ie not disappear) with the position of the report. Is this possible in RS?

View 10 Replies View Related

Default Sort Order When Order By Column Value Are All The Same

Apr 14, 2008

Hi,
We got a problem.
supposing we have a table like this:

CREATE TABLE a (
aId int IDENTITY(1,1) NOT NULL,
aName string2 NOT NULL
)
go
ALTER TABLE a ADD
CONSTRAINT PK_a PRIMARY KEY CLUSTERED (aId)
go


insert into a values ('bank of abcde');
insert into a values ('bank of abcde');
...
... (20 times)

select top 5 * from a order by aName
Result is:
6Bank of abcde
5Bank of abcde
4Bank of abcde
3Bank of abcde
2Bank of abcde

select top 10 * from a order by aName
Result is:
11Bank of abcde
10Bank of abcde
9Bank of abcde
8Bank of abcde
7Bank of abcde
6Bank of abcde
5Bank of abcde
4Bank of abcde
3Bank of abcde
2Bank of abcde

According to this result, user see the first 5 records with id 6, 5, 4, 3, 2 in page 1, but when he tries to view page 2, he still see the records with id 6, 5, 4, 3, 2. This is not correct for users. :eek:

Of course we can add order by aid also, but there are tons of sqls like this, we can't update our application in one shot.

So I ask for your advice here, is there any settings can tell the db use default sort order when the order by column value are the same? Or is there any other solution to resolve this problem in one shot?

View 14 Replies View Related

Default Sort Order When The Order By Column Value Are All The Same

Apr 14, 2008

Hi,
We got a problem.
supposing we have a table like this:

CREATE TABLE a (
aId int IDENTITY(1,1) NOT NULL,
aName string2 NOT NULL
)
go
ALTER TABLE a ADD
CONSTRAINT PK_a PRIMARY KEY CLUSTERED (aId)
go

insert into a values ('bank of abcde');
insert into a values ('bank of abcde');
...
... (20 times)

select top 5 * from a order by aName
Result is:
6 Bank of abcde
5 Bank of abcde
4 Bank of abcde
3 Bank of abcde
2 Bank of abcde

select top 10 * from a order by aName
Result is:
11 Bank of abcde
10 Bank of abcde
9 Bank of abcde
8 Bank of abcde
7 Bank of abcde
6 Bank of abcde
5 Bank of abcde
4 Bank of abcde
3 Bank of abcde
2 Bank of abcde

According to this result, user see the first 5 records with id 6, 5, 4, 3, 2 in page 1, but when he tries to view page 2, he still see the records with id 6, 5, 4, 3, 2. This is not correct for users.
Of course we can add order by aid also, but there are tons of sqls like this, we can't update our application in one shot.
So I ask for your advice here, is there any settings can tell the db use default sort order when the order by column value are the same? Or is there any other solution to resolve this problem in one shot?

View 5 Replies View Related

Tracking Changes Made In The Database

Sep 12, 2000

Here is the situation. I have an employee who is making changes but I can't prove it. He thinks he knows more than he does and he's mess'n everything up. I would like to know if SQL or some third party product has the capability to see the change and log what was changed with the persons username attached?

View 1 Replies View Related

Saving Changes Made To Database

Jul 20, 2005

I have an application that connects and edits a database's tables.My question is, what is the best way to save who did what changes tothis database? I need to be able to display this in the application.The best way I have thought of so far is, to create a new table with'user', 'date', 'table_name', 'primary_key', 'old field_value' and'new_field_value' fields. Then I can save which table row had beenchanged, by which user and on which date.I'm sure that there is a way to do this in SQL Server, but I'm notsure how.Any help would be appriciated.Jagdip

View 1 Replies View Related

A Connection Cannot Be Made To The Database. Timeout Expired.

Feb 19, 2008

Looks like I'm up to no good as usual.

Basically due to a client spec, I had to create 115 datasets (one for each of my 115 tables).

After I created the 111th, I am getting the following error:

A connection cannot be made to the database. Timeout Expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.


How do I clear the pooled connections or increase the max pool size?

I suppose what happened is each time I create a dataset it opens a connection to the database and these connections aren't being terminated...

View 3 Replies View Related

Restore Master Database With SQL 2005 Made An Error With Me

Mar 6, 2007

- First I made a full backup of master database

- Then I stopped SQL Server Service

- in cmd: cd C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLBinn

then sqlservr.exe -m

- I entered that single user mode

---> Problem here:

- I tried to connect to sql by sqlcmd -E --->

HResult 0xE9, Level 16, State 1
Shared Memory Provider: No process is on the other end of the pipe.

Sqlcmd: Error: Microsoft SQL Native Client : Communication link failure.

---> I tried to connect to management studio:

TITLE: Connect to Server
------------------------------

Cannot connect to BISHOYSERVER.

------------------------------
ADDITIONAL INFORMATION:

Login failed for user 'OFFICIALPROJECTAdministrator'. Reason: Server is in single user mode. Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461)

View 6 Replies View Related

Analysis :: Update Actual Database Tables With Changes Made In Cube

Oct 7, 2015

I am very new to SSAS. I have two queries:

1) As per my project requirement, if the changes in SSAS cube are approved, they should be committed back to the actual SQL Server 2012 tables. Is that possible, if yes how?

2) For rolling back to original data I truncate the relevant writeback table and process the cube.

View 4 Replies View Related

Recordset's Order And Database's Physical Order?

Jul 20, 2005

Hi,guys!I have a table below:CREATE TABLE rsccategory(categoryid NUMERIC(2) IDENTITY(1,1),categoryname VARCHAR(20) NOT NULL,PRIMARY KEY(categoryid))Then I do:INSERT rsccategory(categoryname) VALUES('url')INSERT rsccategory(categoryname) VALUES('document')INSERT rsccategory(categoryname) VALUES('book')INSERT rsccategory(categoryname) VALUES('software')INSERT rsccategory(categoryname) VALUES('casus')INSERT rsccategory(categoryname) VALUES('project')INSERT rsccategory(categoryname) VALUES('disert')Then SELECT * FROM rsccategory in ,I can get a recordeset with the'categoryid' in order(1,2,3,4,5,6,7)But If I change the table definition this way:categoryname VARCHAR(20) NOT NULL UNIQUE,The select result is in this order (3,5,7,2,6,4,1),and 'categoryname 'in alphabetic.Q:why the recordset's order is not the same as the first time since'categoryid' is clustered indexed.If I change the table definition again:categoryname VARCHAR(20) NOT NULL UNIQUE CLUSTEREDthe result is the same as the first time.Q:'categoryname' is clustered indexed this time,why isn't in alphabeticorder?I am a newbie in ms-sqlserver,or actually in database,and I do havesought for the answer for some time,but more confused,Thanks for yourkind help in advance!

View 2 Replies View Related

Ready-made Sample Script To Display A Search Reslut From A Database

Jan 25, 2007

Hi all,
Can anyone give me a ready-made sample script to display a search reslut from a database?
My intention is to give a dynamic web page for the search in the BookStore website.
The result will show the following:-
First the picture of the book.
To the right of it,  'Title' field, below that, 'description' field
It should be created programatically as the contents are in the database.
I tried several ways, but could not succeed. I have the BookID, Title, BookPicture, and the Description fields
Thanking you in advance,
Tomy
 
 

View 1 Replies View Related

Updating The Column Defined As 'Text' Column

Oct 31, 2002

Hi,

I have a table with col_noteText defined as 'Text' datatype column.

I want to search a pattern 'Lawyer' and replace with 'Attorney' in the column col_noteText.

Does anyone know how to do this for 'Text' datatype column.

Thanks in advance.

jfk

View 1 Replies View Related

Updating A Column With A Column From Another Table

Apr 1, 2004

Hi, I am kinda new to this so here it goes:

table name: USERS
field names : LName, FName, EmpID


other table name:PERSONNEL
field names : (same as USERS)

The EmpID column in my USERS table is blank (I have 30 records in the table.)

I would like to update the USERS.EmpID table with the PERSONNEL.EmpID data

how would I do this or what would the code be?

sanctos

View 2 Replies View Related

Updating Max(column) And Between_dates Column

Apr 26, 2008



Hi,

I have dataset which has max(column) and between_sale_dates columns So I would like to
update those columns. CustomerNo and Sales_date are important . This group by for these 2
colums. I can write a sproc but How can I do with SSIS?

thanks

View 5 Replies View Related

Updating A Column In SQL

Dec 13, 2006

I am trying to update a users status from Pending to either Approved or Rejected.  I created the following handers to update me db by I keep getting a syntax error. What am I doing wrong? public partial class admin_beta : System.Web.UI.Page{    protected void ApproveButton_Click(object sender, EventArgs e)    {        SqlConnection conn = new SqlConnection("Data Source=TECATE;Initial Catalog=subscribe_mainSQL; User Id=maindb Password=$$ricardo; Integrated Security=SSPI");        SqlCommand cmd = new SqlCommand("UPDATE [main] ([status]) VALUES (@status)", conn);        conn.Open();        cmd.Parameters.AddWithValue("@status", "Approved");        int i = cmd.ExecuteNonQuery();        conn.Dispose();    }    protected void DenyButton_Click(object sender, EventArgs e)    {        SqlConnection conn = new SqlConnection("Data Source=TECATE;Initial Catalog=subscribe_mainSQL; User Id=maindb Password=$$ricardo; Integrated Security=SSPI");        SqlCommand cmd = new SqlCommand("UPDATE [main] ([status]) VALUES (@status)", conn);        conn.Open();        cmd.Parameters.AddWithValue("@status", "Rejected");        int i = cmd.ExecuteNonQuery();        conn.Dispose();    }}

View 4 Replies View Related

Updating A Column By The Value Of '1'

Nov 6, 2007

Hi,Its probably simple but.. How do I update a column by just '1'..for example - heres my code:    protected void Button1_Click(object sender, EventArgs e)    {        SqlConnection con = new SqlConnection();        con.ConnectionString = "HiddenConnection";        con.Open();        SqlCommand command = new SqlCommand();        command.Connection = con;        Label productIDLabel = (Label)DataList1.FindControl("productIDLabel");        command.CommandText = "UPDATE Items SET numberclickedin = numberclickedin + 1 WHERE productID=@productID";        command.Parameters.Add("@productID", productIDLabel.Text);        command.ExecuteNonQuery();        con.Close();        command.Dispose();    } As you can see in the bold text, I want to add 1 to the numberclickedin column.. and in my primative way Ive just typed +1What should I use instead?Thanks in advance,Jon 

View 1 Replies View Related

HELP Updating Column

Nov 15, 2006

How can I create an update statement that will allow me to fill in a column if the previous column already has data in it? I am trying to do and UPDATE/SET command that allows me to extract information and populate columns within a table. However, the UPDATE/SET has to make sure that it is not overwriting information that is already in the column and if there is, to populate the column next to it, and so on until all the columns are populated.

Here is my table:


Code:

create table #add_diags(
add_diag_1 varchar(10) null,
add_diag_2 varchar(10) null,
add_diag_3 varchar(10) null,
add_diag_4 varchar(10) null,
add_diag_5 varchar(10) null,
add_diag_6 varchar(10) null,
add_diag_7 varchar(10) null,
add_diag_8 varchar(10) null
)



In my UPDATE/SET I am pulling data from another table to populate into the 'add_diag' columns however, I'm not sure how to write in SQL the ability to monitor all the columns, and if for example the first three are full to then populate into the fourth and so on.

Here is my UPDATE/SET statement (while involved, I think I need something in my WHERE clause in order for this to be resolved):


Code:

update ad
set add_diag_1=dsm_code
from #add_diags ad
join Doc_Entity de
on ad.patient_id=de.patient_id
and ad.episode_id=de.episode_id
and doc_code = 'DCDIAG'
join Patient_Assignment pa
on de.patient_assignment_id = pa.patient_assignment_id
and convert(char(8),de.effective_date,112) = convert(char(8),pa.date_discharged,112)
left outer join Doc_Diag_Axis_I_III dx1
on de.doc_session_no=dx1.doc_session_no
and de.current_version_no=dx1.version_no
where dx1.sequence_no=2
and de.is_locked = 'Y'
and dx1.rule_out = 'N'
and is_billable = 'Y'
and dx1.axis_type IN ('1','2')
and de.status in ('CO' , 'SA')



Any ideas? Thanks!

View 7 Replies View Related

Updating Identity Column

Sep 18, 2007

Please excuse my ignorance.  I've researched this and it appears I am asking to do something that is ridiculous, so please let me know what is wrong with my design (or my brain).  I'd like to update an ID number in a table.  It is an identity column.  In my solution I'm adding a lot of new entries into my table and deleting old ones.  Call me anal, but It's driving me nuts that my ID numbers are growing so large so quickly and that I have so many unused ID's.  If you were to look over my data ID's they would be something like 1,3,45,78,88,89,103,140,219.  What I'm trying to do is renumber my data so that my data currently at say ID# 1067 can be moved to the unused ID#2, etc.  So I either need a way to update an Identity column...or I need a way find the lowest unused number among a list of ID's.  So is there anyway to achieve what I am trying to do?  I see this guy had my same OCD issue (without a solution) :-(

View 3 Replies View Related

Updating A Text Column

Sep 20, 2001

how can i update a column with datatype of text with a combination of columns having a datatype of float? do i convert the float columns to varchar/char/?? and/or can i convert the column i am updating?

thanks!

View 2 Replies View Related

Updating IDENTITY Column

May 2, 2000

Do anyone knows if there is a way that I can manually update the value in an IDENTITY column?

Thanks

View 2 Replies View Related

Updating Column In A Table

Jan 18, 2001

hi i want to update column with new value in a table is it possible to do so in stored procedure . the name of the column will be an input to stored procedure ie at the time of writing the stored procedure i dont know which column the user will be updating

View 2 Replies View Related

Updating Table With New Column

May 25, 2001

Does anyone know of the SQL statement to add additional comuns to an existing table. I know i can do it through enterprise managaer, but I want to see if I can do it from query analyzer or some script to add columns without having to recreate the table. Or a way to save off the data and recreate the table then placing data back in? I hope that makes sense. I am thinking of this from the sens of doing all updates through source safe

Thanks!

View 1 Replies View Related

Column Chechking While Updating

Jan 3, 2004

If amc.amc2>0 then
“UPDATE amc set AMC2= AMC2 + “ & val(txtamt.text) & “where am1=1”
else if amc.amc2<0 then
“UPDATE amc set AMC2= “ & val(txtamt.text) & “where am1=1”
end if

Here I check the value of column with if condition statement. I need to check the column with out if condition statement.

What is my doubt is that how I can check value of amc2 column while updating.

eg: update amc set case when amc2>0 then AMC2= AMC2 + “ & val(txtamt.text)
like this

Is it possible with “case-when -end”?

If it is possible I can solve a big problem in my project.
Can you give me an example with this query?

View 1 Replies View Related

Updating Rows Of A Column

Apr 10, 2008

Hi, I need to update column week14 in table PastWeeks with data from Eng_Goal and then result of some calculation from table AverageEngTime in the row Goal and Used respectively. I used the following and was not successful. Please advice. Thank you.

Update Pastweeks
set
week14 = (SELECT Eng_Goal,((Mon_Day + Mon_Night + Tue_Day + Tue_Night + Wed_Day + Wed_Night + Thu_Day + Thu_Night + Fri_Day + Fri_Night + Sat_Day + Sat_Night + Sun_Day + Sun_night)* 100/168) FROM AverageEngTime where Shifts = 'Average')
where Weeks = ('Goal','Used' )

View 8 Replies View Related

Updating Column Data On The Fly?

Jul 23, 2005

Hey...newbie question: I've got three columns in my database, thethird of which is blank right now, and I need it to equal the value ofcolumn one minus column two. While I can accomplish this in the .aspxpage with a subroutine, I want to do it in SQL Server so I can simplyread the data in the page and not have to do any calculations. Helpplease? Thanks.Erik

View 5 Replies View Related

Updating Telephone Column

Apr 3, 2006

Hi all

I have a column the customer table which holds the telephone numbers,

0293 232 232 2

2323 23232333

0222229999 00

the problem is that they all appear with spaces in different places, i need to remove all the spaces, remove the leading 0 if ther eis one and put +44 on the front.

Any suggestions??

Regards Rich

View 3 Replies View Related

Updating Column In A Dataset Not Working

Aug 8, 2006

Hi all,
I'm trying to update various rows in my DB with a new value once an action occurs.  I am using the following code which does not throw any exceptions, but also does not change the values.  Can someone steer me in the right direction?
public static void ChangeRefCode()
{
SqlConnection dbConn = new SqlConnection(ConnectDB.getConnectString());
SqlDataAdapter dataAdapt = new SqlDataAdapter("Select * from Posting",dbConn);

DataSet ds = new DataSet();

SqlCommandBuilder sqBuilder = new SqlCommandBuilder(dataAdapt);
dataAdapt.Fill(ds, "Posting");
foreach (DataRow dr in ds.Tables["Posting"].Rows)
{
dr["ref_code"] = DateTime.Today.Ticks + "-" + dr["poster"].ToString();
}
try
{
dataAdapt.Update(ds, "Posting");
}
catch (Exception ex)
{
ex.ToString();
}
finally
{
dbConn.Close();
}
}

View 3 Replies View Related

Sytntax Error Updating Column

Jan 30, 2007

Hello
I am trying to update a column within a table.  I need to change all the (null) to yes. But  I keep seeing Error:  0 record(s) affected.  What is wrong with my syntax. 
My table name:  Cat_B
Column: Blog
Current cell data: (null)
Update to: yes
This is the syntax I have tried:
 UPDATE Cat_B
SET  Blog = 'yes'
Where Blog = '(null)'
 
Thanks
Lynn
 
 

View 2 Replies View Related

Updating A Column With A Count From Another Table?

May 18, 2014

My goal is to with one update statement, fill TABLE1.counter (currently empty) with the total count of TABLE2.e# (employee). Also, if TABLE1.e# isn't in TABLE2.e# then it sets it to "0" (TABLE1.e# 8 and 9 should have a counter of 0) This is for sqlplus.

e.g. TABLE2:

e#
--
1
2
3
4
5
5
6
7
7
1
2
3
4
5
UPDATE TABLE1
SET counter = (
SELECT COUNT(TABLE2.e#)
FROM TABLE2 INNER JOIN TABLE1 ON (TABLE2.e# = TABLE1.e#)
GROUP BY TABLE2.e#);

--^Doesn't work

so my TABLE1 should be:

e# counter
-----------
1 .. 2
2 .. 2
3 .. 2
4 .. 2
5 .. 3
6 .. 1
7 .. 2
8 .. 0
9 .. 0

(The .. is just spacing to show the table here)

View 1 Replies View Related

Updating A Column With Input Parameter?

May 19, 2014

Is it possible to assign to a column a value passed as a parameter?

When I run the proc I get the following error :

Msg 245, Level 16, State 1, Procedure Transfer, Line 17

Conversion failed when converting the varchar value '@ID' to data type int.

----####################################################
USE [tbldata]
GO
/****** Object: StoredProcedure [dbo].[Transfer] Script Date: 05/19/2014 11:26:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Transfer](@ID int)

[code].....

View 1 Replies View Related

Updating Column Using Replace And SubStr?

Mar 26, 2015

I have an update where I need to replace the first two numbers of a string with SF and remove the last number - e.g.:

029929229 >> SF992922

I have an update statement which works in Oracle, but I cannot get it to work in SQL Server. The statement is below:

UPDATE oraowner.ems_sam
SET sam_id = replace(sam_id,sam_id,'SF'||substr(sam_id, 3, length(sam_id) - 3));

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved