Data Column Issue

[Stephen Costanzo]

I have a control table that lists the columns to pull from the associated
data table. If there is a column name in the control table that doesn't
exist in the data table, it generates a IndexOutOfRange exception. My
question is this: Why is the error message the name of the column:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click

Dim conn As New SqlConnection
Dim connectionString As String
Dim sqlQuery As New SqlCommand

connectionString = "Persist Security Info=False; Data Source=APOLLO" _
& ";Initial Catalog=ABC" & ";User ID=xxxx" & ";Password=yyyyy"

conn.ConnectionString = connectionString
conn.Open()

Try
Dim appInfo As SqlDataReader

sqlQuery.CommandText = "SELECT * FROM tbl_Applications WHERE
ApplicationName = 'TestApplication'"
sqlQuery.Connection = conn

appInfo = sqlQuery.ExecuteReader

MessageBox.Show(CStr(appInfo.Item("Georgia")))

Catch idxex As IndexOutOfRangeException
MessageBox.Show("IndEX:" + idxex.Message)
Catch sqlex As SqlException
MessageBox.Show("SQLEX: " + sqlex.Message + " #:" + CStr(sqlex.Number))
Catch ex As Exception
MessageBox.Show("EX: " + ex.Message)
End Try

End Sub

The message box comes up with IndEX:Georgia.

 

[Chris]

I have a control table that lists the columns to pull from the associated
data table. If there is a column name in the control table that doesn't
exist in the data table, it generates a IndexOutOfRange exception. My
question is this: Why is the error message the name of the column:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click

Dim conn As New SqlConnection
Dim connectionString As String
Dim sqlQuery As New SqlCommand

connectionString = "Persist Security Info=False; Data Source=APOLLO" _
& ";Initial Catalog=ABC" & ";User ID=xxxx" & ";Password=yyyyy"

conn.ConnectionString = connectionString
conn.Open()

Try
Dim appInfo As SqlDataReader

sqlQuery.CommandText = "SELECT * FROM tbl_Applications WHERE
ApplicationName = 'TestApplication'"
sqlQuery.Connection = conn

appInfo = sqlQuery.ExecuteReader

MessageBox.Show(CStr(appInfo.Item("Georgia")))

Catch idxex As IndexOutOfRangeException
MessageBox.Show("IndEX:" + idxex.Message)
Catch sqlex As SqlException
MessageBox.Show("SQLEX: " + sqlex.Message + " #:" + CStr(sqlex.Number))
Catch ex As Exception
MessageBox.Show("EX: " + ex.Message)
End Try

End Sub

The message box comes up with IndEX:Georgia.

The reader tries to resolve the column name to the column index. Since
that returns "nothing" you are trying then to get an index of nothing.
You are basically asking it to do:

appInfo.Item(Nothing)

I believe you should be able to do a "Reader.GetOrdinal" to see if it is
a valid column name.

Hope that helps.

 

[Stephen Costanzo]

Chris,

Good 'pre error' check suggestion. I was more suprised that the
idxEx.Message wasn't "Column not found" as opposed to just the column name.
Its not a really expressive message.