搬家第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中将字段转换成字符串那种类型才通过了语法判断。

 

 

 

新浪博客似乎会自动屏蔽一些字符，我脚本里面的一些字符就被自动删除了，还是附截图吧