select count(*)的时候,同样的表结构,不同的数据源,居然一个会有语法错误,另外一个却好好的

[SmartHeart]

sqlstr = "select COUNT_BIG(*) from detail where state<>0 and ttyid='" & ttyname & "'"
sqlstr = sqlstr & datestr
countsucdr = SqlHelper.ExecuteReader(constr, CommandType.Text, sqlstr)
If countsucdr.Read() Then
succcount = countsucdr.GetInt32(0)



将 varchar 值 '.' 转换为数据类型为 int 的列时发生语法错误。
说明: 执行当前 Web 请求期间，出现未处理的异常。请检查堆栈跟踪信息，以了解有关该错误以及代码中导致错误的出处的详细信息。

异常详细信息: System.Data.SqlClient.SqlException: 将 varchar 值 '.' 转换为数据类型为 int 的列时发生语法错误。

在这行 If countsucdr.Read() Then 有错误

[poplarjvjv1981]

你用后台的查询分析器直接执行下面的语句出错吗？
select COUNT_BIG(*) from detail where state<>0 and ttyid='00001'

[SmartHeart]

select COUNT_BIG(*) from detail where state<>0 and ttyid='00001'

没有问题

[SmartHeart]

搞不明白,我如果把数据源指向另外一台机器的数据库的时候,所有都是好好的.执行结果也是正确的.

[Camus_Li]

ttyid是什么类型?
ttyname可能是什么值?

[SmartHeart]

ttyid 和 ttyname 都是char(10)

[SmartHeart]

datestr在调试的时候是空

[SmartHeart]

Dim ttyname As String
Dim succcount As Int32

[miaojx]

是不是這句錯了？ succcount = countsucdr.GetInt32(0)

[poplarjvjv1981]

你能给出ttyname的值是怎么获取的吗？最好把整段程序都贴一下

[SmartHeart]

我刚才试了一些,直接赋值
ttyname="00001", 这值在数据库中肯定存在.还是不行

succcount = countsucdr.GetInt32(0)还没有执行到,在countsucdr.Read() 的时候就错了

[SmartHeart]

我刚开始用vb,程序写得乱,大家别笑
Function CreateDataSource() As ICollection

' Create sample data for the DataList control.
Dim dt As DataTable = New DataTable
Dim dr As DataRow

' Define the columns of the table.
dt.Columns.Add(New DataColumn("终端号", GetType(String)))
dt.Columns.Add(New DataColumn("成功交易", GetType(Int32)))
dt.Columns.Add(New DataColumn("失败交易", GetType(Int32)))
dt.Columns.Add(New DataColumn("成功百分比", GetType(String)))

' Populate the table with sample values.
Dim ttyname As String
Dim succcount As Int32
Dim failcount As Int32
Dim succeedpersent As Double
Dim strsucceedpresent As String
Dim sqlstr As String
Dim datestr As String

mydr = SqlHelper.ExecuteReader(constr, CommandType.Text, "select ttyid from tty")

datestr = ""

If date_begin.Value <> "" Then
datestr = "and date >= '" & date_begin.Value & "' "
End If

If date_end.Value <> "" Then
datestr = datestr & "and date <= '" & date_end.Value & "' "
End If

While mydr.Read()

dr = dt.NewRow()
ttyname = mydr("ttyid")



sqlstr = "select count(*) from detail where state =0 and ttyid='" & ttyname & "'"
sqlstr = sqlstr & datestr
countsucdr = SqlHelper.ExecuteReader(constr, CommandType.Text, sqlstr)
If countsucdr.Read() Then
succcount = countsucdr.GetInt32(0)
End If

sqlstr = "select COUNT_BIG(*) from detail where state<>0 and ttyid='" & ttyname & "'"
sqlstr = sqlstr & datestr
countsucdr = SqlHelper.ExecuteReader(constr, CommandType.Text, sqlstr)
If countsucdr.Read() Then
failcount = countsucdr.GetInt32(0)
End If

Dim predot As Integer
If (succcount + failcount) = 0 Then
strsucceedpresent = "无交易"
Else
succeedpersent = succcount / (succcount + failcount)
predot = succeedpersent * 100
strsucceedpresent = CStr(predot) + "%"
End If



dr(0) = ttyname
dr(1) = succcount
dr(2) = failcount
dr(3) = strsucceedpresent

dt.Rows.Add(dr)

End While

Dim dv As DataView = New DataView(dt)
Return dv

End Function

[SmartHeart]

请问一下,因为我在做测试,我在服务器端一直有操作在写数据库,insert, 会不会是由于同时对数据库操作造成的冲突?

如果是这个问题,应该如何同步呢?

[poplarjvjv1981]

你可以执行一下下边的看看有没有问题：
sqlstr = "select count(*) from detail where state <>0 and ttyid='" & ttyname & "'"
sqlstr = sqlstr & datestr
countsucdr = SqlHelper.ExecuteReader(constr, CommandType.Text, sqlstr)
If countsucdr.Read() Then
succcount = countsucdr.GetInt32(0)
End If

[SmartHeart]

ok, 我试试

[SmartHeart]

奇怪,

sqlstr="Select count(*) from detail"是可以的

[baibai520]

問題解決了碼

[SmartHeart]

知道问题所在,却还不知道怎么解决

问题在于表内符合条件的数据过多,我把表中符合ttyid='00001'的数据删除后,执行可以过了.只是不知道为什么?那以后表的数据增多了呢?

[SmartHeart]

我表里符合条件的数据也才8000多条啊.奇怪

[SmartHeart]

换成count_big(*)也不行