Android Question B4a Update ms sql Server table problem

[DavidMeees]

I have a problem with updating an Ms SQL table in B4A giving me the following error
Logger connected to: 127.0.0.1:5555
--------- beginning of system
--------- beginning of main
*** Service (starter) Create ***
** Service (starter) Start **
** Activity (main) Create (first time) **

(Exception) java.lang.Exception: java.sql.SQLException: Incorrect syntax near 'Part'.
** Activity (main) Pause event (activity is not paused). **
*** Service (starter) Create ***
** Service (starter) Start **
** Activity (main) Create (first time) **
#SupportedOrientations attribute must be set to landscape or portrait.
Call B4XPages.GetManager.LogEvents = True to enable logging B4XPages events.

java.sql.SQLException: Incorrect syntax near 'Part'.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2988)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2421)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:671)
at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:613)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:572)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeImpl(JtdsStatement.java:809)
at net.sourceforge.jtds.jdbc.JtdsStatement.execute(JtdsStatement.java:1282)
at anywheresoftware.b4j.objects.SQL.ExecNonQuery(SQL.java:156)
at java.lang.reflect.Method.invoke(Native Method)
at anywheresoftware.b4a.shell.Shell.runVoidMethod(Shell.java:777)
at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:354)
at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:255)
at java.lang.reflect.Method.invoke(Native Method)
at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:157)
at anywheresoftware.b4a.BA$1.run(BA.java:360)
at android.os.Handler.handleCallback(Handler.java:873)
at android.os.Handler.dispatchMessage(Handler.java:99)
at android.os.Looper.loop(Looper.java:193)
at android.app.ActivityThread.main(ActivityThread.java:6816)
at java.lang.reflect.Method.invoke(Native Method)
at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:518)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:858)
(Exception) java.lang.Exception: java.sql.SQLException: Incorrect syntax near 'Part'.

I originally had this problem with my B4J project and with the help of Erel solved this by adding the following to the project


#CustomBuildAction: After Packager, %WINDIR%\System32\robocopy.exe, C:\B4JOBS\ObsNew\OBSNew\ThreePages\Objects\temp\build\bin\ Charsets.properties
#PackagerProperty: IncludedModules = jdk.charsets

I am presuming I have the same issue but after 6 hours of trying I am at a loss as to how to fix this

Thanks

 

[Daestrum]

In your attached sql you have a couple of places that have a double apostrophe at the end of a field, whether that makes a difference I don't know.
If you use smart strings it's easier to spot typos (plus you save the worlds supply of ampersands too)

ConnectionModule.Sql1.ExecNonQuery($"UPDATE Service_Booking_Form SET Booking_Name = '${TextName.text}', Bookiing_Address ='${TextAddress1.text}', [Prefered_Date] ='${BookDate.text}', [Contact_Name] ='${TextContact.text}', [Contact Number] ='${TextContNum.text}', [Mobile_Number] ='${TextMobileNum.text}', [Boiler_Type] ='${TextArea2.text}', [Boiler_Make] ='${TextArea1.text}' , [Booking_Date] ='${BookDate.text}', [Service_Time] ='${SerTimeBox.Selecteditem}', [Notes_For_Engineer] ='${TextserviceNote.text}', [Service_Engineer] ='${EngineerBox.Selecteditem}', [Service_Zone] ='${ServiceZone.Selecteditem}', [Service_Date] ='${ServiceDate.Date}', [Service_Postcode] ='${TextPostcode.text}', [Booking_Colour] ='${hexColor}', [Booking_Status] ='${StatusBox.Selecteditem}', [Status_Colour] ='${hexColor}', [Booking_Note] ='${TextserviceNote.text}', [Call_Number] ='${ServSlotBox.Selecteditem}', [Service_Type] ='${servTypeBox.Selecteditem}', [Type_Colour] ='${hexColor}', [Contact_Email] ='${TextEmail.text}', [Booking_Title] ='${TitleBox.Selecteditem}', [Last_Text_Date] ='${ServiceDate.Date}', [Job_Number] ='${TextJobNum.text}', [Booked_By] ='${TextBookedBy.text}' WHERE [Job_Number] ='${TextJobNum.text}'"$)

If that doesn't cure it check the values you are adding don't have an apostrophe like Part's as that will cause an sql error - change it to Part''s (double apostrophe).

 

[Jeffrey Cameron]

As an aside: @Daestrum makes a very important point in that you should never concatenate user-entered data directly into a SQL statement. We must do what we can to prevent a "Bobby Tables" incident, and something innocuous such as a user creating un-escaped character in a string literal.

 

[DavidMeees]

Thanks guys We learn something new every day. even for us hobbyist heating engineers
I have learned something new and ever grateful