MFC+Access 数据库存储数值异常

[hawk_shadow]

我使用DTU给上位机发数据，原始数据格式为unsigned char tezhengzhiyuanshi[61]={0};，转换的存储数据格式为float tezhengzhizhuanhuan[61]={0}; 转换公式为tezhengzhizhuanhuan[24]=tezhengzhiyuanshi[48]>>4)&0x0f)*10+(tezhengzhiyuanshi[48]&0x0f)+((tezhengzhiyuanshi[49]>>4)&0x0f)*0.1+(tezhengzhiyuanshi[49]&0x0f)*0.01;
SQL的插入语句为：
StrSQL.Format("insert into 波浪(采样时间,最大波高,最大波周期,有效波高,有效波周期,十分之一波高,十分之一波周期,平均波高,平均波周期,波数,波向,D1,D2,D3,D4,D5,D6,D7,D8,D9,D10,D11,D12,D13,D14,D15,D16,水温) values(#%s#,'%.2f','%.2f','%.2f','%.2f','%.2f','%.2f','%.2f','%.2f','%.2f','%.2f','%.2f','%.2f','%.2f','%.2f','%.2f','%.2f','%.2f','%.2f','%.2f','%.2f','%.2f','%.2f','%.2f','%.2f','%.2f','%.2f','%.2f')",SampleTime,tezhengzhizhuanhuan[2],tezhengzhizhuanhuan[3],tezhengzhizhuanhuan[6],\
tezhengzhizhuanhuan[7],tezhengzhizhuanhuan[4],tezhengzhizhuanhuan[5],tezhengzhizhuanhuan[0],tezhengzhizhuanhuan[1],tezhengzhizhuanhuan[8],zhuboxiang,tezhengzhizhuanhuan[9],tezhengzhizhuanhuan[10],tezhengzhizhuanhuan[11],tezhengzhizhuanhuan[12],tezhengzhizhuanhuan[13],tezhengzhizhuanhuan[14],tezhengzhizhuanhuan[15],tezhengzhizhuanhuan[16],tezhengzhizhuanhuan[17],tezhengzhizhuanhuan[18],tezhengzhizhuanhuan[19],tezhengzhizhuanhuan[20],tezhengzhizhuanhuan[21],\
tezhengzhizhuanhuan[22],tezhengzhizhuanhuan[23],tezhengzhizhuanhuan[24],watertemp);


现出现如图所示问题：

D16(D1到D16、水温)字段均是短文本，字段大小255，只有最后两个D16、水温出现了异常值乱码，异常值格式为X0.00，X是如图268开头的一个20多位的乱码值，请问是否是我上述转换公式造成的经度丢失而产生的异常值。原始数据我有另存DAT文件，打开个明确看到对应的tezhengzhiyuanshi[]是0

[worldy]

楼主你读这样的代码不累吗？

[赵4老师]

StrSQL.Format("insert into 波浪("
"采样时间,"                /*00*/
"最大波高,"                /*01*/
"最大波周期,"              /*02*/
"有效波高,"                /*03*/
"有效波周期,"              /*04*/
"十分之一波高,"            /*05*/
"十分之一波周期,"          /*06*/
"平均波高,"                /*07*/
"平均波周期,"              /*08*/
"波数,"                    /*09*/
"波向,"                    /*10*/
"D1,"                      /*11*/
"D2,"                      /*12*/
"D3,"                      /*13*/
"D4,"                      /*14*/
"D5,"                      /*15*/
"D6,"                      /*16*/
"D7,"                      /*17*/
"D8,"                      /*18*/
"D9,"                      /*19*/
"D10,"                     /*20*/
"D11,"                     /*21*/
"D12,"                     /*22*/
"D13,"                     /*23*/
"D14,"                     /*24*/
"D15,"                     /*25*/
"D16,"                     /*26*/
"水温"                     /*27*/
") values("
"#%s#,"                    /*00*/
"'%.2f',"                  /*01*/
"'%.2f',"                  /*02*/
"'%.2f',"                  /*03*/
"'%.2f',"                  /*04*/
"'%.2f',"                  /*05*/
"'%.2f',"                  /*06*/
"'%.2f',"                  /*07*/
"'%.2f',"                  /*08*/
"'%.2f',"                  /*09*/
"'%.2f',"                  /*10*/
"'%.2f',"                  /*11*/
"'%.2f',"                  /*12*/
"'%.2f',"                  /*13*/
"'%.2f',"                  /*14*/
"'%.2f',"                  /*15*/
"'%.2f',"                  /*16*/
"'%.2f',"                  /*17*/
"'%.2f',"                  /*18*/
"'%.2f',"                  /*19*/
"'%.2f',"                  /*20*/
"'%.2f',"                  /*21*/
"'%.2f',"                  /*22*/
"'%.2f',"                  /*23*/
"'%.2f',"                  /*24*/
"'%.2f',"                  /*25*/
"'%.2f',"                  /*26*/
"'%.2f')",                 /*27*/

SampleTime,                /*00*/
tezhengzhizhuanhuan[2],    /*01*/
tezhengzhizhuanhuan[3],    /*02*/
tezhengzhizhuanhuan[6],    /*03*/
tezhengzhizhuanhuan[7],    /*04*/
tezhengzhizhuanhuan[4],    /*05*/
tezhengzhizhuanhuan[5],    /*06*/
tezhengzhizhuanhuan[0],    /*07*/
tezhengzhizhuanhuan[1],    /*08*/
tezhengzhizhuanhuan[8],    /*09*/
zhuboxiang,                /*10*/
tezhengzhizhuanhuan[9],    /*11*/
tezhengzhizhuanhuan[10],   /*12*/
tezhengzhizhuanhuan[11],   /*13*/
tezhengzhizhuanhuan[12],   /*14*/
tezhengzhizhuanhuan[13],   /*15*/
tezhengzhizhuanhuan[14],   /*16*/
tezhengzhizhuanhuan[15],   /*17*/
tezhengzhizhuanhuan[16],   /*18*/
tezhengzhizhuanhuan[17],   /*19*/
tezhengzhizhuanhuan[18],   /*20*/
tezhengzhizhuanhuan[19],   /*21*/
tezhengzhizhuanhuan[20],   /*22*/
tezhengzhizhuanhuan[21],   /*23*/
tezhengzhizhuanhuan[22],   /*24*/
tezhengzhizhuanhuan[23],   /*25*/
tezhengzhizhuanhuan[24],   /*26*/
watertemp                  /*27*/
);

