CODEWITHSUNDEEP
sqlvb.net
SpyingSphinx
SpyingSphinx

Reputation: 78

Unable to use parameter in SQL Statement

I'm trying to select a certain number of records from an Access table using VB.Net, however I'm having trouble setting the parameters. The code is listed below:

Dim sqlQry As String
Dim iNumber As Integer = Convert.ToInt32(txtNoMemberships.Text)

sqlQry = "SELECT TOP @MemberNo [ID] FROM tblMembership WHERE [Taken] IS NULL"

Dim objConn As New OleDb.OleDbConnection
Dim cmd As New OleDb.OleDbCommand
Try
    objConn.ConnectionString = dbConnection
    objConn.Open()
    cmd.Connection = objConn
    cmd.CommandText = sqlQry
    cmd.Parameters.AddWithValue("@MemberNo", iNumber)
    cmd.ExecuteNonQuery()
Catch ex As Exception
    MessageBox.Show(ex.Message)
End Try

If I change sqlQry to something like "SELECT 5 [ID] FROM tblMembership WHERE [Taken] IS NULL" then the command runs fine and I get no debug. But if I try to use parameters, I get the error:

The SELECT statement includes a reserved word or argument name that is misspelled or missing, or the punctuation is incorrect.

I know I could get away with it doing the following, but i thought it's best to err on the side of caution due to SQL injection:

sqlQry = "SELECT TOP " & iNumber & " [ID] FROM tblMembership WHERE [Taken] IS NULL"

Upvotes: 1

Views: 313

Answers (3)

asmgx
asmgx

Reputation: 8044

Try this instead

Dim sqlQry As String
Dim iNumber As Integer = Convert.ToInt32(txtNoMemberships.Text)

sqlQry = "SELECT TOP " & iNumber.ToString()  & " [ID] FROM tblMembership WHERE [Taken] IS NULL"

Dim objConn As New OleDb.OleDbConnection
Dim cmd As New OleDb.OleDbCommand
Try
    objConn.ConnectionString = dbConnection
    objConn.Open()
    cmd.Connection = objConn
    cmd.CommandText = sqlQry
    cmd.ExecuteNonQuery()
Catch ex As Exception
    MessageBox.Show(ex.Message)
End Try

Upvotes: 0

Henrik Sozzi
Henrik Sozzi

Reputation: 26

As HardCode already said I think too it's not possible to use a parameter for the TOP N clause of the SELECT command. So, why not to concatenate the string to not use the parameter?

sqlQry = "SELECT TOP " & Convert.ToInt32(txtNoMemberships.Text).ToString() & " [ID] FROM tblMembership WHERE [Taken] IS NULL"

SQL Injection is not an issue as we're converting txtNoMembership to an Int32 then back to a String (remember to never concatenate strings without any cure on them first!)

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 176164

You should use brackets:

sqlQry = "SELECT TOP (@MemberNo) [ID] FROM tblMembership WHERE [Taken] IS NULL"

db<>fiddle demo

Upvotes: 1

Related Questions