[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Need some help wiht :CAST AS Double

Posted on 2016-09-13
11
Medium Priority
?
71 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 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
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 

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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

591 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