Link to home
Start Free TrialLog in
Avatar of Thomas Stockbruegger
Thomas StockbrueggerFlag for Germany

asked on

Need some help wiht :CAST AS Double

Hello,
I would like to cast Breite1, Laenge, Staerke AS double that this query will work right.
In my sql they have the typ: varchar.

Sql_Search_String.Format("SELECT * FROM Angebot_Anfrage WHERE Angebot_Anfrage='%s' AND\
                                                       Typ>='%s' AND Typ<='%s' AND\
                                                       Staerke>='%s' AND Staerke<='%s' AND\
                                                       Breite1>='%s' AND Breite1<='%s' AND\
                                                       Laenge>='%s' AND Laenge<='%s'AND\
                                                       Gewicht>='%s' AND Gewicht<='%s' AND\
                                                       KdNr>='%s' AND KdNr<='%s' AND\
                                                       CONVERT(datetime,Ausstellungsdatum)>='%s' AND CONVERT(datetime,Ausstellungsdatum)<='%s' AND\
                                                       Guete LIKE '%s' AND Kennzahl_Auftrag_erhalten=1 \
                                                       ORDER BY Typ ASC, Abmessung ASC,\
                                                       Ausstellungsdatum DESC, Gewicht ASC,Nr ASC",\
                                                       str_Angebot_Anfrage,suche_Typ_von,suche_Typ_bis,\
                                                       suche_Staerke_von,suche_Staerke_bis,\
                                                       suche_Breite_von,suche_Breite_bis,\
                                                       suche_Laenge_von,suche_Laenge_bis,\
                                                       suche_Gewicht_von,suche_Gewicht_bis,\
                                                       suche_Kunde_von,suche_Kunde_bis,\
                                                       suche_AngebotsDatum_von,suche_AngebotsDatum_bis,\
                                                       suche_Artikelkurzbezeichnung,str_Auftrag_erhalten);