[赵4老师]

调试这种数据被莫名其妙改变的bug，请学会使用数据断点：

#include <time.h>
#include <stdlib.h>
#include <windows.h>
int main() {
    int a,b[11];//本来是b[10]，为判断哪句越界，故意声明为b[11]

    srand((unsigned int)time(NULL));//按两次F11，等黄色右箭头指向本行时，调试、新建断点、新建数据断点，地址：&b[10]，字节计数：4，确定。
    while (1) {//按F5，会停在下面某句，此时a的值为10，b[10]已经被修改为对应0..4之一。
        b[(a=rand()%11)]=0;
        Sleep(100);
        b[(a=rand()%11)]=1;
        Sleep(100);
        b[(a=rand()%11)]=2;
        Sleep(100);
        b[(a=rand()%11)]=3;
        Sleep(100);
        b[(a=rand()%11)]=4;
        Sleep(100);
    }
    return 0;
}

[wxf54318]

应该是数据溢出，应该考虑对超过FLOAT精度范围的值进行处理

[zgl7903]

把strSQL TRACE出来看看 是否正常？
D16字段是否类型不对？

[hawk_shadow]

我监视了一下，TRACE(SQL)里面，SQL的值最后两个就是'268.........0.00','00.00',也就是说就是乱码，问题出现在SQL.Format里面，监视SQL.Format，很明显看到tezhengzhizhuanhuan[24]的值是正确的0.00，但是Format给了SQL就异常乱码，乱码出现情况为最后两列随机出现，有时候都是正常的0.00，有时候某一个或两个都是乱码

[hawk_shadow]

引用 4 楼 zgl7903 的回复:

把strSQL TRACE出来看看 是否正常？ D16字段是否类型不对？

字段类型正确，我用了两个版本，一个短文本，一个单精度，标准，小数点2位，均是会出现上述情况。当我缩短列数的时候就没有这种情况出现，列的数目多少也影响format赋值么？

[hawk_shadow]

引用 2 楼 zhao4zhong1 的回复:

