SQL Statement in app is returning unexpected results.

Posted on 2006-05-19
Last Modified: 2010-04-23
I am using a console app to transfer data to/from an site. I am using the following to create a dataset. I have tried Xferstatus as an integer and as text, but do not get any records returned.

The number of records returned is 0, although there are two records that meet the criteria. If I drop the 'Where" clause, all records are returned. If I run the statement from Enterprise Manager, I get the correct records returned.

Here is the complete statement:
        Dim dsRequests As New DataSet
        Dim da As New SqlDataAdapter(cmdSQL)
        Dim newS As Integer = s - 1
        cmdSQL.CommandText = "SELECT * FROM WorkRequests WHERE XferStatus = 0;"

        Catch ex As Exception
            strLogMsg = "Error in GetRequests"
            strExceptMsg = ex.Message
            ReqError = True
            NoException = False

            If cmdSQL.Connection.State = ConnectionState.Open Then cmdSQL.Connection.Close()
        End Try

        If NoException Then
                If dsRequests.Tables(0).Rows.Count = 0 Then

In this case, count = 0 and it should be 2.
Question by:jberv534
    LVL 96

    Assisted Solution

    by:Bob Learned
    What is 'XferStatus' declared as in SQL Server?


    Author Comment

    I have set as both an integer and char. In the form you see, it is an integer.
    LVL 3

    Assisted Solution

    I think the matter is the sql statement:  cmdSQL.CommandText = "SELECT * FROM WorkRequests WHERE XferStatus = 0"
    LVL 4

    Assisted Solution


    i think there is no problem with the statement. Did u get the code working inside the block

    If NoException Then
                    If dsRequests.Tables(0).Rows.Count = 0 Then

    Can u post yr complete code????
    LVL 34

    Accepted Solution

    If I drop the 'Where" clause, all records are returned.

    What happens with

            cmdSQL.CommandText = "SELECT * FROM WorkRequests WHERE XferStatus <> 0;"

    Does it return all records?  Or no records?  Or all records except those where XferStatus = 0?


    Author Comment

    Thank you all for the help. It turns-out, and I hate to admit this, but it was strictly my problem in controlling the application.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
    Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
    This video discusses moving either the default database or any database to a new volume.
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    737 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

    19 Experts available now in Live!

    Get 1:1 Help Now