>Since SPT is just passing a string through ODBC to the data source, any way you want to create the string will work. Just remember that the string must be able to be parsed by the backend in its native language.
>
>FieldStr = "FacilityID,DeptID,PatNumber,LastName,FirstName"
>ValueStr = gnFacID +","+ gnDeptID +","+ thisform.txtPatNumber.value +","+ thisform.txtLastName.value +","+ thisform.txtFirst.value
>
>sqlexec(gnConnHandle,"Insert into CQIPATIENTS ("+m.FieldStr +") VALUES ("+ m.ValueStr +")"
Also, don't forget that string have to be enclosed in quotes, so this would be better:
ValueStr = gnFacID + "," + ;
gnDeptID + ",'" + ;
thisform.txtPatNumber.value + "','" + ;
thisform.txtLastName.value + "','" + ;
thisform.txtFirst.value "'"
Another pitfall is that if you get a string that contains a quote (such as "O'Brien" for the last name), it will terminate the string prematurely. To solve that problem, you must send "
O''Brien
" (notice the doubling of the single quote). You can do this by using the
STRTRAN()
function. So, the final line would look like this:
ValueStr = gnFacID + [,] + ;
gnDeptID + [,'] + ;
STRTRAN(thisform.txtPatNumber.value, ['], ['']) + [','] + ;
STRTRAN(thisform.txtLastName.value, ['], ['']) + [','] + ;
STRTRAN(thisform.txtFirst.value, ['], ['']) + [']
I used the [] delimiters instead of the double quote because I find it more readable. I would also add ALLTRIMs just to be safe.
Sylvain Demers