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

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

news:EDY6e.25476$vt1.11985@edtnps90...
Show quoteHide quote

Tom,

Casting in VBNet is DirectCast, CType is converting

Cor

Tom,

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

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdataoledboledbdatareaderclassitemtopic.asp

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

I hope this helps,

Cor

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

news:EDY6e.25476$vt1.11985@edtnps90...
Show quoteHide quote

Tom,
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).ToString() +
Chr(9))
            Console.Write(myDataReader.GetString(fieldFirstName) + " " +
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).ToString()
+ Chr(10))
            End If
        Loop

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


news:EDY6e.25476$vt1.11985@edtnps90...
Show quoteHide quote

Translate C# to VB.net?
Splash window
XmlTextReader not getting all Elements
Singleton Pattern for Database Access --- Leave Open or Close Connection
Combobox and DataSource
"Add Reference" hosed--doesn't display the dialog in VS.NET
PDF to Picturebox
Newbie: substrings
VB.net Datagrid Parent Row
Open a form and select the tab page