By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,850 Members | 970 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,850 IT Pros & Developers. It's quick & easy.

SQLS7&VB6 Date Update Gives Syntax (Not Date Format) Error In Stored Procedure

P: n/a
Hi,

I have a problem with updating a datetime column,
When I try to change the Column from VB I get "Incorrect syntax near
'942'" returned from [Microsoft][ODBC SQL Server Driver][SQL Server]

'942' is the unique key column value

However if I update any other column the syntax is fine

The same blanket update query makes the changes no matter what is
updated

The problem only happens when I set a unique key on the date field in
question
Key is a composite of an ID, and 2 date fields

If I allow duplicates in the index it all works perfectly

I am trying to trap 'Duplicate value in index' (which is working on
other non-date columns in other tables)

This is driving me nuts

Any help would be appreciated
Jul 20 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a

"S.Patten" <sp****@hotmail.com> wrote in message
news:56**************************@posting.google.c om...
Hi,

I have a problem with updating a datetime column,
When I try to change the Column from VB I get "Incorrect syntax near
'942'" returned from [Microsoft][ODBC SQL Server Driver][SQL Server]

'942' is the unique key column value

However if I update any other column the syntax is fine

The same blanket update query makes the changes no matter what is
updated

The problem only happens when I set a unique key on the date field in
question
Key is a composite of an ID, and 2 date fields

If I allow duplicates in the index it all works perfectly

I am trying to trap 'Duplicate value in index' (which is working on
other non-date columns in other tables)

This is driving me nuts

Any help would be appreciated


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
Jul 20 '05 #2

P: n/a
> 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
Jul 20 '05 #3

P: n/a
S.Patten (sp****@hotmail.com) writes:
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


It is not wholly clear how you pass the above to SQL Server, but it
seems as you are simply passing it as an SQL statement with
adCmdText. In such case the error message is obvious, because the
above is not syntactially correct T-SQL. You don't have parentheses
around parameter lists when you can stored procedures.

The uniqueness of the index does not matter. I would guess that you were
changing something else at the same time.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #4

P: n/a
Erland Sommarskog <so****@algonet.se> wrote in message news:<Xn********************@127.0.0.1>...
S.Patten (sp****@hotmail.com) writes:
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


It is not wholly clear how you pass the above to SQL Server, but it
seems as you are simply passing it as an SQL statement with
adCmdText. In such case the error message is obvious, because the
above is not syntactially correct T-SQL. You don't have parentheses
around parameter lists when you can stored procedures.

The uniqueness of the index does not matter. I would guess that you were
changing something else at the same time.


No (I have just re-tried this to prove it),

The same VB form with an command button works perfectly when index in
non-unique, fails when index unique - this is without closing the vb
only altering the index

This is actually not originally my application, I am maintaining it
for the future, I am not always sure whether the syntax I am passing
is ODBC (which MS give examples using brackets) or T-SQL, which is a
new variation to me, and as you say does not like brackets

I actually figured out how to fix it by trial and error with Query
Analyzer

As I said earlier, There are many examples in this app of bracketed
SQL function calls which work

THe example below WORKS PERFECTLY BRACKETS AND ALL unless I am
updating a Unique-key column (which has been expanded to include
DestinationID, DepartureDate, ReturningDate, Category, TourOperator,
Arrangement, Country)

The call is made by passing a built sql see below (the de-bracketed
version)

I am happy to use non-bracketed versions but would still like some
eplanatio to why the unique index issue exists, maybe it is versions
of other transport software (MDAC? v2.7 I believe) my PC is running

Thanks again.

UpdateHoliday(ByRef objConn As ADODB.Connection, _ (rest of header
left off)

strSQL = "dbo.up_parmupd_Holiday '" & lngHolidayID & "','" & _
strDepartureDate & "','" & strReturningDate & "','" & bytCategory &
"','" & _
intTourOperator & "','" & bytArrangement & "','" & bytCountry & "','"
& _
strComment & "','" & strInformation & "','" & curAdultCost & "','" & _
curChildCost & "','" & curDiscountCost & "','" & curSingleSupp & "','"
& _
curDeposit & "','" & curCommission & "','" & curTourOpInsurance &
"','" & _
curMISInsurance & "','" & intRooms1 & "','" & intRooms2 & "','" & _
intRooms3 & "','" & intRooms4 & "','" & strCancelled & "','" & _
strDateOverrideAllowed & "'"

'Execute the UPDATE procedure.
objConn.Execute strSQL
Jul 20 '05 #5

P: n/a
S.Patten (sp****@hotmail.com) writes:
As I said earlier, There are many examples in this app of bracketed
SQL function calls which work

THe example below WORKS PERFECTLY BRACKETS AND ALL unless I am
updating a Unique-key column (which has been expanded to include
DestinationID, DepartureDate, ReturningDate, Category, TourOperator,
Arrangement, Country)
...

strSQL = "dbo.up_parmupd_Holiday '" & lngHolidayID & "','" & _
...
strDateOverrideAllowed & "'"

'Execute the UPDATE procedure.
objConn.Execute strSQL


But there are no parentheses in this example, unless they there are
hiding in lngHolidayID and strDateOverrideAllowed.

There may be other problems in your application when you update a
column in a unique index. However, the syntax you showed is never
legal.

One possibility is that the error handling in this application is not fool-
proof. I recognize from the system I work. Sometimes it presents completely
bogus errors. This is because the error message is picked up from some
global area, but the error message was passed somewhere else, and this
message is an old and tried one.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.