How To Update Using A Function

Jul 20, 2005

Take a table, where not all the columns are populated:

CREATE TABLE #T (A int, B int, C int, D int)
INSERT #T (A,B) VALUES (1,2)
INSERT #T (A,B) VALUES (3,4)
INSERT #T (A,B) VALUES (5,6)
INSERT #T (A,B) VALUES (7,8)
INSERT #T (A,B) VALUES (9,10)

The values for C and D can be computed as functions of A and B. For this
example, let's say they are twice A and three times B, respectively:

CREATE FUNCTION dbo.F(@A int,@B int)
RETURNS @Tbl TABLE (X int, Y int)
AS BEGIN
INSERT @Tbl (X,Y) VALUES (@A*2, @B*3)
RETURN
END

Now we use the function to compute the other columns:

UPDATE #T SET C=X, D=Y
FROM dbo.F(A,B)

Right? Well, no. Instead, I get this message:

Server: Msg 155, Level 15, State 1, Line 2
'A' is not a recognized OPTIMIZER LOCK HINTS option.

Any suggestions? I would like to use this structure, if possible.

Jim Geissman
Countrywide Home Loans

View 7 Replies


ADVERTISEMENT

Update Function: Why SQL Server Update An Empty String With 0?

May 13, 2008

I'm new to this forum.
This 'problem' has occured many times, but I've always found a way around it.
I have pages with datagrids, in which a user can edit a certain fields and then update the tables with new data. Lets say when a user edit a Name field and a money field. If he/she left those two fields blank, the table is automatically updated with a <null> (for the name field) and a 0 (for the money field.) Both these columns were set up to allow Null values.
Anyone has an idea why they were updated that way? And is there like a standard on how the data types are updated if a field is left blank?
Thank you very much.

View 23 Replies View Related

Using Max In Update Function

Feb 1, 2015

I have one table1 which contain data like this
ID --- year --- months
1 --- 2015 --- null

table2 like this
ID --- Year --- months
1 --- 2015 --- 6
1 --- 2015 --- 7

I want to update the max value of month for id 1,which is 7 using update function, How can i do this.

View 3 Replies View Related

UPDATE From A Function In CLR

Feb 14, 2008


I have been trying to get a function to UPDATE a row in the database. I know that this is not supported but by using code from a previous post this can be accomplished [See my code below].

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1863258&SiteID=1





Code Snippet
[SqlFunction(DataAccess = DataAccessKind.Read)]
public static int GetNextValue(string dbName)
{
int rows = 0;
lock (obj)
{
SqlPipe sp = SqlContext.Pipe;
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
SqlCommand cmd = new SqlCommand("SELECT id FROM Items", conn);
conn.Open();
rows = int.Parse(cmd.ExecuteScalar().ToString()) + 1;
using (SqlConnection updateCon = new SqlConnection("Data Source=" + Environment.MachineName + ";Initial Catalog=" + dbName + ";Integrated Security=True;Pooling=False"))
{
SqlCommand updateCmd = new SqlCommand("UPDATE Items SET id=" + rows,updateCon);
updateCon.Open();
updateCmd.ExecuteScalar();
updateCmd.Dispose();
}

return rows;
}
}






I hope this is helpful.

Regards

tribal

View 3 Replies View Related

C# Update Function. Where AND Where Not Working.

Jan 29, 2008

