VFP调用Sql Server的插入语句（通用类）！

lihanchuan125

　　* ----------------------------------------------------------------------- \\ sql_insert
　　m_dbf1='sys_menu' && \\ 插入的表名
m_count=4 && \\ 插入字段个数
　　x1='class' && \\ 远程表字段
y1=menu2.class && \\ 本地表变量和内容
　　x2='bj'
y2=menu2.bj
　　x3='sort'
y3=m_sort
x4='name'
y4=aaa
do cx\sql_insert
thisform.retu



* ----------------------------------------------------------------------- 调用
    TRY
a=zz_key
CATCH
zz_key=''
ENDTRY
*    MESSAGEBOX(zz_key)
IF LEN(ALLTRIM(zz_key))=0 OR UPPER(ALLTRIM(zz_key))=UPPER('id')&& \\ 判断主键变量是不要+1赋值
* =================================================================================== \\ 没有主键的正常插入
* --------------------------------------------- \\ 总结原数据表字段      
m_insert1=''
f=0
DO whil f<m_count
f=f+1
ff=ALLTRIM(STR(f))
m_insert1=m_insert1+x&ff+","
* MESSAGEBOX(ALLTRIM(STR(f))+CHR(13)+ALLTRIM(STR(m_count)))
ENDDO
m_insert1=SUBSTR(m_insert1,1,LEN(m_insert1)-1)
* --------------------------------------------- \\ 替换字段的内容
m_insert2=''
m_insert9=''
f=0
DO whil f<m_count
f=f+1
ff=ALLTRIM(STR(f))
m_insert2=m_insert2+"?y&ff"+","
DO CASE
CASE TYPE('y&ff')='c' OR TYPE('y&ff')='C'
m_insert9=m_insert9+"'"+ALLTRIM(y&ff)+"'"+","
LOOP
CASE TYPE('y&ff')='n' OR TYPE('y&ff')='N'
m_insert9=m_insert9+ALLTRIM(STR(y&ff))+","
LOOP
CASE TYPE('y&ff')='t' OR TYPE('y&ff')='T'
m_insert9=m_insert9+TTOC(y&ff)+","
LOOP
CASE TYPE('y&ff')='d' OR TYPE('y&ff')='D'
m_insert9=m_insert9+DTOC(y&ff)+","
LOOP
OTHERWISE
MESSAGEBOX(y&ff)
ENDCASE
ENDDO
m_insert2=SUBSTR(m_insert2,1,LEN(m_insert2)-1)
m_insert9=SUBSTR(m_insert9,1,LEN(m_insert9)-1)
* --------------------------------------------- \\ 连接sql操作命令
m_sql="insert into "+m_dbf1+" ("+m_insert1+") values ("+m_insert2+")"
ELSE
* =================================================================================== \\ 针对【主键】的正常插入 MAX
* --------------------------------------------- \\ 总结原数据表字段      
m_insert1=zz_key+','
f=0
DO whil f<m_count
f=f+1
ff=ALLTRIM(STR(f))
m_insert1=m_insert1+x&ff+","
* MESSAGEBOX(ALLTRIM(STR(f))+CHR(13)+ALLTRIM(STR(m_count)))
ENDDO
m_insert1=SUBSTR(m_insert1,1,LEN(m_insert1)-1)
* --------------------------------------------- \\ 替换字段的内容
*!*SELECT
*!*   (case when max([ID]) is null then 0 else max([ID]+1) end ),
*!*   'ItemOne'
*!*FROM dbo.TBL_Item

*          m_insert2='select (case when MAX('+zz_key+')+1,'
m_insert2='select (case when MAX('+zz_key+') is null then 1 else MAX('+zz_key+')+1 end) ,'
m_insert9=''
f=0
DO whil f<m_count
f=f+1
ff=ALLTRIM(STR(f))
m_insert2=m_insert2+"?y&ff"+","
DO CASE
CASE TYPE('y&ff')='c' OR TYPE('y&ff')='C'
m_insert9=m_insert9+"'"+ALLTRIM(y&ff)+"'"+","
LOOP
CASE TYPE('y&ff')='n' OR TYPE('y&ff')='N'
m_insert9=m_insert9+ALLTRIM(STR(y&ff))+","
LOOP
CASE TYPE('y&ff')='t' OR TYPE('y&ff')='T'
m_insert9=m_insert9+TTOC(y&ff)+","
LOOP
CASE TYPE('y&ff')='d' OR TYPE('y&ff')='D'
m_insert9=m_insert9+DTOC(y&ff)+","
LOOP
OTHERWISE
MESSAGEBOX(y&ff)
ENDCASE
ENDDO
m_insert2=SUBSTR(m_insert2,1,LEN(m_insert2)-1)
m_insert9=SUBSTR(m_insert9,1,LEN(m_insert9)-1)
* --------------------------------------------- \\ 连接sql操作命令
*-- insert into bfbf (class_id,number) select max(class_id)+1,1 from bfbf
m_sql="insert into "+m_dbf1+" ("+m_insert1+") "+m_insert2+" from "+ m_dbf1
ENDIF

*   MESSAGEBOX(m_sql)
*      m_sql1='"'+SUBSTR(ALLTRIM(m_sql),1,150)+'"+"'
*      m_sql2=ALLTRIM(SUBSTR(ALLTRIM(m_sql),151,500))+'"'
*      m_sql=m_sql1+m_sql2
* =============================================================================================== \\ 执行数据库操作命令
STRTOFILE(m_sql ,"c:\sql111.TXT")
m_retu=0
IF SQLEXEC(gnMyLogHandle,m_sql) = -1
m_retu=1
AERROR( laError )
MESSAGEBOX( '读取数据库错误提示：' + ALLTRIM(STR(laError[ 1 ]))  + CHR( 13 ) +laError[ 2 ])
ENDIF
* ------------------------------------------------------------------------------------------- \\ 检查是否出错
IF m_retu=1
MESSAGEBOX(m_sql)
MESSAGEBOX("insert into "+m_dbf1+" ("+m_insert1+") values ("+m_insert9+")" )
STRTOFILE(m_sql+CHR(13)+"insert into "+m_dbf1+" ("+m_insert1+") values ("+m_insert9+")" ,"c:\Error.TXT")
MESSAGEBOX('请注意：系统在【插入】数据【'+m_name+'】时，发生错误，请检查网络是否畅通或与系统管理员联系！'+m_message,48,m_message1)
RETURN
ENDIF
TRY
IF m_info=1
MESSAGEBOX('数据插入成功！请返回！'+m_message,48,m_message1)
m_info=0
ENDIF
CATCH
ENDTRY
zz_key=''
WAIT clear