Solved

Error message executing a sql statement in an ASP page

Posted on 2007-03-25
11
206 Views
Last Modified: 2010-03-20
I am receiving the following error on my asp page.

Microsoft OLE DB Provider for SQL Server error '80040e14'

Incorrect syntax near ','.

/doUpload.asp, line 102

On line 102, it is trying to execute the following sql statement:

INSERT INTO FacilitySource VALUES ('3/4/2007 15:00','RIT-00107','RIT AID CORPORATION','107','','FS',3,4396,'',4396,'Preventive Maintenance','PARKINGLOTSWEEPINGMAINTPM','PARKING LOT SWEEPING PM','RIT-00107 - PARKING LOT SWEEPING PM - 1X/WK','REISINGER, JENNIFER','PS','PM-10009','INPRG','In Progress (INPRG)','43',,8490,'GENESIS','GENESIS','0','NO','','NO','43','43','1/18/2007','NO','','654 COLVIN AVENUE','KENMORE','NY',14217-2825,'716-447-9128','No','No','','','','');

Any help on fixing this problem is greatly appreciated.

Thanks
0
Comment
Question by:sigkappu
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 50

Expert Comment

by:Ryan Chong
ID: 18788228
>>'43',,8490,

some value is missing here, try check your scripting to rectify this...
0
 

Assisted Solution

by:Gadik
Gadik earned 20 total points
ID: 18788240
The following value is not correct - it is not a number, so it should be in single quotation marks:

14217-2825
0
 

Author Comment

by:sigkappu
ID: 18788280
'43',,8490, Is just a blank field on creation it gets updated at a later date to contain a value
I changed 14217-2825  to 14217 and I still get the same error.

Any other of ideas of what it might be?  I'm at  a loss.
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

Expert Comment

by:Gadik
ID: 18788293
Is the first field of your table defined as character or date/time? Probably the date should be formatted...
0
 
LVL 50

Expert Comment

by:Ryan Chong
ID: 18788295
try change '43',,8490 to '43','',8490 and change 14217-2825 to '14217-2825' ?

like:

INSERT INTO FacilitySource VALUES ('3/4/2007 15:00','RIT-00107','RIT AID CORPORATION','107','','FS',3,4396,'',4396,'Preventive Maintenance','PARKINGLOTSWEEPINGMAINTPM','PARKING LOT SWEEPING PM','RIT-00107 - PARKING LOT SWEEPING PM - 1X/WK','REISINGER, JENNIFER','PS','PM-10009','INPRG','In Progress (INPRG)','43','',8490,'GENESIS','GENESIS','0','NO','','NO','43','43','1/18/2007','NO','','654 COLVIN AVENUE','KENMORE','NY','14217-2825','716-447-9128','No','No','','','','');
0
 

Author Comment

by:sigkappu
ID: 18788313
The first field is defined as date/time.  What formatting do I need to do to it?
0
 
LVL 50

Accepted Solution

by:
Ryan Chong earned 480 total points
ID: 18788338
Your first error of:

Incorrect syntax near ','.

is definitely that you got a missing value in your SQL statement.


If your first field is a date/time data type, you can try use date format of mm/dd/yyyy, or other formats like: yyyy/mm/dd or dd mmm yyyy, etc
0
 

Author Comment

by:sigkappu
ID: 18788425
For the field I want to be empty when it inserts, it is a numeric field.  What can I put into the SQL statement to put an empty field in?  If it were a text field I would put '', but I don't know what to put for a numberic field.
0
 
LVL 50

Expert Comment

by:Ryan Chong
ID: 18788464
>>What can I put into the SQL statement to put an empty field in?  If it were a text field I would put '', but I don't know what to put for a numberic field.

try put a 0 (zero) or if it allows NULL, you can try put NULL on that field.

or else, explicitly describe what fields you want to insert with values in your Insert SQL statement, like:

Insert into yourTable (field1, field2, fieldN ) values ('value1' , 12345, '3/25/2007');
0
 
LVL 50

Assisted Solution

by:Ryan Chong
Ryan Chong earned 480 total points
ID: 18788482
in your case:

INSERT INTO FacilitySource (field1, field2, fieldN... ) VALUES ('3/4/2007 15:00','RIT-00107','Other Fields value...' );

get the idea?
0
 
LVL 2

Expert Comment

by:bandolex2
ID: 18791099
provide a 0 for numeric fields that you want empty.
band.
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question