最近参照PetShop的架构做了一个小东西,可以支持Access和SqlServer,但是Access在处理货币数据类型时出了点问题,在Items表中有个Money字段,数据类型为货币,格式也是货币,小数位自动,在程序中我都是用Decimal类型来处理这个字段,但是在执行Insert语句时提示Insert into语句出错,然后我把这个Money 字段从Insert语句中去掉就没问题,我的Insert 语句是"insert into Items (ItemName,ItemDate,Money) values ('"+acc.ItemName+"','"+acc.ItemDate+"',"+acc.Money+")",刚开始是用了参数"insert into Items (ItemName,ItemDate,Money) values (@ItemName,@ItemDate,@Money)"都一样出错,然后我就把Money的数据类型换成“数字”,格式为“单精度”,在程序里面对应用Single和float也不行,再换成“双精度”,程序里对应为Double还是一样,换成整型就不会出错,但是我这个字段是要输入小数的,我都快愁死了,SQLServer处理起来就一点问题都没有,哪路高手为我解决这个问题,高分酬谢!
解决方案 »
- c#.net正则替换问题
- 怎么没有更新数据?
- updata 无法更新数据库 请求帮忙!
- gridview如何实现后台插入模板列,加入dropdownlist控件?
- 创建asp.net项时的问题
- 我已在数据库中设计好了用户权限值,我该如何将它与webform关联呢?给一个思路吧?或者一段代码?感激不尽
- 关于postedfile控件问题
- 请问能把dataset转90度与datagrid绑定吗?
- HELP:如何在datagrid绑定时,将状态列(两种状态)用单选框表现出来,以及如何从datagrid中获得该结果回填到数据库?
- 小问题,大虾来瞧瞧(50)
- 关于水晶报表控件生成带表格线报表的问题?
- 上次为公司面试了ASP.NET的程序员,出了几个题目,现在奉献给大家,大家都来做做,提高一下自己的基本功吧
float 类型能够以约 7 位的准确度表示大至 1038(正或负)的数字和小至 10-44 的数字。float 类型还可以表示 NaN(非数字)、正负无穷和正负零。
该类型对于需要大数但不需要很高的准确度的应用程序很有用。如果需要非常准确的数字,请考虑使用 Decimal 数据类型。
相应的 .NET Framework 数据类型为 System.Single。float 数据类型的属性和方法与 System.Single 属性和方法相同。Decimal 变量以 128 位(16 个字节)带符号的整数形式存储,并以 10 为幂值变比。比例因子指定小数点右面的数字位数,其范围为 0 到 28。小数位数为 0(没有小数位)时,最大的可能值为 +/-79,228,162,514,264,337,593,543,950,335。而在有 28 个小数位的情况下,最大值为 +/-7.9228162514264337593543950335,而最小的非零值为 +/-0.0000000000000000000000000001 (+/-1E-28)。
————————————————————————————
ACCESS 不支持这样的参数形式吧, 是用 ? 的吧。
Access支持(@ItemName,@ItemDate,@Money)
-------------------------------------------------------------------------------
to:bestxjp(超蓝)
是不是可以这样!类型选货币,然后格式选常规数字!然后在程序中用Decimal试试!
-------------------------------------------------------------------------------
这种情况也试过了
我把代码贴出来
using System;
using System.Data;
using System.Data.OleDb;using KW.Model;
using KW.IDAL;namespace KW.AccessDAL
{
/// <summary>
/// Account 的摘要说明。
/// </summary>
public class Account : Base,IAccount
{
private const string PARM_ITEMID = "@ItemID";
private const string PARM_TYPEID = "@TypeID";
private const string PARM_ITEMNAME = "@ItemName";
private const string PARM_ITEMDATE = "@ItemDate";
private const string PARM_INOUT = "@InOut";
private const string PARM_MONEY = "@Money";
private const string PARM_WHO = "@Who";
private const string PARM_REMARK = "@Re"; private const string SQL_INSERT_ACCOUNT = "INSERT INTO Items(TypeID,ItemName,ItemDate,InOut,Money,Who,Re) VALUES(@TypeID,@ItemName,@ItemDate,@InOut,@Money,@Who,@Re)";
private const string SQL_UPDATE_ACCOUNT = "UPDATE Items SET TypeID=@TypeID,ItemName=@ItemName,ItemDate=@ItemDate,InOut=@InOut,Money=@Money,Who=@Who,Re=@Re WHERE ItemID=@ItemID";
private const string SQL_DELETE_ACCOUNT = "UPDATE Items SET DelFlag=1 WHERE ItemID=@ItemID";
private const string SQL_SELECT_ACCOUNT = "SELECT TypeID,ItemName,ItemDate,InOut,Money,Who,Re FROM Items WHERE ItemID=@ItemID";
private const string SQL_SELECT_ACCOUNT_ITEMID = "SELECT ItemID FROM Items WHERE DelFlag=0";
private const string SQL_SELECT_ACCOUNT_COUNT = "SELECT COUNT(*) FROM Items WHERE DelFlag=0"; public Account()
{
} /// <summary>
/// 新增账目信息
/// </summary>
/// <param name="acc"></param>
public void Insert(AccountInfo acc)
{
strSQL = SQL_INSERT_ACCOUNT; OleDbParameter[] parms = GetAccountParameters();
SetAccountParameters(parms,acc); try
{
this.ExecuteSql(strSQL,parms);
}
catch(System.Data.OleDb.OleDbException e)
{
throw new Exception(e.Message);
}
} /// <summary>
/// 修改账目信息
/// </summary>
/// <param name="acc"></param>
public void Update(AccountInfo acc)
{
strSQL = SQL_UPDATE_ACCOUNT; OleDbParameter[] parms = GetAccountParameters();
SetAccountParameters(parms,acc); try
{
this.ExecuteSql(strSQL,parms);
}
catch(System.Data.OleDb.OleDbException e)
{
throw new Exception(e.Message);
}
} /// <summary>
/// 删除一条账目信息
/// </summary>
/// <param name="ItemID"></param>
public void Delete(int ItemID)
{
strSQL = SQL_DELETE_ACCOUNT; OleDbParameter[] parms;
parms = new OleDbParameter[]{
new OleDbParameter(PARM_ITEMID,OleDbType.Integer)
};
parms[0].Value = ItemID; try
{
this.ExecuteSql(strSQL,parms);
}
catch(System.Data.OleDb.OleDbException e)
{
throw new Exception(e.Message);
}
} /// <summary>
/// 获取一条账目信息
/// </summary>
/// <param name="ItemID"></param>
/// <returns></returns>
public AccountInfo GetAccount(int ItemID)
{
strSQL = SQL_SELECT_ACCOUNT;
AccountInfo acc = null; OleDbParameter[] parms;
parms = new OleDbParameter[]{
new OleDbParameter(PARM_ITEMID,OleDbType.Integer)
};
parms[0].Value = ItemID; OleDbDataReader odr = this.ExecuteSql4Dr(strSQL,parms); while(odr.Read())
{
acc = new AccountInfo(ItemID,odr.GetInt32(0),odr.GetString(1),odr.GetDateTime(2),odr.GetString(3),odr.GetDecimal(4),odr.GetString(5),odr.GetString(6));
} return acc;
} /// <summary>
/// 获取帐目信息列表
/// </summary>
/// <returns></returns>
public AccountInfo[] GetAccountList()
{
int i = 0;
int count; count = this.ExecuteSql4Value(SQL_SELECT_ACCOUNT_COUNT); if(count == 0)
return null; AccountInfo[] acc = new AccountInfo[count]; OleDbDataReader odr = this.ExecuteSql4Dr(SQL_SELECT_ACCOUNT_ITEMID); while(odr.Read())
{
acc[i] = this.GetAccount(odr.GetInt32(0));
i++;
}
return acc;
} /// <summary>
/// 获取参数
/// </summary>
/// <returns></returns>
private static OleDbParameter[] GetAccountParameters()
{
OleDbParameter[] parms; parms = new OleDbParameter[] {
new OleDbParameter(PARM_ITEMID,OleDbType.Integer),
new OleDbParameter(PARM_TYPEID,OleDbType.Integer),
new OleDbParameter(PARM_ITEMNAME,OleDbType.VarChar,100),
new OleDbParameter(PARM_ITEMDATE,OleDbType.Date),
new OleDbParameter(PARM_INOUT,OleDbType.VarChar,10),
new OleDbParameter(PARM_MONEY,OleDbType.Decimal),
new OleDbParameter(PARM_WHO,OleDbType.VarChar,10),
new OleDbParameter(PARM_REMARK,OleDbType.VarChar,100)
}; return parms;
} /// <summary>
/// 给参数赋值
/// </summary>
/// <param name="parms"></param>
/// <param name="acc"></param>
private void SetAccountParameters(OleDbParameter[] parms,AccountInfo acc)
{
parms[0].Value = acc.ItemID;
parms[1].Value = acc.TypeID;
parms[2].Value = acc.ItemName;
parms[3].Value = acc.ItemDate;
parms[4].Value = acc.InOut;
parms[5].Value = acc.Money;
parms[6].Value = acc.Who;
parms[7].Value = acc.Re;
}
}
}
{
/// <summary>
/// AccountInfo 的摘要说明。
/// </summary>
[Serializable]
public class AccountInfo
{
private int _ItemID; //编号(自动编号)
private int _TypeID; //类型编号
private string _ItemName; //收支项目名称
private DateTime _ItemDate; //日期
private string _InOut; //收入/支出
private decimal _Money; //金额
private string _Who; //
private string _Re; //备注 public AccountInfo()
{
} public AccountInfo(int ItemID,int TypeID,string ItemName,DateTime ItemDate,string InOut,decimal Money,string Who,string Re)
{
this._ItemID = ItemID;
this._TypeID = TypeID;
this._ItemName = ItemName;
this._ItemDate = ItemDate;
this._InOut = InOut;
this._Money = Money;
this._Who = Who;
this._Re = Re;
} public int ItemID
{
get { return this._ItemID;}
set { this._ItemID = value;}
} public int TypeID
{
get { return this._TypeID;}
set { this._TypeID = value;}
} public string ItemName
{
get { return this._ItemName;}
set { this._ItemName = value;}
} public DateTime ItemDate
{
get { return this._ItemDate;}
set { this._ItemDate = value;}
} public string InOut
{
get { return this._InOut;}
set { this._InOut = value;}
} public decimal Money
{
get { return this._Money;}
set { this._Money = value;}
} public string Who
{
get { return this._Who;}
set { this._Who = value;}
} public string Re
{
get { return this._Re;}
set { this._Re = value;}
} }
}
应该不是位数不一致,我试过了"insert into Items (ItemName,ItemDate,Money) values ('"+acc.ItemName+"','"+acc.ItemDate+"',32.32)",/32.2300/32直接插入数字都没用,不知搞什么鬼,只要不写上这个Money 字段,其他两个字段都可以插进去
to:gxh973121(gxh)
我最开始就是用了System.Data.OleDb.OleDbType.Currency,但是不行
谢谢各位的热心解答!
"insert into Items (TypeID,ItemName,ItemDate,InOut,Money,Who,Re) values (8,'请客','2004-9-10 0:00:00','支出',50.42,'ww','')"
这个字符串会有问题吗,但是把Money 去掉就不会出错,真是出了鬼了
insert into Items (TypeID,ItemName,ItemDate,InOut,[Money],Who,Re) values ........