Parse textbox words for query values

  • Thread starter Thread starter Yukon Tu
  • Start date Start date
Y

Yukon Tu

I've created a very simple asp.net web form (via visual studio 2008) that
has a text box and a submit button. I want to be able to paste a bunch of
'words' into the textbox and have the words used in a select statement.
Something like

SELECT name, address, status
FROM StatsInfo
WHERE status IN (textbox-word1, textbox-word2, textbox-word3,...)

I've got something simple working where a single word can be queried, but I
can't find a way to parse the contents of the textbox so the query searches
for each word. Do you have or know of any examples you could share that
would clue me into how to do this?
 
You can "parse" the textbox value using "SPLIT" or regular
expressions.... "split" is the easier way to do it....

Assuming the "separator" is a comma (,):

string[] words=textbox1.Text.Split(',');

then you just enumerate the array and built your SQL statement.

If you are using Stored Procedures... well... the idea is the same...
just use whatever syntax you have available in you DB Server.

Daniel.
 
If you merely want words and you want a rather efficient method in SQL
Server, turn then words into XML and then use the XML features in SQL
Server. The same can be done with Oracle, of course.

With the execute sql stored procedure, you can build the statement and run
it. You can also set up the SQL statement in your .NET application, but you
have to parameterize or risk ending up with SQL injection.

--
Peace and Grace,
Greg

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

************************************************
| Think outside the box! |
************************************************
 
The text that goes in the textbox will come from a control-v paste that could
been copied from a word doc, excel spreadsheet, text file, etc. I've no way
of controling the input other than the assumption that the items will be
separated by spaces and/or newlines. One example would be to paste in a list
of serial numbers, click submit and the page returns the name of the items
associated with each serial number. Since the audience is pretty small and
access will be restricted, I'm not concerned about any sql injection issues,
(I do understand and accept the risk). I just need the functionality and if
the solution protects against injection it's a bonus. Since I'm new at
Visual Studion and ASP.net, any verbose example would be grealy appreciated.
The backend db is MSSQL, and I don't want to use stored procedures.

Thanks.
 
Yukon said:
I've created a very simple asp.net web form (via visual studio 2008) that
has a text box and a submit button. I want to be able to paste a bunch of
'words' into the textbox and have the words used in a select statement.
Something like

SELECT name, address, status
FROM StatsInfo
WHERE status IN (textbox-word1, textbox-word2, textbox-word3,...)

I've got something simple working where a single word can be queried, but I
can't find a way to parse the contents of the textbox so the query searches
for each word. Do you have or know of any examples you could share that
would clue me into how to do this?
Click to expand...

The textbox has a text property -- textbox.text. Textbox.text is 'string'.

You pass the Textbox.text list of words into a method.


private void SeperateWordsInString(string instring)
{

}

You then use a string.split. Something has to be a delimiter in the
instring of words like a 'space' character. But something has to be the
delimiter to separate the words.

Then you use the code in the link.

http://msdn.microsoft.com/en-us/library/ms228388(VS.80).aspx

You're going to have to go into some kind of foreach loop with each
iteration selecting a word out of the array.

The for each itteration, you're going to build part of the T-SQL Select
statement based word in the array for the Where.

In other words, you're going to dynamically build the select statement.

string strsql = "Select fld1, fld2 From table Where ("

then its this

foreach(string word in words)
{
strsql = strsql + word + ","
}

At the end, you complete the statement.

strsql = strsql + ")"

You may have to do an IndexOfLast(",") to strip out the last ",".

By building the T-SQL statement programmically that's how you do it.

You can put the code at the stop of you're method at the top of your
code tp call execute against MySQL.

This is just an example. But what if a "'" is part of the word. What are
you going to do to deal with it, because you're for sure get a syntax
error with the Select? I also thought 'sometext' had to have quotes
around the text.

This is VB and the Replace statment and the Replace is used in C# .net too.

sql01 = "UPDATE EquipmentTbl SET "
sql01 = sql01 & "SerialNumber = '" & request.form(strSerialNum) & "', "
sql01 = sql01 & "Description = '" & request.form(strDesc) & "', "
sql01 = sql01 & "Location = '" & request.form(strLoc) & "', "

sql01 = sql01 & Replace(request.form(strLoc),"'","''") & "', "
 
Then you can use regular expressions.

A basic example would be:

*************
string text = "this is a long text, another word "; // <-- This
emulates your TEXTBOX
string sql = "select * from table where field in ({0})";
StringBuilder where = new StringBuilder();

Regex rx = new Regex(@"\b\w+\b");
MatchCollection matches = rx.Matches(text);
foreach (var item in matches)
{
where.Append(String.Format((where.Length == 0) ? "'{0}'" : ",'{0}'",
item));
}

Console.WriteLine(String.Format(sql, where));
*************

The regular expression I am using is just an example... you might need
to change it to comply with you internal requirements... but at least it
gives you a starting point.

You can read more about regex at:

http://msdn.microsoft.com/en-us/library/hs600312.aspx

If you are wondering why I am using StringBuilder instead of String....
check the documentation:

http://msdn.microsoft.com/en-us/library/system.text.stringbuilder.aspx

Daniel.
 
Back
Top