Can someone please tell me why in the bloody hell this isnt working? It ignores the WHERE VENDORID match portion and marks all instances of USERID match to TRUE. I've been banging my head for an hour... have I really forgotten basic sql???!!!!public static void UpdateVendor(VendorEvaluationEntity VEE)
{int vendorid = Convert.ToInt32(VEE.VendorevalVendor);
int userid = Convert.ToInt32(VEE.VendorevalUser);SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["VendorEvaluationConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand("Update tblVendorUser set vendoruser_vendor_evaluated = 'true' where (vendoruser_vendor_id = @vendorid) and (vendoruser_user_id=@userid)", conn);SqlParameter pmvendorid = new SqlParameter();
SqlParameter pmuserid = new SqlParameter();pmvendorid.ParameterName = "@vendorid";pmvendorid.SqlDbType = SqlDbType.Int;
pmvendorid.Value = vendorid;
pmuserid.ParameterName = "@userid";pmuserid.SqlDbType = SqlDbType.Int;
pmuserid.Value = userid;
cmd.Parameters.Add(pmvendorid);
cmd.Parameters.Add(pmuserid);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
 
}

View 3 Replies View Related

Trigger And Update() Function!

Jun 28, 1999

In the SQL 7.0 Documentation is indicated that the UPDATE(<fieldname>), if
used in a isert/update trigger, it return TRUE or FALSE based on presence of <field> in the SET List, if the trigger was fired by an UPDATE operation, or in the list of field to fill, if the trigger was fired by an INSERT operation.

Below there is a piece of code that demostrate the false documentation
assertion in the case of a trigger fired by an INSERT operation, infact even
if i have specified only the FIELD1 in INSERT Statement the trigger Print in the screen that i have touched FIELD2 too.

Now the SP1 don't solve this BUG, even if i had read some month ago (before
that SP1 was out) that in SP1 this will be fixed!

Anyone Can Help me to solve this problem, indicating me where i can post the
problem to sensitize the Microsoft's Service Pack Factory ?

Thank in Advance

PS:Sorry for my Bad English .... correct me please if some part are not
readable!

------[Cut Here]-------------------

CREATE TABLE TEST (FIELD1 INTEGER , FIELD2 INTEGER )
GO

CREATE TRIGGER TriggerIU_Test
ON TEST
FOR INSERT, UPDATE
AS

IF UPDATE(FIELD1) PRINT 'Field 1 Touched'

IF UPDATE(FIELD2) PRINT 'Field 2 Touched'
GO

print 'InsertIng '
INSERT INTO TEST (FIELD1) VALUES (2)

print 'Updating '
UPDATE TEST set FIELD1 = 1

GO
DROP TABLE TEST
------[Cut Here]-------------------

View 2 Replies View Related

Urgent : Update() Function

Jun 2, 2000

IF OBJECT_ID('dbo.TestTrigger') IS NOT NULL
BEGIN
DROP TABLE dbo.TestTrigger
IF OBJECT_ID('dbo.TestTrigger') IS NOT NULL
PRINT '<<< FAILED DROPPING TABLE dbo.TestTrigger >>>'
ELSE
PRINT '<<< DROPPED TABLE dbo.TestTrigger >>>'
END
go
CREATE TABLE dbo.TestTrigger
(
colA int NULL,
colB int NULL
)
go
IF OBJECT_ID('dbo.TestTrigger') IS NOT NULL
PRINT '<<< CREATED TABLE dbo.TestTrigger >>>'
ELSE
PRINT '<<< FAILED CREATING TABLE dbo.TestTrigger >>>'
go


IF OBJECT_ID('dbo.TestTrigger_i') IS NOT NULL
BEGIN
DROP TRIGGER dbo.TestTrigger_i
IF OBJECT_ID('dbo.TestTrigger_i') IS NOT NULL
PRINT '<<< FAILED DROPPING TRIGGER dbo.TestTrigger_i >>>'
ELSE
PRINT '<<< DROPPED TRIGGER dbo.TestTrigger_i >>>'
END
go
CREATE TRIGGER dbo.TestTrigger_i
ON dbo.TestTrigger
FOR INSERT AS

IF UPDATE(colA)
select "updating col A"

IF UPDATE(colB)
select "updating col B"

go
go
IF OBJECT_ID('dbo.TestTrigger_i') IS NOT NULL
PRINT '<<< CREATED TRIGGER dbo.TestTrigger_i >>>'
ELSE
PRINT '<<< FAILED CREATING TRIGGER dbo.TestTrigger_i >>>'
go



insert into TestTrigger
(colA)
values
(1)
go

insert into TestTrigger
(colA, colB)
values
(2,3)
go

View 1 Replies View Related

Can A DTS Function Update The Transaction Log

Mar 14, 2000

I have an application which uses DTS to import data. After the data is imported the transaction logs are no good since the import wasn't logging.
Is there a way to turn logging on?

Thanks

