我正在研究食谱库 . 我有一个包含3个列表框(lstIngredient,lstSelected,lstRecipe)和一个查找按钮的表单 . lstIngredient填充了存储在数据库中的成分名称 .
当用户在lstIngredient中选择(双击)一个名称时,它将被列为lstSelected . 之后,当用户点击查找按钮时,它将在数据库中找到食谱使用这些成分的内容 . 数据库中的表如下所示:
id | recipe | ingredient
1 | spaghetti | tomato sause
2 | spaghetti | cheese
3 | spaghetti | hotdog
4 | burger | bread bun
5 | burger | burger patty
6 | burger | cheese
所以在这里,说用户选择 cheese , bread bun 和 hotdog (注意:这些项目是逐个选择的),这些将列在lstSelected中 . 单击“查找”按钮时,结果应为 burger 和 spaghetti . 如果您注意到,汉堡是列表中的第一项......我已经做了以下事情:
Call Connect()
Dim dt As New DataTable
Dim cmd As New MySqlCommand
Try
lstRecipe.Items.Clear()
cmd.Connection = myConn
cmd.CommandText = "select recipe from cook_book where ingredient = @item"
cmd.Parameters.AddWithValue("item", lstSelected.Items)
myReader = myCmd.ExecuteReader
If (myReader.Read()) Then
myReader.Close()
myAdptr.SelectCommand = cmd
myAdptr.Fill(dt)
lstRecipe.DisplayMember = "recipe"
For Each row As DataRow In dt.Rows
lstRecipe.Items.Add(row("recipe"))
Next
Dim builder As New StringBuilder()
builder.Append("select distinct recipe from cook_book where")
For y As Integer = 0 To lstSelected.Items.Count - 1
Dim parameterName As String = "@item" & y.ToString()
If y <> 0 Then
builder.Append("and ")
End If
builder.Append(parameterName)
builder.Append(" in (select ingredient from cook_book where recipe = i.recipe) ")
cmd.Parameters.AddWithValue(parameterName, lstSelected.Items(y))
Next
cmd.CommandText = builder.ToString()
End If
Catch ex As Exception
MsgBox(ex.Message)
End Try
cmd = Nothing
myReader = Nothing
myConn.Close()
Call Disconnect()
代码在btnFind_Click事件下 . 我没有收到任何错误,但它没有按照它应该的方式运行 . 它只找到用户选择的最后一种成分的最后一个配方 .