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

[milecimm]

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

Dim EmailAlertDataSource As New SqlDataSource()
        EmailAlertDataSource.ConnectionString = ConfigurationManager.ConnectionStrings("DB_EmailAlertConnectionString1").ToString()
 
        EmailAlertDataSource.InsertCommandType = SqlDataSourceCommandType.Text
        EmailAlertDataSource.InsertCommand = "INSERT INTO Table_Emails (EmailAccount, EmailAccountDateEntered, EmailCategory) VALUES (@EmailAccount, @EmailAccountDateEntered, @EmailCategory)"
        EmailAlertDataSource.InsertParameters.Add("EmailAccount", tbemail.Text) 'HOW TO CHECK THIS EMAIL EXISTS IN DATABASE??
        EmailAlertDataSource.InsertParameters.Add("EmailAccountDateEntered", DateTime.Now.ToString("f", New CultureInfo("en-GB")))
 
        Dim MyItem As ListItem
        Dim catSelected As String = ""
        Dim catSelected1 As String = ""
        'Dim r As Integer
        For Each MyItem In CheckBoxList1.Items
            If MyItem.Selected = True Then
                catSelected = catSelected & MyItem.Text & ","
                Dim i As Integer = Len(catSelected)
                catSelected1 = Mid(catSelected, 1, i - 1)
                'lblMessage.Text = catSelected1
            End If
        Next
 
        EmailAlertDataSource.InsertParameters.Add("EmailCategory", catSelected1)
 
        Dim rowsAffected As Integer = 0
 
        Try
 
            rowsAffected = EmailAlertDataSource.Insert()
 
        Catch ex As Exception
 
 
            'Response.Write("<p>strTime:" & strTime)
            lblError.Text = "An error has occured/"
            lblError.Text += ex.Message()
 
 
            'TODO: To create this page
            'Server.Transfer("email_problem_new.aspx")
 
        Finally
 
            EmailAlertDataSource = Nothing
 
        End Try
 
        If rowsAffected <> 1 Then
 
            'Server.Transfer("email_problem_new.aspx")
 
        Else
 
            'Server.Transfer("email_confirm_new.aspx")
 
        End If
 

[Shashi Sadasivan]

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 !!!

[dip_developer]

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

Dim EmailAlertDataSource As New SqlDataSource()
EmailAlertDataSource.ConnectionString = ConfigurationManager.ConnectionStrings("DB_EmailAlertConnectionString1").ToString()
 
EmailAlertDataSource.InsertCommandType = SqlDataSourceCommandType.Text
EmailAlertDataSource.InsertCommand = "INSERT INTO Table_Emails (EmailAccount, EmailAccountDateEntered, EmailCategory) VALUES (@EmailAccount, @EmailAccountDateEntered, @EmailCategory)"
EmailAlertDataSource.InsertParameters.Add("EmailAccount", tbemail.Text) 'HOW TO CHECK THIS EMAIL EXISTS IN DATABASE??
EmailAlertDataSource.InsertParameters.Add("EmailAccountDateEntered", DateTime.Now.ToString("f", New CultureInfo("en-GB")))
 
Dim MyItem As ListItem
Dim catSelected As String = ""
Dim catSelected1 As String = ""
'Dim r As Integer
For Each MyItem In CheckBoxList1.Items
If MyItem.Selected = True Then
catSelected = catSelected & MyItem.Text & ","
Dim i As Integer = Len(catSelected)
catSelected1 = Mid(catSelected, 1, i - 1)
'lblMessage.Text = catSelected1
End If
Next
 
EmailAlertDataSource.InsertParameters.Add("EmailCategory", catSelected1)
 
Dim rowsAffected As Integer = 0
 
Try
 
rowsAffected = EmailAlertDataSource.Insert()
 
Catch ex As Exception
 
 
'Response.Write("<p>strTime:" & strTime)
lblError.Text = "An error has occured/"
lblError.Text += ex.Message()
 
 
'TODO: To create this page
'Server.Transfer("email_problem_new.aspx")
 
Finally
 
EmailAlertDataSource = Nothing
 
End Try
 
If rowsAffected <> 1 Then
 
'Server.Transfer("email_problem_new.aspx")
 
Else
 
'Server.Transfer("email_confirm_new.aspx")
 
End If
 

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)

[kunal pawar]

Hi,
I suggest you to make that column as Primary key. So its through Exception catch tht one and do your action