Database Coding

[gautamshaw]

I did the following code:

Imports System.Data
Imports System.data.OleDb
Public Class Form1
Dim con As OleDbConnection
Dim cmd As OleDbCommand
Dim dr As OleDbDataReader
Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Try
con = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Gautam\Documents\Visual Studio 2005\Projects\WindowsApplication7\WindowsApplication7\db2.mdb;")
con.Open()
cmd = New OleDbCommand("Select * from Table2", con)
dr = cmd.ExecuteReader
While dr.Read()
TextBox1.Text = dr(0)
TextBox2.Text = dr(1)
TextBox3.Text = dr(2)
End While
Catch ex As Exception
End Try
dr.Close()
con.Close()
End Sub
End Class

By this i can fetch only one row(the last row) of the access....

how to modify the above code to fetch all the rows starting from the begining from the database..........

[jmcilhinney]

Consider this. Let's say that you have a block of wood with one hole in it. I give you 10 pegs and tell you to put them in the whole. You put the first peg in, then you take it out and put the second one in, then you take it out and put the third one in, etc. until you put the last peg in the hole. You then give me the block of wood. All I can see is the last peg in the hole, even though you put all 10 pegs in there, one after the other.

The exact same thing is happening with your code. You are retrieving every record and you are putting the data of every record in the TextBoxes, but you are replacing the previous data each time so, at the end, all you see is the last set of data. If you've only got one set of TextBoxes then how can you hope to see more than one record's worth of data at a time?

[gautamshaw]

ok then if i take more than one text box then what will be my coding in the while loop?

Frankly speaking i cant follow hoe the following while loop is working....

While dr.Read()
TextBox1.Text = dr(0)
TextBox2.Text = dr(1)
TextBox3.Text = dr(2)
End While


Please explain the above while loop......

what is the actual meaning of.....dr(0),dr(1),dr(2)?

[jmcilhinney]

'dr' is a DataReader. Each time you call Read it reads a record from the result set. You are reading a record from the result set and then loading the fields of that record into your TextBoxes. Each time you do that you are replacing the previous record's data so, at the end of the loop, all you see is the last record's data. All the other data was in the TextBoxes at some point but you simply replaced it.

This is a classic example of trying to write code when you don't actually know what you're trying to achieve. As I said, how can you possibly show more than one record's data if all you have is one set of TextBoxes? You have to first decide how you want to display the data before you can actually display it that way. Do you want a grid that displays all the data at the same time? Do you want to be able to navigate through the data one record at a time using your one set of TextBoxes? Do you want something else? Until you/we know what problem you/we are trying to solve the you/we can hardly write code to solve it.

[gautamshaw]

it means dr(0),dr(1),dr(2) denotes the column number of the access table from where i will fetch ........1st column is dr(0),2nd column is dr(1) and 3rd column is dr(3)....

now if i add one more set of textbox to retrieve a couple of rows from the database....then dr(0),dr(1),dr(2) will ve to be displayed for each iteration of the while loop in their corresponding text box?

Is the concept something like this?

[gautamshaw]

i added three more text box but i cant display the values at each iteration to the text box.....
give me the codings of the loop........

[jmcilhinney]

You probably won't use a loop in that case, because you'll have to refer to three different TextBoxes. That said, are there only ever going to be two records in your table? If not then two sets of TextBoxes isn't really going to be much use. As I've already said, you'll generally either use a grid to display all the data at the same time or else one set of TextBoxes or the like that will show one record at a time and you'll navigate through the records.

I think you need to STOP and THINK about exactly how you want your user interface to behave because I don't think the code you are trying to write is going to be very useful.

[gautamshaw]

I think you r right.........
how can i do it with the grid then?
give the codings then......

[jmcilhinney]

You should populate a DataTable and bind it to a DataGridView, which you do by setting the grid's DataSource property. I suggest that you follow the CodeBank link in my signature and find my Saving & Retrieving Data thread to see how to populate a DataTable.

[gautamshaw]

if i go for the combo box inspite of the grid then?
Each combo box for each column?
I did the below coding but i cant complete the coding........
just check it.........

Imports system.data
Imports System.Data.OleDb
Public Class Form1
Dim con As OleDbConnection
Dim cmd As OleDbCommand


Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Try
con = New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=C:\Users\Gautam\Documents\Visual Studio 2005\Projects\WindowsApplication1\WindowsApplication1\db3.mdb;")
con.Open()
cmd = New OleDbCommand("Select * from table1", con)
Dim da As New OleDbDataAdapter(cmd)
Dim dt As New DataTable
da.fill(dt)
con.Close()
ComboBox1.DataSource = dt
ComboBox2.DataSource = dt
ComboBox3.DataSource = dt
ComboBox4.DataSource = dt
ComboBox1.Items.Add("Name")
ComboBox2.Items.Add("Roll")
ComboBox3.Items.Add("Sex")
ComboBox4.Items.Add("Address")
ComboBox.databind()
Catch ex As Exception

End Try
End Sub
End Class

i cant code the selected portion properly........