SQL INSERT problem with FoxPro Boolean

[Criminologist]

I am attempting to write an SQL statement that would allow me to create a new record in a FoxPro table and add my fields to it from the web. All has gone well until I attempted to feed my table a boolean and all hell broke loose.

I want to have a user answer True/False to a question, have that answer put into a SQL statement and inserted into FoxPro, but every method I have tried to find the correct syntax has met me with failure.

Could anyone please tell me the correct syntax for placing a true/false into an SQL statement in VBScript/ASP?

Any help would be greatly appreciated!

 

[ramani]

HI
Have you tried "F" or "T" ?
ramani
(Subramanian.G),FoxAcc, ramani_g@yahoo.com

 

[Guest_imported]

I have tried to use the standard FoxPro .T. and .F. to no avail. I have also used Numeric 1 and 0, -1 and 0, and string 1 and 0, -1 and 0.

Is it as simple as using just T or F?

 

[Criminologist]

Okay, after some testing, I have determined that whatever the result is going to be, it will require NOT having single quotes. When I tried sending through the SQL statement WITH quotes, I got a data type mismatch, but when I sent it through WITHOUT quotes, the statement sent, but my DB table blew up.

 

[rgbean]

Are you using ODBC or OLEBD to access this FoxPro table? Can you post an example of the Insert code?

Rick

 

[Criminologist]

Sure I can. I am writing my SQL statement in VBScript in my asp page and linking to my FxPro table through ODBC. Here is a reduced chunk of my SQL statement as it is long:

SQLInsert = "INSERT INTO rccnhd10 (CONTNO, ENDFIRM, INVCOMM) VALUES ('" & NextRecord & "', " & Request("ENDFIRM") & ", '')"

ENDFIRM is an example of where I want to place a boolean value.

 

[Criminologist]

Does anyone have anything to offer to help me?

 

[danceman]

try FALSE or TRUE do not put them in quotes. Attitude is Everything

 

[wgcs]

Maybe you tried this already, but what works in native foxpro is:

INSERT INTO tablename ( fieldname1, fieldname2 ) VALUES ( .T., .F. )

Notice, no quotes around the .T. or .F.

Going through ODBC may change things so that the constant for true/false are
INSERT INTO tablename ( fieldname1, fieldname2 ) VALUES ( T, F )

or

INSERT INTO tablename ( fieldname1, fieldname2 ) VALUES ( True, False )

But I would expect quotes NEVER to work, as they are the way to specify a character string:
INSERT INTO tablename ( f1,f2,f3,f4,f5,f6,f7,f8) VALUES ( ".T.", '.T.', "T", 'T', "True", 'True', "False", 'False')

 

[Leland123]

Hi,

You might try making your VFP SQL something like:

INSERT INTO the_table (first) value ( iif("true",.T.,.F.) ) Leland F. Jackson, CPA
Software - Master (TM)

http://www.smvfp.com

Nothing Runs Like the Fox