Unable to use parameter in SQL Statement

Question

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"

Henrik Sozzi · Accepted Answer · 2019-10-08 22:16:20Z

1

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!)

answered Oct 8, 2019 at 22:16

Henrik Sozzi

264 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SpyingSphinx Over a year ago

Henrik, good point on the converting, this eleviates the risk of sql injection! Ive amended my code to this format and viola all is running fine.

Lukasz Szozda · Accepted Answer · 2019-10-08 19:14:05Z

1

You should use brackets:

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

db<>fiddle demo

answered Oct 8, 2019 at 19:14

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

1 Comment

SpyingSphinx Over a year ago

unfortunately this doesnt work, as others have later said i dont think you can put a parameter as an expression. Thanks though, it made another bit of my code work :)

asmgx · Accepted Answer · 2019-10-08 22:18:56Z

0

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

answered Oct 8, 2019 at 22:18

asmgx

8,14819 gold badges102 silver badges184 bronze badges

Collectives™ on Stack Overflow

Unable to use parameter in SQL Statement

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related