Link to home
Start Free TrialLog in
Avatar of FMabey
FMabeyFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Using the SQLDataReader with empty parameters

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
            Return
        End If

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

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

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

        SqlConnection1.Close()

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)

Cheers.
Avatar of FMabey
FMabey
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

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

Cheers
ASKER CERTIFIED SOLUTION
Avatar of Wayne Taylor (webtubbs)
Wayne Taylor (webtubbs)
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial