搬家第13天-165.C脚本初步12-条件查询并将结果显示在表格控件中
有时候我们需要将数据库条件查询的结果显示在表格控件中,前面的博客记录过VBS的方法,本篇记录使用c脚本实现同样的功能。
在画面上放置一个microsoft date and time picker,名字修改为DT,放置一个microsoft Hierarchical flex control,名字修改为grid,放置一个按钮,用以查询并将结果显示在grid控件中。
后台数据库有这样一个表,结构如下:
在全局脚本有定时添加纪录的脚本,具体方法参加前面的博客。查询按钮的鼠标点击事件中添加以下c脚本:
#include "apdefap.h"
void OnClick(char* lpszPictureName, char* lpszObjectName, char* lpszPropertyName)
{
#pragma code("kernel32.dll")
#define GetObject GetObject
char selyear[4],seldate[10];
int i,j;
__object *pdl=NULL;
__object *pic=NULL;
__object *obj=NULL;
__object *cn, *rs;
char *connstr="Provider = SQLOLEDB.1;password = sa;user id = sa;Initial Catalog =Report;Data Source=ES\\wincc";
char sql[256];
sprintf(selyear,"%s",GetPropChar(lpszPictureName,"DT","Year"));
if (GetPropWord(lpszPictureName,"DT","Month")>=10)
{
if (GetPropWord(lpszPictureName,"DT","Day")>=10)
sprintf(seldate,"%s-%d-%d",selyear,GetPropWord(lpszPictureName,"DT","Month"),GetPropWord(lpszPictureName,"DT","Day"));
else
sprintf(seldate,"%s-%d- ",selyear,GetPropWord(lpszPictureName,"DT","Month"),GetPropWord(lpszPictureName,"DT","Day"));
}
else
{
if (GetPropWord(lpszPictureName,"DT","Day")>=10)
sprintf(seldate,"%s- -%d",selyear,GetPropWord(lpszPictureName,"DT","Month"),GetPropWord(lpszPictureName,"DT","Day"));
else
sprintf(seldate,"%s- - ",selyear,GetPropWord(lpszPictureName,"DT","Month"),GetPropWord(lpszPictureName,"DT","Day"));
}
pdl = __object_create("PDLRuntime");
pic=pdl->GetPicture("");
obj=pic->GetObject("grid");
cn=__object_create("ADODB.Connection");
rs=__object_create("ADODB.RecordSet");
sprintf(sql,"%s '%s' %s","select CAST(CurDate as CHAR(10)), CAST(CurTime as CHAR(8)),convert(varchar(10),flow1),convert(varchar(10),flow2),convert(varchar(10),flow3) from report1 where curDate=",seldate,"order by curNo");
cn->open(connstr);
if (cn->State == 0)// 为0表示连接数据库不成功
MessageBox(NULL,"连接数据库失败!","警告",MB_OK);
else
{
rs->Open(sql,cn,3,1);
obj->rows=1+rs->recordcount;
obj->cols=6;
//设备标题栏
obj->row=0;
obj->col=1;
SetPropChar(lpszPictureName,"grid","Text","日期");
obj->col=2;
SetPropChar(lpszPictureName,"grid","Text","时间");
obj->col=3;
SetPropChar(lpszPictureName,"grid","Text","流量1");
obj->col=4;
SetPropChar(lpszPictureName,"grid","Text","流量2");
obj->col=5;
SetPropChar(lpszPictureName,"grid","Text","流量3");
rs->movefirst;
for (i=1;i<=rs->recordcount;i++)
{
obj->row=i;
obj->col=1;
SetPropChar(lpszPictureName,"grid","Text",rs->fields(0)->value);
obj->col=2;
SetPropChar(lpszPictureName,"grid","Text",rs->fields(1)->value);
obj->col=3;
SetPropChar(lpszPictureName,"grid","Text",rs->fields(2)->value);
obj->col=4;
SetPropChar(lpszPictureName,"grid","Text",rs->fields(3)->value);
obj->col=5;
SetPropChar(lpszPictureName,"grid","Text",rs->fields(4)->value);
rs->movenext;
}
}
if(cn->State != 0)
cn->Close;
__object_delete(obj);
__object_delete(pic);
__object_delete(pdl);
}
最终查询到的结果是这样子的
最开始我希望c脚本像vbs一样可以直接对grid控件的datasource属性直接赋值,但是怎么都调不出来,后来决定逐行逐列赋值。从脚本对数据类型有严格的要求,直接select的话,语法判断时会说无法判断rs->fields(1)->value的数据类型,这里走了不少弯路,最后在select中将字段转换成字符串那种类型才通过了语法判断。
新浪博客似乎会自动屏蔽一些字符,我脚本里面的一些字符就被自动删除了,还是附截图吧