> It's not possible to say for sure without seeing the UPDATE statement, but
it may be that you're building the UPDATE statement in VB and you're not
quoting a new column value correctly. You should probably print out the SQL
string you're sending to the server to check that it's what you expect, and
try to execute it directly in Query Analyzer.
However, that's just a guess - if you can post the DDL for your table, as
well as the UPDATE statement, it will be easier for someone to help you.
Simon
I have found out how to fix my problem - but don't understand the
reason the original version works when a non-unique index field is
being undated
from vb this SP call works if unique index (on first 3 fields) is made
non-unique
**** this is a test update that should cause a Duplicate error ****
dbo.up_parmupd_Holiday
('942','02/08/03','02/08/03','35','24','7','16','Early morning
pickup','2 days by coach','79.5','0','0','9','15','11','8.5','7.5','1 ','9','0','0','N')
however if I make the index unique and re-rerun then I get the ADO
error (NativeError=170):
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect
syntax near '942'.
but it works correctly giving "Duplicate value in index" if I remove
the brackets () from the statement
This is strange because until I added the unique to the index it all
worked perfectly
I wouldn't have thought the table/index structures should matter to
the syntax of the stored procedure call in regard to whether brackets
are used or not
This app has hundreds of SP calls and they are all bracket'ed without
issue - until this one
I guess I should go on a bracket hunt
Here are the TABLE/SP definitions
CREATE TABLE [dbo].[Holiday_T] (
[Holiday_ID] [int] IDENTITY (1, 1) NOT NULL ,
[Destination_ID] [int] NOT NULL ,
[Departure_Date_dt] [datetime] NOT NULL ,
[Return_Date_dt] [datetime] NOT NULL ,
[Category_ID] [tinyint] NULL ,
[Tour_Op_ID] [int] NULL ,
[Arrangement_ID] [int] NULL ,
[Country_ID] [int] NULL ,
[Comment_vc] [varchar] (100) NULL ,
[Information_vc] [varchar] (500) NULL ,
[Adult_Cost_sm] [smallmoney] NULL ,
[Child_Cost_sm] [smallmoney] NULL ,
[Discount_Cost_sm] [smallmoney] NULL ,
[Single_Supplement_sm] [smallmoney] NULL ,
[Deposit_sm] [smallmoney] NULL ,
[Commission_sm] [smallmoney] NULL ,
[Tour_Op_Insurance_sm] [smallmoney] NULL ,
[MIS_Insurance_sm] [smallmoney] NULL ,
[Room1_si] [smallint] NULL ,
[Room2_si] [smallint] NULL ,
[Room3_si] [smallint] NULL ,
[Room4_si] [smallint] NULL ,
[Cancelled_ch] [char] (1) NULL ,
[Current_bt] [bit] NULL
) ON [PRIMARY]
GO
Unique index on [Holiday_ID]
Index on [Departure_Date_dt]
Unique index on [Destination_ID][Departure_Date_dt][Return_Date_dt]
CREATE PROCEDURE up_parmupd_Holiday
(@Holiday_ID int,
@Departure_Date datetime,
@Return_Date datetime,
@Category_ID int, @Tour_Operator_ID int, @Arrangement_ID int,
@Country_ID int,
@Comment VarChar(100), @Information VarChar(500),
@Adult_Cost Real, @Child_Cost Real, @Discount_Cost Real,
@Single_Supplement Real, @Deposit Real, @Commission Real,
@Tour_Op_Insurance Real, @MIS_Insurance Real,
@Room1 int, @Room2 int, @Room3 int, @Room4 int,
@Cancelled Char(1)) AS
UPDATE Holiday_T
SET Departure_Date_dt = @Departure_Date,
Return_Date_dt = @Return_Date,
Category_ID = @Category_ID,
Tour_Op_ID = @Tour_Operator_ID,
Arrangement_ID = @Arrangement_ID,
Country_ID = @Country_ID,
Comment_vc = @Comment,
Information_vc = @Information,
Adult_Cost_sm = @Adult_Cost,
Child_Cost_sm = @Child_Cost,
Discount_Cost_sm = @Discount_Cost,
Single_Supplement_sm = @Single_Supplement,
Deposit_sm = @Deposit,
Commission_sm = @Commission,
Tour_Op_Insurance_sm = @Tour_Op_Insurance,
MIS_Insurance_sm = @MIS_Insurance,
Room1_si = @Room1,
Room2_si = @Room2,
Room3_si = @Room3,
Room4_si = @Room4,
Cancelled_ch = @Cancelled
WHERE Holiday_ID = @Holiday_ID
GO
Thanks
