Take the 2-minute tour ×

I know I've asked a similar question before but I still can't get this working.

I'm using C# in VS2010

What I'm doing is creating an application that takes user entered words in textbox1 clicks a button and all words with "*" in front of them print in textbox2

I'm using a SQL Server database to do this because I want the "*" words to be stored in the database so I can later add a counter to show how many times that word has been entered.

For example:

  • User enters the cat is *brown in textbox1, presses a button, then *brown appears in textbox2

My code for SQL Server is this:

SqlConnection con = new SqlConnection(@"Server=.\SQLEXPRESS;Database=StoreList;Integrated Security=sspi");

String queryStr = "SELECT item FROM StoreList WHERE item LIKE '*%'";
SqlCommand com = new SqlCommand(queryStr, con);
SqlDataReader sdr = com.ExecuteReader();

while (sdr.Read())
   this.textbox2.Text = sdr.GetValue(0).ToString();


What I need help with is how to further that code to get the app working. So I don't know how to make it get the word from textbox1.

Any help guys?

share|improve this question
Any other answers would be appreciated greatly as nothing i've tried works –  user1300788 Apr 20 '12 at 11:05
sadface...... –  user1300788 Apr 20 '12 at 11:05
Please have a look at my answer I have edited it so that you can also do it without the stored procedure, using 2 separate lines of SQL –  Lex Apr 20 '12 at 12:21

2 Answers 2

Ignoring your code example it seems to me there are a couple of things going on here.

  1. You want to built a list of star'd words entered into textbox1
  2. You want to output the star'd word list to textbox2
  3. Store a frequency list of the stared words

Points 1 and 2 can be done without interaction with the database. Perhaps using a regex to pattern match words being with *.

Point 3 should be a case executing a sql statement that either inserts new words with a count of 1 or updates the count of known words by 1.

EDIT following comments

Point 1 - build a list of words

var matches = new System.Text.RegularExpressions.Regex("\\*[^ \t]+").Matches(textbox1);

foreach (Match match in matches)

Point 3 - store words for frequency list

//using statement to ensure connection is cleaned up correctly
using (SqlConnection connection = new SqlConnection(@"Server=.\SQLEXPRESS;Database=StoreList;Integrated Security=sspi"))

    foreach (Match match in matches)
        var command = new SqlCommand("UpdateWordFrequency", connection);

        command.CommandType = System.Data.CommandType.StoredProcedure;
        command.Parameters.Add(new SqlParameter("@word", match.Value));


Where the procedure looks like this

create procedure UpdateWordFrequency
    @word varchar(50)
if not exists (select 1 from WordList where word = @word)
    insert into WordList (word, frequency) values (@word, 0)

update WordList set frequency = frequency + 1 where word = @word

And the WordList table has 2 fields word (varchar) and frequency (int)

Point 2 - output star'd words

Well this should now be a case of selecting from the WordList table

share|improve this answer
Yeah I understand how to do this without interaction with the database. But the whole point of the application is so that it uses the database so that's what i'm trying to do –  user1300788 Apr 20 '12 at 10:25
You mean the whole point is the frequency list should be stored and retrievable from the database? –  Chris Moutray Apr 20 '12 at 10:29
Yeah so if I enter "*brown" twice. It's stored in the database and counted 2 times. so it would print "*brown 2" –  user1300788 Apr 20 '12 at 10:31
And what about existing entries made in the past e.g. Monday you enter *brown twice then Tuesday you enter *brown again - What you you expect to see on the Tuesday brown - 1 or brown - 3 (ie includes entries made Monday)? –  Chris Moutray Apr 20 '12 at 10:38
It would show 3 *browns. So i'm permanently adding them to the database –  user1300788 Apr 20 '12 at 10:41

It might be better to use a stored procedure for this.

By using a stored procedure you can do something like this:

create procedure Insert_StoreList
@Word nvarchar(50)

insert StoreList (
item )
values (
@Word )

SELECT item FROM StoreList WHERE item LIKE '*%'

Then you can use the following in C#

SqlCommand com = new SqlCommand("Insert_StoreList", con);
com.CommandType = CommandType.StoredProcedure;
com.Parameters.Add(new SqlParameter("@Word", Textbox1.Text));
SqlDataReader sdr = com.ExecuteReader();

Edit - If you won't like to use stored procedures you can do this with 2 lines of SQL too, The first one would be an ExecuteNonQuery to get the value into the Table, and the second one would be to select all the words from the table.

The first sql would be:

String queryStr = "insert StoreList (item) values (" + Textbox1.Text + ")"; 

The second sql would be:

share|improve this answer
If I was to use a stored procedure, would I use the created database or is this creating one itself? –  user1300788 Apr 20 '12 at 10:18
Hmm, I thought my code would be correct but I think it's just needing amended a little so it takes the word from textbox1 –  user1300788 Apr 20 '12 at 10:22
I disagree about Stored Procedures, they are very useful, and if set up correctly also a big help preventing SQL injections. –  Lex Apr 20 '12 at 10:25
@Lex SQL injections can be prevented if you check the inputs correctly, there are always going to be problems with injection and other security issues but just using a stored procedure won't protect against it. Plus when editing your code, when you want to look at what a function does if it needs modification, you should be able to see it right there, rather than having to go into the SQL and change it. –  JakeJ Apr 20 '12 at 10:34
@JakeJ using stored procedures or not is a personal preference. My work involves a lot of POS data needed to be stored and retrieved from many tables, having a stored procedure makes that a lot easier. And here we have several layers between the database and the actual data displayed, so editing the code you would still have to look at several layers of code. –  Lex Apr 20 '12 at 10:38

Your Answer


