Hi

I desperately need some help. I want to have a DataView sorted
alphabetically by a specific column, then I want to find the first record
that begins with the letter the user inputs and get that record number. I
don't however want a filter as the user must still be able to scroll up and
down the DataView.

Thanks. Any help would be greatly appreciated.

Dazz

Dazz,
Have you tried using the DataView.Find method to find the letter you are
interested in?

In case you don't have it David Sceppa's book "Microsoft ADO.NET - Core
Reference" covers every thing you ever wanted to know about ADO.NET but were
afraid to ask.

Hope this helps
Jay

"Dazz" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Hi
>
> I desperately need some help. I want to have a DataView sorted
> alphabetically by a specific column, then I want to find the first record
> that begins with the letter the user inputs and get that record number. I
> don't however want a filter as the user must still be able to scroll up
and
> down the DataView.
>
> Thanks. Any help would be greatly appreciated.
>
> Dazz
>
>

Doh!

That's not going to work, as DataView.Find finds a row with the specified
key, you want to find a partial key...

I would use either a second DataView where the second one has a filter of
the letter I am interested in. Or the DataTable.Select statement to find the
rows with the letter I am interested in. Based on the first row returned
from either of the above I would have the key of the row of interest to pass
the DataView.Find method of the first DataView.

Of course I believe if you added a computed column to your DataTable that
contained the first letter of the specific column, you could find on the
computed column...

Hope this helps (this time ;-))
Jay

"Jay B. Harlow [MVP - Outlook]" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Dazz,
> Have you tried using the DataView.Find method to find the letter you are
> interested in?
>
> In case you don't have it David Sceppa's book "Microsoft ADO.NET - Core
> Reference" covers every thing you ever wanted to know about ADO.NET but
were
> afraid to ask.
>
> Hope this helps
> Jay
>
> "Dazz" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
> > Hi
> >
> > I desperately need some help. I want to have a DataView sorted
> > alphabetically by a specific column, then I want to find the first
record
> > that begins with the letter the user inputs and get that record number.
I
> > don't however want a filter as the user must still be able to scroll up
> and
> > down the DataView.
> >
> > Thanks. Any help would be greatly appreciated.
> >
> > Dazz
> >
> >
>
>

Hi Jay,

I think you're on the right track, but it doesn't work - I opened a table in
"addr" order; I added the column; I assigned values to the column of the
initial letter of "addr" - while the 'G's were all together, they were in
random order, not in the order of the table that was opened (I assigned by
running a for each loop that saw the datatable in strict addr order, as I
reviewed this).

But when you now search on the computed column, it finds a 'G' randomly,
not, as you would expect, the G from, say, Gable, evidently because any 'G'
will do.

Any ideas how to get around this?

Bernie Yaeger

"Jay B. Harlow [MVP - Outlook]" <(E-Mail Removed)> wrote in message
news:e%(E-Mail Removed)...
> Doh!
>
> That's not going to work, as DataView.Find finds a row with the specified
> key, you want to find a partial key...
>
> I would use either a second DataView where the second one has a filter of
> the letter I am interested in. Or the DataTable.Select statement to find
the
> rows with the letter I am interested in. Based on the first row returned
> from either of the above I would have the key of the row of interest to
pass
> the DataView.Find method of the first DataView.
>
> Of course I believe if you added a computed column to your DataTable that
> contained the first letter of the specific column, you could find on the
> computed column...
>
> Hope this helps (this time ;-))
> Jay
>
> "Jay B. Harlow [MVP - Outlook]" <(E-Mail Removed)> wrote in
message
> news:%(E-Mail Removed)...
> > Dazz,
> > Have you tried using the DataView.Find method to find the letter you are
> > interested in?
> >
> > In case you don't have it David Sceppa's book "Microsoft ADO.NET - Core
> > Reference" covers every thing you ever wanted to know about ADO.NET but
> were
> > afraid to ask.
> >
> > Hope this helps
> > Jay
> >
> > "Dazz" <(E-Mail Removed)> wrote in message
> > news:%(E-Mail Removed)...
> > > Hi
> > >
> > > I desperately need some help. I want to have a DataView sorted
> > > alphabetically by a specific column, then I want to find the first
> record
> > > that begins with the letter the user inputs and get that record
number.
> I
> > > don't however want a filter as the user must still be able to scroll
up
> > and
> > > down the DataView.
> > >
> > > Thanks. Any help would be greatly appreciated.
> > >
> > > Dazz
> > >
> > >
> >
> >
>
>

Hi,

You can try this.

Dim conn As SqlConnection

Dim strConn As String

Dim strSQL As String

Dim ds As New DataSet

Dim da As SqlDataAdapter

Dim dv As DataView

Dim cm As CurrencyManager

Dim iRow As Integer

strConn = "Server = " + Environment.MachineName + "\VSdotNet;"

strConn += "Database = Northwind;"

strConn += "Integrated Security = SSPI;"

conn = New SqlConnection(strConn)

da = New SqlDataAdapter("Select * from Customers", conn)

da.Fill(ds, "Customers")

