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


ADVERTISEMENT

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 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

Updating A Column In A CommonTable Expression

Nov 22, 2007

I'm new to CTE and I'm trying to update some columns in my cte listed below


USE ELU;

GO

WITH FollowupGrid (TransactionID,

DetailLineNum,

TransactionDate,

Quantity,

ItemDescription,

ProductSize,

TransactionType,

FollowupResolve,

ReplenishResolve) AS

(

SELECT TransactionID,

DetailLineNum,

TransactionDate,

Quantity,

ItemDescription,

ProductSize,

TransactionType,

FollowupResolve,

ReplenishResolve

FROM vwFollowupTransactions

WHERE CustomerNum = N'6299'

UNION

SELECT

r.TransactionID,

r.DetailLineNum,

r.TransactionDate,

r.Quantity,

r.ItemDescription,

r.ProductSize,

r.TransactionType,

r.FollowupResolve,

r.ReplenishResolve

FROM vwReplenishTransactions r

WHERE CustomerNum = N'6299'

)



UPDATE FollowupGrid

SET FollowupResolve = N'Yes'

FROM vwFollowupTransactions f INNER JOIN

FollowupGrid ON f.DetailLineNum = FollowupGrid.DetailLineNum AND

f.TransactionID = FollowupGrid.TransactionID

WHERE (f.FollowupResolve is null)

UPDATE FollowupGrid

SET ReplenishResolve = N'Yes'

FROM vwReplenishTransactions t INNER JOIN

FollowupGrid ON t.DetailLineNum = FollowupGrid.DetailLineNum AND

t.TransactionID = FollowupGrid.TransactionID

WHERE (f.ReplenishResolve is null)

(

select * from FollowupGrid

)

GO


When I run this query I get an error message which states"

"Update or insert of view or function 'FollowupGrid' failed because it contains a derived or constant field."

How would I go about updating the FollowupResolve and ReplenishResolve fields in a CTE?

View 1 Replies View Related

Stored Procedure For Updating Bit Datatype Column

Mar 25, 2008

Hi guys,
I have a table with following columns and records.
Empid       Empname        Phone     Flag
14             Rajan                 2143          116             Balan                 4321          122             Nalini                 3456          023             Ganesh              9543          0
Now i need to create a stored procedure which will convert the flag values to vice versa since it is a bit datatype. That is if execute the stored procedure it should convert all the flag values to 1 if it zero and zero's to 1. How? Pls provide me the full coding for the stored procedure.
Thanx. 

View 2 Replies View Related

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 View Related

SQL Problem Updating A Column Of Type DECIMAL(11,4)

Apr 29, 2005

This problem is strictly an SQL problem, and I am about to lose my mind over it.  I have a table named Inventory that has a column named PartialQty which is of type DECIMAL(11,4).  I have shortened my query for simplicity, but here it is.
DECLARE @pqty1  DECIMAL(11,4)
SELECT @pqty1 = PartialQty FROM Inventory WHERE InventoryID = @invIDPRINT @pqty1 UPDATE Inventory   SET PartialQty = 6.5513   WHERE InventoryID = @invID
SELECT @pqty1 = PartialQty FROM Inventory WHERE InventoryID = @invIDPRINT @pqty1
Before this query is ran, the value in PartialQty is 1.2345.  If I run this 2 times in a row, this is the output I get:
RUN 1:
1.2345
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
6.0000
 
RUN 2:
6.0000
(1 row(s) affected)
6.5513
 
I don't understand 2 things.  One is why the first run displays (1 row(s) affected) 4 times even though there is only one record with that InventoryID.
And the other thing I don't understand is why the first time I run it, it loses all of the values after the decimal point. 
Please help,
Scott

View 2 Replies View Related

Updating Column Values In Multipe Tables

May 26, 2000

I am looking for suggestions on how to accomplish the following task with the least amount of hand keying that I can get away with.

I have a main table named Office and 4 other tables that have the Office table ID field value in them.
I have been given a new set of ID values that need to replace the values that are currently in the Office table and then update the ID field in the other 4 tables.

I have only thought of 2 solution and I don’t like either one.
1 Add a new column to the Office table and key in the new ID’s then go through the pages that reference the old ID field and change the SQL queries to use the new field.

2 Change the value of the current Office ID field to the new value. Search the other 4 tables for the old value and then update them to the new value.

Anyone got a better Idea?

View 1 Replies View Related

Updating Modified/updated Date Column?

May 13, 2008

If your tables contain created and modified/updated dates what is the best practice for these?

1. Should you use UTC dates?
2. Do you use a default for the creation date (I assume yes)?
3. Should you create a trigger to handle the last update date? Or do you update the column directly in your stored procedures that modify data?

Also, as an aside if you store the user who created/updated the record do you store a foreign key reference to the user table or do you store the username as a varchar? Of course I know you'd normally store the fk, but I wasn't sure if the "logging" nature of the column suggests storing a string value.

View 2 Replies View Related

Updating A Column With Scope_Identity After An Insert Sometimes Does Not Work

Jan 21, 2008



Hello,

I have the following stored procedure that inserts records and updates the new record. The parameter @rpt_id has a value of -1 when entering the procedure. It needs to be updated with the new record if (identity) once the record is inserted, bu sometimes the update does not happen. The new records ends up with -1 in the rpt_id column.

I have included the stored procedure. I will appreciate any ideas?


ALTER PROCEDURE [dbo].[thhill_InsertReport]

