vb6 使用MySQL数据库的存储过程模糊及精确查询客户信息

存储过程可以应用程序变得简单
学习模糊查询 如何编辑语句
CREATE DEFINER=root@localhost PROCEDURE BIAOCX(IN BIAOMC VARCHAR(20),IN p_select_sql VARCHAR(200), in kusl INT)
label:BEGIN
– 学习模糊查询
IF BIAOMC=‘WLDWB’ THEN
IF kusl=0 THEN
select 序号,编码,往来单位,联系人,电话,地址,状态,手机,身份证,账号1,账号2,其他,级别,备注,登记日期 from WLDWB where 往来单位 like CONCAT(“%”,p_select_sql,“%”) OR 拼音 like CONCAT(“%”,p_select_sql,“%”) OR 编码 like CONCAT(“%”,p_select_sql,“%”) order by 序号 DESC ;
else
select 序号,编码,往来单位,联系人,电话,地址,状态,手机,身份证,账号1,账号2,其他,级别,备注,登记日期 from WLDWB where 序号=CONVERT(p_select_sql,SIGNED) ;
END IF;
LEAVE label;
END IF;
END
VB6调用
Private Sub 查询()
n= spkccx(MSHFlexGrid1, “WLDWB”, “黑龙江”, 0)
End Sub
’ 参数 表格名称 ，要查询的表名称 ， 查询条件词语， 变换存储过程里面是模糊还是精确查询
Function spkccx(BIAOGE As Object, CUNCMC As String, pyhz As String, a1 As Integer) As Integer '用存储过程显示查询到的库存数据库写到表格里面
Dim a2, a3 As Long
'On Error Resume Next
spkccx = 0
Dim sqlstr As String
sqlstr = “call BIAOCX('” & CUNCMC & “‘,’” & pyhz & “',” & a1 & “)”
Set RST = CNN.Execute(sqlstr)
With BIAOGE
.Clear: .Rows = 2
If Not RST.EOF Then
a1 = RST.Fields.Count - 1
If a1 = -1 Then GoTo l
.Redraw = False
.Cols = a1 + 1
For a2 = 0 To a1
.TextMatrix(0, a2) = RST.Fields.Item(a2).Name
Next
a2 = 0
Do While Not RST.EOF
a2 = a2 + 1
.Rows = a2 + 1
For a3 = 0 To a1
If IsNull(RST(a3)) = False Then .TextMatrix(a2, a3) = RST(a3)
Next a3
RST.MoveNext
Loop
End If
spkccx = a2
.Redraw = True
End With
l:
RST.Close
Set RST = Nothing
End Function
精确查询 双击表格内容 获得序号
Private Sub MSHFlexGrid1_DblClick()
With MSHFlexGrid1
strsql = “call BIAOCX(‘WLDWB’,'” & Val(.TextMatrix(.Row, 0)) & “',1)”
Set RST = CNN.Execute(strsql)
Text1(0) = Val(.TextMatrix(.Row, 0))
For I = 1 To Text1.Count - 1
Text1(I) = “”
Text1(I).Locked = True
Text1(I).BackColor = &H80000005
If IsNull(RST.Fields(Label1(I).Caption)) = False Then Text1(I) = RST.Fields(Label1(I).Caption)
Next I
RST.Close
Picture1.Visible = True
Command1.Caption = “开始修改F8”
End With
l:
End Sub

用函数 其他界面查询都可以借用

没有高深代码 实际应用总结