can somone help me tell what is wrong with this coding?

GLOBAL STRING Line1_BatchID
GLOBAL STRING Line1_CompletedDate
GLOBAL INT Line1_CompletedQty
GLOBAL STRING Line1_CompletedTime
GLOBAL STRING Line1_ExpiryDate
GLOBAL INT Line1_Qty
GLOBAL STRING Line1_OrderID

GLOBAL INT Line1_BatchID_ID
GLOBAL INT Line1_CompletedDate_ID
GLOBAL INT Line1_CompletedQty_ID
GLOBAL INT Line1_CompletedTime_ID
GLOBAL INT Line1_ExpiryDate_ID
GLOBAL INT Line1_OrderStatus_ID

GLOBAL STRING Line2_BatchID
GLOBAL STRING Line2_CompletedDate
GLOBAL INT Line2_CompletedQty
GLOBAL STRING Line2_CompletedTime
GLOBAL STRING Line2_ExpiryDate
GLOBAL STRING Line2_OrderStatus
GLOBAL INT Line2_Qty
GLOBAL STRING Line2_OrderID

GLOBAL INT Line2_BatchID_ID
GLOBAL INT Line2_CompletedDate_ID
GLOBAL INT Line2_CompletedQty_ID
GLOBAL INT Line2_CompletedTime_ID
GLOBAL INT Line2_ExpiryDate_ID
GLOBAL INT Line2_OrderStatus_ID

GLOBAL STRING Line3_BatchID
GLOBAL STRING Line3_CompletedDate
GLOBAL INT Line3_CompletedQty
GLOBAL STRING Line3_CompletedTime
GLOBAL STRING Line3_ExpiryDate
GLOBAL STRING Line3_OrderStatus
GLOBAL INT Line3_Qty
GLOBAL STRING Line3_OrderID

GLOBAL INT Line3_BatchID_ID
GLOBAL INT Line3_CompletedDate_ID
GLOBAL INT Line3_CompletedQty_ID
GLOBAL INT Line3_CompletedTime_ID
GLOBAL INT Line3_ExpiryDate_ID
GLOBAL INT Line3_OrderStatus_ID

GLOBAL STRING result
GLOBAL STRING result2

FUNCTION getOrderDetail(STRING Product_ID)
INT counter = 0
INT statusSQL, sqlResult;
STRING Sql1
STRING Sql2
STRING Sql3
result = ""

statusSQL = SQLConnect("DSN=SQLSRV_TBLS;SRVR=localhost;DB=FYPJ Integration of SAP NetweaverData;UID=labuser;PWD=success;");
IF statusSQL <> -1 THEN //If Connection Success
Sql1 = "SELECT SetId FROM ProductionDataField WHERE (Field = 'productid') AND (DataValue = '" + Product_ID + "')"
sqlResult = SQLExec(statusSQL, Sql1);
IF sqlResult = 0 THEN //If SQL Success
WHILE SQLNext(statusSQL) = 0 DO
IF result <> "" THEN
result = result + ","
END
result = result + SQLGetField(statusSQL, "SetId")
END
END

Sql2 = "SELECT SetId FROM ProductionDataField WHERE (Field = 'order status') AND (DataValue = 'pending') AND (SetId IN (" + result + ")) Order by SetID"
sqlResult = SQLExec(statusSQL, Sql2);
IF sqlResult = 0 THEN //If SQL Success
result = ""
IF SQLNext(statusSQL) = 0 THEN
//Select first SetId
result = SQLGetField(statusSQL, "SetId")
END
END

Sql3 = "SELECT Id, DataValue FROM ProductionDataField WHERE (SetId IN (" + result + ")) and (IsActive = 1) Order by field"
sqlResult = SQLExec(statusSQL, Sql3);
IF sqlResult = 0 THEN //If SQL Success
WHILE SQLNext(statusSQL) = 0 DO
IF Product_ID = "Biscuit" THEN
IF counter < 9 THEN
IF counter = 0 THEN
Line1_BatchID_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 1 THEN
Line1_CompletedDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 2 THEN
Line1_CompletedQty_ID = SQLGetField(statusSQL , "ID")
Line1_CompletedQty = SQLGetField(statusSQL , "DataValue")
END
IF counter = 3 THEN
Line1_CompletedTime_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 4 THEN
Line1_ExpiryDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 5 THEN
Line1_OrderStatus_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 6 THEN
Line1_OrderID = SQLGetField(statusSQL , "DataValue")
END
IF counter = 7 THEN
END
IF counter = 8 THEN
Line1_Qty = SQLGetField(statusSQL , "DataValue")
END
END
END

IF Product_ID = "ChocolateBiscuit" THEN
IF counter = 0 THEN
Line2_BatchID_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 1 THEN
Line2_CompletedDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 2 THEN
Line2_CompletedQty_ID = SQLGetField(statusSQL , "ID")
Line2_CompletedQty = SQLGetField(statusSQL , "DataValue")
END
IF counter = 3 THEN
Line2_CompletedTime_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 4 THEN
Line2_ExpiryDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 5 THEN
Line2_OrderStatus_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 6 THEN
Line2_OrderID = SQLGetField(statusSQL , "DataValue")
END
IF counter = 7 THEN

END
IF counter = 8 THEN
Line2_Qty = SQLGetField(statusSQL , "DataValue")
END
END

IF Product_ID = "PeanutButterBiscuit" THEN
IF counter = 0 THEN
Line3_BatchID_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 1 THEN
Line3_CompletedDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 2 THEN
Line3_CompletedQty_ID = SQLGetField(statusSQL , "ID")
Line3_CompletedQty = SQLGetField(statusSQL , "DataValue")
END
IF counter = 3 THEN
Line3_CompletedTime_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 4 THEN
Line3_ExpiryDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 5 THEN
Line3_OrderStatus_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 6 THEN
Line3_OrderID = SQLGetField(statusSQL , "DataValue")
END
IF counter = 7 THEN

END
IF counter = 8 THEN
Line3_Qty = SQLGetField(statusSQL , "DataValue")
END
END

counter = counter + 1
END
END

END
SQLEnd(statusSQL)
SQLDisconnect("DSN=SQLSRV_TBLS")
END

FUNCTION UpdateBatchID(STRING Product_ID)
INT statusSQL, sqlResult;
STRING Sql1
statusSQL = SQLConnect("DSN=SQLSRV_TBLS;SRVR=localhost;DB=FYPJ Integration of SAP NetweaverData;UID=labuser;PWD=success;");
IF statusSQL <> -1 THEN //If Connection Success
IF Product_ID = "Biscuit" THEN
Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line1_BatchID + "' WHERE [Id] = " + IntToStr(Line1_BatchID_ID)
END
IF Product_ID = "ChocolateBiscuit" THEN
Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line2_BatchID + "' WHERE [Id] = " + IntToStr(Line2_BatchID_ID)
END
IF Product_ID = "PeanutButterBiscuit" THEN
Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line3_BatchID + "' WHERE [Id] = " + IntToStr(Line3_BatchID_ID)
END

sqlResult = SQLExec(statusSQL, Sql1);
END
SQLEnd(statusSQL)
SQLDisconnect("DSN=SQLSRV_TBLS")
END

FUNCTION UpdateCompletedQty(STRING Product_ID)
INT statusSQL, sqlResult;
STRING Sql1
statusSQL = SQLConnect("DSN=SQLSRV_TBLS;SRVR=localhost;DB=FYPJ Integration of SAP NetweaverData;UID=labuser;PWD=success;");
IF statusSQL <> -1 THEN //If Connection Success
IF Product_ID = "Biscuit" THEN
Sql1 = "UPDATE ProductionDataField SET DataValue = '" + IntToStr(Line1_CompletedQty) + "' WHERE [Id] = " + IntToStr(Line1_CompletedQty_ID)
END
IF Product_ID = "ChocolateBiscuit" THEN
Sql1 = "UPDATE ProductionDataField SET DataValue = '" + IntToStr(Line2_CompletedQty) + "' WHERE [Id] = " + IntToStr(Line2_CompletedQty_ID)
END
IF Product_ID = "PeanutButterBiscuit" THEN
Sql1 = "UPDATE ProductionDataField SET DataValue = '" + IntToStr(Line3_CompletedQty) + "' WHERE [Id] = " + IntToStr(Line3_CompletedQty_ID)
END

sqlResult = SQLExec(statusSQL, Sql1);
END
SQLEnd(statusSQL)
SQLDisconnect("DSN=SQLSRV_TBLS")
END

