Strange Results When Writing To SQL Database

[warrenk]

I am writing line item detail to a SQL database. The problem is the value written to PODLineNumber is always 6. This is strange as I have a total of 3 line items. When I run the code below, I am getting the following values:

gTotalLines = 3
tPOLine = 1,2,3

When I check the database, PODLineNumber is always 6. It doesn't matter how many line items I have, the PODLineNumber is always 6. PODLineNumber is setup as a integer. Any ideas? I am banging my head against the wall staring at this one!

answer gTotalLines
repeat with x = 1 to gTotalLines
put aItem[x] into tItem
put aDescription[x] into tDescription
put aCost[x] into tCost
put aLine[x] into tPOLine
answer tPOLine
revexecutesql gConID, "INSERT INTO podetail (PoDNumber, PODLineNumber, PoDItemNumber, PoDItemDesc, PoDUnitCost) values(:1,:2,:3,:4,:5)", "tPONumber","tPOLine", "tItem", "tDescription" , "tCost"
end repeat

[Janschenkel]

I would think it's rather unhappy about the fact that you're trying to stuff a string "1,2,3" into an integer column. Why not just

Code: Select all

put x into tPOLine

inside the repeat loop?

Jan Schenkel.

[warrenk]

Jan,

I wasn't to clear when I mentioned the values of tPOLine = 1,2,3. I meant to say the values are:

1
2
3

...I put the commas in to separate them for the example but I am indeed using numerics.

I did find where the 6 was coming from for the PODLineNumber. It is putting the first digit from the PoDUnitCost into the PodLineNumber. Now the question is...why is this happening?

repeat with x = 1 to gTotalLines
put aItem[x] into tItem
put aDescription[x] into tDescription
put aCost[x] into tCost
put aLine[x] into tPOLine

answer tCost & "*" & tPONumber & "*" & tPOLine & "*" & tItem & "*" & tDescription

revexecutesql gConID, "INSERT INTO podetail (PoDNumber, PODLineNumber, PoDItemNumber, PoDItemDesc, PoDUnitCost) values(:1,:2,:3,:4,:5)", "tPONumber","tPOLine", "tItem", "tDescription" , "tCost"
end repeat

For the above code, I am getting the following on the dialog:

tPONumber = 4638
tPOLine = 1
tItem = Test Item 1
tDescription = Test Description 1
tCost = 12.12

tPONumber = 4638
tPOLine = 2
tItem = Test Item 2
tDescription = Test Description 2
tCost = 67.56

When I check my SQL database, the values are:

PODNumber = 4638
PODLineNumber = 1 (first digit from PodUnitCost)
PODItemNumber = Test Item 1
PodDescription = Test Description 1
PodUnitCost = 12.12

PODNumber = 4638
PODLineNumber = 6 (first digit from PodUnitCost)
PODItemNumber = Test Item 2
PodDescription = Test Description 2
PodUnitCost = 67.56

Any clue as to why my PodUnitCost is flowing into the PodLineNumber?

[Janschenkel]

Perhaps your SQL database has a log that can show the full INSERT statement after the variables are merged into it?

Jan Schenkel.