[Mono-list] SQLLite problem

[Paul Johnson]

Hi,

I seem to be having a problem with inserting into my SQLLite database. 
It is set up like this

I have the SQLLite.cs file referenced in a helper class that provides 
the interface to the app (just to make my life simple really). This 
helper class creates the table, deletes the table and does everything else.

I'm having an issue with two methods (both oddly at the same point). The 
method looks like this

public void addBoyRecords(List<BoyRecords> br)
         {
             if (br.Count == 0)
                 return;
             lock (this.dbLock)
             {
                 using (SQLiteConnection sqlCon = new 
SQLiteConnection(this.DBPath))
                 {
                     sqlCon.Execute(Constants.DBClauseSyncOff);
                     for (int m = 0; m < br.Count; ++m)
                         try
                         {
                             if (sqlCon.Execute("UPDATE BoyRecords SET " +
                                 "ReplicationGuid=?, LkwdID=?, 
MoneyID=?, AffiliationID=?, Affiliation1ID=?, DescriptionID=?, 
Description1ID=?, TypeID=?, " +
                                 "Type1ID=?, LastName=?, FirstName=?, 
HebrewName=?, DateOfBirth=?, Calculated=?, Height=?, Address1=?, 
Address2=?, " +
                                 "Address3=?, City=?, State=?, 
Country=?, Zip=?, Phone1=?, Phone2=?, FatherCell=?, MotherCell=?, 
FatherEmailAddress=?, " +
                                 "MotherEmailAddress=?, OwnAddress1=?, 
OwnAddress2=?, OwnCityID=?, OwnState=?, OwnCountry=?, OwnHomePhone1=?, " +
                                 "OwnCellPhone=?, OwnEmailAddress=?, 
DateCameToYeshiva=?, HighSchool=?, Yeshiva1=?, Yeshiva2=?, Yeshiva3=?, 
Working=?",
                                            br [m].ReplicationGuid, br 
[m].LkwdID, br [m].MoneyID, br [m].AffiliationID, br [m].Affiliation1ID, 
br [m].DescriptionID,
                                            br [m].Description1ID, br 
[m].TypeID, br [m].Type1ID, br [m].LastName, br [m].FirstName, br 
[m].HebrewName, br [m].DateOfBirth,
                                            br [m].Calculated, br 
[m].Height, br [m].Address1, br [m].Address2, br [m].Address3, br 
[m].City, br [m].State, br [m].Country,
                                            br [m].Zip, br [m].Phone1, 
br [m].Phone2, br [m].FatherCell, br [m].MotherCell, br 
[m].FatherEmailAddress,
                                            br [m].MotherEmailAddress, 
br [m].OwnAddress1, br [m].OwnAddress2, br [m].OwnCityID, br 
[m].OwnState, br [m].OwnCountry,
                                            br [m].OwnHomePhone1, br 
[m].OwnCellPhone, br [m].OwnEmailAddress, br [m].DateCameToYeshiva, br 
[m].HighSchool,
                                            br [m].Yeshiva1, br 
[m].Yeshiva2, br [m].Yeshiva3, br [m].Working) == 0)
                                 sqlCon.Insert(br [m], typeof(BoyRecords));

                             if (sqlCon.Execute("UPDATE BoyRecords SET " +
                                 "Job=?, College=?, ParentsLastName=?, 
ParentsMaritalStatus=?, FathersName=?, FathersHebrewName=?, 
FathersTitle=?, " +
                                 "FathersOrigin=?, FathersJob=?, 
FathersSchull=?, MothersName=?, MothersHebrewName=?, MothersTitle=?, 
MothersMaiden=?, " +
                                 "MothersOrigin=?, MothersJob=?, 
OrderInFamily=?, FamilySize=?, Mechutanim=?, Siblings=?, References=?, 
Notes=?, " +
                                 "RoomMate1=?, RoomMate2=?, RoomMate3=?, 
RoomMate4=?, NamePartner1=?, NamePartner2=?, RoshChaburah1=?, 
RoshChaburah2=?, " +
                                 "Seat1=?, Seat2=?, DateAdded=?, 
Engaged=?, UpdateNo=? WHERE ReplicationGuid=?", br [m].Job, br 
[m].College, br [m].ParentsLastName,
                                            br [m].ParentsMaritalStatus, 
br [m].FathersName, br [m].FathersHebrewName, br [m].FathersTitle, br 
[m].FathersOrigin,
                                            br [m].FathersJob, br 
[m].FathersSchull, br [m].MothersName, br [m].MothersHebrewName, br 
[m].MothersTitle, br [m].MothersMaiden,
                                            br [m].MothersOrigin, br 
[m].MothersJob, br [m].OrderInFamily, br [m].FamilySize, br 
[m].Mechutanim, br [m].Siblings,
                                            br [m].References, br 
[m].Notes, br [m].RoomMate1, br [m].RoomMate2, br [m].RoomMate3, br 
[m].RoomMate4, br [m].NamePartner1,
                                            br [m].NamePartner2, br 
[m].RoshChaburah1, br [m].RoshChaburah2, br [m].Seat1, br [m].Seat2, br 
[m].DateAdded,
                                            br [m].Engaged, br 
[m].UpdateNo, br [m].ReplicationGuid) == 0)
                                 sqlCon.Insert(br [m], typeof(BoyRecords));
                         } catch (Exception ex)
                         {
                             #if DEBUG
                             Console.WriteLine("Error in addBoyRecords : 
{0}-{1}", ex.Message, ex.StackTrace);
                             #endif
                             sqlCon.Rollback();
                         }
                 }
             }
         }

The list is provided from an XML file (I managed to sort that one from 
yesterday by using reader.Depth - quick and easy!).

No matter what I do, I always get a syntax error near "References". I've 
checked all of the inputs and they are the correct type and non-null. My 
thinking was that SQLLite doesn't like more than so many parameters 
going in at once (which is why I've split the input). That made no 
difference. Next was that the XML string being passed in was causing an 
issue. In the XML for <References> is R', so I've replaced that 
with ' using reader.Value.Replace("'", "'"); - still I get the same 
error.

The XML file is using UTF-8. Do I need to do anything within my code to 
ensure the strings being fed into the database are also UTF-8?

Any clues here would be useful!

Paul
-- 
"Space," it says, "is big. Really big. You just won't believe how 
vastly, hugely, mindbogglingly big it is. I mean, you may think it's a 
long way down the road to the chemist's, but that's just peanuts to 
space, listen..."
Hitch Hikers Guide to the Galaxy, a truly remarkable book!