vfp 链接 sql 代码查询并保存为 vfp 表。
自己编写的sql代码之前的方式是直接在“sql查询分析器”中查询并保存为csv格式,然后在VFP中切csv文档。看到有一个VFP自带程序,能自动从SQL里面导出数据,看看能不能实现直接用vfp从sql导出数据。最好是可以一次性导出两张表的数据,并保存为对应的表名,谢谢。
可能实现该功能的vfp代码:
nCon=SQLSTRINGCONNECT("driver=SQL Server;Server=192.168.1.250;Uid=cwnew;pwd=sv;database=AIS20050914072538")
ndd=SQLEXEC(ncon,"SELECT * FROM T_ITEM",'自定义游标名')
select * from 自定义游标名 into table po
sql代码:(这里显示两个不同表的获取。第一个表:po,第二个表item。)
Po 采购订单
select
t1.FbillNo as 订单编号,
t1.fentryid as 订单序号,
CONVERT(varchar(100), t1.Fdate, 23),
t1.FsupplyIDName as 供应商,
t1.FFullNumber as 物料长代码,
t1.FbaseUnitId as 单位,
t1.FtaxRate as 税率,
t1.FbaseQty as 数量,
t1.famount/t1.fbaseqty as 单价,
t1.FAmount as 金额,
t1.fclose as 结案
from vwICBill_26 t1
item 物料表
select
t1.FItemID as 物料内码,
t1.fnumber 物料编码,
replace(replace(t1.Fname,',',' '),' ','') 物料名称,
replace(replace(t1.FModel,',',' '),' ','') 规格型号,
t1.FERPCLSID as 物料属性内码,
case when t1.FERPCLSID=1 then '外购' when t1.FERPCLSID=2 then '自制' when t1.FERPCLSID=3 then '委外加工' else '未知' end as 物料属性 ,
t1.FDefaultLoc as 默认仓库内码,
t2.FName as 默认仓库,
t1.F_106 as 是否为结构件内码,
case when t1.F_106=1 then '是' when t1.F_106=0 then '否' else '未知' end as 是否为结构件,
t3.fcreatedate 创建日期,
t4.fnumber 存货科目,
t4.fnumber 收入科目,
t4.fnumber 成本科目,
t1.fdeleted 禁用与否,
t10.fname 物料类型,
t11.flowestbomcode
from t_icitem t1 left join t_stock t2 on t1.FDefaultLoc=t2.FItemID
left join t_baseproperty t3 on t1.fitemid=t3.fitemid
left join t_account t4 on t1.facctid =t4.faccountid--存货科目
left join t_account t5 on t1.fsaleacctid=t5.faccountid--收入科目
left join t_account t6 on t1.fcostacctid=t6.faccountid--成本科目
left join t_submessage t10 on t1.F_123=t10.FinterID --物料类别
left join t_lowestbomcode t11 on t1.fitemid =t11.fitemid
where t3.ftypeid =3 and t1.fnumber<>' '