Notes/Domino 6 and 7 Forum : Update SQL with multiple value fields

Hi...

I have a program in LN wherein i am going to dump the data to SQL by using the INSERT method... My problem is when im going to UPDATE the multiple value fields all the entries will have the same values. Example :

During my first insert to SQL...

itemno Name
1---------A
2 --------B
3---------C

During Updating to SQL... (this is where im having my problem)... All the entries will have the same values..

itemno Name
1-------- A
1-------- A
1-------- A

Here's my cose

mpno = doc.tpo_pono(0)

For x = 0 To ub

mitemno = doc.tpo_itemno(x)
mquantity = doc.tpo_quantity(x) 'integer
mskugroupsize = doc.tpo_sku(x)
mgroup = doc.tpo_group(x)
mdescription = "--"
msize = doc.tpo_size(x)
mkind = doc.tpo_kind(x)
munitprize = doc.tpo_price(x) 'integer
mcost = doc.tpo_cost(x) 'integer
mremarks = doc.tpo_remarks(x)

mvalues = mpno + "," + Cstr(mitemno) + ", " +Cstr( mquantity) + ",' "+ mskugroupsize + "', '" + mgroup + "', '" + mdescription + "' ,'"+ mkind + "' ,'" + msize + "', " + Cstr(munitprize) + ", "+ Cstr(mcost)+" , '" + mremarks + "'"

Set qry.connection = con

If uidoc.isnewdoc Then
qry.SQL= "Insert into U_PO_itemDtl VALUES (" + mvalues + ")"
Else
qry.SQL= "Update U_PO_itemDtl set item_no = " + Cstr(mitemno) + ", item_qty = "+ Cstr(mquantity) +", item_sku_GrpSize = '" +mskugroupsize +"', item_group = '"+ mgroup+"', item_description = '"+mdescription+"', item_kind = '"+ mkind +"', item_size = '"+msize+ "', item_unitPrice = " + Cstr(munitprize) + ", item_cost = "+Cstr(mcost) +", item_remarks = '"+mremarks+"' where Po_no = " + Cstr(mpno)+Cstr(mpno) + " and item_No = " + Cstr(mitemno) + ""
End If

Set res.query = qry

If Not res.Execute Then
Messagebox res.GetExtendedErrorMessage,, res.GetErrorMessage
Exit Sub
End If

Next

Thanks..

Notes/Domino 6 and 7 Forum

Resources

Lotus Support

Wikis

Lotus Forums