Easy one I guess... ADO.Net & Access

[BY]

Hi,

This is my code and it works fine...

Dim Conn As OleDb.OleDbConnection
Dim Cmd As OleDb.OleDbCommand
Dim Reader As OleDb.OleDbDataReader
Dim i As Integer

Conn = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\SAMPLES\Northwind.mdb;Persist Security Info=False")
Cmd = New OleDb.OleDbCommand("SELECT CompanyName FROM CUSTOMERS", Conn)
Conn.Open()

Try
Reader = Cmd.ExecuteReader
i = 1
While (Reader.Read())
ListBox1.Items.Add(i & " " & Reader.GetString(0))
i = i + 1
End While
Finally
Reader.Close()
Conn.Close()
End Try

I have got 2 problems actually where I couldn't find any explanation for in
"Microsoft Visual Basic. Net Step by Step Deluxe Learning Edition"... But I
am still learning...

The first thing is, when someone clicks to a CompanyName in the ListBox(see
code above) I want to display additional information about that company on a
label...

My second problem is, I have another database with contacts... I have a
TextBox, a Button and again a label... If I write the name of a person in
the TextBox and clicks the Button the program should find that person in the
database and show all adress details on the label...

I know that both are related, but I really couldn't find anything about it
on the internet... I have read that the recordsets/records/fields are
changed into .Item, but I couldn't find any detailed information about it,
just as I couldn't find any information about GetString (see code above...?)

I am not expecting that you solve my problems, but I hope that you can point
me in the right direction...

Thanks in advance for all your help.

Regards,

BY

 

[Bernie Yaeger]

Hi BY,

Re your first question, a listbox has a selectedindexchanged event; you can
assign a value to the text property of the label there:
label1.text = "ok just clicked " & listbox1.selecteditem

Re your second question, you should set up a dataview to allow you to search
the table for the value you need. I'd strongly recommend you purchase ADO
..Net by David Sceppa - it's one of the best on finding values in a
datatable, whether MS Access or Sql Server. This isn't really that
difficult; if you don't have time for the book and want some sample code,
let me know - I'll put something together for you.

HTH,

Bernie Yaeger

 

[Cor]

Hi By,

What you are doing is very nice and compact very usable for the internet
also.

You have enough in your code to do this while I write it in pseudo, while I
follow your approach although I myself would do it on another way.

The next thing you have to do is to construct a new select from your listbox
with the selecteditem and read the information you want to display.

You should do that in a event like "SelectedIndexChanged" (that is
automaticly made when you push on the listbox on your IDE) or as Jan stated
in this newsgroup yesterday " SelectionChangeCommitted" that you has to
choise in your Ide at the top.
(The problem with the selectedIndexChanged is that it fires when it is
loaded, so you have to stop that with a switch and Jan said he had beter
results with SelectionChangeCommitted).

In that event you make a select string as
dim sqlString = _
"SELECT CompanyName, CompanyAdres, etc FROM CUSTOMERS WHERE CompanyName =
'" & _
listbox1.selectedItem.tostring & "'"

Then you can use your reader again to get the information.

I hope that this helps a little bit so far,

Cor

 

[Clifford]

-----Original Message-----

Hi,

This is my code and it works fine...

Dim Conn As OleDb.OleDbConnection
Dim Cmd As OleDb.OleDbCommand
Dim Reader As OleDb.OleDbDataReader
Dim i As Integer

Conn = New OleDb.OleDbConnection ("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\SAMPLES\Northwind.mdb;Persist Security Info=False")
Cmd = New OleDb.OleDbCommand("SELECT CompanyName FROM CUSTOMERS", Conn)
Conn.Open()

Try
Reader = Cmd.ExecuteReader
i = 1
While (Reader.Read())
ListBox1.Items.Add(i & " " & Reader.GetString(0))
i = i + 1
End While
Finally
Reader.Close()
Conn.Close()
End Try

I have got 2 problems actually where I couldn't find any explanation for in
"Microsoft Visual Basic. Net Step by Step Deluxe Learning Edition"... But I
am still learning...

The first thing is, when someone clicks to a CompanyName in the ListBox(see
code above) I want to display additional information about that company on a
label...

REPLY TO 1:
On the event SelectedIndexChanged
Define the CompanyName
Define the CompanyCode

Label1 as Label = ""
ListBox1.DataTextField = "CompanyName"
ListBox1.DataValueField = "CompanyCode"

Once the user clicked the CompanyName on your listbox
Pass the value to your label control.

Label1.Text = ListBox.SelectedItem.Value
No need to create another connection string, But if you
need to display on your label moer than one field on more
than one Label then you have to do some connection again.

My second problem is, I have another database with contacts... I have a
TextBox, a Button and again a label... If I write the name of a person in
the TextBox and clicks the Button the program should find that person in the
database and show all adress details on the label...

REPLY 2: The same with the rest...