View 1 Replies View Related

Aggregate Function Update

Mar 8, 2004

I am trying to write an update statement based on an aggregate and it will not let me. Please find below the SQL.

update abtimesummary
set hours = sum(a.hours)
from abtimestore a
join abtimesummary b
on (cast(a.weekno as varchar(10)))+'-'+(cast(a.empno as varchar(10))) = b.summaryid

and this is the error message:

Server: Msg 157, Level 15, State 1, Line 2
An aggregate may not appear in the set list of an UPDATE statement.

Can someone tell me how to get round this please?

Many thanks

View 2 Replies View Related

How To Update A Table With SUM Function?

Jul 23, 2005

Table test2 has multiple amounts for each account, I would like to sumthe amounts for the same account and use the result to update thevariable 'tot_amount' in table test1. But SQL does not allow me to usesum function in update. Is there any other way to do this? Thanks.update test1set tot_amount=sum(b.amount)from test1 as ajoin test2 as bon a.acc_no=b.acc_no

View 4 Replies View Related

UPDATE Another Table Using Aggregate Function

May 22, 2008

Here is the example:

I have two tables. One has Projects with the total amt of hours worked on the project itself. The other is an Employee_Projects table with individual rows of hrs per employee worked on the above referenced projects.

I need to SUM all the hrs from the Employee_Projects table and GROUP BY project number, then UPDATE the Projects table with the sum of hours where the Project Number from table A matches the Project Number from table B.

Of course, you cant use an aggregate function in an UPDATE clause, so what would be the easiest way to do this??

Any help would be much appreciated.

-C

View 2 Replies View Related

Update Using Group By And Aggregate Function

Jan 22, 2004

I'm trying to update a varchar field using SUM. I keep getting the error that the sub query returns more than one value.

UPDATE CIRSUB_M
SET TRM_DMO = SUBSTRING(TRM_DMO,1,11) +
(SELECT CAST(SUM(COPIES) AS VARCHAR(5)) FROM CIRSUB_M
WHERE BIL_ORG = '02' AND CRC_STS IN ('R','P','Q','T')
GROUP BY PUB_CDE, DNR_NBR)
WHERE BIL_ORG = '02' AND CRC_STS IN ('R','P','Q','T')

Example

PUB_CDE DNR_NBR COPIES TRM_DMO
THN 000000092637 100 A
THN 000000092637 200 B
THN 000000082455 100 A
THN 000000082455 200 B
THN 000000051779 100 A

Updated
THN 000000092637 100 A300
THN 000000092637 200 B300
THN 000000082455 100 A300
THN 000000082455 200 B300
THN 000000051779 100 A100

View 4 Replies View Related

Exec Sproc In A Update Function

Feb 5, 2004

Folks

Here is a query which updates certain values. GetAddress is another
sproc which returns addrId. I have to pass certain values ie
strAddress1 strCity .....intZip4 values in the sproc GetAddress and execute the update query. In doing so it says GetAddress in
not a recognized function name. Is the syntax correct to exec sproc
GetAddress.


update Persons
set
Persons.strLastName=H.strLastName,
Persons.strNameSuffix=H.strNameSuffix,
Persons.lngHomeID= GetAddress (H.strAddress1,strAddress2,H.strCity,H.strState,H. strZip,H.intZip4),
Persons.lngMailID= GetAddress(H.strAddress1,strAddress2,H.strCity,H.s trState,H.strZip,H.intZip4)
from ALSHeadr H
where Persons.lngSSN=H.lngFedTaxID



FYI I can post GetAddress sproc but it is working properl.
I just want to know how to pass the values in ALSHeadr table into
the sproc.


Thanx

View 3 Replies View Related

Function To UPDATE Multiple Records

Jan 7, 2015

I have a question, what kind of Query or function do I have to use to UPDATE multiple record that I just want to add a letter at the end of the existing OrderNum.

Before
ID NAME OrderNum
1 Pete WEB123
2 Paul WEB124
3 Sam WEB125
4 Tim WEB126

After
ID NAME OrderNum
1 Pete WEB123A
2 Paul WEB124A
3 Sam WEB125A
4 Tim WEB126A

