B4J Question [SOLVED] JRDC2 Client strange index 1 is out of range problem

makis_best · Jun 2, 2020

Hi

I have one Android client trying to insert into MSSQL Server a record using JRDC2.
My android client code command has 20 fields to import.

B4X:

Sub InsertRecord_NewClients (InsertedMap As Map) As ResumableSub
    Dim cmd As DBCommand = CreateCommand("sql.insert_NEW_CLIENT", Array(InsertedMap.Get("ClientCode"), InsertedMap.Get("Name"), InsertedMap.Get("KathestosFPA"), _
    InsertedMap.Get("NomikiMorfi"), InsertedMap.Get("AFM"), InsertedMap.Get("DOY"), InsertedMap.Get("Epaggelma"), InsertedMap.Get("Address"), InsertedMap.Get("ZipCode"), _
    InsertedMap.Get("Phone1"), InsertedMap.Get("Fax1"), InsertedMap.Get("Phone2"), InsertedMap.Get("Fax2"), InsertedMap.Get("Email"), InsertedMap.Get("WebSite"), _
    InsertedMap.Get("Transport"), InsertedMap.Get("PosostoFPA"), InsertedMap.Get("Pricelist"), InsertedMap.Get("ISSYNC"), InsertedMap.Get("SalesmanCode")))
    Dim j As HttpJob = CreateRequest.ExecuteBatch(Array(cmd), Null)
    Wait For(j) JobDone(j As HttpJob)
    If j.Success Then
        Log("Inserted Successfully!")
        j.Release
        Return True
    Else
        Log("Inserted Unsuccessfully!!!")
        j.Release
        Return False
    End If
End Sub

On config.properties file I have the command

B4X:

# NEW CLIENT
sql.insert_NEW_CLIENT=INSERT INTO EGM_SALES_NEW_CLIENT (ClientCode, Name, KathestosFPA, NomikiMorfi, AFM, DOY, Epaggelma, Address1, ZipCode, Phone1, \
Fax1, Phone2, Fax2, Email, WebSite, Transport, PosostoFPA, Pricelist, ISSYNC, SalesmanCode) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

With 20 fields and 20 ?

On my table I have

And I keep getting one error about index 1 is out of range.

*** Service (httputils2service) Create ***
** Service (httputils2service) Start **
ResponseError. Reason: com.microsoft.sqlserver.jdbc.SQLServerException: The index 1 is out of range., Response: <html>
<head>
<meta http-equiv="Content-Type" content="text/html;charset=utf-8"/>
<title>Error 500 com.microsoft.sqlserver.jdbc.SQLServerException: The index 1 is out of range.</title>
</head>
<body><h2>HTTP ERROR 500</h2>
<p>Problem accessing /rdc. Reason:
<pre> com.microsoft.sqlserver.jdbc.SQLServerException: The index 1 is out of range.</pre></p><hr><a href="http://eclipse.org/jetty">Powered by Jetty:// 9.4.z-SNAPSHOT</a><hr/>
</body>
</html>
Inserted Unsuccessfully!!!
Completed. Success: false

Any help to understand why I get the error appreciated
Thank you.

OliverA · Jun 2, 2020

makis_best said:
sql.insert_NEW_CLIENT

In the CreateCommand on the client side you don’t use the sql. prefix, it should just be insert_NEW_CLIENT

makis_best · Jun 2, 2020

Dear @OliverA when I did the change you propose the error change to

B4X:

<pre>    com.microsoft.sqlserver.jdbc.SQLServerException: An explicit value for the identity column in table &apos;EGM_SALES_NEW_CLIENT&apos; can only be specified when a column list is used and IDENTITY_INSERT is ON.</pre></p><hr><a href="http://eclipse.org/jetty">Powered by Jetty:// 9.4.z-SNAPSHOT</a><hr/>

This is SQL error I think.
Correct?

OliverA · Jun 2, 2020

Yes. Looks like one of the columns that you are updating has been defined with the IDENTITY keyword and cannot be updated as is. For more information, see http://www.sql-server-helper.com/error-messages/msg-8101.aspx

makis_best · Jun 2, 2020

OK... The strange error message came back I get again now index 1 is out of range after I fix IDENTITY problem
I only remove AA field from table.
But config.properties stay the same.

makis_best · Jun 2, 2020

Ok... Done.

The problem solved only when I change the config.properties file from

B4X:

# NEW CLIENT
sql.insert_NEW_CLIENT=INSERT INTO EGM_SALES_NEW_CLIENT (ClientCode, Name, KathestosFPA, NomikiMorfi, AFM, DOY, Epaggelma, Address1, ZipCode, Phone1, \
Fax1, Phone2, Fax2, Email, WebSite, Transport, PosostoFPA, Pricelist, ISSYNC, SalesmanCode) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

to

B4X:

# NEW CLIENT
sql.insert_NEW_CLIENT=INSERT INTO EGM_SALES_NEW_CLIENT VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

I don't know why but it works.
Thank you all for your help

B4J Question [SOLVED] JRDC2 Client strange index 1 is out of range problem

makis_best

Well-Known Member

OliverA

Expert

makis_best

Well-Known Member

OliverA

Expert

makis_best

Well-Known Member

makis_best

Well-Known Member

Similar Threads