Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Need some help wiht :CAST AS Double

Posted on 2016-09-13
11
Medium Priority
?
61 Views
Last Modified: 2016-09-14
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
0
Comment
Question by:tsp2002
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 11

Expert Comment

by:HuaMinChen
ID: 41795592
Please ensure these columns should be with number data type, like integer, float or decimal.
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 41795614
it's the other way round:
AND CAST(Staerke AS Double)>='%s'

should be:
AND Staerke  >= CAST('%s' AS Double)

the same for the other columns.
0
 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 41795730
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.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:tsp2002
ID: 41795731
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);
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 41795734
I see nothing wrong at this code ...
maybe you review/show the sql resulting string?
0
 

Author Comment

by:tsp2002
ID: 41795747
0
 
LVL 30

Accepted Solution

by:
Olaf Doschke earned 1000 total points
ID: 41795759
Remove any term with brackets - one by one - until you don't get the error. Then you know the last term had the error. What seems wrong to me here is the {0x... part, but that's not addressed by the error at all, so seems to be specific to whatever you use here.

Besides, you'll also need CONVERT(datetime,'01.01.1980'), as - again - you can't compare two dates as strings, and even less when one side is converted to datetime. SQL Server could implicitly convert '01.01.1980' to a datetime, but only with all server side settings in german.

Could you perhaps cut out just the sql part and execute it within SSMS. Is the error really in the brackets of the SQL or outside?

Bye, Olaf.
0
 

Author Comment

by:tsp2002
ID: 41795886
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);
0
 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 41795896
Well, then keep shortening to find the essential missing or extra bracket. Also count number of %s and parameters.

Bye, Olaf.
0
 

Author Comment

by:tsp2002
ID: 41797821
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
0
 

Author Closing Comment

by:tsp2002
ID: 41797832
Thank you for your help
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question