我是菜鸟!问一个关于ADO连接数据库在MSflexgrid中显示查询结果的问题?
窗体中有,一个msflexgrid控件,一个textbox,一个清空button,一个查询button,一个返回button。
目的:在textbox中输入药品名字的拼音首写字母,在数据库中查到药品信息在msflexgrid中显示。
问题:不能返回正确的查询结果(数据库中有要查找的药品信息),总是返回窗口“数据库中无可用记录”。最重要的是我曾经用存储过程实现了返回正确的结果,可是选清空后输入新的查询条件后(药品首写字母)不能返回正确结果,返回窗口“数据库中无可用记录”。另外,where 语句中变量incode2和%的连接也不明白怎样写,请指教我找了很多资料但都没有弄明白,先谢了。
Option Explicit
Dim incode1 As String '字符串1
Dim incode2 As String '字符串2
Dim cn As New ADODB.connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
Dim pm As New ADODB.Parameter
Private Sub Command_c_Click(Index As Integer)
Text1 = ""
MSFlexGrid1.Clear
Form_Load
End Sub
Private Sub Command_e_Click(Index As Integer)
Unload Me
mainfrm.Show
End Sub
Private Sub Command1_Click(Index As Integer)
Select Case Index
Case 0
incode1 = "Q"
Case 9
incode1 = "W"
Case 8
incode1 = "E"
Case 7
incode1 = "R"
Case 6
incode1 = "T"
Case 5
incode1 = "Y"
Case 4
incode1 = "U"
Case 3
incode1 = "I"
Case 2
incode1 = "O"
Case 1
incode1 = "P"
Case 10
incode1 = "A"
Case 18
incode1 = "S"
Case 17
incode1 = "D"
Case 16
incode1 = "F"
Case 15
incode1 = "G"
Case 14
incode1 = "H"
Case 13
incode1 = "J"
Case 12
incode1 = "K"
Case 11
incode1 = "L"
Case 25
incode1 = "Z"
Case 24
incode1 = "X"
Case 23
incode1 = "C"
Case 22
incode1 = "V"
Case 21
incode1 = "B"
Case 20
incode1 = "N"
Case 19
incode1 = "M"
End Select
Text1 = incode2 + incode1 '显示结果
incode2 = Trim(Text1)
incode1 = ""
End Sub
Private Sub Command_s_Click(Index As Integer)
Dim source As String
Dim i As Long
On Error Resume Next
source = "Provider=MSDASQL.1;Persist Security Info=False;User ID=sa;Data Source=health;Mode=Read;Initial Catalog=testhis"
Set rs = New ADODB.Recordset
rs.CursorType = adOpenStatic
rs.Open "SELECT drugbase.drugname as '药品名称'," & _
"case when drugbaseelse.dname is null then '' else drugbaseelse.dname end as '药品别名'," & _
"drugbase.drugregu as '药品规格'," & _
"drugbase.drugunit as '药品单位'," & _
"drugbase.lprice as '单价'," & _
"drugprop.des as '剂型'," & _
"case when drugbase.ybid is null then '非医保药品' else '医保药品' end as '医保药品'," & _
"discounttype.name as '支付类型'" & _
"FORM " & _
"drugbase left join drugbaseelse on drugbase.drugid=drugbaseelse.drugid" & _
"left join drugprop on drugbase.jlid=drugprop.id and drugprop.type=1" & _
"left join discounttype on drugbase.discounttype=discounttype.id" & _
"WHERE" & _
"drugbase.stopflag= 0 and" & _
"(drugbase.incode = '& incode2&%&' or drugbaseelse.incode like '&incode2&%')", source, , , adCmdText
'cn.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;User ID=sa;Data Source=health;Mode=Read;Initial Catalog=testhis"
'cn.Open
'Set cmd.ActiveConnection = cn
'With cmd
' .CommandType = adCmdStoredProc
' .CommandText = "gogotrain_drugs" '"select drugbase.drugname as '药品名称',case when drugbaseelse.dname is null then '' else drugbaseelse.dname end as '药品别名' ,drugbase.drugregu as '药品规格',drugbase.drugunit as '药品单位',drugbase.lprice as '单价',drugprop.des as '剂型',case when drugbase.ybid is null then '非医保药品' else '医保药品' end as '医保药品',discounttype.name as '支付类型' from drugbase left join drugbaseelse on drugbase.drugid=drugbaseelse.drugid left join drugprop on drugbase.jlid=drugprop.id and drugprop.type=1 left join discounttype on drugbase.discounttype=discounttype.id where drugbase.stopflag= 0 and (drugbase.incode like '&source&' or drugbaseelse.incode like '&source&')"
'End With
'Set pm = cmd.CreateParameter(source, adVarChar, adParamInput, 40, incode2)
' cmd.Parameters.Append pm
' cmd.Parameters(source).Value = incode2
'Set rs = cmd.Execute
rs.MoveFirst '将记录集指针移到开始位置
If rs.BOF And rs.EOF Then '无数据
MSFlexGrid1.Rows = 1 '清除原来的显示
MsgBox "数据库中无可用记录", , "提示"
Exit Sub
End If
i = 1
Do Until rs.EOF '
MSFlexGrid1.Rows = i + 1 '增加一行记录
MSFlexGrid1.Row = i '设置行位置
MSFlexGrid1.Col = 0 '设置为第一列
MSFlexGrid1.Text = " " & Str(i)
MSFlexGrid1.Col = 1 '设置为第二列
MSFlexGrid1.Text = " " & rs!药品名称
MSFlexGrid1.Col = 2 '设置为第三列
MSFlexGrid1.Text = " " & rs!药品别名
MSFlexGrid1.Col = 3 '设置为第四列
MSFlexGrid1.Text = " " & rs!药品规格 '& Str(rs!年龄)
MSFlexGrid1.Col = 4 '设置为第五列
MSFlexGrid1.Text = " " & rs!药品单位
MSFlexGrid1.Col = 5 '设置为第六列
MSFlexGrid1.Text = " " & CCur(rs!单价)
MSFlexGrid1.Col = 6 '设置为第七列
MSFlexGrid1.Text = " " & rs!剂型
MSFlexGrid1.Col = 7 '设置为第八列
MSFlexGrid1.Text = " " & rs!医保药品
MSFlexGrid1.Col = 8 '设置为第九列
MSFlexGrid1.Text = " " & rs!支付类型
rs.MoveNext
i = i + 1
Loop
rs.Close
cn.Close
End Sub
Private Sub Form_Load()
incode1 = ""
incode2 = ""
'设置网格宽度
MSFlexGrid1.ColWidth(0) = 500
MSFlexGrid1.ColWidth(1) = 2000
MSFlexGrid1.ColWidth(2) = 1500
MSFlexGrid1.ColWidth(3) = 1500
MSFlexGrid1.ColWidth(4) = 500
MSFlexGrid1.ColWidth(5) = 1000
MSFlexGrid1.ColWidth(6) = 800
MSFlexGrid1.ColWidth(7) = 1000
MSFlexGrid1.ColWidth(8) = 1000
'设置字段名显示
MSFlexGrid1.Row = 0
MSFlexGrid1.Col = 0
MSFlexGrid1.Text = " 序号"
MSFlexGrid1.Col = 1
MSFlexGrid1.Text = "药 品 名 称"
MSFlexGrid1.Col = 2
MSFlexGrid1.Text = "药 品 别 名"
MSFlexGrid1.Col = 3
MSFlexGrid1.Text = "药 品 规 格"
MSFlexGrid1.Col = 4
MSFlexGrid1.Text = "单 位"
MSFlexGrid1.Col = 5
MSFlexGrid1.Text = "单 价"
MSFlexGrid1.Col = 6
MSFlexGrid1.Text = "剂 型"
MSFlexGrid1.Col = 7
MSFlexGrid1.Text = "医 保 药 品"
MSFlexGrid1.Col = 8
MSFlexGrid1.Text = "支 付 类 型"
End Sub