SQL and VFP 6.0

[jjgraf]

I have a bit of a problem when updating or inserting new records into a SQL server 7.0 sp3 running on W2K sp2.

I am used Foxweb to create an intranet web applications that is used by the Quality Control Inspector to record all Work orders he inspects.

This Web App inserts new records into a Sql server database which works great no problems. Until when the QC inspector uses the comment field and puts Quotes i.e. theses guys ( ' or " ) into that field. When Sql gets this insert into command with screwed up Quoted identifiers something looking like this

Insert into Qclog Values ( 'indexkey', 'workorder', 'inspector', 'dateinspec', 'comment field with a message with a quote in it like ' or " ', dateshipped )

It did not insert the new record

I understand why this does not work. The Sql server is not able to understand where a Field begins and ends.

What is the work around? Right know I wrote a function to stripe out the quotes in a text string.

Justin

 

[DEDMOD]

Does it give you the same problem if you use something like

MyComment = "say 'hi' to Fred"
INSERT INTO Qclog VALUES ('indexkey', ..., MyComment, ...)?

Or what about using chr(34) in place of ' " '? I.e. do a search and replace using '+chr(34)+' for ".

Dave Dardinger

 

[jjgraf]

ok those to methods work and i do that in my stripping function

but how do i handle an issue where they us both the Single and Double quotes in the comment field.

lets say they use it to designate measurement
" for inches
' for feet

This is a manufacture business and this happens all the time.

below is the error message from query analyzer that actual took place.

insert into qclog (inspecnum, comment) values ('0001', 'this is 5" to short send it back.')

Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string 'this is 5" to short send it back.')
'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'this is 5" to short send it back.')

 

[DEDMOD]

OK, it took me a while to install my evaluation copy of SQL 7 and so forth, but I was able to verify what I suspected. While it's true that you get that error in QA with the code you were running:

 insert into  qclog (inspecnum, comment) values ('0001', 'this is 5' +'"' +' too short send it back.')

will work fine both in QA and in the VFP command window. Rather than having to use '"" you might want to define a variable vInches = chr(34).

 

[jjgraf]

cool thanks for the help

Now on the declare varible do i want to it on SQL server or in the VFP code.

Right know i don't let SQL do much of anything other than store and sort the data for me.

I realy don't care for Transcat SQL, this scriptig language seems like a hodgepodge VB/FoxPro/C++ code.

i'm not that good on SQL server

 

[DEDMOD]

Well, I'd surely do it in VFP code, presuming you're going to use it regularly. That is assuming it's not such a large network that you need to push as many thing off onto the server as possible. The nice thing about SQL in general, however is that about any database will accept the commands; at least as long as you stick to the ANSI standard version. Transact SQL, however isn't that much different than the other brands as far as things you're likely to want to do at first goes.

Anyway, the advantage of using ODBC is that you can just treat the remote view like any other VFP table. -- Dave

 

[jonscott8]

For future reference, see

HOWTO: Passing Single Quotes in a String to SQL Server using SQL Passthrough

http://support.microsoft.com/support/kb/articles/Q236/1/26.ASP

Jon Hawkins