Creating an MS Access related Application

[ring_of_fire]

Hi All!,

I'm new with to VB.NET (2003) and especially novice at connecting databases to an application. I'm trying to create an application described below:

What I have?
I have an MDB file containing one simple table called as "Test" with three columns: Date, Title, Content.

My Purpose?
I want to build an application which will display Content and its Title according to date (for ex. for 15.02.2006, corresponding Content and its Title will be displayed).

• Users should have the ability/control to select from a calendar control a date and display its content, by clicking.
• The displayed text should be read-only and so the database itself; it will be modified from exterior. Title and Content will be both displayed inside read-only TextBox controls.
• The application will display only one Title + Content at a time based on the selected date (today's date by default).

Can someone guide me?

Thank you for your help in advance!

Ring_of_Fire

 

[vis781]

Have a look at TechGnome's ADO.Net tutorials. They are in his sig.

 

[ring_of_fire]

First steps for building this application

Here is what I've found after some research. By doing so I was able to access and retrieve data from my database.

This code, what does it do?
It retrieves, as far as I understood, contents of defined columns from the table: TEST and "injects" data into 2 textbox controls in my Form.

[FONT=Courier New]Imports System.Data.OleDb[/FONT]
 

[FONT=Courier New]Public Class Form1 [/FONT]
[FONT=Courier New]Inherits System.Windows.Forms.Form[/FONT][INDENT]
[FONT=Courier New]Dim cn As OleDbConnection[/FONT]
[FONT=Courier New]Dim cmd As OleDbCommand[/FONT][FONT=Courier New]Dim dr As OleDbDataReader[/FONT]
[/INDENT]

[INDENT][FONT=Courier New]Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load[/FONT]


[/INDENT][INDENT][FONT=Courier New]Try[/FONT]
 

[INDENT][FONT=Courier New]cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=mydb.mdb;")[/FONT]
[FONT=Courier New]'provider to be used when working with access database[/FONT]
[FONT=Courier New]cn.Open()[/FONT]
[FONT=Courier New]cmd = New OleDbCommand("select * from Test", cn)[/FONT]
[FONT=Courier New]dr = cmd.ExecuteReader[/FONT]

[FONT=Courier New]While dr.Read()[/FONT][INDENT][FONT=Courier New]TextBox1.Text = """" + dr(2) + """"[/FONT]
[FONT=Courier New]TextBox2.Text = dr(1)[/FONT]
[FONT=Courier New]' loading data into TextBoxes by column index[/FONT]

[/INDENT][FONT=Courier New]End While[/FONT]


[/INDENT][FONT=Courier New]Catch[/FONT]
[FONT=Courier New]End Try[/FONT]
[FONT=Courier New]dr.Close()[/FONT]
[FONT=Courier New]cn.Close()[/FONT]
 


[/INDENT][FONT=Courier New]End Sub[/FONT]

And now?
As mentioned before, the database contains a date column, as well. I want to be able to retrieve data according to date. For this I think I should use a calendar control and connect it somehow to this database.

Any help at this point?
Thanks,

Ring_Of_Fire

 

[vis781]

You can do it that way, however when changing the date you want the inforamtion for it will incurr an extra round trip to the database to retreive the informatio. So in this instance i would not use an oledbdatareader. I would use a oledbdataadapter instead and fill a datatable with all the information. Like so.....

Imports System.Data.OleDb


Public Class Form1
Inherits System.Windows.Forms.Form

Dim cn As OleDbConnection
Dim cmd As OleDbCommand
Dim Da As New OleDbDataAdapter
Dim DtTest as new Datatable("Test")

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 
Try
cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=mydb.mdb;")
'provider to be used when working with access database
cmd = New OleDbCommand("select * from Test", cn)
Me.Da.SelectCommand = cmd
Da.Fill(Me.DtTest)
 
Catch Ex As OleDbException
Messagebox.show(Ex.Message)
Finally
Con.close
End Try

This will fill a datatable with all the information from your database. You can access the information in the datatable using a DataRow Object. So what yuo need to do is get the data from your DateTime Picker control Then Iterate through the datarow collection and check to see if there is a dat that matches. For example, in a click event of a button called 'search'

Dim Matches as Integer = 0
For each Dr as datarow in Me.DtTest.Rows
 
If CDate(Dr.Item(The Index Of The Column With The dates In It)) = 'the output from the DataTimePicker Control' then
TextBoxTitle.Text = convert.tostring(dr.item(the column index with the title in it))
textboxcontent.text = convert.tostring(dr.item(the column index with the content in it))
Matches+=1
end if
next
 
If Matches = 0 then 
MessageBox.Show("No Matches Found")
 
end sub

​

 

[ring_of_fire]

Strange behaviours...

Thank you a lot for your guidance vis781!

Now I'm getting to understand far better, but I have some points to clarify.

I've tried your code and it works as expected but with a little strange behaviour that I'm not able to understand:

I'm using a MonthCalendar Control to retrieve the requested row from the data table. So I did the following, (code is exactly yours):

[SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] MonthCalendar1_DateChanged([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] sender [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Object, [/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] e [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Windows.Forms.DateRangeEventArgs) _

[/SIZE][SIZE=2][COLOR=#0000ff]Handles[/COLOR][/SIZE][SIZE=2] MonthCalendar1.DateChanged[/SIZE][INDENT][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] Matches [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Integer[/COLOR][/SIZE][SIZE=2] = 0[/SIZE][SIZE=2]

[/SIZE][SIZE=2][COLOR=#0000ff]For[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Each[/COLOR][/SIZE][SIZE=2] Dr [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] DataRow [/SIZE][SIZE=2][COLOR=#0000ff]In[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].DtTest.Rows[/SIZE][INDENT][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]CDate[/COLOR][/SIZE][SIZE=2](Dr.Item(0)) = [/SIZE][SIZE=2][COLOR=#0000ff]CDate[/COLOR][/SIZE][SIZE=2](MonthCalendar1.SelectionRange.Start.Date) [/SIZE][SIZE=2][COLOR=#0000ff]Then[/COLOR][/SIZE][SIZE=2][COLOR=#008000]'the output from the DataTimePicker Control' then
[/COLOR][/SIZE][SIZE=2]TextBox1.Text = """" + Convert.ToString(Dr.Item(2)) + """"
TextBox2.Text = Convert.ToString(Dr.Item(1))
Matches += 1
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE]
[/INDENT][SIZE=2][COLOR=#0000ff]Next
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] Matches = -1 [/SIZE][SIZE=2][COLOR=#0000ff]Then

[/COLOR][/SIZE][SIZE=2][INDENT]MessageBox.Show("Sorry, No Matches Found... ")

[/INDENT][/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2]
[/SIZE]
[/INDENT][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub
[/COLOR][/SIZE]

So I use "MonthCalendar1.SelectionRange.Start.Date" for getting the output of MonthCalendar Control and compare it with the data table's date column. Maybe it is a wrong way but it is the only way that I've found so far getting the selected date in DD.MM.YYYY format.

Anyway, now when I run the program it works: user selects a date and the text with its title is displayed inside textboxes...

But here is the strange behaviour: This only works for February... When I change months it shows the "No Matches Found" windows, so it can not match any date later than february... Here is a sample row from my table:
Table: TEST
<date> <title> <text>

Maybe I should modify my Table? insert numbers for each row? or is it possile to solve the problem by using more lines of code?

Thanks in advance!
Ring_of_Fire

 

[vis781]

This isn't a problem with your table. It's more likely that its the way we are attempting to find the matching date in the datatable. I havent tested that code as i wrote it from memory in my lunch break (looked ok at the time!!) Has your database got a primary key? What we need to do is find out exactly what the output it from your datetime picker. So use

messagebox.show( here put the output from the date/time picker)

and check and see it is displaying correctly.

 

[ring_of_fire]

ooops... but mission accomplished!

After your e-mail, my eyes are wide opened and I see my inattentiveness at one point.

Everyhing (in the code) was correct but one detail: the database that was linked to the program was the initial table with only february month...

I was updating a Database with the same name but in another directory..

I really appreciated your help, especially your efforts during your lunch time.

Now I'll go on with finishing touches, mainly GUI work..

Thanks.