FUNCTION UpdateCompletedOrder(STRING Product_ID)
INT statusSQL, sqlResult;
STRING Sql1
statusSQL = SQLConnect("DSN=SQLSRV_TBLS;SRVR=localhost;DB=FYPJ Integration of SAP NetweaverData;UID=labuser;PWD=success;");
IF statusSQL <> -1 THEN //If Connection Success
IF Product_ID = "Biscuit" THEN
Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line1_CompletedDate + "' WHERE [Id] = " + IntToStr(Line1_CompletedDate_ID)
sqlResult = SQLExec(statusSQL, Sql1);

Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line1_CompletedTime + "' WHERE [Id] = " + IntToStr(Line1_CompletedTime_ID)
sqlResult = SQLExec(statusSQL, Sql1);

Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line1_ExpiryDate + "' WHERE [Id] = " + IntToStr(Line1_ExpiryDate_ID)
sqlResult = SQLExec(statusSQL, Sql1);

Sql1 = "UPDATE ProductionDataField SET [DataValue] = 'Completed' WHERE [Id] = " + IntToStr(Line1_OrderStatus_ID)
sqlResult = SQLExec(statusSQL, Sql1);
END

IF Product_ID = "ChocolateBiscuit" THEN
Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line2_CompletedDate + "' WHERE [Id] = " + IntToStr(Line2_CompletedDate_ID)
sqlResult = SQLExec(statusSQL, Sql1);

Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line2_CompletedTime + "' WHERE [Id] = " + IntToStr(Line2_CompletedTime_ID)
sqlResult = SQLExec(statusSQL, Sql1);

Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line2_ExpiryDate + "' WHERE [Id] = " + IntToStr(Line2_ExpiryDate_ID)
sqlResult = SQLExec(statusSQL, Sql1);

Sql1 = "UPDATE ProductionDataField SET [DataValue] = 'Completed' WHERE [Id] = " + IntToStr(Line2_OrderStatus_ID)
sqlResult = SQLExec(statusSQL, Sql1);

END

IF Product_ID = "PeanutButterBiscuit" THEN
Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line3_CompletedDate + "' WHERE [Id] = " + IntToStr(Line3_CompletedDate_ID)
sqlResult = SQLExec(statusSQL, Sql1);

Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line3_CompletedTime + "' WHERE [Id] = " + IntToStr(Line3_CompletedTime_ID)
sqlResult = SQLExec(statusSQL, Sql1);

Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line3_ExpiryDate + "' WHERE [Id] = " + IntToStr(Line3_ExpiryDate_ID)
sqlResult = SQLExec(statusSQL, Sql1);

Sql1 = "UPDATE ProductionDataField SET [DataValue] = 'Completed' WHERE [Id] = " + IntToStr(Line3_OrderStatus_ID)
sqlResult = SQLExec(statusSQL, Sql1);
END
END
SQLEnd(statusSQL)
SQLDisconnect("DSN=SQLSRV_TBLS")
END

INT FUNCTION getLine1_Qty()
RETURN Line1_Qty
END

INT FUNCTION getLine1_CompletedQty()
RETURN Line1_CompletedQty
END

STRING FUNCTION getLine1_OrderID()
RETURN Line1_OrderID
END

STRING FUNCTION getLine1_BatchID()
RETURN Line1_BatchID
END

INT FUNCTION getLine2_Qty()
RETURN Line2_Qty
END

INT FUNCTION getLine2_CompletedQty()
RETURN Line2_CompletedQty
END

STRING FUNCTION getLine2_OrderID()
RETURN Line2_OrderID
END

STRING FUNCTION getLine2_BatchID()
RETURN Line2_BatchID
END

INT FUNCTION getLine3_Qty()
RETURN Line3_Qty
END

INT FUNCTION getLine3_CompletedQty()
RETURN Line3_CompletedQty
END

STRING FUNCTION getLine3_OrderID()
RETURN Line3_OrderID
END

STRING FUNCTION getLine3_BatchID()
RETURN Line3_BatchID
END

FUNCTION dbTest()

INT statusSQL, sqlResult;
INT counter = 0
STRING Sql1
STRING Sql2
STRING Sql3
STRING Product_ID = "Biscuit"
result = ""

