Public Function AddNewMember() As Long
' Function adds a new member to the database
' Returns the new member Id on success, or 0 if not successful
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim er As ADODB.Error
Dim lRecs As Long
Dim NewId As Long
Set cn = New ADODB.Connection
cn.Open sConnection
If cn.state = adStateOpen Then
Set cmd = New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandText = "spnewmember"
cmd.CommandType = adCmdStoredProc
'@ipMemberDateJoined datetime
Set prm = cmd.CreateParameter("MemDateJoined", adDate, adParamInput, , Date)
cmd.Parameters.Append prm
'@ipMemberFirstName nvarchar(200),
Set prm = cmd.CreateParameter("MemFirstName", adVarWChar, adParamInput, 200, Me.MemberFirstName)
cmd.Parameters.Append prm
'@ipMemberLastName nvarchar(200),
Set prm = cmd.CreateParameter("MemLastName", adVarWChar, adParamInput, 200, Me.MemberLastName)
cmd.Parameters.Append prm
'@ipMemberMiddleName nvarchar(200),
Set prm = cmd.CreateParameter("MemMiddleName", adVarWChar, adParamInput, 200, Me.MemberMiddleName)
cmd.Parameters.Append prm
'@ipMemberAddress nvarchar(1000),
Set prm = cmd.CreateParameter("MemAddress", adVarWChar, adParamInput, 1000, Me.MemberAddress)
cmd.Parameters.Append prm
'@ipMemberPostCode nchar(4),
Set prm = cmd.CreateParameter("MemPostCode", adWChar, adParamInput, 4, Me.MemberPostcode)
cmd.Parameters.Append prm
'@ipMemberPhone nvarchar(50),
Set prm = cmd.CreateParameter("MemPhoneHome", adVarWChar, adParamInput, 50, Me.MemberPhoneHome)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("MemPhoneWork", adVarWChar, adParamInput, 50, Me.MemberPhoneWork)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("MemPhoneMobile", adVarWChar, adParamInput, 50, Me.MemberPhoneMobile)
cmd.Parameters.Append prm
'@ipMemberSex bit,
Set prm = cmd.CreateParameter("MemSex", adBoolean, adParamInput, , Me.MemberSex)
cmd.Parameters.Append prm
'@ipMemberSessions int,
Set prm = cmd.CreateParameter("MemSessions", adInteger, adParamInput, , Me.MemberSessions)
cmd.Parameters.Append prm
'@ipMemberConcurrencyId int,
Set prm = cmd.CreateParameter("MemConcurrencyId", adInteger, adParamInput, , 0)
cmd.Parameters.Append prm
' @opMemberId int OUTPUT
' Important note output parameter doesn't require size or value
Set prm = cmd.CreateParameter("opMemberId", adInteger, adParamOutput)
cmd.Parameters.Append prm
'Make sure you tell it no records returned by stored proc
cmd.Execute lRecs, , adExecuteNoRecords
If lRecs Then
Me.MemberId = cmd.Parameters("opMemberId")
AddNewMember = cmd.Parameters("opMemberId")
Else
AddNewMember = 0
End If
cn.Close
Set prm = Nothing
Set cmd = Nothing
Set cn = Nothing
Else
' If the connection didn't open A-OK, then
' Display all the errors in the connection object
For Each er In cn.Errors
MsgBox er.Description
Next
End If
End Function
