Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Value error on Insert Records (Read 3845 times)
grahamf
YaBB Newbies
*


I Love SQL*XL

Posts: 13
Value error on Insert Records
20.03.07 at 12:37:59
 
I was testing my code which inserts data into the database, and all seemed well, howver, when I single stepped my function I noticed that the SQL*XL call was inserting "Value error in <fieldname>" in - as well as the "Successful" indicator under the header column (vertical oriented data entry)
 
The destination data type in the Oracle table was NUMBER and the value of the cell in the sheet was 3 (albeit derived from a calculation). Any ideas why the Value Error was flagged up? (Data was sucessfully inserted)
Thx
Graham
Back to top
 
 
  IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Value error on Insert Records
Reply #1 - 20.03.07 at 15:36:13
 
The outcome of formulas will be taken for insert and not the formula itself.
If the data could not be entered in the table and a value error was received the error you mention will be put in the column next to the data if you have the data oriented horizontally and in the row below the data if you have the data oriented vertically. The succesful indicator will be put in the cell if the rows was inserted successfully.
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Value error on Insert Records
Reply #2 - 13.06.07 at 07:29:05
 
Value error on date fields
 
Question:
I am getting the error "SQLXL: Value Error"for some date fields in an Oracle database. Not all date fields in the same table give errors and I have no problem calling up the data in other applications. Any ideas what to check?
 
Answer:
Value errors are due to the data put into the field being different from field definition. So if you have a number field and you try to put a text into it, it will display the value error.
 
You say you get the problems with some dates but not with other dates. SQL*XL will take the date values from Excel and not the displayed date texts. Ensure all the dates you enter are true dates and not texts. You can check this by observing whether the date values all align correctly. Excel will as standard align text to the left of the cell and numbers and dates to the right. Also checking the cell formatting you can see whether a cell contains a date or a text.
 
If you enter text in a cell, SQL*XL can still enter that information into the database in a date field however implicitly the conversion to a date occurs. This is probably where your problem is. Consider the following scenarios:
 
Cell value (text):         Possible problems:
10/Jan/2008                This will probably always be correctly handled
10/01/2008                 In the US this date is the 1st of October. In Europe it will be the 10th of January.
20/01/2008                 In Europe this date is a proper date: 20/Jan/2008 however in the US it is invalid.
                                 There is no 20th month.
 
I hope you get a feeling for implicit conversions. If you are not explicit as to what the value is the database will try its best to figure out what you mean. Sometimes it will get it wrong.
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1