VB.NET - 为什么这个 sql 命令作为字符串而不是参数化查询工作? [英] VB.NET - Why does this sql command work as a string but not as a parameterized query?

问题描述

Dim command_sbsp As New OleDbCommand("SELECT * FROM SNOMED_ORG_INFO WHERE ConceptID=@ID AND Genus='SALMONELLA' AND SubSpecies=@Subsp", SNOMED_DB)

For Each match As Long In choices
 command_sbsp.Parameters.Clear()
 command_sbsp.Parameters.AddWithValue("@Subsp", word)
 command_sbsp.Parameters.AddWithValue("@ID", match)

以上方法无效.查询尝试说没有任何匹配的内容.但是当我尝试将它作为简单的字符串连接时，它工作正常.

The above doesn't work. The query attempt says there's nothing that matches that. But when I try it as a simple string concatenation, it works fine.

command_sbsp = New OleDbCommand("SELECT * FROM SNOMED_ORG_INFO WHERE ConceptID=" + match.ToString + " AND Genus='SALMONELLA' AND SubSpecies='" + word + "'", SNOMED_DB)

我自己和一位同事对此进行了很多关注，我们无法弄清楚为什么它不起作用.问题在于@ID 的值(因为当我将另一个参数化时它仍然有效).有趣的是，就在它上面的几行代码中，我有一个不同的查询，它为相同的 ID 设置参数化值，使用与 For Each 循环从中获取匹配变量的相同选择列表.

Myself and a coworker have stared at this quite a lot and we can't figure out why it's not working. The problem is in the value for @ID (since it still works when I leave the other one parameterized). The funny thing is, Just a few lines of code above it I have a different query that sets a parameterized value for the same ID, using the the same choices list that the For Each loop is getting the match variable from.

Choices 是一个长列表，当我使用choices(0) 在查询中参数化ID 时，它可以工作.但是现在在这个循环中，我有新的比赛很长，它不想让它对我有用.有什么想法吗?

Choices is a list of longs, and when I use choices(0) to parameterize ID in a query, it works. But now down here in this loop I have the new match long, and it doesn't want to make it work for me. Any thoughts?

推荐答案

尽管 OLEDB 类可以使用命名参数，但它实际上只是使用了一个?"标记在背景中，因此参数按 INDEX 顺序排列.您的 sql 在 Subsp 变量之前出现了 ID 变量.

Despite the fact that the OLEDB class can use named parameters, it is actually just using a '?' marker in the background, so the parameters are in INDEX order. Your sql has the ID variable occurring before the Subsp variable.

切换它，像这样:

For Each match As Long In choices
 command_sbsp.Parameters.Clear()
 command_sbsp.Parameters.AddWithValue("@ID", match)
 command_sbsp.Parameters.AddWithValue("@Subsp", word)

这篇关于VB.NET - 为什么这个 sql 命令作为字符串而不是参数化查询工作?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！