Conversion from string to type double is not valid

Mike Seargent

Hi Phillip,

I can't work out why the following query was giving me a conversion from string to type double error? The sql query returns the correct values when run in query analyser (result is warehouse bin location of the line items in the delivery.

LD.QueryData("SELECT t3.u_bin FROM DLN1 t1, ODLN t2, OITW t3 WHERE t1.DocEntry = t2.DocEntry AND t1.ItemCode = t3.ItemCodet1.WhsCode = t3.WhsCode AND T2.DocNum In '" + GetData("B1_Data.Document.DocNum") + "'")

Any help appreciated.

Thanks,
Mike

Markus Rewak

hi Mike,

1.maybe try to convert your docnum into string by using the fstr()
2. delete the last apostroph
3. you can use = instead of in if you expect only 1 document no.
function:

LD.QueryData("SELECT t3.u_bin FROM DLN1 t1, ODLN t2, OITW t3 WHERE t1.DocEntry = t2.DocEntry AND t1.ItemCode = t3.ItemCodet1.WhsCode = t3.WhsCode AND T2.DocNum = '" + LD.FStr(GetData("B1_Data.Document.DocNum"),"0") )


best regards

Markus

Mike Seargent

hmmm returns "incorrect syntax near '=' " in the report field but script is correct when I run the script check!!?? :(

Manuel Marhold

Hi Mike,

DocNum is a numeric field, so you must use

"...T2.DocNum = " + GetData("B1_Data.Document.DocNum")

Best regards,

Manuel Marhold

Mike Seargent

Hi Manuel, that is the same syntax i was using at the start when i had the conversion error (except i used 'in' instead of '='). problem is the conversion. i'll try to contact the forum administrator.

Philipp Knecht

Hi Mike, hi Manuel

A a general rule. Use the & operator to concancenate strings.
And maybe there is really a DBNull in DocNum so use Data() instead of GetData().

-> "DocNum=" & Data("B1_Data.Document.DocNum")

hth

Mike Seargent

i tried adding the following line to the script - AND T3.u_bin is not null- because it was returning two values when i ran it in SQL ('442' - the correct value and 'null' which I don't want). However when i run the following script in CS Designer the field in the document just shows the name value WH_VALUE!

obviously there is still a syntax issue still. what the *!#$ am I doing wrong! :bang:

LD.QueryData("SELECT t3.u_bin FROM DLN1 t1, ODLN t2, OITW t3 WHERE t1.DocEntry = t2.DocEntry AND t1.ItemCode = t3.ItemCode AND t1.WhsCode = t3.WhsCode AND T3.u_bin is not null AND T2.DocNum = '"& Data("B1_Data.Document.DocNum")+ "'")

Manuel Marhold

Hi Mike..

I would say, regarding to Philipps entry, that the correct Code should be:

LD.QueryData("SELECT t3.u_bin FROM DLN1 t1, ODLN t2, OITW t3 WHERE t1.DocEntry = t2.DocEntry AND t1.ItemCode = t3.ItemCode AND t1.WhsCode = t3.WhsCode AND T3.u_bin is not null AND T2.DocEntry = " & Data("B1_Data.Document.DocEntry"))

I changed to DocEntry instead of DocNum, because DocNum is not an index without the Series-field.

hth

Manuel

Philipp Knecht

Hi Mike

Good point from manuel. Sometimes DocNum is not unique enough...
Besides that try to run the query in Query Analyzer with the corresponding params set.
We are not doing any magic just execute the query like query analyzer...

hth

Mike Seargent

Thanks guys this solution works. Your help is much appreciated!