How to create a module to add, save, delete update records on different forms

You could create a generic module with a single generic function, and pass to it a parametized SQL statement and a collection of parameters - s can be sued for adding, updating or deleting records. I actually did something like this once - it worked quite well.

In my case, I only needed to worry about 5 different parameter types - you may want to add others:

Friend Enum ParamType
   eNull = 0
   eString = 1
   eInteger = 2
   eDecimal = 3
   eDate = 4
End Enum

and I created a function create new parameters

''' <summary>
      ''' Defining parameters for database command
      ''' </summary>
      ''' <param name="sName">Parameter name (omit any leading @)</param>
      ''' <param name="eType">Parameter type (Enum ParamType)</param>
      ''' <param name="sValue">Paramter value (as Object)</param>
      ''' <param name="bRepeat">Repeat parameter in order after others in collection</param>
      Friend Function NewParam(ByVal sName As String, ByVal eType As Integer, ByVal sValue As Object, ByVal bRepeat As Boolean) As clsParam
         Dim cls As New clsParam
         cls.sName = "@" & sName
         cls.eType = eType
         cls.sValue = sValue
         cls.bRepeat = bRepeat
         Return cls
      End Function

The point of the bRepeat paramter is only for those scenarios wheere you want to pass an "insert.... on duplicate key update..." statement, and ou then set bRepeat to True for any in the "on duplicate key update" clause.

Then the generic function is simply: (NB in my case it was helpful to pass an already opened db connection, but this is obviously not necessary - depends on your scenario. You may also be using a different db connector - I was using the MySqlData.dll connector.)

As I say, it all worked well - NB you do need to be careful when creating your collection that the NewParam parameters are added in the correct order.

''' <summary>
      ''' Generic database ExecuteNonQuery function
      ''' </summary>
      ''' <param name="sql">Parametized SQL query</param>
      ''' <param name="coll">Collection of clsParam parameters</param>
      ''' <param name="objConn">An OPEN database connection</param>
      ''' <remarks>Params in coll MUST be added in the correct order!</remarks>
      Friend Sub UpdateDB(ByVal sql As String, ByVal coll As Collection, ByVal objConn As MySqlConnection)
         Dim objCmd As New MySqlCommand
         Try
            objCmd.Connection = objConn
            objCmd.CommandText = sql
            If Not coll Is Nothing AndAlso coll.Count > 0 Then
               Dim colR As New Collection
               Dim cls As clsParam
               Dim r As Integer
               For r = 1 To coll.Count
                  cls = CType(coll(r), clsParam)
                  Select Case cls.eType
                     Case ParamType.eString
                        objCmd.Parameters.AddWithValue("@" & cls.sName, CStr(cls.sValue))
                     Case ParamType.eInteger
                        objCmd.Parameters.AddWithValue("@" & cls.sName, CInt(cls.sValue))
                     Case ParamType.eDecimal
                        objCmd.Parameters.AddWithValue("@" & cls.sName, CDec(cls.sValue))
                     Case ParamType.eDate
                        objCmd.Parameters.AddWithValue("@" & cls.sName, CDate(cls.sValue))
                     Case Else
                        objCmd.Parameters.AddWithValue("@" & cls.sName, DBNull.Value)
                  End Select
                  If cls.bRepeat Then
                     colR.Add(cls)
                  End If
               Next
               If colR.Count > 0 Then
                  For r = 1 To colR.Count
                     cls = CType(colR(r), clsParam)
                     Select Case cls.eType
                        Case ParamType.eString
                           objCmd.Parameters.AddWithValue("@b" & cls.sName, CStr(cls.sValue))
                        Case ParamType.eInteger
                           objCmd.Parameters.AddWithValue("@b" & cls.sName, CInt(cls.sValue))
                        Case ParamType.eDecimal
                           objCmd.Parameters.AddWithValue("@b" & cls.sName, CDec(cls.sValue))
                        Case ParamType.eDate
                           objCmd.Parameters.AddWithValue("@b" & cls.sName, CDate(cls.sValue))
                        Case Else
                           objCmd.Parameters.AddWithValue("@b" & cls.sName, DBNull.Value)
                     End Select
                  Next
               End If
            End If
            objCmd.Prepare()
            objCmd.ExecuteNonQuery()
         Catch ex As Exception
            ' log the error
         End Try
      End Sub