Access 2003 Insert Into Problem

[gjcarrillo]

I am programming in Windows XP with powercobol 7 to add dates in a data base Access 2003 and when I execute the WriteRecord I obtain a syntax error in sentence INSERT INTO. Somebody podria to help me with this problem?

 

[fredericofonseca]

what method are you using to access Access?

ESQL
ADO
or other?

Please give more details, including all type of controls used for this, whether they are the ones supplied by Fujitsu, or third party.

Code snippets may also help.


If using ESQL can you please post the FULL SQL used.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[gjcarrillo]

Receive my excuses fo the lack of information. My database is composed of three fields: Installer:Text, Code:Numeric and DateInate

The test Program is:
ENVIRONMENT DIVISION.
DATA DIVISION
WORKING-STORAGE SECTION.
01 returnvalue pic s9(9) comp-5.
PROCEDURE DIVISION.
INVOKE CmDb1 "OpenDB" RETURNING ReturnValue.
move "John Smith" to "Isntaller" of CmDb1.
move 156 to "Code" of CmDb1.
move "2005/10/10 12:00:00" to "DateIn" of cmDb1.
INVOKE CmDb1 "WriteRecord" RETURNING ReturnValue.
If ReturnValue = 0
INVOKE POW-SELF "DisplayMessage" USING "Ok"
else
INVOKE POW-SELF "DisplayMessage" USING "Bad"
end-if.
INVOKE CmDb1 "CloseDB" RETURNING ReturnValue.
* Database COBOL PIC: Installer = X(50)
Code = S9(4) comp-5
DateIn = yyyy/mm/dd hh:mm:ss
The error occurred at Execution of WRITERECORD is:
SQL Error State:37000, Native Error Code:FFFFF252, ODBC Error:[Microsoft][ODBC Microsoft Access] Sintax Error on instrution INSERT INTO

Thank You

 

[3gm]

You might check the spelling of "Isntaller".

Regards.

Glenn

 

[fredericofonseca]

that may be the reason why.

I have just tried a small program like the above and It works fine.


But just a a piece of warning, stay away from DBAccess, as it is very limited.

Better to use ADO directly. More work initially to setup all required "methods" of dealing with it, but worthwhile in terms of capabilities.

See faq209-5475 for some examples of using ADO with Fujitsu.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[gjcarrillo]

Thank you very much 3GM,
The error has been fixed, but the problem persists.

TO fredericofonseca,
The problem, is generated by inserting a value in the field (DateIn)and executes the WRITERECORD method, on the other hand if I omit the field (DateIn) in the selected fields in the database, the WRITERECORD method functions successfully.
I think that the problem is in the format of the (DateIn) field.
I am also thinking of using the ADO method, but I would like to resolve the previous problem before-hand, and I will also appreciate any help you can offer me in this matter.

kind regards,
Gustavo Carrillo.

 

[PHV]

Try either this:
move "2005-10-10 12:00:00" to "DateIn" of cmDb1.
or this:
move "#2005-10-10 12:00:00#" to "DateIn" of cmDb1.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[fredericofonseca]

PHV the particular code that Gustave gave works perfectly with my version of Netcobol (V7) which is also the same he uses.



Gustavo,


If you are using a form field to populate the DB field (instead of the hardcoding sample we have, then you need to be aware of the following.

In order for this to work the value that is sent to "field1" of cmdb1
MUST be on a datetime format, e.g. yyyy/mm/dd or yyyy/mm/dd hh:mi.

So if using a cobol picture or a date render format you will need to
convert from the value returned by the "Text" property into the format
above.

e.g. assuming a "date render format" of MM/dd/yyyy you would need to
do the following.

ENVIRONMENT DIVISION.
DATA DIVISION.
WORKING-STORAGE SECTION.
01 ReturnValue pic s9(9) comp-5.
01 returnzz pic -(9)9.
01 val1 pic 9999/99/99.
PROCEDURE DIVISION.
move "Text" OF cm4 to val1.
move val1 to "field1" of cmdb1
INVOKE CmDb1 "WriteRecord" returning returnvalue.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[fredericofonseca]

Just to test, I decided to use
move "2005-10-10 12:00:00" to "DateIn" of cmDb1.
or this:
move "#2005-10-10 12:00:00#" to "DateIn" of cmDb1.

It doesn't fail, but it doesn't write a valid date either. It does write a record with a empty date.


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[PHV]

Sorry, my bad :~/

 

[gjcarrillo]

Cordial regards, Frederico Fonseca

I have used all your suggestions and I obtained the same error. I would like to know, if it is possible for me to send to you the database design and the project,through your website. If you have any other ideas on any changes in windows xp settings or access 2003 settings please let me know.

I remain,

Gustavo Carrillo.

 

[fredericofonseca]

Please email me at frederico_fonseca at the obvious domain below with a sample project of yours (copy all the files on the project folder please), along with a .mdb with the table in question. All zipped please.

I will either place a demo project on my site later on. around 18:00 GMT (Dublin time) or I will send one by email if you decide to enter that route.


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[gjcarrillo]

Regards Frederico,

I have found a solution for the problem and I have fixed it atlast.
The problem was actually in the database, in the options and in the tab tables/queries, it should be marked with the option "SQL server compatiable sintax (ANSI 92)".
I hope that this thread is usefull for the new users of powercobol 7.
I would like to thank you for all the help that you have been able to provide to me.

Kind regards and goodluck,

Gustavo Carrillo.

 

[fredericofonseca]

That should not be the reason.

I do not have that option set on my .mdb, and it works fine

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com