dv = New DataView(ds.Tables("Customers"))

DataGrid1.DataSource = dv

cm = CType(Me.BindingContext(dv), CurrencyManager)

dv.Sort = "CustomerID"

For x As Integer = 0 To dv.Count - 1

Dim drv As DataRowView = dv(x)

Dim strCustomer As String = drv.Item("CustomerID")

If strCustomer.ToUpper.StartsWith("P") Then

cm.Position = x

Exit For

End If

Next

Ken

-------------------------

"Bernie Yaeger" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi Jay,
>
> I think you're on the right track, but it doesn't work - I opened a table
in
> "addr" order; I added the column; I assigned values to the column of the
> initial letter of "addr" - while the 'G's were all together, they were in
> random order, not in the order of the table that was opened (I assigned by
> running a for each loop that saw the datatable in strict addr order, as I
> reviewed this).
>
> But when you now search on the computed column, it finds a 'G' randomly,
> not, as you would expect, the G from, say, Gable, evidently because any
'G'
> will do.
>
> Any ideas how to get around this?
>
> Bernie Yaeger
>
> "Jay B. Harlow [MVP - Outlook]" <(E-Mail Removed)> wrote in
message
> news:e%(E-Mail Removed)...
> > Doh!
> >
> > That's not going to work, as DataView.Find finds a row with the
specified
> > key, you want to find a partial key...
> >
> > I would use either a second DataView where the second one has a filter
of
> > the letter I am interested in. Or the DataTable.Select statement to find
> the
> > rows with the letter I am interested in. Based on the first row returned
> > from either of the above I would have the key of the row of interest to
> pass
> > the DataView.Find method of the first DataView.
> >
> > Of course I believe if you added a computed column to your DataTable
that
> > contained the first letter of the specific column, you could find on the
> > computed column...
> >
> > Hope this helps (this time ;-))
> > Jay
> >
> > "Jay B. Harlow [MVP - Outlook]" <(E-Mail Removed)> wrote in
> message
> > news:%(E-Mail Removed)...
> > > Dazz,
> > > Have you tried using the DataView.Find method to find the letter you
are
> > > interested in?
> > >
> > > In case you don't have it David Sceppa's book "Microsoft ADO.NET -
Core
> > > Reference" covers every thing you ever wanted to know about ADO.NET
but
> > were
> > > afraid to ask.
> > >
> > > Hope this helps
> > > Jay
> > >
> > > "Dazz" <(E-Mail Removed)> wrote in message
> > > news:%(E-Mail Removed)...
> > > > Hi
> > > >
> > > > I desperately need some help. I want to have a DataView sorted
> > > > alphabetically by a specific column, then I want to find the first
> > record
> > > > that begins with the letter the user inputs and get that record
> number.
> > I
> > > > don't however want a filter as the user must still be able to scroll
> up
> > > and
> > > > down the DataView.
> > > >
> > > > Thanks. Any help would be greatly appreciated.
> > > >
> > > > Dazz
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Bernie,
I was shooting from the hip. ;-)

I wonder if you need a compound sort? Sort the dataview on the first letter
& the addr field. Then do the find on just the first letter field, can you
do the find on a single item, although the view is sorted on two fields...

I'll try and play this later.

I am fairly certain that the DataTable.Select method will work.

Something like:

Dim table As DataTable
Dim rows As DataRow
rows = table.Select("SUBSTRING(addr, 0, 1) = 'G'", "addr")

Hope this helps
Jay


"Bernie Yaeger" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi Jay,
>
> I think you're on the right track, but it doesn't work - I opened a table
in
> "addr" order; I added the column; I assigned values to the column of the
> initial letter of "addr" - while the 'G's were all together, they were in
> random order, not in the order of the table that was opened (I assigned by
> running a for each loop that saw the datatable in strict addr order, as I
> reviewed this).
>
> But when you now search on the computed column, it finds a 'G' randomly,
> not, as you would expect, the G from, say, Gable, evidently because any
'G'
> will do.
>
> Any ideas how to get around this?
>
> Bernie Yaeger
>
> "Jay B. Harlow [MVP - Outlook]" <(E-Mail Removed)> wrote in
message
> news:e%(E-Mail Removed)...
> > Doh!
> >
> > That's not going to work, as DataView.Find finds a row with the
specified
> > key, you want to find a partial key...
> >
> > I would use either a second DataView where the second one has a filter
of
> > the letter I am interested in. Or the DataTable.Select statement to find
> the
> > rows with the letter I am interested in. Based on the first row returned
> > from either of the above I would have the key of the row of interest to
> pass
> > the DataView.Find method of the first DataView.
> >
> > Of course I believe if you added a computed column to your DataTable
that
> > contained the first letter of the specific column, you could find on the
> > computed column...
> >
> > Hope this helps (this time ;-))
> > Jay
> >
> > "Jay B. Harlow [MVP - Outlook]" <(E-Mail Removed)> wrote in
> message
> > news:%(E-Mail Removed)...
> > > Dazz,
> > > Have you tried using the DataView.Find method to find the letter you
are
> > > interested in?
> > >
> > > In case you don't have it David Sceppa's book "Microsoft ADO.NET -
Core
> > > Reference" covers every thing you ever wanted to know about ADO.NET
but
> > were
> > > afraid to ask.
> > >
> > > Hope this helps
> > > Jay
> > >
> > > "Dazz" <(E-Mail Removed)> wrote in message
> > > news:%(E-Mail Removed)...
> > > > Hi
> > > >
> > > > I desperately need some help. I want to have a DataView sorted
> > > > alphabetically by a specific column, then I want to find the first
> > record
> > > > that begins with the letter the user inputs and get that record
> number.
> > I
> > > > don't however want a filter as the user must still be able to scroll
> up
> > > and
> > > > down the DataView.
> > > >
> > > > Thanks. Any help would be greatly appreciated.
> > > >
> > > > Dazz
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Hi Guys

