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.R ead())
Console.Write(m yDataReader.Get Int32(0).ToStri ng() + Chr(9))
Console.Write(m yDataReader.Get String(2) + " " +
myDataReader.Ge tString(1) + Chr(9))
Console.Write(m yDataReader.Get String(3) + Chr(9))
if (myDataReader.I sDBNull(4)) then
Console.Write(" N/A" + Chr(10))
else
Console.Write(m yDataReader.Get Int32(4).ToStri ng() + 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*******@anon ymous.com> wrote in message
news:EDY6e.2547 6$vt1.11985@edt nps90...

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

do while (myDataReader.R ead())
Console.Write(m yDataReader.Get Int32(0).ToStri ng() + Chr(9))
Console.Write(m yDataReader.Get String(2) + " " +
myDataReader.Ge tString(1) + Chr(9))
Console.Write(m yDataReader.Get String(3) + Chr(9))
if (myDataReader.I sDBNull(4)) then
Console.Write(" N/A" + Chr(10))
else
Console.Write(m yDataReader.Get Int32(4).ToStri ng() + 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(B yVal sender As System.Object, _
ByVal e As System.EventArg s) Handles Button1.Click
Dim conn As New SqlClient.SqlCo nnection("Serve r=MyServer;" & _
"DataBase=North wind; Integrated Security=SSPI")
Dim cmd As New SqlClient.SqlCo mmand("Select Count(*) from
Employees", conn)
conn.Open()
ProgressBar1.Ma ximum = DirectCast(cmd. ExecuteScalar, Integer)
ProgressBar1.St ep = 1
ProgressBar1.Mi nimum = 0
cmd.CommandText = "SELECT * FROM Employees"
Dim rdr As SqlClient.SqlDa taReader = cmd.ExecuteRead er()
Dim dt As DataTable
While rdr.Read
If dt Is Nothing Then
dt = New DataTable
Dim dtschema As DataTable
dtschema = rdr.GetSchemaTa ble
For Each drschema As DataRow In dtschema.Rows
dt.Columns.Add( drschema("Colum nName").ToStrin g, _
Type.GetType(dr schema("DataTyp e").ToString ))
Next
End If
ProgressBar1.Pe rformStep()
Dim dr As DataRow = dt.NewRow
Dim tempObject(dt.C olumns.Count - 1) As Object
rdr.GetValues(t empObject) 'did not go in one time
dr.ItemArray = tempObject
dt.Rows.Add(dr)
Threading.Threa d.Sleep(500) 'only for showing
End While
DataGrid1.DataS ource = dt
rdr.Close()
conn.Dispose()
End Sub
///
Maybe it gives you as well some ideas?

Cor

"Tom Edelbrok" <an*******@anon ymous.com> schreef in bericht
news:EDY6e.2547 6$vt1.11985@edt nps90...

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

do while (myDataReader.R ead())
Console.Write(m yDataReader.Get Int32(0).ToStri ng() + Chr(9))
Console.Write(m yDataReader.Get String(2) + " " +
myDataReader.Ge tString(1) + Chr(9))
Console.Write(m yDataReader.Get String(3) + Chr(9))
if (myDataReader.I sDBNull(4)) then
Console.Write(" N/A" + Chr(10))
else
Console.Write(m yDataReader.Get Int32(4).ToStri ng() + 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.GetO rdinalfunction.

Something like:

myDataReader = myCommand.Execu teReader(...)

Dim fieldId As Integer = myDataReader.Ge tOrdinal("Id")
Dim fieldLastName As Integer = myDataReader.Ge tOrdinal("LastN ame")
Dim fieldFirstName As Integer = myDataReader.Ge tOrdinal("First Name")
Dim fieldAddress As Integer = myDataReader.Ge tOrdinal("Addre ss")
Dim fieldAmount As Integer = myDataReader.Ge tOrdinal("Amoun t")

Do While (myDataReader.R ead())
Console.Write(m yDataReader.Get Int32(fieldId). ToString() +
Chr(9))
Console.Write(m yDataReader.Get String(fieldFir stName) + " " +
myDataReader.Ge tString(fieldLa stName) + Chr(9))
Console.Write(m yDataReader.Get String(fieldAdd ress) + Chr(9))
If (myDataReader.I sDBNull(fieldAm ount)) Then
Console.Write(" N/A" + Chr(10))
Else
Console.Write(m yDataReader.Get Int32(fieldAmou nt).ToString()
+ 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*******@anon ymous.com> wrote in message
news:EDY6e.2547 6$vt1.11985@edt nps90...
|I notice that using the SqlDataReader requires the use of ordinal field
| references rather than by name.
| For example,
|
| do while (myDataReader.R ead())
| Console.Write(m yDataReader.Get Int32(0).ToStri ng() + Chr(9))
| Console.Write(m yDataReader.Get String(2) + " " +
| myDataReader.Ge tString(1) + Chr(9))
| Console.Write(m yDataReader.Get String(3) + Chr(9))
| if (myDataReader.I sDBNull(4)) then
| Console.Write(" N/A" + Chr(10))
| else
| Console.Write(m yDataReader.Get Int32(4).ToStri ng() + 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
|
|
|