销售情况是调用数据库中SALEPER存储过程,数据显示与MSFlexGrid1控件中,是通过单击command1实现
明细是调用数据库中SALEPERdtl存储过程,数据显示与MSFlexGrid2控件中,是通过单击command2实现
明细存储过程比销售情况多一个输入参数,由双击MSFlexGrid1控件获得
现在销售情况可以查看,明细无法查看,问题出在哪请大侠们指出,
本人菜鸟,程序无错误处理机制,现在只求能将明细数据显示子啊MSFlexGrid2中
Option Explicit
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim parm_opt As ADODB.Parameter '参数1
Dim parm_std As ADODB.Parameter '参数2
Dim parm_end As ADODB.Parameter '参数3
Dim parm_cod As ADODB.Parameter '参数4
Dim parm_nbm As ADODB.Parameter '参数5
Public ser As String
Private Sub command1_Click()
Dim i As Integer, j As Integer, str As String 'str用于存放字段内容
Dim rstcol As Integer
Set cmd = New ADODB.Command
Set parm_opt = New ADODB.Parameter
parm_opt.Name = "optcode"
parm_opt.Type = adInteger '参数类型
parm_opt.Size = 4 '参数长度
parm_opt.Direction = adParamInput '参数方向,输入或输出
parm_opt.Value = 9999 '参数的值
cmd.Parameters.Append parm_opt '加入参数
Set parm_std = New ADODB.Parameter
parm_std.Name = "startday"
parm_std.Type = adVarChar
parm_std.Size = 10
parm_std.Direction = adParamInput
parm_std.Value = Format(DTPicker1.Value, "YYYY-MM-DD")
'Format(EndDay.Value, "YYYY-MM-DD")
cmd.Parameters.Append parm_std
Set parm_end = New ADODB.Parameter
parm_end.Name = "EndDay"
parm_end.Type = adVarChar
parm_end.Size = 10
parm_end.Direction = adParamInput
parm_end.Value = Format(DTPicker2.Value, "YYYY-MM-DD")
cmd.Parameters.Append parm_end
Set parm_cod = New ADODB.Parameter
parm_cod.Name = "condition"
parm_cod.Type = adVarChar
parm_cod.Size = 64
parm_cod.Direction = adParamInput
parm_cod.Value = Trim(Text1.Text)
cmd.Parameters.Append parm_cod
cmd.ActiveConnection = cnn
'指定该command 的当前活动连接
cmd.CommandText = "SALEPER"
'myprocedure 是你要调用的存储过程名称
cmd.CommandType = adCmdStoredProc
'表明command 为存储过程
Set rst = New ADODB.Recordset
Set rst = cmd.Execute()
'首先判断记录集是否有内容[如果无内容则不做任何操作就直接返回]
If rst.RecordCount < 1 Then
Exit Sub
End If
'下面进行表格填充
MSFlexGrid1.Redraw = False '不重绘,目的是提高速度
With MSFlexGrid1
.Clear
.FixedRows = 1 '固定行
.Row = 1 '活动单元行
.Col = 1 '活动单元列
rst.MoveLast
rst.MoveFirst '移动记录,如果不移动,有时候RecordCount会统计不准确
.Cols = rst.Fields.Count + 1 '设定表格列数,统计字段数量,列数要比字段数多一个,第一列为空列,当记录指示器用
.Rows = rst.RecordCount + 1 '该设定决定表格有多少行显示数据,很重要
.TextMatrix(0, 0) = "" '设定第0列表头,为空
'添加各个字段的名称[添加表头]
For i = 1 To rst.Fields.Count '循环显示字段名称,有多少个字段则循环多少次
'i就是表格的当前列,第0列是记录指示器,不在本循环中处理
rstcol = i - 1 '比如第1列存放的是rst(0)的内容,故有i-1的关系
.Row = 0
.Col = i
.CellAlignment = 4 '表头都设成居中对齐
.TextMatrix(0, i) = rst.Fields(rstcol).Name '显示字段名称
Next
'第一行放字段名,再加上记录数,就是表格应有的总行数
For i = 1 To rst.RecordCount '循环显示记录,有多少条记录则循环多少次
.Row = i '当前行随着添充数据走
'第0列做记录指示器用,不填充数据,所以每一行都从第一列开始填
For j = 1 To rst.Fields.Count '循环处理各个列
.Col = j '表格定位到相应的列
rstcol = j - 1 '字段的序号,第一列对应rst的第0列字段,故有j-1的关系
'根据不同的类型,设置不同的格式
Select Case rst.Fields(rstcol).Type
Case adDecimal, adDouble, adSingle, adNumeric
'设定为右对齐
.CellAlignment = 7
If Val(rst.Fields(rstcol).Value & "") = 0 Then '为0要不显示
str = ""
Else
'根据数据库中的字段小数位数的定义设置格式
Select Case rst.Fields(rstcol).NumericScale
Case 1
str = Format(Trim(rst.Fields(rstcol) & ""), "#0.0")
Case 2
str = Format(Trim(rst.Fields(rstcol) & ""), "#0.00")
Case 3
str = Format(Trim(rst.Fields(rstcol) & ""), "#0.000")
Case Else
str = Format(Trim(rst.Fields(rstcol) & ""), "#0.0#")
End Select
End If
Case adBigInt, adInteger, adTinyInt, adSmallInt
'设定为右对齐
.CellAlignment = 7
If Val(rst.Fields(rstcol).Value & "") = 0 Then '为0要不显示
str = ""
Else
str = Trim(rst.Fields(rstcol).Value & "")
End If
Case Else
'设定为左对齐
.CellAlignment = 1
str = Trim(rst.Fields(rstcol).Value & "")
End Select
.TextMatrix(i, j) = str
Next
rst.MoveNext '显示下一条记录
Next
'设定第几行显示在最前面(用toprow属性)
.TopRow = 1
'返回true
End With
MSFlexGrid1.Redraw = True '填完数据后,充许重绘
Exit Sub
End Sub
Private Sub Command2_Click()
Dim n As Integer
Dim k As Integer
Set cmd = New ADODB.Command
Set parm_opt = New ADODB.Parameter
parm_opt.Name = "optcode"
parm_opt.Type = adInteger '参数类型
parm_opt.Size = 4 '参数长度
parm_opt.Direction = adParamInput '参数方向,输入或输出
parm_opt.Value = 9999 '参数的值
cmd.Parameters.Append parm_opt '加入参数
Set parm_std = New ADODB.Parameter
parm_std.Name = "startday"
parm_std.Type = adVarChar
parm_std.Size = 10
parm_std.Direction = adParamInput
parm_std.Value = Format(DTPicker1.Value, "YYYY-MM-DD")
'Format(EndDay.Value, "YYYY-MM-DD")
cmd.Parameters.Append parm_std
Set parm_end = New ADODB.Parameter
parm_end.Name = "EndDay"
parm_end.Type = adVarChar
parm_end.Size = 10
parm_end.Direction = adParamInput
parm_end.Value = Format(DTPicker2.Value, "YYYY-MM-DD")
cmd.Parameters.Append parm_end
Set parm_cod = New ADODB.Parameter
parm_cod.Name = "condition"
parm_cod.Type = adVarChar
parm_cod.Size = 64
parm_cod.Direction = adParamInput
parm_cod.Value = Trim(Text1.Text)
cmd.Parameters.Append parm_cod
Set parm_nbm = New ADODB.Parameter
parm_nbm.Name = "ItemAdd"
parm_nbm.Type = adVarChar
parm_nbm.Size = 64
parm_nbm.Direction = adParamInput
parm_nbm.Value = Trim(Text2.Text)
cmd.Parameters.Append parm_nbm
cmd.ActiveConnection = cnn
'指定该command 的当前活动连接
cmd.CommandText = "SALEPERdtl"
'myprocedure 是你要调用的存储过程名称
cmd.CommandType = adCmdStoredProc
'表明command 为存储过程
Set rst = New ADODB.Recordset
Set rst = cmd.Execute()
n = 0
Do While Not rst.EOF
n = n + 1 ' i 中保存记录个数
rst.MoveNext
Loop
MSFlexGrid2.Rows = n + 1
'动态设置MSFlexGrid的行和列
MSFlexGrid2.Cols = rst.Fields.Count + 1
MSFlexGrid2.Row = 0
For n = 0 To rst.Fields.Count - 1
MSFlexGrid2.Col = n + 1
MSFlexGrid2.Text = rst.Fields.Item(n).Name
Next '设置第一行的标题,用域名填充
n = 0
Set rst = cmd.Execute()
Do While Not rst.EOF
n = n + 1
MSFlexGrid2.Row = n '确定行
For k = 0 To rst.Fields.Count - 1
MSFlexGrid2.Col = k + 1
MSFlexGrid2.Text = rst(k)
'添充所有的列
Next
rst.MoveNext
Loop '这个循环用来填充MSFlexGrid的内容
End Sub
Private Sub Command3_Click()
Set cnn = New ADODB.Connection
Dim s As String
ser = Text3.Text
s = "Driver={sql server};server=" & ser & ";uid=sa;pwd=;database=pub50"
Command3.Enabled = False
cnn.Open s '打开连接
cnn.CursorLocation = adUseClient
End Sub
Private Sub Form_Load()
DTPicker1.Value = Date
DTPicker2.Value = Date
End Sub
Private Sub Form_Unload(Cancel As Integer)
cnn.Close '关闭连接
Set cnn = Nothing
Form1.Show
End Sub
Private Sub MSFlexGrid1_DblClick()
Text2.Text = MSFlexGrid1.TextMatrix(MSFlexGrid1.Row, 1)
End Sub
Private Sub Text1_KeyPress(KeyAscii As Integer)
If KeyAscii = 13 Then
command1_Click
End If
End Sub
Private Sub Text3_KeyPress(KeyAscii As Integer)
If KeyAscii = 13 Then
Command3_Click
End If
End Sub
6 个解决方案
#1
各位大侠看不到吗?新注册的用户分少啊,帮帮忙啊
#2
还是没有人关注?
#3
代码好长,没有耐心看。
如果1成功,而2不成功,你用F8跟踪一下,还有可能是你的存储过程有问题也说不定...
如果1成功,而2不成功,你用F8跟踪一下,还有可能是你的存储过程有问题也说不定...
#4
+1
N长的代码没具体问题,头疼。
#5
Option Explicit
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim parm_opt As ADODB.Parameter '参数1
Dim parm_std As ADODB.Parameter '参数2
Dim parm_end As ADODB.Parameter '参数3
Dim parm_cod As ADODB.Parameter '参数4
Dim parm_nbm As ADODB.Parameter '参数5
Public ser As String
Private Sub command1_Click()
Dim i As Integer, j As Integer, str As String 'str用于存放字段内容
Dim rstcol As Integer
Set cmd = New ADODB.Command
Set parm_opt = New ADODB.Parameter
parm_opt.Name = "optcode"
parm_opt.Type = adInteger '参数类型
parm_opt.Size = 4 '参数长度
parm_opt.Direction = adParamInput '参数方向,输入或输出
parm_opt.Value = 9999 '参数的值
cmd.Parameters.Append parm_opt '加入参数
Set parm_std = New ADODB.Parameter
parm_std.Name = "startday"
parm_std.Type = adVarChar
parm_std.Size = 10
parm_std.Direction = adParamInput
parm_std.Value = Format(DTPicker1.Value, "YYYY-MM-DD")
'Format(EndDay.Value, "YYYY-MM-DD")
cmd.Parameters.Append parm_std
Set parm_end = New ADODB.Parameter
parm_end.Name = "EndDay"
parm_end.Type = adVarChar
parm_end.Size = 10
parm_end.Direction = adParamInput
parm_end.Value = Format(DTPicker2.Value, "YYYY-MM-DD")
cmd.Parameters.Append parm_end
Set parm_cod = New ADODB.Parameter
parm_cod.Name = "condition"
parm_cod.Type = adVarChar
parm_cod.Size = 64
parm_cod.Direction = adParamInput
parm_cod.Value = Trim(Text1.Text)
cmd.Parameters.Append parm_cod
cmd.ActiveConnection = cnn
'指定该command 的当前活动连接
cmd.CommandText = "SALEPER"
'myprocedure 是你要调用的存储过程名称
cmd.CommandType = adCmdStoredProc
'表明command 为存储过程
Set rst = New ADODB.Recordset
Set rst = cmd.Execute()
set MSFlexGrid1.DataSource=rst
end sub
为什么这样写连第一个都不对了啊?
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim parm_opt As ADODB.Parameter '参数1
Dim parm_std As ADODB.Parameter '参数2
Dim parm_end As ADODB.Parameter '参数3
Dim parm_cod As ADODB.Parameter '参数4
Dim parm_nbm As ADODB.Parameter '参数5
Public ser As String
Private Sub command1_Click()
Dim i As Integer, j As Integer, str As String 'str用于存放字段内容
Dim rstcol As Integer
Set cmd = New ADODB.Command
Set parm_opt = New ADODB.Parameter
parm_opt.Name = "optcode"
parm_opt.Type = adInteger '参数类型
parm_opt.Size = 4 '参数长度
parm_opt.Direction = adParamInput '参数方向,输入或输出
parm_opt.Value = 9999 '参数的值
cmd.Parameters.Append parm_opt '加入参数
Set parm_std = New ADODB.Parameter
parm_std.Name = "startday"
parm_std.Type = adVarChar
parm_std.Size = 10
parm_std.Direction = adParamInput
parm_std.Value = Format(DTPicker1.Value, "YYYY-MM-DD")
'Format(EndDay.Value, "YYYY-MM-DD")
cmd.Parameters.Append parm_std
Set parm_end = New ADODB.Parameter
parm_end.Name = "EndDay"
parm_end.Type = adVarChar
parm_end.Size = 10
parm_end.Direction = adParamInput
parm_end.Value = Format(DTPicker2.Value, "YYYY-MM-DD")
cmd.Parameters.Append parm_end
Set parm_cod = New ADODB.Parameter
parm_cod.Name = "condition"
parm_cod.Type = adVarChar
parm_cod.Size = 64
parm_cod.Direction = adParamInput
parm_cod.Value = Trim(Text1.Text)
cmd.Parameters.Append parm_cod
cmd.ActiveConnection = cnn
'指定该command 的当前活动连接
cmd.CommandText = "SALEPER"
'myprocedure 是你要调用的存储过程名称
cmd.CommandType = adCmdStoredProc
'表明command 为存储过程
Set rst = New ADODB.Recordset
Set rst = cmd.Execute()
set MSFlexGrid1.DataSource=rst
end sub
为什么这样写连第一个都不对了啊?
#6
大仙指路 啊
#1
各位大侠看不到吗?新注册的用户分少啊,帮帮忙啊
#2
还是没有人关注?
#3
代码好长,没有耐心看。
如果1成功,而2不成功,你用F8跟踪一下,还有可能是你的存储过程有问题也说不定...
如果1成功,而2不成功,你用F8跟踪一下,还有可能是你的存储过程有问题也说不定...
#4
+1
N长的代码没具体问题,头疼。
#5
Option Explicit
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim parm_opt As ADODB.Parameter '参数1
Dim parm_std As ADODB.Parameter '参数2
Dim parm_end As ADODB.Parameter '参数3
Dim parm_cod As ADODB.Parameter '参数4
Dim parm_nbm As ADODB.Parameter '参数5
Public ser As String
Private Sub command1_Click()
Dim i As Integer, j As Integer, str As String 'str用于存放字段内容
Dim rstcol As Integer
Set cmd = New ADODB.Command
Set parm_opt = New ADODB.Parameter
parm_opt.Name = "optcode"
parm_opt.Type = adInteger '参数类型
parm_opt.Size = 4 '参数长度
parm_opt.Direction = adParamInput '参数方向,输入或输出
parm_opt.Value = 9999 '参数的值
cmd.Parameters.Append parm_opt '加入参数
Set parm_std = New ADODB.Parameter
parm_std.Name = "startday"
parm_std.Type = adVarChar
parm_std.Size = 10
parm_std.Direction = adParamInput
parm_std.Value = Format(DTPicker1.Value, "YYYY-MM-DD")
'Format(EndDay.Value, "YYYY-MM-DD")
cmd.Parameters.Append parm_std
Set parm_end = New ADODB.Parameter
parm_end.Name = "EndDay"
parm_end.Type = adVarChar
parm_end.Size = 10
parm_end.Direction = adParamInput
parm_end.Value = Format(DTPicker2.Value, "YYYY-MM-DD")
cmd.Parameters.Append parm_end
Set parm_cod = New ADODB.Parameter
parm_cod.Name = "condition"
parm_cod.Type = adVarChar
parm_cod.Size = 64
parm_cod.Direction = adParamInput
parm_cod.Value = Trim(Text1.Text)
cmd.Parameters.Append parm_cod
cmd.ActiveConnection = cnn
'指定该command 的当前活动连接
cmd.CommandText = "SALEPER"
'myprocedure 是你要调用的存储过程名称
cmd.CommandType = adCmdStoredProc
'表明command 为存储过程
Set rst = New ADODB.Recordset
Set rst = cmd.Execute()
set MSFlexGrid1.DataSource=rst
end sub
为什么这样写连第一个都不对了啊?
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim parm_opt As ADODB.Parameter '参数1
Dim parm_std As ADODB.Parameter '参数2
Dim parm_end As ADODB.Parameter '参数3
Dim parm_cod As ADODB.Parameter '参数4
Dim parm_nbm As ADODB.Parameter '参数5
Public ser As String
Private Sub command1_Click()
Dim i As Integer, j As Integer, str As String 'str用于存放字段内容
Dim rstcol As Integer
Set cmd = New ADODB.Command
Set parm_opt = New ADODB.Parameter
parm_opt.Name = "optcode"
parm_opt.Type = adInteger '参数类型
parm_opt.Size = 4 '参数长度
parm_opt.Direction = adParamInput '参数方向,输入或输出
parm_opt.Value = 9999 '参数的值
cmd.Parameters.Append parm_opt '加入参数
Set parm_std = New ADODB.Parameter
parm_std.Name = "startday"
parm_std.Type = adVarChar
parm_std.Size = 10
parm_std.Direction = adParamInput
parm_std.Value = Format(DTPicker1.Value, "YYYY-MM-DD")
'Format(EndDay.Value, "YYYY-MM-DD")
cmd.Parameters.Append parm_std
Set parm_end = New ADODB.Parameter
parm_end.Name = "EndDay"
parm_end.Type = adVarChar
parm_end.Size = 10
parm_end.Direction = adParamInput
parm_end.Value = Format(DTPicker2.Value, "YYYY-MM-DD")
cmd.Parameters.Append parm_end
Set parm_cod = New ADODB.Parameter
parm_cod.Name = "condition"
parm_cod.Type = adVarChar
parm_cod.Size = 64
parm_cod.Direction = adParamInput
parm_cod.Value = Trim(Text1.Text)
cmd.Parameters.Append parm_cod
cmd.ActiveConnection = cnn
'指定该command 的当前活动连接
cmd.CommandText = "SALEPER"
'myprocedure 是你要调用的存储过程名称
cmd.CommandType = adCmdStoredProc
'表明command 为存储过程
Set rst = New ADODB.Recordset
Set rst = cmd.Execute()
set MSFlexGrid1.DataSource=rst
end sub
为什么这样写连第一个都不对了啊?
#6
大仙指路 啊