CODEWITHSUNDEEP
sqlvb.net
cost
cost

Reputation: 4480

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)

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 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?

Upvotes: 1

Views: 823

Answers (1)

LarsTech
LarsTech

Reputation: 81620

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.

Switch it around, like so:

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

Upvotes: 5

Related Questions