📄 form_store.frm
字号:
VERSION 5.00
Object = "{831FDD16-0C5C-11D2-A9FC-0000F8754DA1}#2.0#0"; "MSCOMCTL.OCX"
Begin VB.Form Frm_Store
Caption = "Form1"
ClientHeight = 7065
ClientLeft = 60
ClientTop = 450
ClientWidth = 7815
LinkTopic = "Form1"
ScaleHeight = 7065
ScaleWidth = 7815
StartUpPosition = 3 '窗口缺省
Begin VB.TextBox Text5
Height = 375
Left = 5040
TabIndex = 12
Text = "Text4"
Top = 5520
Width = 1695
End
Begin VB.TextBox Text4
Height = 375
Left = 2760
TabIndex = 10
Text = "Text4"
Top = 5520
Width = 1695
End
Begin VB.TextBox Text3
Height = 495
Left = 2760
TabIndex = 9
Text = "Text3"
Top = 4200
Width = 3975
End
Begin VB.CommandButton Command1
Caption = "执行"
Height = 375
Left = 6600
TabIndex = 5
Top = 480
Width = 975
End
Begin VB.TextBox Text2
Height = 270
Left = 3360
TabIndex = 4
Top = 600
Width = 2895
End
Begin VB.TextBox Text1
Height = 270
Left = 3360
TabIndex = 3
Top = 120
Width = 2895
End
Begin MSComctlLib.ListView ListView1
Height = 2655
Left = 120
TabIndex = 0
Top = 960
Width = 7455
_ExtentX = 13150
_ExtentY = 4683
View = 3
LabelWrap = -1 'True
HideSelection = -1 'True
FullRowSelect = -1 'True
GridLines = -1 'True
_Version = 393217
ForeColor = -2147483640
BackColor = -2147483643
BorderStyle = 1
Appearance = 1
NumItems = 0
End
Begin VB.Label Label8
Caption = "客户数量"
Height = 255
Left = 2760
TabIndex = 14
Top = 3840
Width = 1215
End
Begin VB.Label Label7
Caption = "第2个客户"
Height = 255
Left = 5040
TabIndex = 13
Top = 5040
Width = 1215
End
Begin VB.Label Label6
Caption = "第1个客户"
Height = 255
Left = 2760
TabIndex = 11
Top = 5040
Width = 1215
End
Begin VB.Label Label5
Caption = "自己创建的存储过程sp_HighAndLow，用来获得第一个客户和最后一个客户编号"
Height = 975
Left = 120
TabIndex = 8
Top = 5160
Width = 1695
End
Begin VB.Label Label4
Caption = "自己创建的存储过程sp_CountCustomer，用来统计客户表customers中的客户数量"
Height = 975
Left = 120
TabIndex = 7
Top = 3840
Width = 1695
End
Begin VB.Label Label3
Caption = "NorthWind自带的存储过程[Sales by Year]，该存储过程需要输入参数"
Height = 735
Left = 120
TabIndex = 6
Top = 120
Width = 1935
End
Begin VB.Label Label2
Caption = "结束日期："
Height = 255
Left = 2280
TabIndex = 2
Top = 600
Width = 1335
End
Begin VB.Label Label1
Caption = "起始日期："
Height = 255
Left = 2280
TabIndex = 1
Top = 120
Width = 1215
End
End
Attribute VB_Name = "Frm_Store"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Dim objConn As New ADODB.Connection
Dim objCmd As New ADODB.Command
Dim objCmd1 As New ADODB.Command
Dim objCmd2 As New ADODB.Command
Private Sub Command1_Click()
ListView1.ColumnHeaders.Clear
ListView1.ListItems.Clear
'设置Command对象的属性
With objCmd
'对象所操作的数据连接
.ActiveConnection = objConn.ConnectionString
'存储过程名，若存储过程名超过一个单词
'则需要整个存储过程名用方括号括起来
.CommandText = "[Sales by Year]"
'Command类型，这里表示这个命令用来执行存储过程
.CommandType = adCmdStoredProc
'建立参数表
'返回值@RETURN_VALUE
.Parameters.Append objCmd.CreateParameter( _
"@RETURN_VALUE", adInteger, adParamReturnValue)
'参数一@Beginning_Date
.Parameters.Append objCmd.CreateParameter( _
"@Beginning_Date", adDBTimeStamp, adParamInput)
'参数二@Ending_Date
.Parameters.Append objCmd.CreateParameter( _
"@Ending_Date", adDBTimeStamp, adParamInput)
'设置参数值
.Parameters("@Beginning_Date") = Text1.Text
.Parameters("@Ending_Date") = Text2.Text
End With
Dim objRS As Recordset
'执行存储过程，返回RecordSet
Set objRS = objCmd.Execute
Dim i As Long
Dim ListX As ListItem
'在ListView1中显示结果
If Not objRS.State = adStateClosed Then
'刷新表头
With ListView1.ColumnHeaders
For i = 0 To objRS.Fields.Count - 1
.Add Key:=objRS.Fields(i).Name, _
Text:=objRS.Fields(i).Name
Next i
End With
'读取数据填充ListView1
While Not objRS.EOF And Not objRS.State = adStateClosed
For i = 0 To objRS.Fields.Count - 1
If i = 0 Then
Set ListX = ListView1.ListItems.Add( _
Text:=objRS.Fields(i).Value)
Else
ListX.ListSubItems.Add Key:= _
objRS.Fields(i).Name, Text:=objRS.Fields(i).Value
End If
Next i
objRS.MoveNext
Wend
End If
'释放资源
If objCmd.State = adStateOpen Then objRS.Close
Set objRS = Nothing
End Sub
Private Sub Form_Load()
'连接字符串
Dim strConnectionString As String
strConnectionString = "Provider=SQLOLEDB;" & _
"data source=(local);initial catalog=Northwind;" & _
"Uid=sa;Pwd=;"
'数据库连接
objConn.ConnectionString = strConnectionString
'统计所有客户数量
Text3.Text = GetCustomersCount
'显示第一个和最后一个客户编号
ShowCustomerNo
End Sub
Private Function GetCustomersCount() As Integer
With objCmd1
'对象所操作的数据连接
.ActiveConnection = objConn.ConnectionString
'存储过程名，若存储过程名超过一个单词
'则需要整个存储过程名用方括号括起来
.CommandText = "sp_CountCustomer"
'Command类型，这里表示这个命令用来执行存储过程
.CommandType = adCmdStoredProc
'建立参数表
'返回值@RETURN_VALUE
.Parameters.Append objCmd1.CreateParameter( _
"@RETURN_VALUE", adInteger, adParamReturnValue)
End With
objCmd1.Execute
GetCustomersCount = objCmd1.Parameters("@RETURN_VALUE")
End Function
Private Sub ShowCustomerNo()
With objCmd2
'对象所操作的数据连接
.ActiveConnection = objConn.ConnectionString
'存储过程名，若存储过程名超过一个单词
'则需要整个存储过程名用方括号括起来
.CommandText = "sp_HighAndLow"
'Command类型，这里表示这个命令用来执行存储过程
.CommandType = adCmdStoredProc
'建立参数表
'参数一@Beginning_Date
.Parameters.Append objCmd2.CreateParameter( _
"@HighUser", adVarChar, adParamOutput, 30)
'参数二@Ending_Date
.Parameters.Append objCmd2.CreateParameter( _
"@LowUser", adVarChar, adParamOutput, 30)
End With
objCmd2.Execute
Text4.Text = objCmd2.Parameters("@LowUser")
Text5.Text = objCmd2.Parameters("@HighUser")
End Sub
Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
Set objCmd = Nothing
Set objCmd1 = Nothing
Set objCmd2 = Nothing
If objConn.State = adStateOpen Then objConn.Close
Set ojbconn = Nothing
End Sub
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -