access中表数据如下图所示:
该表中[试样编号]是以字符即文本格式填写在表中。
C#中使用DataGridView显示上述access表中指定数据,但数据需要按照[试样编号]的数字大小排列。
如果代码如下ORDER BY [试样编号],则DataGridView显示中显示的数据是以试样编号的字符顺序排列!
SQLCmd = "SELECT 试样编号,m值,试验日期,备注 FROM 试验数据_MCGS WHERE [任务单编号]="
+ "'" + taskinf.PjtSheetNo + "'" + " AND" + "([试验名称]=" + "'" + taskinf.TstName + "'"
+ " OR" + "[试验名称]=" + "'" + taskinf.TstName + "补做" + "'" + ")" + " ORDER BY [试样编号] ";
tstDataDataSet = tstDataAccess.ReturnDataSet(SQLCmd);
将SQL语句改为ORDER BY val(试样编号)或者ORDER BY CInt(试样编号),则可达到预期目的!
SQLCmd = "SELECT 试样编号,m值,试验日期,备注 FROM 试验数据_MCGS WHERE [任务单编号]=" + "'" + taskinf.PjtSheetNo + "'" + " AND" + "([试验名称]=" + "'" + taskinf.TstName + "'" + " OR" + "[试验名称]=" + "'" + taskinf.TstName + "补做" + "'" + ")" + " ORDER BY val(试样编号) "; tstDataDataSet = tstDataAccess.ReturnDataSet(SQLCmd);
当试样编号为NULL时, ORDER BY val(试样编号) 和 ORDER BY cint(试样编号) 均会出错 ORDER BY val(试样编号) 错误提示为 标准表达式中数据类型不匹配 ORDER BY cint(试样编号) 错误提示为 无效的NULL使用 为避免出现这种情况,SQL语句改为 SELECT * FROM 试验数据_MCGS ORDER BY CInt(IIf(IsNull(试样编号),'0',试样编号))