0

I'm in the process of making an ASP.net web application which will allow users to paste values into a multi-line text box and press a submit button. On button click, this will run a database query based on the product codes they've input into the textbox.

I've managed to get this working for one code in the text box. However, What I'm struggling to do is allow for multiple product codes in the text box. How do I split the values in the text box, to be comma separated and use in my ACCESS SQL query?

The code below is what I used for a single line in a text box, which works fine.

string selectString = "SELECT Patt1, Description, Oversize FROM Item WHERE Patt1 = '" + TextBox1.Text + "'";

Essentially, what I'd like to do is use the same kind of query but like below:

string selectString = "SELECT Patt1, Description, Oversize FROM Item WHERE Patt1 sInStatement";

Where sInStatement would be "IN (1234, 1235, 1236, 1237) **The codes here would be found in TextBox1 which I'd need to separate out with a comma, etc.

Can this be done?

jamesc100
  • 137
  • 1
  • 6
  • 14
  • Are users typing the comma or are they putting codes on new lines? You simply split on either new line or comma or whatever delimiter. But textbox is the wrong choice of controls, too easy for a user to enter bad data. Use some kind of list control. – Crowcoder Sep 29 '16 at 12:15
  • The users would be copying from an excel sheet into this text box and they would appear on each line in the text box. Would the list control support users pasting in data? – jamesc100 Sep 29 '16 at 12:19
  • Paste support of excel data would probably require a custom control or some javascript. – Crowcoder Sep 29 '16 at 12:23

3 Answers3

0

It can be done but it is a bad idea to pass user input directly into SQL queries.

Read about SQL injection

It is better to use parameters.

Trifon
  • 1,081
  • 9
  • 19
  • Thanks Trifon. For this scenario, the web application will only be used locally i.e. I don't plan to deploy this to a web site. I will be using this on a local network only. The users wouldn't be able to use SQL injection to hack into the DB – jamesc100 Sep 29 '16 at 12:10
  • In an enterprise it tends o be the case that when a database (spreadsheet, wiki, etc) proves to be useful then more people/applications start to want to accessing it. Best to start on the right path and use views and procedures you can grant/deny privileges on. – onedaywhen Sep 29 '16 at 12:45
  • Even so it is not good idea what are you trying to do. Have a look here for a solution with a split function: http://stackoverflow.com/a/16872537/3260013 – Trifon Sep 29 '16 at 13:06
0

You can modify the SQL:

string selectString = "SELECT Patt1, Description, Oversize FROM Item WHERE Patt1 IN ("' + string.Join("','", TextBox1.Text.Replace(" ", "").Split(',')) + "')";

If the values are numerics:

string selectString = "SELECT Patt1, Description, Oversize FROM Item WHERE Patt1 IN (" + string.Join(",", TextBox1.Text.Split(',')) + ")";

//or simply:

string selectString = "SELECT Patt1, Description, Oversize FROM Item WHERE Patt1 IN (" + TextBox1.Text + ")";
Gustav
  • 53,498
  • 7
  • 29
  • 55
  • Thanks Gustav. I have tried the above but it doesn't seem to display any data as I'd expect. When I input two or more codes, it shows no data. If I put a comma in manually into the text box at the end of each row, the first code is displayed but no more data after that – jamesc100 Sep 29 '16 at 12:06
  • Ideally, what I would be looking for is a way of putting all the codes that are in the text box, into a string that I can then pass to the SQL statement – jamesc100 Sep 29 '16 at 12:08
  • I'm afraid that just causes an error. The product codes are stored as short text in the ms-access DB so wouldn't be classed as numeric – jamesc100 Sep 29 '16 at 12:53
  • Oh, then you probably have to remove spaces. See edit. – Gustav Sep 29 '16 at 13:23
  • Thanks Gustav. Unfortunately, it still show no data. When I add a comma manually to the end of each product code in the text box, it displays data for the first row and then nothing else...But with no commas manually added, there is no data at all. – jamesc100 Sep 29 '16 at 13:34
  • Well, study `selectString` and adjust the code to the desired output. It is, however, confusing when you state that the string should look like `"IN (1234, 1235, 1236, 1237)"` and at the same time tell that the content of the field is text; it doesn't match. – Gustav Sep 29 '16 at 13:45
0

Consider a stored procedure with as many arguments as optional parameters you want to be able to pass. Use an out of range default value for the optional parameters e.g. -999.

The following SQL DDL requires ANSI-92 Query Mode:

CREATE PROCEDURE GetItemsByPatt1s
( :arg1 INT,
  :arg2 INT = -999,
  :arg3 INT = -999,
  :arg4 INT = -999 ) AS
SELECT Patt1, Description, Oversize
  FROM Item 
 WHERE Patt1 IN ( :arg1, :arg2, :arg3, :arg4 );
onedaywhen
  • 55,269
  • 12
  • 100
  • 138