View 3 Replies View Related

UPDATE RECORDS WITH AGGREGATE FUNCTION

Mar 2, 2007

Problem is very simple

I want to update sum of a field from another table to first table

TABLE ONE:
==========
ItemID
QtyInStock


Table TWO:
==========
BatchID
ItemID
Qty

I want to Update the QtyInStock of First Table with Sum(Batch.Qty)

here is the query i am writing but giving error

UPDATE ITEMS
SET
INSTOCKQTY=CASE WHEN QtyInBatch>1 THEN QTYINBATCH ELSE 0 END
FROM ITEMS, (
SELECT ITEMS.ITEMID, SUM(Batch.Qty) AS QtyInBatch FROM Batch INNER JOIN Items ON Batch.ItemID = Items.ItemID GROUP BY ITEMS.ITEMID
)

appericiating anyones help in advance




FAZEEL AMJAD
Systems Engineer
Crystal Technologies

View 4 Replies View Related

Update Table With Joins And Aggregate Function

Apr 3, 2007

Is this possible? What I am looking for is something like:UPDATE T_SitesSET T_Sites.LastDate = T_Inspections.DateFROM T_SitesINNER JOIN T_Assets ON T_Sites.SiteID = T_Assets.AssetIDLEFT OUTER JOIN T_Insecptions ON T_Assets.AssetID = T_Inspections.AssetID-- But I need only the last inspection done on the site (including if it is null) 

View 1 Replies View Related

SP5 Problem For Insert Trigger Update Function

Mar 8, 1999

In recent testing, I have found that the trigger function UPDATE under SQL Server 6.5 SP5 behaves differently then SP4. For insert, the update function returns true on items that have not been explicitly inserted.

Is this a bug? Is this a new feature change? Where can I get more information on a bug list for SP5?

View 4 Replies View Related

T-SQL (SS2K8) :: Create Function For Dynamically Update Every Year?

May 27, 2015

UPDATE Report

SET MSYear= casewhen MSDate > '2011/06/30' and MSDate < '2012/07/01' THEN '2012'
when MSDate > '2012/06/30' and MSDate < '2013/07/01' THEN '2013'
when MSDate > '2013/06/30' and MSDate < '2014/07/01' THEN '2014'
when MSDate > '2014/06/30' and MSDate < '2015/07/01' THEN '2015'
when MSDate > '2015/06/30' and MSDate < '2016/07/01' THEN '2016'
when MSDate > '2016/06/30' and MSDate < '2017/07/01' THEN '2017'
End

Actually our business year starts from 1st july and for this code I need function which is dynamically updates the every year for example 2014-07-01 to 2015-06-30 this is called as a 2015 year like this I need function which will dynamically update a year.

View 4 Replies View Related

Default Date(current Date) Function W/ Update?

Mar 10, 2008

I have a reference table that currently has no web front-end. It's a small table(<10 rows) that's not going to change very often (maybe once every few months).

We manually update rows on the table via the GUI table interface in Enterprise Mgr., not in T-SQL.

What I'd like to do is have SQL Server automatically update the "Last_Modified" column with the current timestamp. I can do it on an Insert using the GetDate() function, but if I update a row, this doesn't work.

Is there a function I can use that can auto-populate for both insert and updates?

View 4 Replies View Related

Help Convert MS Access Function To MS SQL User Defined Function

Aug 1, 2005

I have this function in access I need to be able to use in ms sql.  Having problems trying to get it to work.  The function gets rid of the leading zeros if the field being past dosn't have any non number characters.For example:TrimZero("000000001023") > "1023"TrimZero("E1025") > "E1025"TrimZero("000000021021") > "21021"TrimZero("R5545") > "R5545"Here is the function that works in access:Public Function TrimZero(strField As Variant) As String   Dim strReturn As String   If IsNull(strField) = True Then      strReturn = ""   Else      strReturn = strField      Do While Left(strReturn, 1) = "0"         strReturn = Mid(strReturn, 2)      Loop   End If  TrimZero = strReturnEnd Function

View 3 Replies View Related

In-Line Table-Valued Function: How To Get The Result Out From The Function?

