AboutPricingCommunityTeamsStart Free TrialLog in
Avatar of Thomas Stockbruegger
Thomas Stockbruegger
Flag 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
Microsoft SQL ServerMicrosoft SQL Server 2008Microsoft SQL Server 2005
Avatar of undefined
Last Comment
Thomas Stockbruegger
8/22/2022 - Mon
Peter Chan
Please ensure these columns should be with number data type, like integer, float or decimal.
SOLUTION
Guy Hengel [angelIII / a3]
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Olaf Doschke
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.
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);
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
SOLUTION
Guy Hengel [angelIII / a3]
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Thomas Stockbruegger
ASKER
ASKER CERTIFIED SOLUTION
Olaf Doschke
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Thomas Stockbruegger
ASKER
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);
Olaf Doschke
Well, then keep shortening to find the essential missing or extra bracket. Also count number of %s and parameters.

Bye, Olaf.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Thomas Stockbruegger
ASKER
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
Thomas Stockbruegger
ASKER
Thank you for your help
Plans and Pricing
Resources
Product
Podcasts
Careers
Contact Us
Teams
Certified Expert Program
Credly Partnership
Udemy Partnership
Privacy Policy
Terms of Use

© 1996-2023 Experts Exchange, LLC. All rights reserved. Covered by US Patent