Taking User Input in SQL SELECT TOP query

Question

I need to acquire the sum of the least n number of values from a table

Dim lot As String = "SELECT SUM(x2) AS x3 FROM (SELECT TOP '" & _TextBox2.Text & "' x1 As x2  
                     FROM (
                           SELECT SharePrice As x1 
                           FROM  Shares  
                           WHERE(Company = '" & _TextBox1.Text & "' AND Availability = True)  
                           ORDER BY SharePrice ASC  )
                           )"

I dont have problems witha nything else but the TOP '" & _TextBox2.Text & "' part Does the SELECT TOP really take parameters in?

I can replace the textbox reference with a hard coded integer and it works. But i want to make it run In visual Basic with user input

MDiesel · Accepted Answer · 2014-07-08 14:45:44Z

3

You cannot use single quotes around the number in the TOP n portion of the query.

Change:

SELECT TOP '" & _TextBox2.Text & "'

To:

SELECT TOP " & _TextBox2.Text & "

I also recommend that you use a parametrized query to help prevent SQL Injection.

answered Jul 8, 2014 at 14:45

MDiesel

2,65713 silver badges15 bronze badges

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

6 Comments

Vulcronos Over a year ago

I don't think you can parameterize the number of rows passed to top. He can paramterize the where clause but you may want to mention in your answer so the OP doesn't try to parameterize both and get another error.

PotatoManager Over a year ago

This worked for me! I will also consider using parameterized queries

Conrad Frix Over a year ago

@Vulcronos parameterizing TOP works fine

Vulcronos Over a year ago

@ConradFrix Really? I am honestly surprised. Thanks for correcting me.

Conrad Frix Over a year ago

@Vulcronos of course I am assuming SQL Server 2005 or later. If the OP is using SQL Compact or Access you're stuck doing dynamic sql

|

Conrad Frix · Accepted Answer · 2014-07-08 14:55:03Z

It's a lot easier to do this as a parameterized query. In addition to preventing sql injection attacks it avoids the problems of when to put quotes around things.

In order to get Top to work you will need to surround the parameter name with parens

Dim lot As String =  " SELECT SUM(x2) AS x3 
                       FROM 
                     (SELECT TOP (@Top) x1 As x2 
                      FROM (SELECT 
                        SharePrice As x1 
                     FROM  Shares 
                       WHERE    
                   (Company = @CompanyName 
                     AND Availability = True)  
                       ORDER BY SharePrice ASC  ))"


  Dim cmd as SqlCommand = new SqlCommand (connection, lot)
  cmd.AddWithValue (@Top, Int32.Parse(_TextBox2.Text))
  cmd.AddWithValue (@CompanyName, _TextBox1.Text)

Collectives™ on Stack Overflow

Taking User Input in SQL SELECT TOP query

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related