借这VFP+SQL这主题贴,也想说说我的想法。以下代码均是借用楼主的代码作适当修改(VFP9)。
1、在主程序连接SQLSERVER
PUBLIC nhandle
SQLSETPROP(0,"DispLogin" ,3)
nhandle=SQLSTRINGCONNECT("driver=sql server;server=71.24.26.100;UID=sa;pwd=sa;database=tz")
IF nhandle=-1
=messagebox('不能连接数据库',48,'提示')
RETURN
ENDIF
=SQLIDLEDISCONNECT(nhandle)
&&不要奇怪为什么在这断开连接,对了,只是被临时断开,以便以后查询时会自动唤醒,期间网络出现问题恢复正常后,也会自动唤醒。
2、表单采用私有数据表(同一表单可以同时多次打开而互不影响)。在grid的init中初始数据源
LOCAL
table_tmp,ret,grid_tmp,i
table_tmp=SYS(2015)
=SQLIDLEDISCONNECT(nhandle)
ret=SQLEXEC(nhandle,"select * from chufa where 1=2",table_tmp) &&建议列示所需的字段
=SQLIDLEDISCONNECT(nhandle)
IF ret=-1
=MESSAGEBOX('查询失败',64,'提示')
RETURN
ENDIF
&& 这样设置grid,目的是为了方便以后代码移植及数据列位置调整
SELECT (table_tmp)
grid_tmp=this
grid_tmp.ColumnCount=7 &&根据实际修改这列值
grid_tmp.recordsource=table_tmp
i=0
i=i+1
grid_tmp.Columns[I].ControlSource =table_tmp+'.'+FIELD(i)
grid_tmp.Columns[I].header1.caption="纳税人识别号"
grid_tmp.Columns[I].header1.forecolor=RGB(255,255,255)
grid_tmp.Columns[I].header1.fontbold=.t.
grid_tmp.Columns[I].header1.backcolor=RGB(111,119,230)
grid_tmp.Columns[I].header1.alignment=2
grid_tmp.Columns[I].width=180
grid_tmp.Columns[I].backcolor=RGB(245,250,254)
i=i+1
grid_tmp.Columns[I].ControlSource =table_tmp+'.'+FIELD(i)
grid_tmp.Columns[I].header1.caption="纳税人名称"
grid_tmp.Columns[I].header1.forecolor=RGB(255,255,255)
grid_tmp.Columns[I].header1.fontbold=.t.
grid_tmp.Columns[I].header1.backcolor=RGB(111,119,230)
grid_tmp.Columns[I].header1.alignment=2
grid_tmp.Columns[I].width=270
grid_tmp.Columns[I].backcolor=RGB(226,238,252)
i=i+1
grid_tmp.Columns[I].ControlSource =table_tmp+'.'+FIELD(i)
grid_tmp.Columns[I].header1.caption="违法事实"
grid_tmp.Columns[I].header1.forecolor=RGB(255,255,255)
grid_tmp.Columns[I].header1.fontbold=.t.
grid_tmp.Columns[I].header1.backcolor=RGB(111,119,230)
grid_tmp.Columns[I].header1.alignment=2
grid_tmp.Columns[I].width=270
grid_tmp.Columns[I].backcolor=RGB(245,250,254)
i=i+1
grid_tmp.Columns[I].ControlSource =table_tmp+'.'+FIELD(i)
grid_tmp.Columns[I].header1.caption="案件编号"
grid_tmp.Columns[I].header1.forecolor=RGB(255,255,255)
grid_tmp.Columns[I].header1.fontbold=.t.
grid_tmp.Columns[I].header1.backcolor=RGB(111,119,230)
grid_tmp.Columns[I].header1.alignment=2
grid_tmp.Columns[I].width=85
grid_tmp.Columns[I].backcolor=RGB(226,238,252)
i=i+1
grid_tmp.Columns[I].ControlSource =table_tmp+'.'+FIELD(i)
grid_tmp.Columns[I].header1.caption="处罚依据"
grid_tmp.Columns[I].header1.forecolor=RGB(255,255,255)
grid_tmp.Columns[I].header1.fontbold=.t.
grid_tmp.Columns[I].header1.backcolor=RGB(111,119,230)
grid_tmp.Columns[I].header1.alignment=2
grid_tmp.Columns[I].width=270
grid_tmp.Columns[I].backcolor=RGB(245,250,254)
i=i+1
grid_tmp.Columns[I].ControlSource =table_tmp+'.'+FIELD(i)
grid_tmp.Columns[I].header1.caption="罚款金额"
grid_tmp.Columns[I].header1.forecolor=RGB(255,255,255)
grid_tmp.Columns[I].header1.fontbold=.t.
grid_tmp.Columns[I].header1.backcolor=RGB(111,119,230)
grid_tmp.Columns[I].header1.alignment=2
grid_tmp.Columns[I].width=85
grid_tmp.Columns[I].backcolor=RGB(226,238,252)
i=i+1
grid_tmp.Columns[I].ControlSource =table_tmp+'.'+FIELD(i)
grid_tmp.Columns[I].header1.caption="执法人员"
grid_tmp.Columns[I].header1.forecolor=RGB(255,255,255)
grid_tmp.Columns[I].header1.fontbold=.t.
grid_tmp.Columns[I].header1.backcolor=RGB(111,119,230)
grid_tmp.Columns[I].header1.alignment=2
grid_tmp.Columns[I].width=85
grid_tmp.Columns[I].backcolor=RGB(245,250,254)
grid_tmp.headerheight=25
grid_tmp.ReadOnly=.t.
thisform.Refresh
*注意在 在grid的Destroy 中关闭数据源
IF NOT EMPTY(this.RecordSource) AND USED(this.RecordSource)
USE IN (this.RecordSource)
ENDIF
3、在“查询”按钮构建SQL查询语句,条件返回查询结果,填充grid数据源
cSQL=''
SELECT (thisform.grid1.RecordSource)
DO CASE
CASE ALLTRIM()=='纳税人识别号'
cSQL="nsrsbh"
CASE ALLTRIM()=="纳税人名称"
cSQL="nsrmc"
CASE ALLTRIM()=="案件编号"
cSQL="ajbh"
OTHERWISE
cSQL="zfry"
ENDCASE
cSQL="select * from chufa where "+cSQL+" like '%"+ALLTRIM(thisform.text1.value)+"%'"
LOCAL table_tmp,ret
table_tmp=SYS(2015)
=SQLIDLEDISCONNECT(nhandle)
ret=SQLEXEC(nhandle,cSQL,table_tmp)
=SQLIDLEDISCONNECT(nhandle)
IF ret=-1
=MESSAGEBOX('查询失败',64,'提示')
RETURN
ENDIF
IF RECCOUNT(table_tmp)=0
MESSAGEBOX("没有找到符合条件的记录!",0,"提示信息")
ENDIF
this.lbjl.Caption=TRANSFORM(RECCOUNT(table_tmp))
ZAP IN (thisform.grid1.RecordSource)
SELECT (thisform.grid1.RecordSource)
APPEND FROM (DBF('table_tmp'))
USE IN (table_tmp)
GO TOP IN (thisform.grid1.RecordSource)
thisform.Refresh
[此贴子已经被作者于2016-1-19 17:10编辑过]