Solved

Need some help wiht :CAST AS Double

Posted on 2016-09-13
11
38 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
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 10

Expert Comment

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

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 250 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 29

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
 

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 142

Assisted Solution

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

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:tsp2002
ID: 41795747
0
 
LVL 29

Accepted Solution

by:
Olaf Doschke earned 250 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 29

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

762 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now