我是菜鸟！问一个关于ADO连接数据库在MSflexgrid中显示查询结果的问题？

[gogotrain]

窗体中有，一个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

[gogotrain]

忘了标注释了，
'清空按钮
Private Sub Command_c_Click(Index As Integer)
'查询按钮
Private Sub Command_s_Click(Index As Integer)
'返回按钮，返回主窗口
Private Sub Command_e_Click(Index As Integer)
'按钮组，实现一个英文键盘
Private Sub Command1_Click(Index As Integer)

• 打赏
• 举报

回复 赞

[jam021]

关注,帮你顶!

• 打赏
• 举报

回复 赞

[supporter]

我也想帮你

• 打赏
• 举报

回复 赞

[mqmmx]

我想主要是那句查询，你把表结构和查询发到数据库版去请教那边的高手比较好。

• 打赏
• 举报

回复 赞

[cbzdream]

太长了。。。

• 打赏
• 举报

回复 赞

[gogotrain]

程序中使用的SQL语句在SQL查询分析器中使用是没有问题的，我主要是对ADO的使用非常的弱。
原来我是用PB的，现在没办法必需用ADO了。还请大家多费心帮忙看一看，程序写得很乱也没写注释。
不好意思了。

• 打赏
• 举报

回复 赞