INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Inserting values into a table

Inserting values into a table

(OP)
Hi
Please can get a pointer on the following error message
when executing this SQL Query on Pervasive SQL 2000I

INSERT INTO "tblPCW"
(PCW,PCW DESCRIPTION,PCW Cat)
VALUES (0,BANK,' ',10,FX TRADING,20,LOCAL MM,30,CASH,)
________________________________________________________

The error message received is as follows :
//ODBC Error: SQLSTATE = 37000, Native error code = 0
Syntax Error: INSERT INTO tblPCW
(PCW,PCW DESCRIPTION<< ??? >>,PCW Cat)
VALUES (0,BANK,' ',10,FX TRADING,20,LOCAL MM,30,CASH,)
Some error(s) encountered while executing SQL statement(script).//

I am not sure if this is CONVERSION error ,Doubt it since i am not trying to convert anything

The Data Types for these Columns are
numeric , longvarchar & longvarchar
Default Size for the 2 longvarchar data types
The numeric data type column has a Prec(Precision) of 15 &Scale of 8
Am i perhaps missing something ??
Thanks in Advance !








RE: Inserting values into a table

I see several things that might cause problems.
First, if the column name is "PCW DESCRIPTION", it needs to be in double quotes because of the space.  
Second, you've got more values than columns in your INSERT.
Third, you've got a string value not enclosed in single quotes.
Fourth, you've got a comma but no value following it.  
Here's a statement that should work:

CODE

INSERT INTO "tblPCW"
(PCW,"PCW DESCRIPTION","PCW Cat")
VALUES (0,'description','cat')

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
http://www.mirtheil.com

RE: Inserting values into a table

(OP)
Thanks Noted

Question though the reason that there are more values than Columns is because i am trying to run a multiple insert on the table

So syntax should read follows :

INSERT INTO "tblPCW"
(PCW,"PCW DESCRIPTION","PCW Cat")
VALUES ('0','BANK',' ',
'10','FX TRADING',''
,'20','LOCAL MM', ' '
,'30','CASH', ' ')
__________________________________________________________
The [,' ',]  if you are wondering what is happening here is i am trying to insert a Null Value because the column should be empty and able to accept Null Values  

RE: Inserting values into a table

I'm not sure PSQL supports multiple values on insert.  Especially PSQL 2000i.  I can't find any documentation that says that it does and a quick test returns an error anout the insert list not matching.  
Also, ['] (an empty string) and null are two different things.  Some older versions of PSQL didn't fully support null and used an empty string as null so be careful.   

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
http://www.mirtheil.com

RE: Inserting values into a table

(OP)
Thanks !!
Noted

I will create separate *.SQL files then,i used your corrected syntax for my query and it worked , also i appreciate the mistakes you pointed out in my code , i definitely will watch the Spaces in the Column Names

Kind regards

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close