Dec 9, 2007

Hi all,

I executed the following sql script successfuuly:

shcInLineTableFN.sql:

USE pubs

GO

CREATE FUNCTION dbo.AuthorsForState(@cState char(2))

RETURNS TABLE

AS

RETURN (SELECT * FROM Authors WHERE state = @cState)

GO

And the "dbo.AuthorsForState" is in the Table-valued Functions, Programmabilty, pubs Database.

I tried to get the result out of the "dbo.AuthorsForState" by executing the following sql script:

shcInlineTableFNresult.sql:

USE pubs

GO

SELECT * FROM shcInLineTableFN

GO


I got the following error message:

Msg 208, Level 16, State 1, Line 1

Invalid object name 'shcInLineTableFN'.


Please help and advise me how to fix the syntax

"SELECT * FROM shcInLineTableFN"
and get the right table shown in the output.

Thanks in advance,
Scott Chang

View 8 Replies View Related

A Function Smilar To DECODE Function In Oracle

Oct 19, 2004

I need to know how can i incoporate the functionality of DECODE function like the one in ORACLE in mSSQL..
please if anyone can help me out...


ali

View 1 Replies View Related

Using RAND Function In User Defined Function?

Mar 22, 2006

Got some errors on this one...

Is Rand function cannot be used in the User Defined function?
Thanks.

View 1 Replies View Related

Pass Output Of A Function To Another Function As Input

Jan 7, 2014

I need to be able to pass the output of a function to another function as input, where all functions involved are user-defined in-line table-valued functions. I already posted this on Stack Exchange, so here is a link to the relevant code: [URL] ...

I am fairly certain OUTER APPLY is the core answer here; there's *clearly* some way in which does *not* do what I need, or I would not get the null output you see in the link, but it seems clear that there should be a way to fool it into working.

View 5 Replies View Related

I Want A Function Like IfNull Function To Use In Expression Builder

Jul 24, 2007

Hi,

I wonder if there a function that i can use in the expression builder that return a value (e.g o) if the input value is null ( Like ifnull(colum1,0) )



i hope to have the answer because i need it so much.



Maylo

View 7 Replies View Related

ROW_NUMBER() Function Is Not Recognized In Store Procedure.(how To Add ROW_NUMBER() Function Into SQL SERVER 2005 DataBase Library )

Feb 4, 2008

Can anybody know ,how can we add  builtin functions(ROW_NUMBER()) of Sql Server 2005  into database library.
I get this error when i used into storeprocedure :
ROW_NUMBER() function is not recognized in store procedure.
i used MS SQL SERVER 2005 , so i think "ROW_FUNCTION()" is not in MS SQL SERVER 2005 database library.
I need to add that function into MS SQL SERVER 2005 database library.
Can anbody know how we can add that function into MS SQL SERVER 2005 database library?
 

View 4 Replies View Related

Function To Call Function By Name Given As Parameter

Jul 20, 2005

I want to write function to call another function which name isparameter to first function. Other parameters should be passed tocalled function.If I call it function('f1',10) it should call f1(10). If I call itfunction('f2',5) it should call f2(5).So far i tried something likeCREATE FUNCTION [dbo].[func] (@f varchar(50),@m money)RETURNS varchar(50) ASBEGINreturn(select 'dbo.'+@f+'('+convert(varchar(50),@m)+')')ENDWhen I call it select dbo.formuła('f_test',1000) it returns'select f_test(1000)', but not value of f_test(1000).What's wrong?Mariusz

View 3 Replies View Related

Error While Creating Inline Function - CREATE FUNCTION Failed Because A Column Name Is Not Specified For Column 1.

Apr 3, 2007



Hi,



I am trying to create a inline function which is listed below.



USE [Northwind]

SET ANSI_NULLS ON

GO

CREATE FUNCTION newIdentity()

RETURNS TABLE

AS

RETURN

(SELECT ident_current('orders'))

GO



while executing this function in sql server 2005 my get this error

CREATE FUNCTION failed because a column name is not specified for column 1.



Pleae help me to fix this error



thanks

Purnima

View 3 Replies View Related

Using A Scalar Valued Function As A Parameter Of A Table Valued Function?

