Insert via C# / Ado.net

[DaveRook]

I have a web page which a user can insert their information (First Name, Last Name and Email) which works well. It writes this to an Access database.

All I want to do is prevent the user entering an email address which already exists! This would then ensure none of the fields were updated. (eg First Name, Last Name And Email). Basically, how do I avoid creating duplicates in the table!

Thanks

Dave

[RedSon]

Select the rows that match the email address, if you return > 0 then it already exists, and you can ignore it.

[Plater]

Set the Email field in the table to be unique? the ODBC driver will throw an exeption if a duplicate is attempted to insert

[DaveRook]

Thank you both for your replies. I set the table up to have a unique field (indexed according to Access) and this works by throwing the error which I can catch.

Red Son, how do match a row? I've worked out how to count rows, but not how to match. The code I use to match is

ds.Tables["BoardToBoa rd"].Rows.Count;

I would love to do ds.Tables["BoardToBoa rd"].Rows.["Email"] =="xxx";

Any suggestions?

Thank you again

Dave

[Curtis Rutland]

I think he meant to do a select against your table and count the number of rows returned

SELECT count(*) FROM table WHERE email = @email

and if the return value is zero, the email address is unique. Otherwise it's not.

Or you could do a foreach loop in your table:

foreach (DataRow r in ds.Tables["BoardToBoard"])
{
  if(r["Email"] == newEmailAddress)
    //at this point, the email is non-unique and you should set some flag or return true
}

It would be better to do this on the database side though.

[Plater]

You could also use the .Select() on the datatable.
[code=c#]
DataRow[] matchingrows= ds.Tables["BoardToBoa rd"].Select("[Email] = "+EmailToCheck) ;
if(matchingrows .Length == 0)
{//value is unique
}
[/code]

[Curtis Rutland]

Good point Plater, I forgot about that. Which do you think would be more efficient? Passing it off to the OleDb engine or doing it in memory from your program?

[DaveRook]

Again, thank you everyone for your helpful suggestions. This has answered many questions I had as well as my main question. Thank you

[RedSon]

Good point Plater, I forgot about that. Which do you think would be more efficient? Passing it off to the OleDb engine or doing it in memory from your program?

I vote for passing it to OleDb.