statusSQL = SQLConnect("DSN=SQLSRV_TBLS;SRVR=localhost;DB=FYPJ Integration of SAP NetweaverData;UID=labuser;PWD=success;");
IF statusSQL <> -1 THEN //If Connection Success
Sql1 = "SELECT SetId FROM ProductionDataField WHERE (Field = 'productid') AND (DataValue = '" + Product_ID + "')"
sqlResult = SQLExec(statusSQL, Sql1);
IF sqlResult = 0 THEN //If SQL Success
WHILE SQLNext(statusSQL) = 0 DO
IF result <> "" THEN
result = result + ","
END
result = result + SQLGetField(statusSQL, "SetId")
END
END

Sql2 = "SELECT SetId FROM ProductionDataField WHERE (Field = 'order status') AND (DataValue = 'pending') AND (SetId IN (" + result + "))"
sqlResult = SQLExec(statusSQL, Sql2);
IF sqlResult = 0 THEN //If SQL Success
result = ""
IF SQLNext(statusSQL) = 0 THEN
result = SQLGetField(statusSQL, "SetId")
END
END

Sql3 = "SELECT Id, DataValue FROM ProductionDataField WHERE (SetId IN (" + result + ")) and (IsActive = 1) Order by field"
sqlResult = SQLExec(statusSQL, Sql3);
IF sqlResult = 0 THEN //If SQL Success
WHILE SQLNext(statusSQL) = 0 DO
IF Product_ID = "Biscuit" THEN
IF counter = 0 THEN
Line1_BatchID_ID = SQLGetField(statusSQL , "ID")
Line1_BatchID = SQLGetField(statusSQL , "DataValue")
END
IF counter = 1 THEN
Line1_CompletedDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 2 THEN
Line1_CompletedQty_ID = SQLGetField(statusSQL , "ID")
Line1_CompletedQty = SQLGetField(statusSQL , "DataValue")
END
IF counter = 3 THEN
Line1_CompletedTime_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 4 THEN
Line1_ExpiryDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 5 THEN
Line1_OrderStatus_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 6 THEN
Line1_OrderID = SQLGetField(statusSQL , "DataValue")
END
IF counter = 7 THEN

END
IF counter = 8 THEN
Line1_Qty = SQLGetField(statusSQL , "DataValue")
END
END

IF Product_ID = "ChocolateBiscuit" THEN
IF counter = 0 THEN
Line2_BatchID_ID = SQLGetField(statusSQL , "ID")
Line2_BatchID = SQLGetField(statusSQL , "DataValue")
END
IF counter = 1 THEN
Line2_CompletedDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 2 THEN
Line2_CompletedQty_ID = SQLGetField(statusSQL , "ID")
Line2_CompletedQty = SQLGetField(statusSQL , "DataValue")
END
IF counter = 3 THEN
Line2_CompletedTime_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 4 THEN
Line2_ExpiryDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 5 THEN
Line2_OrderStatus_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 6 THEN
Line2_OrderID = SQLGetField(statusSQL , "DataValue")
END
IF counter = 7 THEN

END
IF counter = 8 THEN
Line2_Qty = SQLGetField(statusSQL , "DataValue")
END
END

IF Product_ID = "PeanutButterBiscuit" THEN
IF counter = 0 THEN
Line3_BatchID_ID = SQLGetField(statusSQL , "ID")
Line3_BatchID = SQLGetField(statusSQL , "DataValue")
END
IF counter = 1 THEN
Line3_CompletedDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 2 THEN
Line3_CompletedQty_ID = SQLGetField(statusSQL , "ID")
Line3_CompletedQty = SQLGetField(statusSQL , "DataValue")
END
IF counter = 3 THEN
Line3_CompletedTime_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 4 THEN
Line3_ExpiryDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 5 THEN
Line3_OrderStatus_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 6 THEN
Line3_OrderID = SQLGetField(statusSQL , "DataValue")
END
IF counter = 7 THEN

END
IF counter = 8 THEN
Line3_Qty = SQLGetField(statusSQL , "DataValue")
END
END

counter = counter + 1
END
END

END
SQLEnd(statusSQL)
SQLDisconnect("DSN=SQLSRV_TBLS")
END

STRING FUNCTION getresult()
RETURN result
END

STRING FUNCTION getresult2()
RETURN result2
END

Give us some clues. What do you think may be the problem?

Most of us are not going to spend much time looking through your code without a hint about what we are looking for.

And it appears to be VB5 (or earlier) code -GLOBAL variables have long fallen by the wayside.