Feb 1, 2006

Ok, I'm pretty knowledgable about T-SQL, but I've hit something that seems should work, but just doesn't...
I'm writing a stored procedure that needs to use the primary key fields of a table that is being passed to me so that I can generate what will most likely be a dynamically generated SQL statement and then execute it.
So the first thing I do, is I need to grab the primary key fields of the table.  I'd rather not go down to the base system tables since we may (hopefully) upgrade this one SQL 2000 machine to 2005 fairly soon, so I poke around, and find sp_pkeys in the master table.  Great.  I pass in the table name, and sure enough, it comes back with a record set, 1 row per column.  That's exactly what I need.
Umm... This is the part where I'm at a loss.  The stored procedure outputs the resultset as a resultset (Not as an output param).  Now I want to use that list in my stored procedure, thinking that if the base tables change, Microsoft will change the stored procedure accordingly, so even after a version upgrade my stuff SHOULD still work.  But... How do I use the resultset from the stored procedure?  You can't reference it like a table-valued function, nor can you 'capture' the resultset for use using the  syntax like:
DECLARE @table table@table=EXEC sp_pkeys MyTable
That of course just returns you the RETURN_VALUE instead of the resultset it output.  Ugh.  Ok, so I finally decide to just bite the bullet, and I grab the code from sp_pkeys and make my own little function called fn_pkeys.  Since I might also want to be able to 'force' the primary keys (Maybe the table doesn't really have one, but logically it does), I decide it'll pass back a comma-delimited varchar of columns that make up the primary key.  Ok, I test it and it works great.
Now, I'm happily going along and building my routine, and realize, hey, I don't really want that in a comma-delimited varchar, I want to use it in one of my queries, and I have this nice little table-valued function I call split, that takes a comma-delimited varchar, and returns a table... So I preceed to try it out...
SELECT *FROM Split(fn_pkeys('MyTable'),DEFAULT)
Syntax Error.  Ugh.  Eventually, I even try:
SELECT *FROM Split(substring('abc,def',2,6),DEFAULT)
Syntax Error.
Hmm...What am I doing wrong here, or can't you use a scalar-valued function as a parameter into a table-valued function?
SELECT *FROM Split('bc,def',DEFAULT) works just fine.
So my questions are:
Is there any way to programmatically capture a resultset that is being output from a stored procedure for use in the stored procedure that called it?
Is there any way to pass a scalar-valued function as a parameter into a table-valued function?
Oh, this works as well as a work around, but I'm more interested in if there is a way without having to workaround:
DECLARE @tmp varchar(8000)
SET @tmp=(SELECT dbo.fn_pkeys('MyTable'))
SELECT *
FROM Split(@tmp,DEFAULT)

View 1 Replies View Related

Update On Machine Runs Immediately, Update On Linked Server Takes 8 Minutes

Jan 2, 2008

What's up with this?

This takes like 0 secs to complete:

update xxx_TableName_xxx
set d_50 = 'DE',modify_timestamp = getdate(),modified_by = 1159

where enc_id in

('C24E6640-D2CC-45C6-8C74-74F6466FA262',

'762E6B26-AE4A-4FDB-A6FB-77B4782566C3',

'D7FBD152-F7AE-449C-A875-C85B5F6BB462')

but From linked server this takes 8 minutes????!!!??!:

update [xxx_servername_xxxx].xxx_DatabaseName_xxx.dbo.xxx_TableName_xxx
set d_50 = 'DE',modify_timestamp = getdate(),modified_by = 1159

where enc_id in

('C24E6640-D2CC-45C6-8C74-74F6466FA262',

'762E6B26-AE4A-4FDB-A6FB-77B4782566C3',

'D7FBD152-F7AE-449C-A875-C85B5F6BB462')


What settings or whatever would cause this to take so much longer from the linked server?

Edit:
Note) Other queries from the linked server do not have this behavior. From the stored procedure where we have examined how long each query/update takes... this particular query is the culprit for the time eating. I thought it was to do specefically with this table. However as stated when a query window is opened directly onto that server the update takes no time at all.

