inserting records with foreign and primary keys

tdaustin · October 4th, 2005, 12:49 AM

Hi all,

im really flustered at this one.... Im having problems doing simple insert statement into a Sybase sql anywhere database.

The error im getting is:

Code:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Sybase][ODBC Driver]Integrity constraint violation: no primary key value for foreign key 'MemberCode' in table 'PersonMember'
/admin/Member_New-Sybase.asp, line 39

What i am doing is first i have added a record to my persons table the next step is i need to add this person id to the PersonMember table with a new MemberCode and then finally add more information to a membership table.

I insert the data into the persons table fine then the above error appears and i don't know why?

Any help with this issue would be much appreciated.

Regards,

Tim

Full code

Code:

<%
' create new person and member on Sybase table: person, PersonMember, Membership
' get mysql record
Membership_ID = Request.QueryString("ID")
ActivationCode = Request.QueryString("ActivationCode")

If IsNumeric(Membership_ID) = False OR Len(Membership_ID) <= 0 Then
Response.Redirect("Error.asp")
End If

strSQL = "SELECT membership.*, membership_details.* FROM membership, membership_details WHERE membership.Membership_ID = "& Membership_ID &" AND membership.ActivationCode = '"& ActivationCode &"' AND membership.Membership_ID = membership_details.Membership_ID"
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open strSQL, objConn, adForwardOnly, adLockReadOnly, adCmdText
'Validate and get required variables
' current date
JoinDate = DatePart("yyyy", Date) & "-" & DatePart("m", Date) & "-" & DatePart("d", Date)
' set null dates
If Len(objRS("DOB")) <= 0 OR IsDate(objRS("DOB")) Then
DOB = DatePart("yyyy", "0000") & "-" & DatePart("m", "00") & "-" & DatePart("d", "00")
End If

'## Step 1: Add to Sybase: person
' call next person id from sybase based on location
NewPersonID = PersonID(objRS("Location"))
strAdd = "INSERT INTO ""dba"".""person"" (UMC, Title, FirstName, LastName, Affiliate, DofB, DateJoined, Address1, Suburb, State, PCode, PostAddr1, PostSuburb, PostState, PostPCode, HMPhone, Mobile, EMail, Judge, Occupation, PensionNo) VALUES ("&NewPersonID&", '"&objRS("Prefix")&"', '"&objRS("FirstName")&"', '"&objRS("LastName")&"', "& 0 &", '"&DOB&"', '"&JoinDate&"', '"&objRS("Address")&"', '"&objRS("City")&"', '"&objRS("Location")&"', '"&objRS("PostCode")&"', '"&objRS("PAddress")&"', '"&objRS("PCity")&"', '"&objRS("PLocation")&"', '"&objRS("PPostCode")&"', '"&objRS("Phone")&"', '"&objRS("Mobile")&"', '"&objRS("Email")&"', "& 0 &", '"&objRS("Occupation")&"', '"&objRS("PensionNumber")&"')"
cn.Execute(strAdd)
' call next member id
NewMemberID = MemberID(objRS("Location"))
' ## Step 2: add to Sybase: PersonMember

Vote = 0
strAdd = "INSERT INTO ""dba"".""PersonMember"" (MemberCode, UMC, EntryDate, Vote) VALUES ('"&NewMemberID&"', '"&NewPersonID&"', '"&JoinDate&"', "&Vote&")"
cn.Execute(strAdd)
' Step 3: add to Sybase: Membership

MemName = objRS("Prefix") & " " & objRS("FirstName") & " " & objRS("LastName")
DatePaid = DatePart("yyyy", objRS("StartDate")) & "-" & DatePart("m", objRS("StartDate")) & "-" & DatePart("d", objRS("StartDate"))
Journal = 0
Nominee = 0
strAdd = "INSERT INTO ""dba"".""Membership"" (MemberCode, MemClass, MemStatus, StatusDate, MemName, StartDate, Duration, Journal, Amount, DatePaid, Nominee, Recipient, MemRestart) VALUES ("&NewMemberID&", '"&MemClass&"', 'N', '"&DatePaid&"', '"&MemName&"', '"&DatePaid&"', "&objRS("Duration")&", "&Journal&", "&objRS("Amount")&", '"&DatePaid&"', "&Nominee&", "&NewPersonID&", '"&DatePaid&"')"
'cn.Execute(strAdd)


objRS.Close
Set objRS = Nothing
%>

TDA