|||i have some field in the SQL table, when information is passed in, the data goes into the wrong field. for eg, i have the field date and quantity, when information is passed into the SQL, the data got mixed up, date data is stored into the quantity field and quantity is stored in to the date field....

Can someone tell me whats wrong with this code?

Hi All

I keep getting back VINET and not Lilas...can someone point me in the
right direction?

Thanks a lot

DECLARE @.idoc int
DECLARE @.doc varchar(1000)
SET @.doc ='
<ROOT
<CustomerID>VINET </CustomerID
<CustomerID>Lilas</CustomerID
</ROOT>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT *
FROM OPENXML (@.idoc, '/ROOT',2)
WITH (

CustomerID varchar(100) )I'm not an expert on SQL Server and XML, but it seems to me that
OPENXML is very picky about the XML form that it will accept; can you
modify your XML statement to read like this?

SET @.doc ='
<ROOT>
<Customer>
<CustomerID>VINET </CustomerID>
</Customer>
<Customer>
<CustomerID>Lilas</CustomerID>
</Customer>
</ROOT>'

and then your OPENXML to statement to read like this:

SELECT *
FROM OPENXML (@.idoc, '/ROOT/Customer',2)
WITH (
CustomerID varchar(100) )

Don't know why it works, but it does.

Stu|||Try this...

DECLARE @.idoc int
DECLARE @.doc varchar(1000)
SET @.doc ='
<ROOT
<CustomerID>VINET </CustomerID
<CustomerID>Lilas</CustomerID
</ROOT>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT *
FROM OPENXML (@.idoc, '/ROOT/CustomerID',2)
WITH (

CustomerID varchar(100) '.')

EXEC sp_xml_removedocument @.iDoc|||Thank you Mark that works great!!!!
Can you explain to my why '.' needed to be included? I can't seem to
locate it in Books online...

Thanks again|||(reezaali@.gmail.com) writes:
> Thank you Mark that works great!!!!
> Can you explain to my why '.' needed to be included? I can't seem to
> locate it in Books online...

Books Online says about col-pattern:

Is an optional, general XPath pattern that describes how the XML nodes
should be mapped to the columns. If the ColPattern is not specified, the
default mapping (attribute-centric or element-centric mapping as
specified by flags) takes place.

'.' maps to the current node, which is /Root/CustomerID.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thank you...

can someone tell me what I am doing wrong

