I have a program in which I have entered over 300 schools into a database. Now that I have got to the letter S (which of course includes St Bede’s, St Joseph’s, etc, I have started getting a syntax error. The error message ssays “near s”, so I can only presume it is the apostrophe that is causing the trouble.
But there is a small catch: If I add the name to the database without the apostrophe and then edit the entry and put the apostrophe in the name, the database accepts it.
This first code block is adding the entry:
[code] If schoolNameField.Text = “” Then
MsgBox(“A School Name is required!”)
Exit
ElseIf addressField.Text = “” Then
MsgBox(“An address is required.”)
Exit
ElseIf cityTownField.Text = “” Then
MsgBox(“A city or a town is required.”)
Exit
ElseIf postcodeField.Text = “” Then
MsgBox(“A postcode is required.”)
Exit
End If
Dim firstLetter, firstLetterNum As String
firstLetter = Left(schoolNameField.Text, 1) //returns First Letter of School Name
Select Case firstLetter
Case “A”
firstLetterNum = “1”
Case “B”
firstLetterNum = “2”
Case “C”
firstLetterNum = “3”
Case “D”
firstLetterNum = “4”
Case “E”
firstLetterNum = “5”
Case “F”
firstLetterNum = “6”
Case “G”
firstLetterNum = “7”
Case “H”
firstLetterNum = “8”
Case “I”
firstLetterNum = “9”
Case “J”
firstLetterNum = “10”
Case “K”
firstLetterNum = “11”
Case “L”
firstLetterNum = “12”
Case “M”
firstLetterNum = “13”
Case “N”
firstLetterNum = “14”
Case “O”
firstLetterNum = “15”
Case “P”
firstLetterNum = “16”
Case “Q”
firstLetterNum = “17”
Case “R”
firstLetterNum = “18”
Case “S”
firstLetterNum = “19”
Case “T”
firstLetterNum = “20”
Case “U”
firstLetterNum = “21”
Case “V”
firstLetterNum = “22”
Case “W”
firstLetterNum = “23”
Case “X”
firstLetterNum = “24”
Case “Y”
firstLetterNum = “25”
Case “Z”
firstLetterNum = “26”
End Select
invoiceDatabase.SQLExecute(“BEGIN TRANSACTION”)
invoiceDatabase.SQLExecute ("INSERT INTO client (name,address,suburb,city_town,postcode,telephone,email,additional_comments,startAlpha) "_
- “VALUES ( '” + schoolNameField.Text + “’,’” + addressField.Text + “’,’” + suburbField.Text + “’,’” + cityTownField.Text + “’,’” + postcodeField.Text + “’,’” + phoneField.Text + “’,’” + emailField.Text + “’,’” + detailsField.Text + “’,’” + firstLetterNum + “’)” )
Dim data As RecordSet
'Get last ClientID
data = InvoiceDatabase.SQLSelect(“SELECT client_id FROM client”)
If data <> Nil Then
While Not data.EOF
DatabaseModule.lastClient = data.Field(“client_id”).IntegerValue
data.MoveNext
Wend
End if
data.Close
Dim i, contactCount As Integer
'Get number of contacts
contactCount = Customer.contacts.Ubound
If contactCount > -1 Then
For i = 0 to contactCount
InvoiceDatabase.SQLExecute ("INSERT INTO contact (contact_type_id,contact_name,contact_telephone,contact_email,contact_other) "_
+ “VALUES (’” + Customer.contacts(i).getContact(“type”) + “’,’” + Customer.contacts(i).getContact(“name”) + “’,’” + Customer.contacts(i).getContact(“telephone”) + “’,’” + Customer.contacts(i).getContact(“email”) + “’,’” + Customer.contacts(i).getContact(“other”) + “’)”)
Next
End if
'Get last contactID
data = InvoiceDatabase.SQLSelect(“SELECT contact_id FROM contact”)
If data <> Nil Then
While Not data.EOF
DatabaseModule.lastContact = data.Field(“contact_id”).IntegerValue
data.MoveNext
Wend
End if
data.Close
Dim contactNum As Integer
If contactCount > -1 Then
contactNum = DatabaseModule.lastContact
For i = contactNum - contactCount to contactNum
InvoiceDatabase.SQLExecute("INSERT INTO client_contact (client_id,contact_id) VALUES (" + Str(DatabaseModule.lastClient) + "," + Str(i) + ")")
Next
End If
ReDim Customer.contacts(-1)
NoContactsField.Text = “”
Dim quant As Integer
For i = 0 to 7
If qtyText(i).Enabled Then
quant = Val(qtyText(i).Text)
InvoiceDatabase.SQLExecute(“INSERT INTO standing_orders (client_id, standOrder_type, standOrder_qty) VALUES (” + Str(DatabaseModule.lastClient) + “,” + Str(qtyText(i).Index) + “,” + Str(quant) + “)”)
End If
Next
If invoiceDatabase.Error Then
MsgBox("Error: " + invoiceDatabase.ErrorMessage)
invoiceDatabase.Rollback
Else
invoiceDatabase.Commit
End If
Self.Close
invoiceWindow.Show[/code]
and this is editing the entry:
[code] //Update the data in the database
If schoolNameField.Text = “” Then
MsgBox(“A School Name is required!”)
Exit
ElseIf addressField.Text = “” Then
MsgBox(“An address is required.”)
Exit
ElseIf cityTownField.Text = “” Then
MsgBox(“A city or a town is required.”)
Exit
ElseIf postcodeField.Text = “” Then
MsgBox(“A postcode is required.”)
End If
Dim clientID As Integer
clientID = Customer.getCustSelection()
InvoiceDatabase.SQLExecute(“BEGIN TRANSACTION”)
InvoiceDatabase.SQLExecute(“UPDATE client SET name = '” + schoolNameField.Text + “’, address = '” + addressField.Text + “’, suburb = '” + suburbField.Text + “’, city_town = '” + cityTownField.Text + “’, postcode = '” + postcodeField.Text + “’, telephone = '” + phoneField.Text + “’, email = '” + emailField.Text + “’, additional_comments = '” + detailsField.Text + "’ WHERE client_id = " + Str(clientID))
//When editing customer details firstly delete all Standing Orders for this customer (any deselected have already been deleted …
InvoiceDatabase.SQLExecute("DELETE FROM standing_orders WHERE client_id = " + Str(Customer.getCustSelection) + “”)
// … then add the Standing Order (both the changed and the same).
Dim quant As Integer
Dim i As Integer
For i = 0 to 7
If qtyText(i).Enabled Then
quant = Val(qtyText(i).Text)
InvoiceDatabase.SQLExecute(“INSERT INTO standing_orders (client_id, standOrder_type, standOrder_qty) VALUES (” + Str(Customer.getCustSelection) + “,” + Str(qtyText(i).Index) + “,” + Str(quant) + “)”)
If InvoiceDatabase.Error Then
InvoiceDatabase.Rollback
MsgBox(InvoiceDatabase.ErrorMessage)
Else
InvoiceDatabase.Commit
//Changed the data
End If
End If
Next
InvoiceWindow.Show
Self.Close[/code]
They are much the same, except for the database commands.
Edit: I am creating this app on a Mac, but it is destined for Windows.