Show records

[Joachim]

Hi,

Als a beginner, I am looking for a way to show records
My code is
Private sub Connection(
Dim odbconn_Pro As OleDbConnectio
Dim odbcomm_Pro As OleDbComman
Dim odbdare_Pro As OleDbDataReade
Dim DataFile as Strin
DataFile = "H:\Production\Prod.mdb
odbconn_Pro = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; "
& "DATA SOURCE=" & DataFile
odbcomm_Pro = New OleDbComman
odbcomm_Pro.CommandText = "SELECT * FROM Collection ORDER BY bt_code
odbcomm_Pro.Connection = odbconn_Pr
odbcomm_Pro.Connection.Open(
odbdare_Pro = odbcomm_Pro.ExecuteReade
If odbdare_Pro.HasRows The
ShowDetail(
End I
End Su

Private Sub ShowDetail(
While odbdare_Pro.Read(
lblCode.Text = odbdare_Pro("bt_code"
lblName.Text = odbdare_Pro("bt_Name"
....
....
End Whil
End Su

When this proram run. It show only the last record. I know why, because I use : While odbdare_Pro.Read()
So the program read all the records and shows the last one
What I want is the program just shows the first record

Meanwhile I have 4 toolbar Buttons : "First Record", "Previous", "Next" and "Last" to navigate which record to show
After the first record is showed, the user can click one of the 4 buttons.
If the "First Record" button is clicked, the first record will be showed
If the "Previous" button is clicked, the previous record of the shown record will be showed
If the "Next" button is clicked, the next record of the shown record will be showed
If the "Last" button is clicked, the last record will be showed

How can I do like this ? Please somebody help me

Thanks in advance

Joachim

[Cor Ligthert]

Hi Joachim,

If you read using the datareader and not store that in an object you have
only the latest information in memory.

However just by using the dataadapter, the dataset (with all what is in
this), using the *data*binding and the bindinmanager you can do all you want
in a very easy way, have a look for your self to it in/on msdn.

Cor

[Joachim]

Cor
Can you give me a sample, please

Thanks.

[Cor Ligthert]

Hi Joachim,

I do this in this message so watch for typos

Private sub Connection()
Dim odbconn_Pro As OleDbConnection
Dim odbcomm_Pro As OleDbCommand
Dim odbdare_Pro As OleDbDataReader
Dim DataFile as String
DataFile = "H:\Production\Prod.mdb"
odbconn_Pro = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " _ & "DATA SOURCE=" & DataFile)
odbcomm_Pro = New OleDbCommand
odbcomm_Pro.CommandText = "SELECT * FROM Collection ORDER BY bt_code" odbcomm_Pro.Connection = odbconn_Pro
odbcomm_Pro.Connection.Open()

dim ds as new dataset
dim da as new dataadaper(odbcomm_Pro)
da.fill(ds)
cma = CType(BindingContext(ds.Tables(0)), CurrencyManager)
textbox1.DataBindings.Add(New Binding("Text", ds.Tables(0),
"YourFieldName"))

With seting the
cma.position = 0 you set the position on the first postision and the first
row will be displayed

I hope this clears it a little bit otherwise reply

Cor

[Joachim]

Cor, thanks for your code. But....

What is cma in :
cma = CType(BindingContext(ds.Tables(0)), CurrencyManager
textbox1.DataBindings.Add(New Binding("Text", ds.Tables(0)
"YourFieldName")

Can I do like this ?
private sub ...
..
..
da.fill(ds
ShowData(0
end su

private sub ShowData(posi
cma = CType(BindingContext(ds.Tables(posi)), CurrencyManager)
textbox1.DataBindings.Add(New Binding("Text", ds.Tables(posi)
"YourFieldName")
end su

[William Ryan eMVP]

Cor is absolutely correct here! Definitely don't use a DataReader is this
is what you need. A reader needs an open and available connection and
leaving a Reader open for the entire user session is bad news. Use a
Datatable! He posted the code but the main reason that I'm writing this is
to emphasize the difference between teh two approaches. DataReaders are
connected objects, only work when connected. DataTables/DataSets etc
aren't. They connect and close so they work independently of a database.
For this you get a little performance hit but it's manageable in most
instances and you get a lot of functionality. Confusing these two approachs
can be very problematic.

--

W.G. Ryan, eMVP

http://forums.devbuzz.com/
http://www.knowdotnet.com/williamryan.html
http://www.msmvps.com/WilliamRyan/
http://www.devbuzz.com/content/zinc_...center_pg1.asp
"Joachim" <an*******@discussions.microsoft.com> wrote in message
news:17**********************************@microsof t.com...

Hi,

Als a beginner, I am looking for a way to show records.
My code is :
Private sub Connection()
Dim odbconn_Pro As OleDbConnection
Dim odbcomm_Pro As OleDbCommand
Dim odbdare_Pro As OleDbDataReader
Dim DataFile as String
DataFile = "H:\Production\Prod.mdb"
odbconn_Pro = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " _ & "DATA SOURCE=" & DataFile)
odbcomm_Pro = New OleDbCommand
odbcomm_Pro.CommandText = "SELECT * FROM Collection ORDER BY bt_code" odbcomm_Pro.Connection = odbconn_Pro
odbcomm_Pro.Connection.Open()
odbdare_Pro = odbcomm_Pro.ExecuteReader
If odbdare_Pro.HasRows Then
ShowDetail()
End If
End Sub

Private Sub ShowDetail()
While odbdare_Pro.Read()
lblCode.Text = odbdare_Pro("bt_code")
lblName.Text = odbdare_Pro("bt_Name")
.....
.....
End While
End Sub
When this proram run. It show only the last record. I know why, because I use : While odbdare_Pro.Read() So the program read all the records and shows the last one.
What I want is the program just shows the first record.

Meanwhile I have 4 toolbar Buttons : "First Record", "Previous", "Next" and "Last" to navigate which record to show. After the first record is showed, the user can click one of the 4 buttons.
If the "First Record" button is clicked, the first record will be showed.
If the "Previous" button is clicked, the previous record of the shown record will be showed. If the "Next" button is clicked, the next record of the shown record will be showed. If the "Last" button is clicked, the last record will be showed.

How can I do like this ? Please somebody help me.

Thanks in advance.

Joachim.

[Cor Ligthert]

Hi Joachim

I was a little bit in a hurry.
\\\
lblCode.DataBindings.Add(New Binding("Text", ds.Tables(0),
"bt_code"))
///

This binding can be done as soon as the dataset is filled (with an untyped
dataset as you have) (and it has to be done only one time)

The currencymanager is not a money manager, it keep track of the current row
in the dataset.
cma is that object
cma.position = x is the current row. You can use it in a click event by
instance in a previous button

\\\
sub click ButtonPrevious
if cma.positions > 0 then
cma.position -= 1
end if
///
You have not to fill the textboxes now or move the contents to a row,
however when you do a dataadapter update, you normaly have to add before the
update (I give it now otherwise you ask it in future)
\\\
cma.EndCurrentEdit()
///
Watch what you set global and what not, that cma you should declare global
of course.
\\
Private cma As CurrencyManager
///
I hope this shows it a little bit?

Cor

[Joachim]

Hi, Co
Here below is my code. But I got a error in line 18
The message of the error is like this

An unhandled exception of type 'System.ArgumentException' occurred in system.windows.forms.dl
Additional information: This would cause two bindings in the collection to bind to the same property

Sorry Cor if I ask you too much, I am really a beginner
Please check my coe, maybe I have done something wrong

Thanks

Joachim

============================
1 Private Sub SetConnection(
2 Dim DataFile as Strin
3 DataFile = "C:\Production\Product.mdb
4 odbconn_Pro = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; "
5 & "DATA SOURCE=" & DataFile
6 odbcomm_Pro = New OleDbComman
7 odbcomm_Pro.CommandText = "SELECT * FROM Collection ORDER BY bt_code
8 odbcomm_Pro.Connection = odbconn_Pr
9 odbcomm_Pro.Connection.Open(
10 ds = New DataSe
11 da = New OleDbDataAdapter(odbcomm_Pro
12 da.Fill(ds
13 ShowDetail(
14 End Su
1
16 Private Sub ShowDetail(
17 Dim i as Intege
18 lblCode.DataBindings.Add(New Binding("Text", ds.Tables(0), "bt_code")
19 lblm1Event.DataBindings.Add(New Binding("Text", ds.Tables(0), "bt_m1event")
20 If Trim(lblm1Event.Text) = "" The
21 SetMatch1_Empty(False
22 Els
23 SetMatch1_Empty(True
24 lblm1Year.DataBindings.Add(New Binding("Text", ds.Tables(0), "bt_m1year")
25 lblm1Discipline.DataBindings.Add(New Binding("Text", ds.Tables(0), "bt_m1discipline")
26 i = Array.IndexOf(st_Dis, lblm1Discipline.Text
27 lblm1Discipline.Text = st_Discipline(i
28 lblm1Player1.DataBindings.Add(New Binding("Text", ds.Tables(0), "bt_m1player1")
29 lblm1Country1.DataBindings.Add(New Binding("Text", ds.Tables(0), "bt_m1country1")
30 lblm1Player2.DataBindings.Add(New Binding("Text", ds.Tables(0), "bt_m1player2")
31 lblm1Country2.DataBindings.Add(New Binding("Text", ds.Tables(0), "bt_m1country2")
32 lblm1Qualification.DataBindings.Add(New Binding("Text", ds.Tables(0), "bt_m1qualification")
33 i = Array.IndexOf(st_Qua, lblm1Qualification.Text
34 lblm1Qualification.Text = st_Qualification(i
35 lblm1Remark.DataBindings.Add(New Binding("Text", ds.Tables(0), "bt_m1remark")
36 End I
3
38 End Su

[Cor Ligthert]

Hi Joachim,

I was expecting this error however could not write more about it because
than I would have confused you to much in my idea.

I wrote the binding has only to be set once. You can do that in a procedure
or just remove the binding everytime, however your code is in a way I make
it myself easy, can you try and tell if this works? (You can as well use
everytime me.textboxX.databinding.clear, before you do the databinding)

This is not ready, just a next step.

Cor

An unhandled exception of type 'System.ArgumentException' occurred in system.windows.forms.dll Additional information: This would cause two bindings in the collection to bind to the same property.
Private cma As CurrencyManager 1 Private Sub SetConnection()
2 Dim DataFile as String
3 DataFile = "C:\Production\Product.mdb"
4 odbconn_Pro = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " _ 5 & "DATA SOURCE=" & DataFile)
6 odbcomm_Pro = New OleDbCommand
7 odbcomm_Pro.CommandText = "SELECT * FROM Collection ORDER BY bt_code" 8 odbcomm_Pro.Connection = odbconn_Pro
9 odbcomm_Pro.Connection.Open()
10 ds = New DataSet
11 da = New OleDbDataAdapter(odbcomm_Pro)
12 da.Fill(ds) DoBinding()
cma = CType(BindingContext(ds.Tables(0)), CurrencyManager) 14 End Sub

Proivate sub ShowText
cma.position = an indexer to the rows you want to show
End Sub

Private Sub DoBinding()
static binded as boolean
if binded = false then
lblCode.DataBindings.Add(New Binding("Text", ds.Tables(0),
"bt_code"))
lblm1Event.DataBindings.Add(New Binding("Text", ds.Tables(0),
"bt_m1event"))
'why you use that set event while you can everytime just test the value of
the label?
lblm1Year.DataBindings.Add(New Binding("Text", ds.Tables(0),
"bt_m1year"))
lblm1Discipline.DataBindings.Add(New Binding("Text",
ds.Tables(0), "bt_m1discipline"))
lblm1Player1.DataBindings.Add(New Binding("Text", ds.Tables(0),
"bt_m1player1"))
lblm1Country1.DataBindings.Add(New Binding("Text", ds.Tables(0),
"bt_m1country1"))
lblm1Player2.DataBindings.Add(New Binding("Text", ds.Tables(0),
"bt_m1player2"))
lblm1Country2.DataBindings.Add(New Binding("Text", ds.Tables(0),
"bt_m1country2"))
lblm1Qualification.DataBindings.Add(New Binding("Text",
ds.Tables(0), "bt_m1qualification"))
lblm1Remark.DataBindings.Add(New Binding("Text", ds.Tables(0),
"bt_m1remark"))
binded = true
End If
End Sub

[Joachim]

Hi Cor
I have followed your code, but I got the same error message

-------------- error message -----------
An unhandled exception of type 'System.ArgumentException' occurred in system.windows.forms.dl
Additional information: This would cause two bindings in the collection to bind to the same property
-------------------------------------------

This time in line 4. I tried to remove the 'New Binding', but the same message came out

What do you mean at line 6

1 Private Sub DoBinding(
2 static binded as boolea
3 if binded = false the
4 lblCode.DataBindings.Add(New Binding("Text", ds.Tables(0),"bt_code")
5 lblm1Event.DataBindings.Add(New Binding("Text", ds.Tables(0),"bt_m1event")
6 'why you use that set event while you can everytime just test the value of the label
7 lblm1Year.DataBindings.Add(New Binding("Text", ds.Tables(0)

[Joachim]

Hi Cor

First of all : THANKS A LOT
Sorry, for my earlier post. I have made a mistake, that's why the error message came uit again
I checked it again and found the bug

Another question
I have 13 field in the table
Field 1 is the key
Field 2,3,4 and 5 are a group
Field 6,7,8 and 9 are a group, and als
Field 10,11,12 and 13 are a group

What I want to do is
If field 6 empty, null, " " or "", so all the fields in the same group (6, 7, 8 and 9) must not be displayed
If field 10 empty, null, " " or "", so all the fields in the same group (10, 11, 12 and 13) must not be displayed
All the fields (1 to 13) are binded, so how can I check whether field 6 and field 10

[Cor Ligthert]

Hi Joachim,

When it is databinded it is binded. However we can prevent to show it.

That is where are the previous and next and other buttons in those you can
call a procedure which only does. (roughly written here not tested)
\\\\
dim test as string
if ds.tables(0).rows(cma.position)item(6) Not Is dbnull.value then
test = ...........(6).tostring.replace(" ","")
else
test = ""
end if
if test = "" then
me.labelwhatever.visible = valse
me...
else
me.labelwhatever.visible = true
me....
end if
if ...... for the other fields
///
I hope this gives some idea's and show you also the use of the
cma.postition

Cor

[Cor Ligthert]

Hi Joachim show something more, where is it in the program.
And is the dataset really filled.

If you want to test that you can do something as
If ds.tables(0) not Is Nothing .....

if ds.tables(0).rows(cma.position).item(6) Not Is dbnull.value then
test = ds.tables(0).rows(cma.position).item(6).tostring.r eplace(" ","")
else
test = ""
end if

[Joachim]

Cor, here below is the code
The message came uti form line *

Private sub SetConnection(
......
da_Badminton.Fill(ds
DoBinding(
cm_Badminton = CType(BindingContext(ds.Tables(0)), CurrencyManager
End Su

Private Sub DoBinding(
Static binded As Boolea
If binded = False The
lblCode.DataBindings.Add(New Binding("Text", ds.Tables(0), "bt_code")
lblm1Event.DataBindings.Add(New Binding("Text", ds.Tables(0), "bt_m1event")
lblm1Year.DataBindings.Add(New Binding("Text", ds.Tables(0), "bt_m1year")
lblm1Player1.DataBindings.Add(New Binding("Text", ds.Tables(0), "bt_m1player1")
lblm1Country1.DataBindings.Add(New Binding("Text", ds.Tables(0), "bt_m1country1")
lblm1Player2.DataBindings.Add(New Binding("Text", ds.Tables(0), "bt_m1player2")
lblm1Country2.DataBindings.Add(New Binding("Text", ds.Tables(0), "bt_m1country2")
lblm1Qualification.DataBindings.Add(New Binding("Text", ds.Tables(0), "bt_m1qualification")
lblm1Remark.DataBindings.Add(New Binding("Text", ds.Tables(0), "bt_m1remark")

** If ds.Tables(0).Rows(cm_Badminton.Position).Item(2) Not Is DBNull.Value The
Tempo = ds.Tables(0).Rows(cm_Badminton.Position).Item(2).T oString.Replace(" ", ""
els
Tempo = "
End I
If Tempo = "" the
DoSomething(
End i

[Cor Ligthert]

Hi Joachim,

I am lucky I asked you to send the code.

See this..

DoBinding()
cm_Badminton = CType(BindingContext(ds.Tables(0)),

CurrencyManager)

In that DoBinding you use that cm_Badminton while it does not even exist.
Not only for that because you can change those rows, however for the use you
have to set that hiding routine outside that DoBinding, hiding those fields
have nothing to do with the binding.

By Instance
\\\
DoBinding()
cm_Badminton = CType(BindingContext(ds.Tables(0)), CurrencyManager)
DoHiding
////
Than you can do on your event from the button clicks as well everytime
\\\
DoHiding
///

[Joachim]

Hi Cor

It works
Again : THANKS A LOT to you Cor
I am very appreciated

Now, I am learning the DataGrid
Maybe you can also help me when I post my question in the other thread

Best regard
Joachim.