(

@RPT_ID int,

@REPORT_DATE datetime,

@COMMIT_DATE datetime,

@JOB_NUMBER nvarchar(50),

@TECH_NAME nvarchar(75),

@CLIENT_NAME nvarchar(255),

@CLIENT_CONTACT nvarchar(75),

@CITY nvarchar(75),

@STATE nvarchar(75),

@COUNTRY nvarchar(75),

@SUPPLIER nvarchar(75),

@WORK_ORDER nvarchar(75),

@RIG_NAME nvarchar(75),

@WELL_NAME nvarchar(75),

@OCSG_NUMBER nvarchar(75),

@AFE_NUMBER nvarchar(75),

@MGMT varchar(6000),

@Complete varchar(3),

@newReportId int output,

@changeCommitDate bit = 1

)

AS

SET NOCOUNT ON;

if @changeCommitDate = 1

set @COMMIT_DATE = getdate()

INSERT INTO [reports] ([RPT_ID], [REPORT_DATE], [COMMIT_DATE], [JOB_NUMBER], [TECH_NAME], [CLIENT_NAME], [CLIENT_CONTACT], [CITY], [STATE], [COUNTRY], [SUPPLIER], [WORK_ORDER], [RIG_NAME], [WELL_NAME], [OCSG_NUMBER], [AFE_NUMBER], [MGMT], [isReportComplete])

VALUES (@RPT_ID, @REPORT_DATE, @COMMIT_DATE, @JOB_NUMBER, @TECH_NAME, @CLIENT_NAME, @CLIENT_CONTACT, @CITY, @STATE, @COUNTRY, @SUPPLIER, @WORK_ORDER, @RIG_NAME, @WELL_NAME, @OCSG_NUMBER, @AFE_NUMBER, @MGMT, @Complete );

declare @reference int, @tmpReportId int

set @tmpReportId = SCOPE_IDENTITY()

set @reference = @tmpReportId * -1


SET NOCOUNT Off;


Update [reports] set [RPT_ID] = @reference where id = @tmpReportId

select @newReportId = RPT_ID

FROM REPORTS

WHERE (ID = @tmpReportId)

View 3 Replies View Related

Updating A Column In A Table That Contains 50 Million Rows

Feb 27, 2008

I'm looking for some performance assistance on updating a column value in a table that contains approximately 50 million rows. I have a permanent table in another database that has the key column and value to be set. My query is listed below, but I'm afraid it will run quite awhile. Any suggestions would be appreciated.

update mytable
set column2 = b.column2
from mytable as a
join mytable1 as b
on a.column1 = b.column1



There is a one to one relationship between the two tables.

View 8 Replies View Related

Transact SQL :: Updating Missing Column Gaps

Aug 24, 2015

I'm a little bit unsure...

declare @t table
(col1 varchar(10),
col2 varchar(10),
col3 varchar(10),
col4 varchar(10) ) 
insert into @t values ('A123', 'Test', '','')
insert into @t values ('', 'Test 1', 'Y','N')

[Code] ...

Which comes out as 

col1 col2 col3 col4
(No column name)
A123 Test
1
Test 1
Y N 0
Y N
0
A125 Test
1
Test 9
Y N 0
N Y
0

but what I would like is col 1 populated As A123 until it hits A125  then populated A125 etc.   I was thinking about using the iff but not getting anywhere fast.

View 11 Replies View Related

Updating Table Column With Cumulative Numbers

Nov 13, 2007

Hello,

One more question about this Custom Calendar table I'm creating. I have a column called "IsWorkdays" which indicates if the day represented by a row is a workday or not. For our purposes, I also need to create a row that accumulates those numbers by month. So, if it is the 3rd workday of the month, this column would have a 3. This is beyond my current T-SQL ability. Does anyone know how to do this?

Thanks a lot,
Andy

SQL version: 2005, Standard edition.

View 1 Replies View Related

Updating Specific Columns If That Column Is Equal To A Value In One Query?

Dec 8, 2006

I want to set a column to 0 if it is set to a certain number. There are several columns to check though, so I am wondering if I can do it all in one query, or if I have to do it in single queries?

here is an example:

account | contact_id_1 | contact_id_2 | contact_id_3
433 | 67 | 23 | 67

so I want to set any contact_id_N = 0 where contact_id_N is = 67

so in the end, the table will look like this:

account | contact_id_1 | contact_id_2 | contact_id_3
433 | 0 | 23 | 0

is there a way to do it in one statement?

View 2 Replies View Related

Updating Systemtables Directly To Change Column-collations

Mar 3, 2005

Hi there.

Is there any way to update the system tables directly, to alter the collations of the columns in the user db's?

I've tried the script below:

UPDATE Syscolumns SET collation = 'SQL_Latin1_General_CP1_CS_AS' WHERE name = '<AddrCode>'
AND id = object_id('<Compliance>')

but, I get the following error message:

Server: Msg 271, Level 16, State 1, Line 1
Column 'collation' cannot be modified because it is a computed column.

Can you please help me! I need to do thousands of these, and most of them has constraints on, so my script I generated to do the ALTER TABLE.... ALTER COLUMN does not suffice.

Thanks.

View 2 Replies View Related

SQL 2012 :: Updating A Column By Default Based On A Rule?

Oct 26, 2015

CREATE TABLE EDI_data_proc_log(
ID int IDENTITY(1,1),
comment VARCHAR(3000),
time_recorded DATETIME DEFAULT GETDATE(),
run_by varchar(100),
duration int );

When a record is inserted I like the duration column to be computed.This should happen only after the first record to the table has gotten inserted.You might say a trigger would be the best.. Ok then, show me the syntax.

Or I am thinking can we write a user defined function that will compute the value for the duration column.

--By default, I would like to update the duration column as follows:

--It should record the number of seconds between the last insertion ( You can get that time from the time_recorded column from the previous record and the current time can be obtained from the getdate() function )

View 2 Replies View Related







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