Using the SQLDataReader with empty parameters

Posted on 2007-07-31
Last Modified: 2010-04-23
I am using the following code to return a value from a SQL Server table depending upon what a user enters into a textbox. I want to put this code in the textboxes TextChanged sub. This will mean that as soon as a match is found the result will appear on screen in another textbox.

        Dim STR_cont As String
        STR_cont = TXT_Cont.Text

        Dim STR_parts As String() = STR_cont.Split("-")

        If STR_parts.Length = 1 Then
            ' - not found in the string decide what to do
        End If

        Dim part1 As String = STR_parts(0)
        Dim part2 As String = STR_parts(1)
        Dim reader As SqlDataReader

        SqlCommand1.Parameters("@Cont#").Value = "" & STR_parts(0) & ""
        SqlCommand1.Parameters("@Str#").Value = "" & STR_parts(1) & ""

        reader = SqlCommand1.ExecuteReader
        TXT_name.Text = reader("NAME")


My problem is that the code fails on line 'TXT_name.Text = reader("NAME")' as soon as a '-' is entered by the user. This is because the value of STR_parts(1) is empty.

How can I get around this?
I am using VB.NET, (visual studio 2003)

Question by:FMabey
    LVL 3

    Author Comment

    Just to say. I've solved this issue buy inserting the TXT_name.Text = reader("NAME") line into an if statement as shown below. Points to me I guess ;o)

            If reader.HasRows Then

                TXT_name.Text = reader("NAME")

            End If

    LVL 46

    Accepted Solution

    Hello FMabey,

    An alternative is to do something like this....

            reader = SqlCommand1.ExecuteReader
            If reader.Read Then
                 TXT_name.Text = reader("NAME")
             End If

    This is because Read returns a boolean if there is a "next" record to read.



    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
    Introduction When many people think of the WebBrowser ( control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    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…

    733 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

    21 Experts available now in Live!

    Get 1:1 Help Now