VBS Script: Error - The text, ntetxt, and image data types cannot be compared or sorted

Posted on 2005-05-17
Last Modified: 2010-05-18
I am using a VBS script to collect a hardware inventory - I have a database HardwareInventory on SQL 2000 which it should add fields & records to.  My problem is it is failing on the last line "myMS2.Open countSQL, myConn, 3,1" line below, giving the error: "The text, ntetxt, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator", Code 80040E14.  - I have pasted the code preceding it .

The Hardware table contains 2 fields, hardware (text, 16) and strComputer (text, 16).

myConn = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=HardwareInventory;Data Source=sifirlsql1"
countSQL = "SELECT COUNT(*) as hits FROM hardware where strComputer = '" & strComputer & "'"
strSQL = "SELECT * FROM hardware WHERE strComputer = '" & strComputer & "'"
set myMS2 = CreateObject("ADODB.RecordSet")
set myMS = CreateObject("ADODB.RecordSet")
myMS2.Open countSQL, myConn, 3,1
Question by:MgtStack
    1 Comment
    LVL 6

    Accepted Solution

    It's exactly as the error message describes.  The field you are comparing "strComputer" is of the Text data type.  This field cannot be used in a comparison using the "=" operator.  Try changing your SQL statement to the following:

    countSQL = "SELECT COUNT(*) as hits FROM hardware where strComputer LIKE '" & strComputer & "'"

    That should work.


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    why to use string 10 53
    matchUp  challenge 9 51
    noX challenge 17 54
    mapShare challenge 13 41
    Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
    Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
    An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
    In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

    755 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

    24 Experts available now in Live!

    Get 1:1 Help Now