2nd Edit:
Could it be to do with this linked server setting?
Collation Compatible
right now it is set to false? I also asked this question in a message below, but figured I should put it up here.

View 5 Replies View Related

SQL Server 2000 - Issue W/ UPDATE - Single Row Update Returns 2 Different Messages

Nov 11, 2007

I am hoping someone can shed light on this odd behavior I am seeing running a simple UPDATE statement on a table in SQL Server 2000.  I have 2 tables - call them Table1 and Table2 for now (among many) that need to have certain columns updated as part of a single transaction process.   Each of the tables has many columns. I have purposely limited the target column for updating to only ONE of the columns in trying to isolate the issue.  In one case the UPDATE runs fine against Table1... at runtime in code and as a manual query when run in QueryAnalyzer or in the Query window of SSManagementStudio - either way it works fine. 
However, when I run the UPDATE statement against Table2 - at runtime I get rowsaffected = 0 which of course forces the code to throw an Exception (logically).  When I take out the SQL stmt and run it manually in Query Analyzer, it runs BUT this is the output seen in the results pane...
(0 row(s) affected)
(1 row(s) affected)
How does on get 2 answers for one query like this...I have never seen such behavior and it is a real frustration ... makes no sense.  There is only ONE row in the table that contains the key field passed in and it is the same key field value on the other table Table1 where the SQL returns only ONE message (the one you expect)
(1 row(s) affected)
If anyone has any ideas where to look next, I'd appreciate it.
Thanks 
 

View 2 Replies View Related

How To Correctly Update A Table Which Values Can Be Either Inserted/updated/deleted On Update?

Feb 16, 2006

Hi SQL fans,I realized that I often encounter the same situation in a relationdatabase context, where I really don't know what to do. Here is anexample, where I have 2 tables as follow:__________________________________________ | PortfolioTitle|| Portfolio |+----------------------------------------++-----------------------------+ | tfolio_id (int)|| folio_id (int) |<<-PK----FK--| tfolio_idfolio (int)|| folio_name (varchar) | | tfolio_idtitle (int)|--FK----PK->>[ Titles]+-----------------------------+ | tfolio_weight(decimal(6,5)) |+-----------------------------------------+Note that I also have a "Titles" tables (hence the tfolio_idtitlelink).My problem is : When I update a portfolio, I must update all theassociated titles in it. That means that titles can be either removedfrom the portfolio (a folio does not support the title anymore), addedto it (a new title is supported by the folio) or simply updated (atitle stays in the portfolio, but has its weight changed)For example, if the portfolio #2 would contain :[ PortfolioTitle ]id | idFolio | idTitre | poids1 2 1 102 2 2 203 2 3 30and I must update the PortfolioTitle based on these values :idFolio | idTitre | poids2 2 202 3 352 4 40then I should1 ) remove the title #1 from the folio by deleting its entry in thePortfolioTitle table2 ) update the title #2 (weight from 30 to 35)3 ) add the title #4 to the folioFor now, the only way I've found to do this is delete all the entriesof the related folio (e.g.: DELETE TitrePortefeuille WHERE idFolio =2), and then insert new values for each entry based on the new givenvalues.Is there a way to better manage this by detecting which value has to beinserted/updated/deleted?And this applies to many situation :(If you need other examples, I can give you.thanks a lot!ibiza

View 8 Replies View Related

ASP Update Method Not Working After A MSDE To MSSQL 2005 Expess Update

Oct 20, 2006

The Folowing code is not working anymore. (500 error)

Set objRS = strSQL1.Execute
strSQL1 = "SELECT * FROM BannerRotor where BannerID=" & cstr(BannerID)
objRS.Open strSQL1, objConn , 2 , 3 , adCmdText
If not (objRS.BOF and objRS.EOF) Then
objRS.Fields("Exposures").Value =objRS.Fields("Exposures").Value + 1
objRS.update
End If
objRS.Close

The .execute Method works fine

strSQL1 = "UPDATE BannerRotor SET Exposures=Exposures+1 WHERE BannerID=" & cstr(BannerID)
objConn.Execute strSQL1

W2003 + IIS6.0

Pls advice?

View 1 Replies View Related







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