- Foxtable(狐表) (http://foxtable.com/bbs/index.asp)
-- 专家坐堂 (http://foxtable.com/bbs/list.asp?boardid=2)
---- [求助] (http://foxtable.com/bbs/dispbbs.asp?boardid=2&id=157876)
-- 发布时间：2020/10/31 19:01:00
-- [求助]
老师好！请求帮助：选择下图中未选择的参数，不论是单选或是集合，都可以运行。选择下图中已选的参数，运行出错，是什么原因？
代码如下：
\' 提取 持股人名单
Dim sq As String = "Select [_identify], shAccountSZ, shName Fro m {ShareHolder} where [shCompanyPost] in (" & sv & ")"
Dim cmd As New SQLCommand
cmd.C
cmd.CommandText = sq
Dim dt As DataTable
dt = cmd.ExecuteReader(True)
Dim sq As String = "Select [_identify], shAccountSZ, shName Fro m {ShareHolder} where [shCompanyPost] in (" & sv & ")"
Dim cmd As New SQLCommand
cmd.C
cmd.CommandText = sq
Dim dt As DataTable
dt = cmd.ExecuteReader(True)
[此贴子已经被作者于2020/11/8 10:20:24编辑过]
-- 发布时间：2020/11/1 20:09:00
--
这个错误和sql没有关系，请贴出出错函数的完整代码，不要截图：http://www.foxtable.com/bbs/dispbbs.asp?boardid=2&Id=69632
-- 发布时间：2020/11/8 10:24:00
--
\'##############################################
\' 持有股份 统计
\'**********************************************
\' 参数: Args(0) 查询条件
Dim tb As Table = Tables("持股统计_Table2")
Dim tvw As WinForm.TreeView = Forms("持股统计").Controls("TreeView1")
Dim nd As WinForm.TreeNode
Dim sv As String
\' 提取 查询条件
For Each nd In tvw.AllNodes
If nd.Checked Then
sv = sv & ", \'" & nd.Name & "\'"
End If
Next
Dim sn() As String
If sv = "" Then
Return Nothing
Else
sv = sv.Trim(","," ")
End If
tb.StopRedraw
\' 提取 持股人名单
Dim sq As String = "Select [_identify], shAccountSZ, shName Fro m {ShareHolder} where [shCompanyPost] in (" & sv & ")"
Dim cmd As New SQLCommand
cmd.C
cmd.CommandText = sq
Dim dt As DataTable
dt = cmd.ExecuteReader(True)
Dim nrec As Integer = dt.DataRows.Count
If nrec = 0 Then Return Nothing
Dim nr As Integer = tb.Rows.Count
Select Case nr - nrec
Case <0
nr = nrec - nr
tb.AddNew(nr)
Case >0
Do
nr = nr - 1
tb.Rows(nr).Delete
Loop While nr > nrec
End Select
nr = 0
For Each dr As DataRow In dt.DataRows
tb.Rows(nr)("股份持有人") = dr("shName")
tb.Rows(nr)("账户") = dr("shAccountSZ")
nr = nr + 1
If nr = nrec Then Exit For
Next
Dim odb As WinForm.DateTimePicker = Forms("持股统计").Controls("dtpBegin")
Dim ode As WinForm.DateTimePicker = Forms("持股统计").Controls("dtpStop")
Dim ndb As Date
Dim nde As Date
Dim nsum As Long
Dim nbl As Double
\' 提取 持股统计日期
sq = "Select top 1 hqHoldDate Fro m {ShareQuantity} where [hqHoldDate] <= #" & odb.Value & "# Order by hqHoldDate DESC"
cmd = New SQLCommand
cmd.C
cmd.CommandText = sq
ndb = cmd.ExecuteScalar()
sq = "Select top 1 hqHoldDate Fro m {ShareQuantity} where [hqHoldDate] <= #" & ode.Value & "# Order by hqHoldDate DESC"
cmd.CommandText = sq
nde = cmd.ExecuteScalar()
For Each tr As Row In tb.Rows
\' 提取 期初持股数
sq = "Select hqHoldQuantity Fro m {ShareQuantity} where [hqHoldDate] = #" & ndb & "# And [hqAccountSZ] = \'" & tr("账户") & "\'"
cmd.CommandText = sq
Dim nv As Long = cmd.ExecuteScalar <-----当数据表中不存在某个账户信息时，返回出错，而不是 Nothing
tr("期初持股数量") = nv
\' 提取 期末持股数
sq = "Select hqHoldQuantity, hqHoldingRadio Fro m {ShareQuantity} where [hqHoldDate] = #" & nde & "# And [hqAccountSZ] = \'" & tr("账户") & "\'"
cmd.CommandText = sq
Dim val = cmd.ExecuteValues
tr("期末持股数量") = val("hqHoldQuantity")
tr("期末持股比例%") = val("hqHoldingRadio")
Next
\' 提取 期末限售统计日期
sq = "Select top 1 pqHoldDate Fro m {ShareRestrict} where [pqHoldDate] <= #" & ode.Value & "# Order by pqHoldDate DESC"
cmd.CommandText = sq
nde = cmd.ExecuteScalar()
\' 统计 查询期末限售数
For Each tr As Row In tb.Rows
sq = "Select pqRestrictedQuantity as qty Fro m {ShareRestrict} where [pqHoldDate] = #" & nde & "# AND [pqAccountSZ] = \'" & tr("账户") & "\'"
cmd.CommandText = sq
Dim nv As Long = cmd.ExecuteScalar
tr("期末持有_限售股份数量") = nv
Next
tb.ResumeRedraw
tb.DataTable.AcceptChanges
Return "OK"
[此贴子已经被作者于2020/11/27 13:34:41编辑过]
-- 发布时间：2020/11/8 20:21:00
--
调试一下，看是哪一句代码出错？http://www.foxtable.com/webhelp/topics/1485.htm
Dim nrec As Integer = dt.DataRows.Count
If nrec = 0 Then Return Nothing
Dim nr As Integer = tb.Rows.Count
Select Case nr - nrec
Case <0
nr = nrec - nr
tb.AddNew(nr)
Case >0
Do
nr = nr - 1
tb.Rows(nr).Delete
Loop While nr > nrec
End Select
If nrec = 0 Then Return Nothing
Dim nr As Integer = tb.Rows.Count
Select Case nr - nrec
Case <0
nr = nrec - nr
tb.AddNew(nr)
Case >0
Do
nr = nr - 1
tb.Rows(nr).Delete
Loop While nr > nrec
End Select
-- 发布时间：2020/11/26 15:23:00
--
请教如何解决？错误出现在这段语句中：
For Each tr As Row In tb.Rows
\' 提取 期初持股数sq = "Select hqHoldQuantity Fro m {ShareQuantity} where [hqHoldDate] = #" & ndb & "# And [hqAccountSZ] = \'" & tr("账户") & "\'"
cmd.CommandText = sq
Dim nv As Long = cmd.ExecuteScalar
tr("期初持股数量") = nv
\' 提取 期末持股数
sq = "Select hqHoldQuantity, hqHoldingRadio Fro m {ShareQuantity} where [hqHoldDate] = #" & nde & "# And [hqAccountSZ] = \'" & tr("账户") & "\'"
cmd.CommandText = sq
Dim val = cmd.ExecuteValues <----如果数据中不存在某账户，则出现错误
tr("期末持股数量") = val("hqHoldQuantity")
tr("期末持股比例%") = val("hqHoldingRadio")
Next
-- 发布时间：2020/11/26 15:26:00
--
if val("hqHoldQuantity").ToString<> Nothing then tr("期末持股数量") = val("hqHoldQuantity") end if if val("hqHoldingRadio").ToString<> Nothing then …… |