This may sound a little weird, but here we go anyway. I have a field in my
table and it is identified as a varchar. In my asp code if I search for
numbers(on this field) it will not work correctly. I thought a varchar
served mulitple purposes?
SELECT * from equip LEFT JOIN COMPANY ON equip.CONTROL = COMPANY.CONTROL
Where advertise='yes' and etype='Levee Plow' AND V1 >= '1' AND V1 <= '2000'
order by model,price
David Responds:
This is a hazard of using varchar fields for numerics. As far as the ASCII
code is concerned, this is a true comparison:
'1' <= 'a'
If the field has nothing but numerics in it, consider changing it to an INT
column or create a new column. Of course, not knowing what is in the column
(other data) then the solutions that will be provided may help.
You may want to research in SQL BOL Convert() and Cast()
Jamie Adds:
I'm not sure if you can use the >=, <= operators with strings.
You should probably look into the CAST or CONVERT functions of T-SQL.
I'm not sure of the syntax, but pseudo code would loke something like:
SELECT * from equip LEFT JOIN COMPANY ON equip.CONTROL = COMPANY.CONTROL
Where advertise='yes' and etype='Levee Plow' AND CONVERT(int,V1) >= 1 AND
convert(int,V1) <= 2000 order by model,price
Look in the BOL for more exact info.
Dan Says:
Try trimming the field - doesn't VARCHAR pad with spaces?
Nick Replies:
VARCHAR means variable character length, and I believe CHAR will become padded with spaces.
VARCHAR is a string, text, letters. They are not numbers, digits, figures,
and a field can't be both a number and string!
If you're using SQL to get data from a table and you want to do a SELECT on
a field that's a VARCHAR but you need to compare it against a number then
you'll need to convert the VARCHAR field to a number in the SQL statement.
So have a look in your SQL Books On Line for CAST and CONVERT. This'll
explain how to do it!
This conversation string was taken from the 15Seconds ASP Listserv on 2/27/01. If you have an ASP-related question or would like to share some of your knowledge with others, you may join the list by clicking here.
Proposion N2N connects Microsoft .NET applications to Lotus Notes and Lotus Domino databases. This ADO.NET managed data provider allows you to perform blindingly fast queries and updates of Notes data from ASP.NET pages, .NET web services, Windows, or Mobile applications. An innovative SQL-like query language leverages the unique features of Notes and makes collaborative software accessible to relational database programmers.
Unlike text-based file formats image files aren't made up of words, which makes searching for an image file by keyword difficult. Instead of being able to simply open the file to see what it contains, we're stuck looking at the text around it and other metadata to determine the image's meaning. In this article, Ziran Sun shows you how to build a simple database-based image keyword system that allows you to associate keywords with images and use these keywords to make finding images easier. [Read This Article][Top]
In the second part of of his article on using MySQL with ASP.NET, Ziran Sun covers how to add a new MySQL user to the database server, assign the user the appropriate permissions, connect to the database, and build a simple ASP.NET page to perform a query. [Read This Article][Top]
Back in the days of classic ASP, if you were building a database-driven
web site, your choice was either to invest a lot of money to get a copy of Microsoft SQL Server
(or some other enterprise-ready database) or invest a lot of time finding a way to deal with the
performance and scalability limitations of Microsoft Access. Luckily these days there's
another viable alternative: MySQL. [Read This Article][Top]
Moving or copying a SQL Server database from one machine to another requires a lot of preparation in order to ensure a smooth transfer. In this article, Dina Fleet Berry examines the different methods and highlights the different issues associated with each of them. [Read This Article][Top]
There are many times when using SQL Server 2000 Query Analyzer to debug SQL statements is a better choice than debugging in Visual Studio .NET. In this article, Dina Fleet Berry explains why and walks you through the debugging process step-by step. [Read This Article][Top]
As a follow up to his article on retrieving objects from SQL Server using SQLXML and serialization, Gianluca Nuzzo discusses saving objects back to SQL Server using a schema definition file and updategrams. [Read This Article][Top]
One area that stands out when comparing ADO.NET 1.x to ADO.NET 2.0 is transaction processing. Bill Ryan shows just how easy transaction processing has become with the TransactionScope object in ADO.NET 2.0. [Read This Article][Top]
Developers often use brute force coding to marshal data between the GUI and application objects. In this article, Luther Stanton explains how to use .NET's out-of-the box data-binding functionality to make this job much easier. [Read This Article][Top]
Learn how to create a console application to queue a message in Microsoft Message Queuing (MSMQ) and then use an extended stored procedure to call the console application from a SQL Server trigger. [Read This Article][Top]
Connection pooling increases the performance of Web applications by reusing active database connections instead of creating a new connection with every request. This article shows how to monitor the connection pool, diagnose a potential problem, and apply the appropriate fix. [Read This Article][Top]
Mailing List Want to receive email when the next article is published? Just Click Here to sign up.