HI all,
I have this trigger on a table
**********
CREATE TRIGGER addtotal ON dbo.ClaimFinancialLoss
AFTER INSERT, UPDATE, DELETE
AS
if update(Ammount)
begin
Declare @.tot int, @.id int
select @.id = ClaimID from inserted
--Print 'Id after insert' +str(@.id)
if exists (select * from deleted)
Begin
select @.id = ClaimID from deleted
--Print 'Id after deleted' +str(@.id)
end
Select @.tot=sum(Ammount) from ClaimFinancialLoss group by ClaimID Having
ClaimID = @.id
update claim set total = @.tot where ClaimID = @.id
end
**************
This updates the claim table. On the claim table I have these two triggers
(they are kept seperate just for simplicity at the moment)
****************
CREATE TRIGGER dateupdated ON dbo.Claim
AFTER INSERT
AS
declare @.id int
select @.id=claimid from inserted
update claim set updated = getdate() where claimid = @.id
*************
CREATE TRIGGER layerchange ON [dbo].[Claim]
FOR INSERT, UPDATE AS
if update(layerid)
select layerid as ins from inserted
select layerid as del from deleted
declare @.id int
select @.id=claimid from inserted
begin
delete from ClaimDeductables where claimid = @.id
insert into claimdeductables SELECT PolLayer.LayerCap,
PolLayer.PayOrder,claim.claimid, Layer.LayerID
FROM Claim INNER JOIN
CP_Covertype ON Claim.LayerID = CP_Covertype.CPCKey
INNER JOIN
Covertype ON CP_Covertype.CTKey = Covertype.CTKey
INNER JOIN
PolLayer ON Covertype.CTKey = PolLayer.CTKey INNER
JOIN
Layer ON PolLayer.layerid = Layer.LayerID
WHERE (Claim.ClaimID = @.id)
raiserror('You have made changes to the layers of this claim',9,1,1)
end
*****************
THe raiserror always fires, when ever I update Claimfinancialloss.ammount.
To me this error should not be raised As I have a if update(Layerid), and I
am not updating the layerid of the claim, only the total (through the
trigger.)
Is having two update triggers on the same table, one of them updating the
claimtable again, causing the layerid column to simulate a be updated.
Thanks
Robertyou need a begin after if update(layerid) because if without begin
looks only at the first statement after the if
example
declare @.x int
select @.x =4
if @.x <> 4
print 'yes'
print'blah'
you see, blah will alway be printed
declare @.x int
select @.x =4
if @.x <> 4
begin
print 'yes'
print'blah'
end
If you use begin and end this won't happen since the if will skip that
whole statement
http://sqlservercode.blogspot.com/|||Without investigating this further, you missed that a trigger is fired
per STATEMENT NOT per ROW, so you better should investigate eliminating
this misdesign.
HTH, jens Suessmeyer.|||HI,
YEp, you hit the nail on the head.
Stupid me, I did have a begin statement after the iff statement, But then
during testing I inserted two select statements after the if statement,
naturally the first select statement was executed as a direct result of the
if statment then the other statments executed as a matter of course.
Thanks
Robert
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1140101176.000869.264260@.g43g2000cwa.googlegroups.com...
> you need a begin after if update(layerid) because if without begin
> looks only at the first statement after the if
> example
> declare @.x int
> select @.x =4
> if @.x <> 4
> print 'yes'
> print'blah'
> you see, blah will alway be printed
>
> declare @.x int
> select @.x =4
> if @.x <> 4
> begin
> print 'yes'
> print'blah'
> end
> If you use begin and end this won't happen since the if will skip that
> whole statement
> http://sqlservercode.blogspot.com/
>|||HI Jens,
Yes I realise that, as I said this is a tempory measure, testing. But
anyway, I was pointed to the fact that I never had my begin statement in the
right place by another post.
Thanks anyway for you valued input
Robert
<Jens.Suessmeyer@.googlemail.com> wrote in message
news:1140101824.576386.320190@.g14g2000cwa.googlegroups.com...
> Without investigating this further, you missed that a trigger is fired
> per STATEMENT NOT per ROW, so you better should investigate eliminating
> this misdesign.
> HTH, jens Suessmeyer.
>

Can someone see whats wrong...??

I have a SQL query in my asp.net & c# application. im trying to retrieve the data from two tables where the ID's of the tables match. once this is found i am obtaining the value associated with one of the keys. e.g. my two tables

Event EventCategoryType

Field Type Example Field Type Example
EventID Int(4) 1 CategoryID(PK)int(4) 1
CategoryID(FK)int(4) 1 Type varchar(50) Exercise
Type varchar (200) Exercise Color varchar(50) Brown

The CategoryID is a 1:n relationship. my SQL query will retrive the values where the CategoryIDs match and the Tyoe matches as well. once this is found it will apply the associated color with that categoryID (each unique category has its own Color).

