Dear forum users
I am using ODBCResultset.SetParameter() method in my program. The SQL statement is given below:
INSERT INTO CUSTOMERLOCATION (BUID, CID, LOCATIONID, LOCATIONNAME, CUSTOMERSAPID, CUSTOMERTITLE, CONTACTPERSON, ADDRESS1, ADDRESS2, ADDRESS3, ADDRESS4, STREET, OTHERCITY, DISTRICT, STATE, COUNTRY, PINCODE1, TELEPHONE1, TELEPHONE2, STATEID, DATASOURCE) VALUES (?BUID?, ?CID?, ?LOCATIONID?, ?LOCATIONNAME?, ?CUSTOMERSAPID?, ?CUSTOMERTITLE?, ?CONTACTPERSON?, ?ADDRESS1?, ?ADDRESS2?, ?ADDRESS3?, ?ADDRESS4?, ?STREET?, ?OTHERCITY?, ?DISTRICT?, ?STATE?, ?COUNTRY?, ?PINCODE1?, ?TELEPHONE1?, ?TELEPHONE2?, ?STATEID?, ?DATASOURCE?)
The RDBMS being used is DB2.
The problem is, if I remove any of the parameters in STATE or STATEID, the code works fine, but if I include both, the ODBCResultset.Execute function generates following error:
[IBM],[CLI Driver][DB2/NT]SQL0117N The number of values assigned is not same as the number of specified or implied columns. SQLSTATE=42802
I have run the code several times with messageboxes to check if all the parameters are being set or not, the messageboxes show that all the parameters are being set correctly, but the code never works. Here is the code:
Uselsx “*LSXODBC”
Sub Querysave(Source As Notesuidocument, Continue As Variant)
On Error Goto eh
Dim con As New ODBCConnection()
Dim qs As New ODBCQuery()
Dim res As New ODBCResultSet()
Dim doc As NotesDocument
Call con.ConnectTo(“CRMDB”, “db2admin”, “password”)
Set qs.Connection = con
qs.SQL = “SQL Query…mentioned above”
Set res.Query = qs
Set doc = Source.Document
For i = 1 To res.NumParameters
param$ = res.GetParameterName(i)
Call res.SetParameter(i, doc.GetItemValue(param$)(0))
Messagebox res.GetParameterName(i) & "/" & res.GetParameter(i)
Next
res.Execute
Call con.Disconnect()
Exit Sub
eh:
Messagebox res.GetError() & “/” & res.GetExtendedErrorMessage()
Call con.Disconnect()
Exit Sub
End Sub