SqlDataReader accessed only by field ordinals?

[Tom Edelbrok]

I notice that using the SqlDataReader requires the use of ordinal field
references rather than by name.
For example,

do while (myDataReader.Read())
Console.Write(myDataReader.GetInt32(0).ToString() + Chr(9))
Console.Write(myDataReader.GetString(2) + " " +
myDataReader.GetString(1) + Chr(9))
Console.Write(myDataReader.GetString(3) + Chr(9))
if (myDataReader.IsDBNull(4)) then
Console.Write("N/A" + Chr(10))
else
Console.Write(myDataReader.GetInt32(4).ToString() + Chr(10))
end if
loop

Is there a way for me to read data from SQL by field name (ie: column name)
rather than by ordinal? It is much easier to program this way and also
handles situations where fields have been inserted or otherwise changed
position in a table.

Thanks in advance,

Tom

[Tom Dacon]

It works fine with field names. You just need to do a cast (CType for VB) to
the correct type, since you get Object back.

Tom Dacon
Dacon Software Consulting

"Tom Edelbrok" <an*******@anonymous.com> wrote in message
news:EDY6e.25476$vt1.11985@edtnps90...

I notice that using the SqlDataReader requires the use of ordinal field
references rather than by name.
For example,

do while (myDataReader.Read())
Console.Write(myDataReader.GetInt32(0).ToString() + Chr(9))
Console.Write(myDataReader.GetString(2) + " " +
myDataReader.GetString(1) + Chr(9))
Console.Write(myDataReader.GetString(3) + Chr(9))
if (myDataReader.IsDBNull(4)) then
Console.Write("N/A" + Chr(10))
else
Console.Write(myDataReader.GetInt32(4).ToString() + Chr(10))
end if
loop

Is there a way for me to read data from SQL by field name (ie: column name) rather than by ordinal? It is much easier to program this way and also
handles situations where fields have been inserted or otherwise changed
position in a table.

Thanks in advance,

Tom

[Cor Ligthert]

Tom,

It works fine with field names. You just need to do a cast (CType for VB)
to
the correct type, since you get Object back.

Casting in VBNet is DirectCast, CType is converting

Cor

[Cor Ligthert]

Tom,

In addition to the other Tom the link too the item from the reader

http://msdn.microsoft.com/library/de...sitemtopic.asp

\\\
Dim arrImage() As Byte = DirectCast(rdr.Item("Photo"), Byte())
///

I hope this helps,

Cor

[Cor Ligthert]

Tom,

Looking at your problem, I got this idea for an often asked question for
filling a datable while using a progressbar
\\\Needs a progressbar, a button and a datagrid
Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
Dim conn As New SqlClient.SqlConnection("Server=MyServer;" & _
"DataBase=Northwind; Integrated Security=SSPI")
Dim cmd As New SqlClient.SqlCommand("Select Count(*) from
Employees", conn)
conn.Open()
ProgressBar1.Maximum = DirectCast(cmd.ExecuteScalar, Integer)
ProgressBar1.Step = 1
ProgressBar1.Minimum = 0
cmd.CommandText = "SELECT * FROM Employees"
Dim rdr As SqlClient.SqlDataReader = cmd.ExecuteReader()
Dim dt As DataTable
While rdr.Read
If dt Is Nothing Then
dt = New DataTable
Dim dtschema As DataTable
dtschema = rdr.GetSchemaTable
For Each drschema As DataRow In dtschema.Rows
dt.Columns.Add(drschema("ColumnName").ToString, _
Type.GetType(drschema("DataType").ToString))
Next
End If
ProgressBar1.PerformStep()
Dim dr As DataRow = dt.NewRow
Dim tempObject(dt.Columns.Count - 1) As Object
rdr.GetValues(tempObject) 'did not go in one time
dr.ItemArray = tempObject
dt.Rows.Add(dr)
Threading.Thread.Sleep(500) 'only for showing
End While
DataGrid1.DataSource = dt
rdr.Close()
conn.Dispose()
End Sub
///
Maybe it gives you as well some ideas?

Cor

"Tom Edelbrok" <an*******@anonymous.com> schreef in bericht
news:EDY6e.25476$vt1.11985@edtnps90...

I notice that using the SqlDataReader requires the use of ordinal field
references rather than by name.
For example,

do while (myDataReader.Read())
Console.Write(myDataReader.GetInt32(0).ToString() + Chr(9))
Console.Write(myDataReader.GetString(2) + " " +
myDataReader.GetString(1) + Chr(9))
Console.Write(myDataReader.GetString(3) + Chr(9))
if (myDataReader.IsDBNull(4)) then
Console.Write("N/A" + Chr(10))
else
Console.Write(myDataReader.GetInt32(4).ToString() + Chr(10))
end if
loop

Is there a way for me to read data from SQL by field name (ie: column
name) rather than by ordinal? It is much easier to program this way and
also handles situations where fields have been inserted or otherwise
changed position in a table.

Thanks in advance,

Tom

[Jay B. Harlow [MVP - Outlook]]

Tom,
| Is there a way for me to read data from SQL by field name (ie: column
name)
| rather than by ordinal?
What I do is get the ordinals just before the loop using the
DataReader.GetOrdinalfunction.

Something like:

myDataReader = myCommand.ExecuteReader(...)

Dim fieldId As Integer = myDataReader.GetOrdinal("Id")
Dim fieldLastName As Integer = myDataReader.GetOrdinal("LastName")
Dim fieldFirstName As Integer = myDataReader.GetOrdinal("FirstName")
Dim fieldAddress As Integer = myDataReader.GetOrdinal("Address")
Dim fieldAmount As Integer = myDataReader.GetOrdinal("Amount")

Do While (myDataReader.Read())
Console.Write(myDataReader.GetInt32(fieldId).ToStr ing() +
Chr(9))
Console.Write(myDataReader.GetString(fieldFirstNam e) + " " +
myDataReader.GetString(fieldLastName) + Chr(9))
Console.Write(myDataReader.GetString(fieldAddress) + Chr(9))
If (myDataReader.IsDBNull(fieldAmount)) Then
Console.Write("N/A" + Chr(10))
Else
Console.Write(myDataReader.GetInt32(fieldAmount).T oString()
+ Chr(10))
End If
Loop

| It is much easier to program this way and also
| handles situations where fields have been inserted or otherwise changed
| position in a table.

When not using GetOrdinal as above, I've seen people use either constants or
Enums instead of integer literals to represent the ordinal positions...

Hope this helps
Jay
"Tom Edelbrok" <an*******@anonymous.com> wrote in message
news:EDY6e.25476$vt1.11985@edtnps90...
|I notice that using the SqlDataReader requires the use of ordinal field
| references rather than by name.
| For example,
|
| do while (myDataReader.Read())
| Console.Write(myDataReader.GetInt32(0).ToString() + Chr(9))
| Console.Write(myDataReader.GetString(2) + " " +
| myDataReader.GetString(1) + Chr(9))
| Console.Write(myDataReader.GetString(3) + Chr(9))
| if (myDataReader.IsDBNull(4)) then
| Console.Write("N/A" + Chr(10))
| else
| Console.Write(myDataReader.GetInt32(4).ToString() + Chr(10))
| end if
| loop
|
| Is there a way for me to read data from SQL by field name (ie: column
name)
| rather than by ordinal? It is much easier to program this way and also
| handles situations where fields have been inserted or otherwise changed
| position in a table.
|
| Thanks in advance,
|
| Tom
|
|
|