my application will read all the data correctly (ive checked it with a breakpoint too and it reads all the different colors for the different ID's) but it wont display the text in the right color from the table. it will just display everything in the first color it comes across.

Im including my code if it helps. can anyone tell me where i am going wrong please?? (the procedures are called on the On_Page_Load method)

private

void Load_Events()
{
///<summary>
///Loads the events added from the NewEvent from into a dataset
///and then just as with the Holidyas, the events are wriiten to
///the appropriate calendar cell by comparing the date. only the
///title and time will be displayed in the cell. other event details
///such as, Objective, owner will be shown in a dialog box syle when
///the user hovers over the event.
///</summary>

mycn =new SqlConnection(strConn);
myda =new SqlDataAdapter("SELECT * FROM Event, EventTypeCategory WHERE Event.CategoryID = EventTypeCategory.CategoryID AND Event.Type = EventTypeCategory.CategoryType", mycn);
myda.Fill(ds2, "Events");
}

privatevoid Populate_Events(object sender, System.Web.UI.WebControls.DayRenderEventArgs e)
{
///<summary>
///This procedure will read all the data from the dataset - Events and then
///write each event to the appropriate calendar cell by comparing the date of
///the EventStartDate. if an event is found, the title and time are written
///to the cell, other details are shown by hovering over the cell to bring
///up another function that will display the data in a dialogBox. once the
///event is written, the appropriate color is applied to the text.
///</summary
if (!e.Day.IsOtherMonth)
{
foreach (DataRow drin ds2.Tables[0].Rows)
{
if ((dr["EventStartDate"].ToString() != DBNull.Value.ToString()))
{
DateTime evStDate = (DateTime)dr["EventStartDate"];
string evTitle = (string)dr["Title"];
string evStTime = (string)dr["EventStartTime"];
string evEnTime = (string)dr["EventEndTime"];
string evColor = (string)dr["CategoryColor"];

if(evStDate.Equals(e.Day.Date))
{
e.Cell.Controls.Add(new LiteralControl("<br>"));
e.Cell.Controls.Add(new LiteralControl("<FONT COLOR = evColor>"));
e.Cell.Controls.Add(new LiteralControl(evTitle + " " + evStTime + " - " + evEnTime));
}
}
}
}
else
{
e.Cell.Text = "";
}
}

e.Cell.Controls.Add(new LiteralControl("<FONT COLOR = " + evColor + ">"));

does that solve the problem ?

|||

Fantastic! works perfectly! Thanks!

Can somebody tell me what is wrong with this?

I am trying to created a view and have a need for conditional logic:

Here is what I presently have (not working):
-----------------------
IF (ISDATE(COMPLETIONDATE) = 1)
BEGIN
CASE
WHEN DATEDIFF(d, COMPLETIONDATE, RESET.RESET_UNIT.MCD) > (SELECT GREEN FROM RESET.THRESHOLDS WHERE TYPE = 'SCHEDULE') THEN 'GREEN'
WHEN DATEDIFF(d, COMPLETIONDATE, RESET.RESET_UNIT.MCD) < (SELECT GREEN FROM RESET.THRESHOLDS WHERE TYPE = 'SCHEDULE') AND DATEDIFF(d, COMPLETIONDATE, RESET.RESET_UNIT.MCD) > 0 THEN 'YELLOW'
WHEN DATEDIFF(d, COMPLETIONDATE, RESET.RESET_UNIT.MCD) < 0 THEN 'RED'
END AS THRESHOLDSTATUS
END
ELSE
IF (ISDATE(COMPLETIONDATE) = 0)
BEGIN
CASE
WHEN DATEDIFF(d, TARGETCOMPLETIONDATE, RESET.RESET_UNIT.MCD) > (SELECT GREEN FROM RESET.THRESHOLDS WHERE TYPE = 'SCHEDULE') THEN 'GREEN'
WHEN DATEDIFF(d, TARGETCOMPLETIONDATE, RESET.RESET_UNIT.MCD) < (SELECT GREEN FROM RESET.THRESHOLDS WHERE TYPE = 'SCHEDULE') AND DATEDIFF(d, TARGETCOMPLETIONDATE, RESET.RESET_UNIT.MCD) > 0 THEN 'YELLOW'
WHEN DATEDIFF(d, TARGETCOMPLETIONDATE, RESET.RESET_UNIT.MCD) < 0 THEN 'RED'
END AS THRESHOLDSTATUS
END
-----------------------

Can someone tell me what I am doing wrong?

Basically I am trying to test to see if "completiondate" is a date and if it is then perform a case operation using it, if it is not a date then I want to perform the case operation using "targetcompletiondate".

Thanks...select THRESHOLDSTATUS =
CASE
WHEN DATEDIFF(d, case when ISDATE(COMPLETIONDATE) = 1 then COMPLETIONDATE else TARGETCOMPLETIONDATE end, RESET.RESET_UNIT.MCD) > (SELECT GREEN FROM RESET.THRESHOLDS WHERE TYPE = 'SCHEDULE') THEN 'GREEN'
WHEN DATEDIFF(d, case when ISDATE(COMPLETIONDATE) = 1 then COMPLETIONDATE else TARGETCOMPLETIONDATE end, RESET.RESET_UNIT.MCD) < (SELECT GREEN FROM RESET.THRESHOLDS WHERE TYPE = 'SCHEDULE') AND DATEDIFF(d, case when ISDATE(COMPLETIONDATE) = 1 then COMPLETIONDATE else TARGETCOMPLETIONDATE end, RESET.RESET_UNIT.MCD) > 0 THEN 'YELLOW'
WHEN DATEDIFF(d, case when ISDATE(COMPLETIONDATE) = 1 then COMPLETIONDATE else TARGETCOMPLETIONDATE end, RESET.RESET_UNIT.MCD) < 0 THEN 'RED'
END
from ...|||First problem: You have a column called "CompletionDate" which may contain something that is NOT A DATE?!?! That's a design issue right there...

Second problem: You are putting this in a VIEW? Complex logic such as this is better off in a stored procedure. You didn't provide your entire CREATE VIEW statement, but depending upon what you are trying to do, your task may not be possible.

You may be able to use nested CASE statements:
CASE WHEN ISDATE(COMPLETIONDATE) = 1
CASE WHEN DATEDIFF(d, COMPLETIONDATE, RESET.RESET_UNIT.MCD) > (SELECT GREEN FROM RESET.THRESHOLDS WHERE TYPE = 'SCHEDULE') THEN 'GREEN'
WHEN DATEDIFF(d, COMPLETIONDATE, RESET.RESET_UNIT.MCD) < (SELECT GREEN FROM RESET.THRESHOLDS WHERE TYPE = 'SCHEDULE') AND DATEDIFF(d, COMPLETIONDATE, RESET.RESET_UNIT.MCD) > 0 THEN 'YELLOW'
WHEN DATEDIFF(d, COMPLETIONDATE, RESET.RESET_UNIT.MCD) < 0 THEN 'RED'
END
ELSE
CASE WHEN DATEDIFF(d, TARGETCOMPLETIONDATE, RESET.RESET_UNIT.MCD) > (SELECT GREEN FROM RESET.THRESHOLDS WHERE TYPE = 'SCHEDULE') THEN 'GREEN'
WHEN DATEDIFF(d, TARGETCOMPLETIONDATE, RESET.RESET_UNIT.MCD) < (SELECT GREEN FROM RESET.THRESHOLDS WHERE TYPE = 'SCHEDULE') AND DATEDIFF(d, TARGETCOMPLETIONDATE, RESET.RESET_UNIT.MCD) > 0 THEN 'YELLOW'
WHEN DATEDIFF(d, TARGETCOMPLETIONDATE, RESET.RESET_UNIT.MCD) < 0 THEN 'RED'
END
END AS THRESHOLDSTATUS

...or a single CASE statement with more complex criteria:
CASE
WHEN ISDATE(COMPLETIONDATE) = 1 AND (DATEDIFF(d, COMPLETIONDATE, RESET.RESET_UNIT.MCD) > (SELECT GREEN FROM RESET.THRESHOLDS WHERE TYPE = 'SCHEDULE')) THEN 'GREEN'
WHEN ISDATE(COMPLETIONDATE) = 1 AND (DATEDIFF(d, COMPLETIONDATE, RESET.RESET_UNIT.MCD) < (SELECT GREEN FROM RESET.THRESHOLDS WHERE TYPE = 'SCHEDULE')) AND (DATEDIFF(d, COMPLETIONDATE, RESET.RESET_UNIT.MCD) > 0) THEN 'YELLOW'
WHEN ISDATE(COMPLETIONDATE) = 1 AND (DATEDIFF(d, COMPLETIONDATE, RESET.RESET_UNIT.MCD) < 0 THEN 'RED'
WHEN ISDATE(COMPLETIONDATE) = 0 AND (DATEDIFF(d, TARGETCOMPLETIONDATE, RESET.RESET_UNIT.MCD) > (SELECT GREEN FROM RESET.THRESHOLDS WHERE TYPE = 'SCHEDULE')) THEN 'GREEN'
WHEN ISDATE(COMPLETIONDATE) = 0 AND (DATEDIFF(d, TARGETCOMPLETIONDATE, RESET.RESET_UNIT.MCD) < (SELECT GREEN FROM RESET.THRESHOLDS WHERE TYPE = 'SCHEDULE')) AND (DATEDIFF(d, TARGETCOMPLETIONDATE, RESET.RESET_UNIT.MCD) > 0) THEN 'YELLOW'
WHEN ISDATE(COMPLETIONDATE) = 0 AND (DATEDIFF(d, TARGETCOMPLETIONDATE, RESET.RESET_UNIT.MCD) < 0 THEN 'RED'
END AS THRESHOLDSTATUS|||I thought that that's what I gave the poster...?|||Yeah, you sniped me by 10 minutes. So you get kudos for being first, but I get points for verbosity!