StrSQL.Format("insert into 波浪("
"采样时间,"                /*00*/
"最大波高,"                /*01*/
"最大波周期,"              /*02*/
"有效波高,"                /*03*/
"有效波周期,"              /*04*/
"十分之一波高,"            /*05*/
"十分之一波周期,"          /*06*/
"平均波高,"                /*07*/
"平均波周期,"              /*08*/
"波数,"                    /*09*/
"波向,"                    /*10*/
"D1,"                      /*11*/
"D2,"                      /*12*/
"D3,"                      /*13*/
"D4,"                      /*14*/
"D5,"                      /*15*/
"D6,"                      /*16*/
"D7,"                      /*17*/
"D8,"                      /*18*/
"D9,"                      /*19*/
"D10,"                     /*20*/
"D11,"                     /*21*/
"D12,"                     /*22*/
"D13,"                     /*23*/
"D14,"                     /*24*/
"D15,"                     /*25*/
"D16,"                     /*26*/
"水温"                     /*27*/
") values("
"#%s#,"                    /*00*/
"'%.2f',"                  /*01*/
"'%.2f',"                  /*02*/
"'%.2f',"                  /*03*/
"'%.2f',"                  /*04*/
"'%.2f',"                  /*05*/
"'%.2f',"                  /*06*/
"'%.2f',"                  /*07*/
"'%.2f',"                  /*08*/
"'%.2f',"                  /*09*/
"'%.2f',"                  /*10*/
"'%.2f',"                  /*11*/
"'%.2f',"                  /*12*/
"'%.2f',"                  /*13*/
"'%.2f',"                  /*14*/
"'%.2f',"                  /*15*/
"'%.2f',"                  /*16*/
"'%.2f',"                  /*17*/
"'%.2f',"                  /*18*/
"'%.2f',"                  /*19*/
"'%.2f',"                  /*20*/
"'%.2f',"                  /*21*/
"'%.2f',"                  /*22*/
"'%.2f',"                  /*23*/
"'%.2f',"                  /*24*/
"'%.2f',"                  /*25*/
"'%.2f',"                  /*26*/
"'%.2f')",                 /*27*/

SampleTime,                /*00*/
tezhengzhizhuanhuan[2],    /*01*/
tezhengzhizhuanhuan[3],    /*02*/
tezhengzhizhuanhuan[6],    /*03*/
tezhengzhizhuanhuan[7],    /*04*/
tezhengzhizhuanhuan[4],    /*05*/
tezhengzhizhuanhuan[5],    /*06*/
tezhengzhizhuanhuan[0],    /*07*/
tezhengzhizhuanhuan[1],    /*08*/
tezhengzhizhuanhuan[8],    /*09*/
zhuboxiang,                /*10*/
tezhengzhizhuanhuan[9],    /*11*/
tezhengzhizhuanhuan[10],   /*12*/
tezhengzhizhuanhuan[11],   /*13*/
tezhengzhizhuanhuan[12],   /*14*/
tezhengzhizhuanhuan[13],   /*15*/
tezhengzhizhuanhuan[14],   /*16*/
tezhengzhizhuanhuan[15],   /*17*/
tezhengzhizhuanhuan[16],   /*18*/
tezhengzhizhuanhuan[17],   /*19*/
tezhengzhizhuanhuan[18],   /*20*/
tezhengzhizhuanhuan[19],   /*21*/
tezhengzhizhuanhuan[20],   /*22*/
tezhengzhizhuanhuan[21],   /*23*/
tezhengzhizhuanhuan[22],   /*24*/
tezhengzhizhuanhuan[23],   /*25*/
tezhengzhizhuanhuan[24],   /*26*/
watertemp                  /*27*/
);

赵4老师，我用你的format格式测试了一下，仍然会出现乱码： 这个是我单独粘了一个测试对话框，直接这么初始化了 float tezhengzhizhuanhuan[61]={0}; int zhuboxiang=0; float watertemp=0.0; 然后就是SQL.Format和TRACE0(StrSQL); VERIFY ( m_AccessDataWithAdo.ExecuteSQLbyConnection( StrSQL )); 监停留在SQL.Format这里，图示可以看出仍然是出现乱码

[赵4老师]

只能说明类型不对。
换%d试试，
或者参考这个逐个Format，别一次Format太多。

    char binstr[80];
            sprintf(binstr,"%08x -",0);
            sprintf(binstr,"%s %02x",binstr,1);

[hawk_shadow]

引用 8 楼 zhao4zhong1 的回复:

只能说明类型不对。 换%d试试， 或者参考这个逐个Format，别一次Format太多。

    char binstr[80];
            sprintf(binstr,"%08x -",0);
            sprintf(binstr,"%s %02x",binstr,1);

可是我要存的数据基本都是float类型的，%d的话不准确。 我现在测试出来一个现象（基于之前版本没问题，加了两列(波向，水温)之后就有这毛病了），我也不知道对不对啊。请教一下各位： 现在出毛病的版本是一共28列，首先format基本格式都正确，就是最后两列随机出乱码，有时候也正确。 之前没问题的是26列（甚至更少），一直稳定运行，拷机一周数据一直正常。我现在自己全部初始化0，测试发现28列就是会出现毛病，我拆成两个表格，反复手动存储30+次，一直正常。如图：

[zgl7903]

是否是存在多线程读写的问题？ 考虑加锁试试看

[老夏]

随机乱码，一般是数据没有初始化，或者 Format函数里的格式(%)的个数大于提供的参数个数，多出的就会乱码

[hawk_shadow]

引用 13 楼 watersoft 的回复:

随机乱码，一般是数据没有初始化，或者 Format函数里的格式(%)的个数大于提供的参数个数，多出的就会乱码

可是这个确实是格式没问题的。。我也很困惑

[hawk_shadow]

引用 11 楼 zgl7903 的回复:

是否是存在多线程读写的问题？ 考虑加锁试试看

存储数据库这块是没有多线程的，不是这方面的原因