Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
SQL insert problem
#1
i my clients database is a field with name 'E-Mail', qm gives error in sql statement.

Code:
Copy      Help
sql="INSERT INTO Adressen (EMISNummer,E-Mail) VALUES (8765,'test@notvaliddomain.com')"

i can't rename it, because another software is using this database.

whatt o do?
pi
#2
found the solution:

Code:
Copy      Help
sql="INSERT INTO Adressen (EMISNummer,`E-Mail`) VALUES (8765,'test@notvaliddomain.com')"

what is the english name for ` ?
pi
#3
it is a 'grave accent';
Quote:On many computer keyboards, the grave accent occupies a key by itself, and is meant to be combined with vowels as a multi-key combination. However, programmers have used the key by itself for a number of tasks.

http://en.wikipedia.org/wiki/%60#Computer-related

on a pc hit "ctl-`" then a vowel and it prints the À à types of letters.
An old blog on QM coding and automation.

The Macro Hook
#4
thanks.
do you know how to deal with with apostrophe in values ?
maybe a escape in the sql statement ?

Code:
Copy      Help
,sql="INSERT INTO Adressen (EMISNummer,FB1,FB2,Strasse,Land,PLZ,Ort,Bundesland,Telefon,Telefax,`E-Mail`,Internet,Adressgruppe,Sonstiges2,UnterAdressgruppe,Kundenstatus,Kontakt) VALUES (XXX)"
,values.format("%i,'%s','%s','%s','%s','%s','%s','%s'," zaehler FB1 FB2 Strasse Land PLZ Ort Bundesland)
,values.formata("'%s','%s','%s','%s','%s'," Telefon Telefax EMail Website Adressgruppe)
,values.formata("'%s','%s','%s','%s'" Sonstiges2 UnterAdressgruppe Kundenstatus Kontakt)


FB1 (company name) is Ain't Missbehavin'
i can't change the name.
pi
#5
yeah, that's where i would start.
An old blog on QM coding and automation.

The Macro Hook
#6
sure
Code:
Copy      Help
FB1.findreplace("'" "´")

works, but in my clients software you can use apostrophe in company and product names.
his program is writing it in plain text to the mdb.

all the tips i found so far speak about chr$(34) or replace ' with ''
but we are not in vb land here Confusedhock:
pi
#7
now i am completely confused

when in the first database where i gather my info the name
is Ain''t Missbehavin'' it will be inserted in the second db
as Ain't Missbehavin'

but when i use
FB1.findreplace("'" "''") on orginal name it will be inserted as
Ain"t Missbehavin"

help :?: :?: :?: :?:
pi
#8
that's because you're searching for the ' and replacing it with a "

i think it'll work with
str FB1.findreplace("''" "'")
An old blog on QM coding and automation.

The Macro Hook
#9
that was just a test, Ain't Missbehavin' is the original name.
pi


Forum Jump:


Users browsing this thread: 1 Guest(s)