Thanks to everyone for tying to help me with this little problem of mine.
What Ken Tucker provided actually worked the way I wanted it to. I have now
learnt something new !! Yay!!

Again thank you for your help. It is much appreciated.

Dazz




"Ken Tucker [MVP]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi,
>
> You can try this.
>
> Dim conn As SqlConnection
>
> Dim strConn As String
>
> Dim strSQL As String
>
> Dim ds As New DataSet
>
> Dim da As SqlDataAdapter
>
> Dim dv As DataView
>
> Dim cm As CurrencyManager
>
> Dim iRow As Integer
>
> strConn = "Server = " + Environment.MachineName + "\VSdotNet;"
>
> strConn += "Database = Northwind;"
>
> strConn += "Integrated Security = SSPI;"
>
> conn = New SqlConnection(strConn)
>
> da = New SqlDataAdapter("Select * from Customers", conn)
>
> da.Fill(ds, "Customers")
>
> dv = New DataView(ds.Tables("Customers"))
>
> DataGrid1.DataSource = dv
>
> cm = CType(Me.BindingContext(dv), CurrencyManager)
>
> dv.Sort = "CustomerID"
>
> For x As Integer = 0 To dv.Count - 1
>
> Dim drv As DataRowView = dv(x)
>
> Dim strCustomer As String = drv.Item("CustomerID")
>
> If strCustomer.ToUpper.StartsWith("P") Then
>
> cm.Position = x
>
> Exit For
>
> End If
>
> Next
>
> Ken
>
> -------------------------
>
> "Bernie Yaeger" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Hi Jay,
> >
> > I think you're on the right track, but it doesn't work - I opened a
table
> in
> > "addr" order; I added the column; I assigned values to the column of the
> > initial letter of "addr" - while the 'G's were all together, they were
in
> > random order, not in the order of the table that was opened (I assigned
by
> > running a for each loop that saw the datatable in strict addr order, as
I
> > reviewed this).
> >
> > But when you now search on the computed column, it finds a 'G' randomly,
> > not, as you would expect, the G from, say, Gable, evidently because any
> 'G'
> > will do.
> >
> > Any ideas how to get around this?
> >
> > Bernie Yaeger
> >
> > "Jay B. Harlow [MVP - Outlook]" <(E-Mail Removed)> wrote in
> message
> > news:e%(E-Mail Removed)...
> > > Doh!
> > >
> > > That's not going to work, as DataView.Find finds a row with the
> specified
> > > key, you want to find a partial key...
> > >
> > > I would use either a second DataView where the second one has a filter
> of
> > > the letter I am interested in. Or the DataTable.Select statement to
find
> > the
> > > rows with the letter I am interested in. Based on the first row
returned
> > > from either of the above I would have the key of the row of interest
to
> > pass
> > > the DataView.Find method of the first DataView.
> > >
> > > Of course I believe if you added a computed column to your DataTable
> that
> > > contained the first letter of the specific column, you could find on
the
> > > computed column...
> > >
> > > Hope this helps (this time ;-))
> > > Jay
> > >
> > > "Jay B. Harlow [MVP - Outlook]" <(E-Mail Removed)> wrote in
> > message
> > > news:%(E-Mail Removed)...
> > > > Dazz,
> > > > Have you tried using the DataView.Find method to find the letter you
> are
> > > > interested in?
> > > >
> > > > In case you don't have it David Sceppa's book "Microsoft ADO.NET -
> Core
> > > > Reference" covers every thing you ever wanted to know about ADO.NET
> but
> > > were
> > > > afraid to ask.
> > > >
> > > > Hope this helps
> > > > Jay
> > > >
> > > > "Dazz" <(E-Mail Removed)> wrote in message
> > > > news:%(E-Mail Removed)...
> > > > > Hi
> > > > >
> > > > > I desperately need some help. I want to have a DataView sorted
> > > > > alphabetically by a specific column, then I want to find the first
> > > record
> > > > > that begins with the letter the user inputs and get that record
> > number.
> > > I
> > > > > don't however want a filter as the user must still be able to
scroll
> > up
> > > > and
> > > > > down the DataView.
> > > > >
> > > > > Thanks. Any help would be greatly appreciated.
> > > > >
> > > > > Dazz
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>