getting sql query in c# code

Dear All

I am a beginner and looking for some help. I have a database with just one column (some names). That is the primary key aswell. Because I want the names to be unique.

I used a grid view control to display the data and included the insert functionality in the grid view by using some code and the part of the code that does the insert is

1public static void Insert(Categories category)2 {3string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;4using (IDbConnection cn =new SqlConnection(connectionString))5 {6 cn.Open();78 IDbCommand cmd =new SqlCommand();910 cmd.CommandText ="INSERT INTO Categories (CategoryName) VALUES " +11"('" + category.CategoryName +"')";1213 cmd.Connection = cn;14 cmd.ExecuteNonQuery();15 }16 }

Question: when someone tried to enter a new name which already exists in the database it throws an error page which is what I want but is there a way to be able to display the user a message sayin g that he/she has entered a name that already exists and hence they need to try a different name? instead of the ugly error page?

Thank you in advance,

Prasad.

Yes. You need to add a Try... Catch block to catch the exception, and handle it:

public static void Insert(Categories category)
{
string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
using (SqlConnection cn = new SqlConnection(connectionString))
{
try
{
SqlCommand cmd = new SqlCommand();

cmd.CommandText = "INSERT INTO Categories (CategoryName) VALUES (@.CategoryName)
cmd.Parameters.AddWithValue("@.CategoryName", category.CategoryName);

cmd.Connection = cn;
cn.Open();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Label1.Text = "That value already exists. Please try another.";
}
}

I have also changed your code to use parameters, which is best practice. This means you don't have to worry about people entering words with apostrophes causing errors, or Sql Injection.

Also, you should ideally query the database to see if the name exists prior to attempting the insert. Throwing exceptions is expensive on the server.

|||

thank you. Hmm..I knew the try catch solution but I was just thinking...that the try catch would catch any exception that came from insert right?

For example if there was some other strange problem in doing the insert, it would still say "name already exists try another name!" which will be kind of strange.

lastly, Thank you for the modified code, I am a newbie and still learning, thanks for the tip! However, when I compile your code, it says

System.Data.IDataParameterCollection' does not contain a definition for 'AddWithValue'

can you please tell me how to check if that name already exists? some code would help please.

something like doing a count and on that particular name and see if count is > 1?

|||

Are you using version 1.1? If so, change that to cmd.Parameters.Add(...). AddWithValue was added to version 2.0.

Instead of hardcoding the error message, you could also use ex.Message

Label1.Text = ex.Message;

However, sometimes the error message might be too obscure for the user to understand. The most likely error will result from an attempt to insert a duplicate value. That's why I recommended that you select the value of the submitted entry first to see if it exists. If it does, abort the insert and show a message. If not, let the insert run.

|||

my mistake, I had not replaced the IDBcommand with SqlCommand

ok now coming to the second problem,

I donot know how to count the number of times the name appears in the database!

can you get me started with the code!

|||

The SQL is "Select Count(*) AS TheCount From Categories Where CategoryName = @.CategoryName"

int theCount = (int)cmd.ExecuteScalar();

Then, if theCount is more than 0, that means there is a least one row already in the database. If it doesn't exist, the value of theCount will be 0.

|||

"For example if there was some other strange problem in doing the insert, it would still say "name already exists try another name!" which will be kind of strange."

That is because you have this column as the Primary Key. The Primary Key has to be unique (ie No Duplicates)

|||

You should use a stored procedure for this kind of operations. There you can first write the code for checking for duplicate values like below.

ifexists (select 1from Categorieswhere CategoryName = @.categoryName)beginraiserror ('The provided category name already exists' , 16 , 1 )with nowaitreturnendelsebegininsert Categories (CategoryName)values (@.categoryname)end

This is just a snap of what can be included in the SP. Look at the line where raiserror function is used. You'll still have to write the SP execution code in a try catch block, but you're sure that the error stating "The provided category name already exists" is generated by your SP and all other errors are because of other problems.

For a general help regarding executing SP from code visithttp://forums.asp.net/t/1165758.aspx.

Hope this will help.