By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,652 Members | 989 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,652 IT Pros & Developers. It's quick & easy.

vb.net/c# : Check duplicate record before inserting in database

P: 25
Hi,
I want to check in the below code if the email exists in the database before inserting. How can I do that?

Expand|Select|Wrap|Line Numbers
  1. Dim EmailAlertDataSource As New SqlDataSource()
  2.         EmailAlertDataSource.ConnectionString = ConfigurationManager.ConnectionStrings("DB_EmailAlertConnectionString1").ToString()
  3.  
  4.         EmailAlertDataSource.InsertCommandType = SqlDataSourceCommandType.Text
  5.         EmailAlertDataSource.InsertCommand = "INSERT INTO Table_Emails (EmailAccount, EmailAccountDateEntered, EmailCategory) VALUES (@EmailAccount, @EmailAccountDateEntered, @EmailCategory)"
  6.         EmailAlertDataSource.InsertParameters.Add("EmailAccount", tbemail.Text) 'HOW TO CHECK THIS EMAIL EXISTS IN DATABASE??
  7.         EmailAlertDataSource.InsertParameters.Add("EmailAccountDateEntered", DateTime.Now.ToString("f", New CultureInfo("en-GB")))
  8.  
  9.         Dim MyItem As ListItem
  10.         Dim catSelected As String = ""
  11.         Dim catSelected1 As String = ""
  12.         'Dim r As Integer
  13.         For Each MyItem In CheckBoxList1.Items
  14.             If MyItem.Selected = True Then
  15.                 catSelected = catSelected & MyItem.Text & ","
  16.                 Dim i As Integer = Len(catSelected)
  17.                 catSelected1 = Mid(catSelected, 1, i - 1)
  18.                 'lblMessage.Text = catSelected1
  19.             End If
  20.         Next
  21.  
  22.         EmailAlertDataSource.InsertParameters.Add("EmailCategory", catSelected1)
  23.  
  24.         Dim rowsAffected As Integer = 0
  25.  
  26.         Try
  27.  
  28.             rowsAffected = EmailAlertDataSource.Insert()
  29.  
  30.         Catch ex As Exception
  31.  
  32.  
  33.             'Response.Write("<p>strTime:" & strTime)
  34.             lblError.Text = "An error has occured/"
  35.             lblError.Text += ex.Message()
  36.  
  37.  
  38.             'TODO: To create this page
  39.             'Server.Transfer("email_problem_new.aspx")
  40.  
  41.         Finally
  42.  
  43.             EmailAlertDataSource = Nothing
  44.  
  45.         End Try
  46.  
  47.         If rowsAffected <> 1 Then
  48.  
  49.             'Server.Transfer("email_problem_new.aspx")
  50.  
  51.         Else
  52.  
  53.             'Server.Transfer("email_confirm_new.aspx")
  54.  
  55.         End If
  56.  
Oct 24 '07 #1
Share this Question
Share on Google+
3 Replies


Shashi Sadasivan
Expert 100+
P: 1,435
create a select command which gets the records based on the email.
If the number of records is more than zero, then the email already exists !!!
Oct 24 '07 #2

dip_developer
Expert 100+
P: 648
Hi,
I want to check in the below code if the email exists in the database before inserting. How can I do that?

Expand|Select|Wrap|Line Numbers
  1. Dim EmailAlertDataSource As New SqlDataSource()
  2. EmailAlertDataSource.ConnectionString = ConfigurationManager.ConnectionStrings("DB_EmailAlertConnectionString1").ToString()
  3.  
  4. EmailAlertDataSource.InsertCommandType = SqlDataSourceCommandType.Text
  5. EmailAlertDataSource.InsertCommand = "INSERT INTO Table_Emails (EmailAccount, EmailAccountDateEntered, EmailCategory) VALUES (@EmailAccount, @EmailAccountDateEntered, @EmailCategory)"
  6. EmailAlertDataSource.InsertParameters.Add("EmailAccount", tbemail.Text) 'HOW TO CHECK THIS EMAIL EXISTS IN DATABASE??
  7. EmailAlertDataSource.InsertParameters.Add("EmailAccountDateEntered", DateTime.Now.ToString("f", New CultureInfo("en-GB")))
  8.  
  9. Dim MyItem As ListItem
  10. Dim catSelected As String = ""
  11. Dim catSelected1 As String = ""
  12. 'Dim r As Integer
  13. For Each MyItem In CheckBoxList1.Items
  14. If MyItem.Selected = True Then
  15. catSelected = catSelected & MyItem.Text & ","
  16. Dim i As Integer = Len(catSelected)
  17. catSelected1 = Mid(catSelected, 1, i - 1)
  18. 'lblMessage.Text = catSelected1
  19. End If
  20. Next
  21.  
  22. EmailAlertDataSource.InsertParameters.Add("EmailCategory", catSelected1)
  23.  
  24. Dim rowsAffected As Integer = 0
  25.  
  26. Try
  27.  
  28. rowsAffected = EmailAlertDataSource.Insert()
  29.  
  30. Catch ex As Exception
  31.  
  32.  
  33. 'Response.Write("<p>strTime:" & strTime)
  34. lblError.Text = "An error has occured/"
  35. lblError.Text += ex.Message()
  36.  
  37.  
  38. 'TODO: To create this page
  39. 'Server.Transfer("email_problem_new.aspx")
  40.  
  41. Finally
  42.  
  43. EmailAlertDataSource = Nothing
  44.  
  45. End Try
  46.  
  47. If rowsAffected <> 1 Then
  48.  
  49. 'Server.Transfer("email_problem_new.aspx")
  50.  
  51. Else
  52.  
  53. 'Server.Transfer("email_confirm_new.aspx")
  54.  
  55. End If
  56.  
you can check for the record with IfExists keyword in SQL before inserting if you are using sql-server as your database

syntax would be like....
IfExists(SELECT email_id FROM myTable WHERE email_id=2)
Oct 25 '07 #3

kunal pawar
100+
P: 297
Hi,
I suggest you to make that column as Primary key. So its through Exception catch tht one and do your action
Oct 25 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.