数据类型问题，搞了一个上午没搞好，高手指点一下啊
存储过程如下：
create proc DoVote(
@CompanyID varchar(50),
@Votesort varchar(50),
@votenum_1 varchar(50),
@strIP varchar(50),
@strMAC varchar(50),
@strUserName varchar(50),
@nIP float(12),
@RtnResult int output
)
as
begin
declare @Count int
declare @Address nvarchar(50)
declare @RowCount int
select @Count= count(companyid) from VoteRecord
where CompanyID = @CompanyID and Votesort=@Votesort and Mac=@strMAC
select @RowCount= count(*) from IP where startip <= @strIP or @strIP > = endip
if(@RowCount = 1)
begin
select @Address = country from IP where startip <= @nIP and endip > = @nIP
end
else
begin
set @Address = '未知IP '
end
if(@Count=0)
begin
insert into VoteRecord(CompanyID,IPAddress,MAC,VoteSort,UserName,Address)
values(@CompanyID,@strIP,@strMAC,@Votesort,@strUserName,@Address)
update vote set votenum=@votenum_1
where CompanyID=@CompanyID and Votesort=@Votesort
set @RtnResult = 1 -- 表示投票成功
end
else
begin
set @RtnResult = 0 -- 表示投票不成功
end
end
－－－－－－－－－－－－－－－－－－－－－
测试程序如下：
＝＝＝＝＝＝＝＝＝＝
USE [ComData]
GO
DECLARE@return_value int,
@RtnResult int
EXEC@return_value = [dbo].[DoVote]
@CompanyID = N '440000001 ',
@Votesort = N '1 ',
@votenum_1 = N '7|4|1|1|10 ',
@strIP = N '192.168.2.101 ',
@strMAC = N '20:04:12:30:33:25 ',
@strUserName = N '企业窗网友 ',
@nIP = 3232236133.0,
@RtnResult = @RtnResult OUTPUT
SELECT@RtnResult as N '@RtnResult '
SELECT 'Return Value ' = @return_value
报错如下：
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
消息 8114，级别 16，状态 5，过程 DoVote，第 22 行
从数据类型 varchar 转换为 float 时出错。
下面是调用存储过程的c#后台程序：
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
//投票
public static int DoVote(string CompanyID, string Votesort, string votenum_1, string strIP, string strMAC, string strUserName, double nIP)
{
try
{
SqlParameter[] parameters = new SqlParameter[8];
parameters[0] = DataAccess.common.CreateParameter( "@CompanyID ", DbType.String, CompanyID, ParameterDirection.Input);
parameters[1] = DataAccess.common.CreateParameter( "@Votesort ", DbType.String, Votesort, ParameterDirection.Input);
parameters[2] = DataAccess.common.CreateParameter( "@votenum_1 ", DbType.String, votenum_1, ParameterDirection.Input);
parameters[3] = DataAccess.common.CreateParameter( "@strIP ", DbType.String, strIP, ParameterDirection.Input);
parameters[4] = DataAccess.common.CreateParameter( "@strMAC ", DbType.String, strMAC, ParameterDirection.Input);
parameters[5] = DataAccess.common.CreateParameter( "@strUserName ", DbType.String, strUserName, ParameterDirection.Input);
parameters[6] = DataAccess.common.CreateParameter( "@nIP ", DbType.Double, nIP, ParameterDirection.Input);
parameters[7] = new SqlParameter( "@RtnResult ", SqlDbType.Int, 4);
parameters[7].Direction = ParameterDirection.Output;
SqlHelper.ExecuteNonQuery(clsAppConfigSetter.connection, System.Data.CommandType.StoredProcedure, "DoVote ", parameters);
int Rtn = int.Parse(parameters[6].Value.ToString());
return Rtn;
}
catch (Exception ex)
{
string strErr = ex.ToString();
return 0;
}
}
高手，大家指点一下啊，老板催得紧，急得我冒汗！
[解决办法]
問題出在這裡，startip和endip好象都是varchar類型的，但是你的@nIP卻是float，將變量的類型和字段的類型設為一致。
或者這麼轉換下
select @Address = country from IP where startip <= Cast(@nIP As Varchar) and endip > = Cast(@nIP As Varchar)
[解决办法]
那這句就有問題
select @RowCount= count(*) from IP where startip <= @strIP or @strIP > = endip
@strIP是N '192.168.2.101 '這樣的數據，怎麼和float型做比較？