I tried this:
Sql_Search_String.Format("SELECT * FROM Angebot_Anfrage WHERE Angebot_Anfrage='%s' AND\
                                                       Typ>='%s' AND Typ<='%s' AND\
                                                       CAST(Staerke AS Double)>='%s' AND CAST(Staerke AS Double)<='%s' AND\
                                                       CAST(Breite1 AS Double)>='%s' AND CAST(Breite1 AS Double)<='%s' AND\........
will not work
Please help.
Thank you.
Best regards,
Thomas


P.S. Sql Server 2000
Avatar of Peter Chan
Peter Chan
Flag of Hong Kong image

Please ensure these columns should be with number data type, like integer, float or decimal.
SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Staerke  >= %s would work, if Staerke is numeric.

If Staerke is varchar, you'd need Cast(Staerke AS Double)  >= CAST('%s' AS Double) or Cast(Staerke AS Double)  >= %s

Comparing numbers as strings fails simply because comparison is not aligned for the decimal points, it's simply left aligned, so '5'>'10' is true, simply because 5>1. You can only keep types as strings and compare them, if you first align them, but, Well, how should I put it friendly? If you want to compare numbers, you better compare numbers, not strings. If you want to sotre numbers, store numbers in number type table columns, etc. Just because a string can hold any data type, strings are not your universal data store type. For example source code also is a string. That's why there are so many string representations for other types, eg for initialising variables. But computing with numbers is better be done in their realm, in their type.

Bye, Olaf.
Avatar of Thomas Stockbruegger

ASKER

I change the code like this. I got an error: wrong syntax at row 1 at ')'
Sql_Search_String.Format("SELECT * FROM Angebot_Anfrage \
WHERE Angebot_Anfrage='%s' AND\
                                                       Typ>='%s' AND Typ<='%s' AND\
                                                       Staerke>= CAST('%s' AS Double) AND Staerke<= CAST('%s' AS Double) AND\
                                                       Breite1>= CAST('%s' AS Double) AND Breite1<= CAST('%s' AS Double) AND\
                                                       Laenge>= CAST('%s' AS Double) AND Laenge<= CAST('%s' AS Double) AND\
                                                       Gewicht>= CAST('%s' AS Double) AND Gewicht<= CAST('%s' AS Double) AND\
                                                       KdNr>='%s' AND KdNr<='%s' AND\
                                                       CONVERT(datetime,Ausstellungsdatum)>='%s' AND CONVERT(datetime,Ausstellungsdatum)<='%s' AND\
                                                       Guete LIKE '%s' \
                                                       ORDER BY Typ ASC, Abmessung ASC,\
                                                       Ausstellungsdatum DESC, Gewicht ASC,Nr ASC",\
                                                       str_Angebot_Anfrage,suche_Typ_von,suche_Typ_bis,\
                                                       suche_Staerke_von,suche_Staerke_bis,\
                                                       suche_Breite_von,suche_Breite_bis,\
                                                       suche_Laenge_von,suche_Laenge_bis,\
                                                       suche_Gewicht_von,suche_Gewicht_bis,\
                                                       suche_Kunde_von,suche_Kunde_bis,\
                                                       suche_AngebotsDatum_von,suche_AngebotsDatum_bis,\
                                                       suche_Artikelkurzbezeichnung);
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I got the same error with this short search string: wrong syntax near by ')'

Sql_Search_String.Format("SELECT * FROM Angebot_Anfrage WHERE Angebot_Anfrage='%s' AND\
                                                                     Typ>='%s' AND Typ<='%s' AND\
                                                                     CAST(Staerke AS Double)>= '%s'\
                                                                     ",str_Angebot_Anfrage,suche_Typ_von,suche_Typ_bis,suche_Staerke_von);
Well, then keep shortening to find the essential missing or extra bracket. Also count number of %s and parameters.

Bye, Olaf.
Hello,
thank you very much for all your help.
It took a while but I have found the error.
I have to use float instead of double.

This code works fine:

Sql_Search_String.Format("SELECT * FROM Angebot_Anfrage WHERE Angebot_Anfrage='%s' AND\
                                                                    CAST(Typ AS float)>='%s' AND CAST(Typ AS float)<='%s' AND \
                                                                    CAST(Staerke AS float)>='%s' AND CAST(Staerke AS float)<='%s' AND \
                                                                    CAST(Breite1 AS float)>='%s' AND CAST(Breite1 AS float)<='%s' AND \
                                                                    CAST(Laenge AS float) >='%s' AND CAST(Laenge AS float)<= '%s' AND \
                                                                    CAST(Gewicht AS float)>='%s' AND CAST(Gewicht AS float)<='%s' AND \
                                                                    CAST(KdNr AS float)>='%s' AND CAST(KdNr AS float)<='%s' AND\
                                                                    CONVERT(datetime,Ausstellungsdatum)>='%s' AND CONVERT(datetime,Ausstellungsdatum)<='%s' AND\
                                                                    Guete LIKE '%s' ORDER BY Typ ASC, Abmessung ASC,Ausstellungsdatum DESC, Gewicht ASC,Nr ASC",\
                                                                    str_Angebot_Anfrage,suche_Typ_von,suche_Typ_bis,\
                                                                    suche_Staerke_von,suche_Staerke_bis,\
                                                                    suche_Breite_von,suche_Breite_bis,\
                                                                    suche_Laenge_von,suche_Laenge_bis,\
                                                                    suche_Gewicht_von,suche_Gewicht_bis,\
                                                                    suche_Kunde_von,suche_Kunde_bis,\
                                                                    suche_AngebotsDatum_von,suche_AngebotsDatum_bis,\
                                                                    suche_Artikelkurzbezeichnung);

I had also some trouble with this Sql_Search_String string and \.
My Visual Studio did not stop at this string (with a breakpoint) , so I had to remove all \
to get one long string and add them again. Now it stops at the break point.

Thank you very much.
Best regards,
Thomas
Thank you for your help