473,381 Members | 1,463 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,381 software developers and data experts.

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

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

"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
> 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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Jeff Thur | last post by:
Can anybody Please demonstrate how to run a stored procedure from ASP Web Matrix. I can not believe all the answers that people have given me that are not working. Does anybody use stored...
1
by: AS400 Guru | last post by:
I have this stored procedure that loops through a table and updates a couple of fields. For some reason one of the fields is not being updated. If I run the same code from query analyzer, it...
4
by: Guru | last post by:
Hi All I am using a Global Temporary table in the Stored procedure and i am creating index for a column in that temporary table.When i am executing it. It is not taking that index. I checked...
3
by: rodchar | last post by:
hey all, i have a number field i need to convert from 20050818 to 08182005 can someone please show me some ways i can do this?
0
by: Johan Neidenmark | last post by:
When i try to run this SQL statements in iSeries Access for windows (against my customers db2) i get: SQL State: 42904 Vendor Code: -7032 Message: SQL procedure, function, or trigger...
3
by: sanika1507 | last post by:
Hi all, in this part of the code it is if the iId <>0 now i have a difficulty . if iId = 0 i should not call this stored proc ....how to carry on with this ...how is the else part written ...can...
0
by: jith87 | last post by:
hi, i need to handle a "NOT FOUND"exception inside Stored Procedure in MySQL. i get an error in the syntax.here is my code... DROP PROCEDURE IF EXISTS insert1// create procedure...
1
by: troubledrpt | last post by:
We have reports that connect to Oracle stored procedures. When we try to update the connection to point to another database, we get connection errors. Have tried numerous things, but nothing works...
10
by: pinman | last post by:
hi i am trying to implemement forms authentication for my website but can't seem to get the stored procedure to output the correct value when checking a users credentials. the code is ALTER...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.