I've got this code:
Protected Function GetArgValsForCompanyName(coName As String) As String()
Dim args(2) As String
Dim sqlConnection1 As New SqlConnection("SERVER=PLATYPUS42;DATABASE=duckbilldata;UID=durante;PWD=pondscum")
Dim cmd As New SqlCommand
Dim reader As SqlDataReader
cmd.CommandText = "select Unit, MemberNo, CustNo from Customers WHERE CompanyName = @CoName"
cmd.CommandType = CommandType.Text
cmd.Parameters.Add("@CoName", SqlDbType.VarChar, 50).Value = coName
cmd.Connection = sqlConnection1
sqlConnection1.Open()
reader = cmd.ExecuteReader()
If reader.HasRows Then
args(0) = reader.Item(0).ToString()
args(1) = reader.Item(1).ToString()
args(2) = reader.Item(2).ToString()
End If
reader.Close()
sqlConnection1.Close()
Return args
End Function
...which fails on this line:
args(0) = reader.Item(0).ToString()
...with:
*System.InvalidOperationException was unhandled
HResult=-2146233079
Message=Invalid attempt to read when no data is present.*
How can it be that it "HasRows" and yet there is no data present?
NOTE: It also fails with the same error when I try this (instead of using the "0" index):
args(0) = reader.Item("Unit").ToString()
The accepted answer works fine in modern apps (e.g., my "sandbox" Windows forms app), but in olden apps, such as a creaky, archaic web site, which uses .NET prehistoric, it doesn't- the "usings" are apparently unrecognized; I get:
Server Error in '/EMS/customerreportingnet' Application.
--------------------------------------------------------------------------------
Compilation Error
Description: An error occurred during the compilation of a resource required to service this request.
Please review the following specific error details and modify your source code appropriately.
Compiler Error Message: BC30203: Identifier expected.
Source Error:
Line 90: Dim args(2) As String
Line 91:
Line 92: Using con As New SqlConnection("SERVER=PLATYPUS42;DATABASE=duckbilldata;UID=durante;PWD=pondscum"),
Line 93: cmd As New SqlCommand("select Unit, MemberNo, CustNo from Customers WHERE
CompanyName = @CoName", con)
Line 94:
--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:2.0.50727.5485; ASP.NET Version:2.0.50727.5491
As you say in your answer, a call to .Read
is required.
The If reader.HasRows
however isn't required. The Do While reader.Read
will handle this. If there are rows then it will enter the loop, otherwise it will bypass.
As an additional note, I think it would be beneficial to implement Using:
Protected Function GetArgValsForCompanyName(coName As String) As String()
Dim args(2) As String
Using con As New SqlConnection("SERVER=PLATYPUS42;DATABASE=duckbilldata;UID=durante;PWD=pondscum"),
cmd As New SqlCommand("select Unit, MemberNo, CustNo from Customers WHERE CompanyName = @CoName", con)
con.Open()
cmd.CommandType = CommandType.Text
cmd.Parameters.Add("@CoName", SqlDbType.VarChar, 50).Value = coName
Using reader As SqlDataReader = cmd.ExecuteReader
While reader.Read
args(0) = reader.Item(0).ToString()
args(1) = reader.Item(1).ToString()
args(2) = reader.Item(2).ToString()
End While
End Using
End Using
Return args
End Function
With Using
you don't have to worry about calling .Close
or the disposing of objects. I also feel it reads better.