RchlE RchlE - 4 months ago 19
SQL Question

How to validate emails from a datagridview (VBA) before i put it in SQL Server

I want to import a

.xlsx
file into a datagridview and then save it in SQL Server. But before I save it, I want to check if all emails are valid or not, and just save the records with valid emails.

Like, it needs to be at least: (1 character)@(3 characters).(2 characters)

but still, it can be like this:

a@abc.def.ghi.com


with a domain and sub-domains

This is my code so far, can someone please help me?

Imports System.IO
Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient
Public Class Importar

Dim conn As OleDbConnection
Dim dtr As OleDbDataReader
Dim dta As OleDbDataAdapter
Dim dts As DataSet
Dim excel As String
Dim counter As Integer
Dim totalR As Integer

'------------------------------------------------------------------BOTÃO PARA IMPORTAR DADOS EXCEL PARA DATAGRIDVIEW----------------------------------------------------------------------'
Private Sub btnImportar_Click(sender As Object, e As EventArgs) Handles btnImportar.Click
OpenFileDialog1.InitialDirectory = My.Computer.FileSystem.SpecialDirectories.MyDocuments '"C:\Users\Utilizador\Documents"
OpenFileDialog1.Filter = "All Files (*.*)|*.*|Excel files (*.xlsx)|*.xlsx|CSV Files (*.csv)|*.csv|XLS Files (*.xls)|*xls"
If (OpenFileDialog1.ShowDialog(Me) = System.Windows.Forms.DialogResult.OK) Then
Dim fi As New FileInfo(OpenFileDialog1.FileName)
Dim FileName As String = OpenFileDialog1.FileName
excel = fi.FullName
conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excel + ";Extended Properties=Excel 12.0;") 'provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\vb.net-informations.xls';Extended Properties=Excel 8.0;")
dta = New OleDbDataAdapter("Select * From [Folha1$]", conn)
dts = New DataSet
dta.Fill(dts, "[Folha1$]")
dtgFicheiro.DataSource = dts
dtgFicheiro.DataMember = "[Folha1$]"
End If
conn.Close()
dtgFicheiro.Columns(0).Name = "ID"
dtgFicheiro.Columns(1).Name = "Nome"
dtgFicheiro.Columns(2).Name = "Email"
totalR = dtgFicheiro.Rows.Count
MessageBox.Show("O ficheiro tem um total de " & totalR & "linhas.")
btnValidar.Enabled = True
btnImportar.Enabled = False
End Sub



'-----------------------------------------------------------------------EXPORTAR DADOS PARA SQL SERVER-------------------------------------------------------------------'
Private Sub btnGuardar_Click(sender As Object, e As EventArgs) Handles btnGuardar.Click
For Each Row As DataGridViewRow In dtgFicheiro.Rows
Dim constring As String = "Data Source=DESKTOP-NMOL9GQ\SQLEXPRESS;Initial Catalog=EMAILS;INTEGRATED SECURITY=SSPI"
Using con As New SqlConnection(constring)
Using cmd As New SqlCommand(" INSERT INTO MAILS VALUES (@ID, @Nome, @Emails)", con)
cmd.Parameters.Clear()
cmd.Parameters.AddWithValue("@ID", Row.Cells("ID").Value)
cmd.Parameters.AddWithValue("@Nome", Row.Cells("Nome").Value)
cmd.Parameters.AddWithValue("@Emails", Row.Cells("Email").Value)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Using
End Using
Next
MessageBox.Show("Registos guardados.")
End Sub

Private Sub btnSair_Click(sender As Object, e As EventArgs) Handles btnSair.Click
Close()
End Sub


End Class

Answer

You can use Regular Expressions to check if the Email is valid(Requires a Reference to "Microsoft VBScript Regular Expressions 5.5"):

Public Function TestEmail(Email As String)

Dim EmailRegEx As String

EmailRegEx = "^\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*$"

Dim regEx As New RegExp

With regEx

    .Global = True
    .MultiLine = True
    .IgnoreCase = False
    .Pattern = EmailRegEx
End With

If regEx.Test(Email) Then

    'Email valid
    Debug.Print "valid"
Else

    'Email not valid
    Debug.Print "invalid"
End If

End Function

Modify this Function to return true or false and you can implement it into your code

(Regular Expression taken from http://emailregex.com/)