data type conversion....

[WellsCarrie]

FIXED!!!!!
the problem was that the reader was executing the wrong sqlstring.

Ok if this question has been answered somewhere just tell me.

I have a SQL statement that returns a container number as a string when run in SQLPlus but when run through the OracleDataReader it comes out as a decimal.

'// Open the detail dataset
sb.Remove(0, sb.Length)
sb.Append("SELECT DECODE(pld.cont_num, '', DECODE(pld.mast_cont_num, '', ")
sb.Append("'NO CONTAINER NUMBER', pld.mast_cont_num), pld.cont_num) AS cont_num, ")
sb.Append("pld.line_num, pld.itm_num, pld.itm_desc, ")
sb.Append("pld.lot_shipped, pld.qty_ord, pld.qty_shipped, ")
sb.Append("pld.itm_wgt, pld.pro_num ")
sb.Append("FROM unisearchpacklistdtl pld ")
sb.Append("WHERE pld.hdr_id = ")
sb.Append(lHeaderID)
sb.Append(" ORDER BY pld.cont_num, pld.line_num ")
ohiocmd5.Connection = ohioConn5
ohiocmd5.CommandText = sb.ToString()
rsDetailData = ohiocmd.ExecuteReader()
If rsDetailData.HasRows() Then
Do While rsDetailData.Read()
'// Get the current container number "cont_num"
If IsDBNull(rsDetailData("cont_num")) Then
sFieldValue = ""
Else
sFieldValue = rsDetailData("cont_num")
End If
....
much more processing
....
End While
End If

The field is defined as VARCHAR2(30) in the database table. It holds values like "00000415544119783174" and "00000415544119783174" these are used to identify the Pallets that go on a specified truck. However when the reader gets to the point of giving me the data it has turned it into a decimal. I have to have the string. Any suggestions?

 

[DavidT_macktool]

Some form of .ToString() should work for you.
I would investigate the reason for returning a decimal, seems like that would be a coding error and the ToString is just a band-aid for the real problem - a data definition somewhere.

 

[WellsCarrie]

Normally, I would agree. However, above is the whole of the code that pulls the container number though. In all other cases I've ever used the oracleDataReader it returns values as they are defined in the datbase. varchars = strings, floats = decimals, ints = ints and so on and so on. I even have another application in production that pulls the container number from the database the same way.... it gives me no problems.

I had .ToString() in the code here...

sFieldValue = rsDetailData("cont_num")

was

sFieldValue = rsDetailData("cont_num").ToString()

I took it off trying to solve the problem. the debugger says rsDetailData("cont_num") is a decimal and the data in it does NOT match the string that SQLPlus returns.


If my code only says

Dim sFieldValue as string
dim rsDetailData as oracleDataReader
sFieldValue = rsDetailData("cont_num")

then where in the code is the problem?

 

[WellsCarrie]

OK the problem was that reDetailData should have been executing OhioCmd5 not OhioCmd.

thanks for the help!