Dim dgvconnection As New SqlConnection("Data Source=(LocalDB)\v11.0;AttachDbFilename=D:\Visual Basic Project\Church Database System\WindowsApplication1\cms.mdf;Integrated Security=True")
Dim dgvcommand As SqlCommand = dgvconnection.CreateCommand()
Try
'open connection
dgvconnection.Open()
'create Insert statement with named parameters
dgvcommand.CommandText = "Insert into members(id,datemembregis,year,Title,FullName,MaidName,DOB,Age,Gender,HomeTown,POB,MemStatus,MemType,MaritalStatus,MarriageType,SpouseName,SpouseDeno,DOBap,PlaceofBap,DOConf,PlaceofConf,gengroup,sergroup,othergroup,daygroup,memsince,transfer) values(@id,@datemembregis,@year,@title,@fullname,@maidname,@dob,@Age,@gender,@hometown,@pob,@MemStatus,@MemType,@maritalstatus,@marriagetype,@SpouseName,@SpouseDeno,@dobap,@placeofbap,@doconf,@placeofconf,@gengroup,@sergroup,@othergroup,@daygroup,@memsince,@transfer)"
'add parameters to command parameters collection
dgvcommand.Parameters.Add("@id", SqlDbType.Int, 50)
dgvcommand.Parameters.Add("@datemembregis", SqlDbType.SmallDateTime)
dgvcommand.Parameters.Add("@year", SqlDbType.NChar, 10)
dgvcommand.Parameters.Add("@title", SqlDbType.NChar, 10)
dgvcommand.Parameters.Add("@fullname", SqlDbType.VarChar, 50)
dgvcommand.Parameters.Add("@maidname", SqlDbType.VarChar, 50)
dgvcommand.Parameters.Add("@dob", SqlDbType.SmallDateTime)
dgvcommand.Parameters.Add("@age", SqlDbType.VarChar, 50)
dgvcommand.Parameters.Add("@gender", SqlDbType.VarChar, 50)
dgvcommand.Parameters.Add("@hometown", SqlDbType.VarChar, 50)
dgvcommand.Parameters.Add("@pob", SqlDbType.VarChar, 50)
dgvcommand.Parameters.Add("@memstatus", SqlDbType.VarChar, 50)
dgvcommand.Parameters.Add("@memtype", SqlDbType.VarChar, 50)
dgvcommand.Parameters.Add("@maritalstatus", SqlDbType.VarChar, 50)
dgvcommand.Parameters.Add("@marriagetype", SqlDbType.VarChar, 50)
dgvcommand.Parameters.Add("@spousename", SqlDbType.VarChar, 50)
dgvcommand.Parameters.Add("@spousedeno", SqlDbType.VarChar, 50)
dgvcommand.Parameters.Add("@dobap", SqlDbType.SmallDateTime)
dgvcommand.Parameters.Add("@placeofbap", SqlDbType.VarChar, 50)
dgvcommand.Parameters.Add("@doconf", SqlDbType.SmallDateTime)
dgvcommand.Parameters.Add("@placeofconf", SqlDbType.VarChar, 50)
dgvcommand.Parameters.Add("@gengroup", SqlDbType.VarChar, 50)
dgvcommand.Parameters.Add("@sergroup", SqlDbType.VarChar, 50)
dgvcommand.Parameters.Add("@othergroup", SqlDbType.VarChar, 50)
dgvcommand.Parameters.Add("@daygroup", SqlDbType.VarChar, 50)
dgvcommand.Parameters.Add("@memsince", SqlDbType.VarChar, 50)
dgvcommand.Parameters.Add("@transfer", SqlDbType.VarChar, 50)
'prepare command for repeated execution
dgvcommand.Prepare()
'data to be inserted
For Each row As DataGridViewRow In dgvExcelRegistration.Rows
If Not row.IsNewRow Then
dgvcommand.Parameters("@id").Value = row.Cells("id").Value
dgvcommand.Parameters("@datemembregis").SqlValue = row.Cells("DateReg").Value
dgvcommand.Parameters("@year").Value = row.Cells("year").Value
dgvcommand.Parameters("@title").Value = row.Cells("Title").Value
dgvcommand.Parameters("@fullname").Value = row.Cells("FullName").Value
dgvcommand.Parameters("@maidname").Value = row.Cells("Maiden").Value
dgvcommand.Parameters("@dob").SqlValue = row.Cells("DOB").Value
dgvcommand.Parameters("@age").Value = row.Cells("Age").Value
dgvcommand.Parameters("@gender").Value = row.Cells("Gender").Value
dgvcommand.Parameters("@hometown").Value = row.Cells("HomeTown").Value
dgvcommand.Parameters("@pob").Value = row.Cells("POB").Value
dgvcommand.Parameters("@memstatus").Value = row.Cells("MemStatus").Value
dgvcommand.Parameters("@memtype").Value = row.Cells("MemType").Value
dgvcommand.Parameters("@maritalstatus").Value = row.Cells("MaritalStatus").Value
dgvcommand.Parameters("@marriagetype").Value = row.Cells("MaritalType").Value
dgvcommand.Parameters("@spousename").Value = row.Cells("SpouseName").Value
dgvcommand.Parameters("@spousedeno").Value = row.Cells("SpouseDeno").Value
dgvcommand.Parameters("@dobap").SqlValue = row.Cells("DOBapt").Value
dgvcommand.Parameters("@placeofbap").Value = row.Cells("PlaceBaptism").Value
dgvcommand.Parameters("@doconf").SqlValue = row.Cells("ConfDate").Value
dgvcommand.Parameters("@placeofconf").Value = row.Cells("ConfPlace").Value
dgvcommand.Parameters("@gengroup").Value = row.Cells("GenGroup").Value
dgvcommand.Parameters("@sergroup").Value = row.Cells("ServiceGrp").Value
dgvcommand.Parameters("@othergroup").Value = row.Cells("OtherGrp").Value
dgvcommand.Parameters("@daygroup").Value = row.Cells("DayBorn").Value
dgvcommand.Parameters("@memsince").Value = row.Cells("MemSince").Value
dgvcommand.Parameters("@transfer").Value = row.Cells("TransferFrom").Value
dgvcommand.ExecuteNonQuery()
dgvcommand.Parameters.Clear()
End If
Next
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
dgvconnection.Close()
End Try