monthcalendar and listview in vb.net using an access database

I am very new to VB.net

I have an application with two forms the main form writes info (dates, username and times that a user would check out a projector) to an access database. I am using a monthcalendar control in my project.   I would like to query my database for the date selected that a user has something checked out, and have it show up in a listivew on the second form. I am using Datasets.

Ex. User clicks May 12th 2006 and I would like it to look for any May 12th 2006 in the access table and writes the information in the listview on the second form.

Using VB Code

Does anyone now where to find such information, Which book, maybe an internet site, any help would be greatly appreciated.

Thanks in advance.
Tmiles75Asked:
Who is Participating?
 
Bob LearnedConnect With a Mentor Commented:
Comments:

1) Use Imports System.Data.OleDb at the top of the module, and you can short-cut some syntax:

Dim con As New OleDb.OleDbConnection

   becomes

Dim con As New OleDbConnection

2) Use the benefit of constructors with objects as much as possible.

    Dim con As New OleDbConnection("connection string goes here")
    Dim myData As OleDbDataReader = cmd1.ExecuteDataReader()

3) Do While...Loop is still supported for legacy VB6 code, but I prefer the While...End While construct

4) It sounds like you need a Where condition for the SQL select statement:

Dim Str1 As String = String.Format("SELECT UID,StDate,StTimeID,EDate,ETimeID,ProjectorID FROM InventoryItem Where eDate Between '{0}' And '{1}'  ", Calendar.SelectionStart, Calendar.SelectionEnd)

Bob      
0
 
Bob LearnedCommented:
That is one very large question.  What would you like help with?  And, everything is not an acceptable answer.

Bob
0
 
Tmiles75Author Commented:
Bob,

What would be the best way to query my database using a dataset. to pull check out date, checkin date, name ,check out time, checkin time, and projector model all from one table? I have searched books , and web sites and had no luck. I can post the code that I have so far if that helps?

Thanks,

Tim
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Bob LearnedCommented:
Tim,

I would love to help in any way that I can.  Often times, someone will ask a very broad question, that has 10000 different options, so I like to narrow it down to something manageable.  I need some information to make the "best" recommendation that I can:

1) .NET version?  2002, 2003, or 2005?  (say 2005, please!!)

2) Database type?  SQL Server, Access, ...?

3) Database version?

Bob
0
 
Tmiles75Author Commented:
Bob,

Sorry didn't think to include that information.

I am using Visual Studio.NET 2003. The database is Microsoft Access 2003 SP2

Thanks,

Tim
0
 
Bob LearnedCommented:
Options to read data:

1) OleDbCommand/OleDbDataReader
2) OleDbDataAdapter/DataTable for single table selects
3) OleDbDataAdapter/DataSet for multiple table selects

The DataReader is a light-weight, forward only view of the data.  You have a lot of control over a DataTable, but it is a heavier, more complex object.  DataSets are basically objects that can have multiple DataTable objects, plus other things like DataRelation objects.

What is your most important consideration here:  speed, usability, row indexing, ...?

Bob
0
 
Tmiles75Author Commented:
Bob,

I would say usability. I am going to post some code that I have and maybe that will help me explain what I am looking for. Like I said I am very new to this.
The code I am posting is my form1.

Dim con As New OleDb.OleDbConnection

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        'database connection
        con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Projector_Database\Projector.mdb"

        'fill the dataset's
        OleDbDataAdapter1.Fill(DataSet11, "Users")
        cmbUser.Text = SystemInformation.UserName
        OleDbDataAdapter2.Fill(DataSet21, "ProjectorName")
        OleDbDataAdapter3.Fill(DataSet31, "StTime")
        OleDbDataAdapter4.Fill(DataSet41, "ETime")

    End Sub

    Private Sub Timer1_Tick(ByVal sender As Object, ByVal e As System.EventArgs) Handles Timer1.Tick

        'Show Time in the stausbar
        Timer1.Start()
        StatusBar1.Text = FormatDateTime(Now(), DateFormat.GeneralDate)

    End Sub

    Private Sub MenuItem3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem3.Click

        Dim frmNonModal As New NewUser

        'Open NewUser form in modeless version
        frmNonModal.Text = "AddUser"
        frmNonModal.Show()

    End Sub

    Private Sub MenuItem4_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles MenuItem4.Click

        Dim frmNonModal As New NewProjector

        'Open NewProjector form in modeless version
        frmNonModal.Text = "Add Projector"
        frmNonModal.Show()

    End Sub

    Private Sub MenuItem2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MenuItem2.Click

        'allows it to exit like winxp
        Dim i As Single
        For i = 1 To 0 Step -0.1
            Me.Opacity = i
            Application.DoEvents()
            System.Threading.Thread.Sleep(100)
        Next
        Me.Dispose()

    End Sub

    Private Sub MonthCalendar1_DateChanged(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DateRangeEventArgs) Handles MonthCalendar1.DateChanged

    End Sub

    Private Sub MonthCalendar1_DateSelected(ByVal sender As Object, ByVal e As System.Windows.Forms.DateRangeEventArgs) Handles MonthCalendar1.DateSelected

        'open connection
        con.Open()

        Dim frmNonModal As New Calendar
        Dim StDate As Date
        Dim EDate As Date
        Dim UID As String
        Dim UID As DataColumn
        Dim StTimeID As Date
        Dim ETimeID As DateTime
        Dim ProjectorID As String
        Dim Drow As DataRow
        Dim Listview1 As ListView


        UID = DataSet51.InventoryItem.UIDColumn

       
        StDate = MonthCalendar1.SelectionStart
        EDate = MonthCalendar1.SelectionRange.End





        'Dim str1 As String = "SELECT * FROM InventoryItem WHERE Start Date = " & StDate & ""
        'Drow("StDate") = ListView1.Items.Add(StDate)

        'EDate = MonthCalendar1.SelectionStart
        'StTimeID = DateTime.Today

        'Fill Listview in Calendar Form
        frmNonModal.DataSet51.Tables("InventoryItem").NewRow()
        frmNonModal.ListView1.Items.Add(UID)
        frmNonModal.ListView1.Items.Add(StDate)
        frmNonModal.ListView1.Items.Add(EDate)

        'Open calendar form in modeless version
        frmNonModal.Text = "Calendar"
        frmNonModal.Show()

        'Dim str1 As String = "SELECT * FROM InventoryItem WHERE Start_Date = " & StDate & "" 'AND End_Date = '" & EDate & "' AND Start_Time = " _
        '& StTimeID & "' AND End_Time = '" & ETimeID & "' AND Projector = '" & ProjectorID & "'"

        'MsgBox(MonthCalendar1.SelectionStart)

        'close connection  
        con.Close()

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        'open connection
        con.Open()

        'adding new record/row to the table
        Dim drw1 As DataRow = DataSet51.Tables("InventoryItem").NewRow()

        'Define Sql String for lookup
        'Dim str1 As String = "SELECT FROM Users WHERE TID = " & cmbUser.Text & ""
        drw1("UID") = cmbUser.Text

        'Dim str2 As String = "SELECT FROM STime WHERE Time = " & cmbSTime.Text & ""
        drw1("StTimeID") = cmbSTime.Text

        drw1("StDate") = DateTimePicker1.Text

        drw1("EDate") = DateTimePicker2.Text

        'Dim str4 As String = "SELECT FROM ETime WHERE Time = " & cmbETime.Text & ""
        drw1("ETimeID") = cmbETime.Text

        'Dim str5 As String = "SELECT FROM ProjectorName WHERE ProName = " & cmbProjector.Text & ""
        drw1("ProjectorID") = cmbProjector.Text

        DataSet51.Tables("InventoryItem").Rows.Add(drw1)

        'update the access database
        OleDbDataAdapter5.Update(DataSet51, "InventoryItem")

        MsgBox("Successfully Checked Out")

        'Clears databindings for comboboxes
        cmbUser.DataBindings.Clear()
        DateTimePicker1.DataBindings.Clear()
        cmbSTime.DataBindings.Clear()
        DateTimePicker2.DataBindings.Clear()
        cmbETime.DataBindings.Clear()
        cmbProjector.DataBindings.Clear()

        'close connection  
        con.Close()

    End Sub
End Class

Thanks Again!
0
 
Bob LearnedCommented:
Well, you are doing pretty good so far, for someone who doesn't know it very well.

What kind of help do you want?  Does this code work?  Do you want it work better?

Bob
0
 
Tmiles75Author Commented:
Thanks lots of reading on the net and books.
Yes the codes works.

I need help with the code listed below. I need help with, when I click on any date in the monthcalendar control i would like to query my InventoryItems table in my access database to search for the date selected, users, time, and porjector and  list them in a listview on another form. I have the other form already made, but I don't know how to query the info and list it in the listview.

 Private Sub MonthCalendar1_DateSelected(ByVal sender As Object, ByVal e As System.Windows.Forms.DateRangeEventArgs) Handles MonthCalendar1.DateSelected

        'open connection
        con.Open()

        Dim frmNonModal As New Calendar
        Dim StDate As Date
        Dim EDate As Date
        Dim UID As String
        Dim UID As DataColumn
        Dim StTimeID As Date
        Dim ETimeID As DateTime
        Dim ProjectorID As String
        Dim Drow As DataRow
        Dim Listview1 As ListView


        UID = DataSet51.InventoryItem.UIDColumn

       
        StDate = MonthCalendar1.SelectionStart
        EDate = MonthCalendar1.SelectionRange.End





        'Dim str1 As String = "SELECT * FROM InventoryItem WHERE Start Date = " & StDate & ""
        'Drow("StDate") = ListView1.Items.Add(StDate)

        'EDate = MonthCalendar1.SelectionStart
        'StTimeID = DateTime.Today

        'Fill Listview in Calendar Form
        frmNonModal.DataSet51.Tables("InventoryItem").NewRow()
        frmNonModal.ListView1.Items.Add(UID)
        frmNonModal.ListView1.Items.Add(StDate)
        frmNonModal.ListView1.Items.Add(EDate)

        'Open calendar form in modeless version
        frmNonModal.Text = "Calendar"
        frmNonModal.Show()

        'Dim str1 As String = "SELECT * FROM InventoryItem WHERE Start_Date = " & StDate & "" 'AND End_Date = '" & EDate & "' AND Start_Time = " _
        '& StTimeID & "' AND End_Time = '" & ETimeID & "' AND Projector = '" & ProjectorID & "'"

        'MsgBox(MonthCalendar1.SelectionStart)

        'close connection  
        con.Close()

    End Sub


Thanks Again,

Tim
0
 
Bob LearnedCommented:
Try something like this example:

Imports System.Data.OleDb

...


Dim connection As New OleDbConnection("connection string goes here")
Dim command As New OleDbCommand("SELECT * FROM InventoryItem WHERE Start Date = #" & StDate & "#", connection)
connection.Open()

Dim reader As OleDbDataReader = command.ExecuteReader()

While reader.Read
 frmNonModal.ListView1.Items.Add(reader("UID").ToString())
 frmNonModal.ListView1.Items.Add(reader("StDate").ToString())
 frmNonModal.ListView1.Items.Add(reader("EDate").ToString())
End While

connection.Close()

Bob
0
 
Tmiles75Author Commented:
Bob,

Thank you very much! I am much closer now. I am getting an error with the query when ever i put the WHERE in the query it erros out at      reader = cmd1.ExecuteReader any Ideas? It put's all of the dates from my table that I have in there. I would like it to put only the date that is selected, any Ideas? This is the code that I have had to use a little modified from what you gave me.

 'open database connection
        Dim cnn1 As System.Data.OleDb.OleDbConnection = con
        cnn1.Open()

        Dim str1 As String = "SELECT StDate FROM InventoryItem" ' Where StDate = DateSelected"

        'Dim Str1 As String = "SELECT * FROM InventoryItem WHERE Start Date = #" & StDate & "#", connection


        'Declare and instantiate Command object
        Dim cmd1 As New System.Data.OleDb.OleDbCommand(str1, cnn1)
       

        'make available a data reader for the shippers table.
        Dim reader As System.Data.OleDb.OleDbDataReader
        reader = cmd1.ExecuteReader

Thanks again,

Tim
0
 
Bob LearnedCommented:
What was wrong with this?

      Dim str1 As String = "SELECT StDate FROM InventoryItem Where StDate = #" & DateSelected & "#"

Also, if you put Imports System.Data.OleDb at the top of the module, you can short-cut the references:

   System.Data.OleDb.OleDbCommand becomes just OleDbCommand

Bob

0
 
Tmiles75Author Commented:
If I put System.Data.OleDb.OleDbCommand at the top I get this C:\Projector_VB\Projector\Projector.vb(737): 'OleDbCommand' is a type in 'OleDb' and cannot be used as an expression.

As soon as I put the WHERE into the (Dim str1 As String = "SELECT StDate FROM InventoryItem Where StDate = #" & DateSelected & "#") Query it errors out on the   reader = cmd1.ExecuteReader..   This is the Error An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll..

Any more Ideas would be greatly appreciated..

Thank you


0
 
Bob LearnedCommented:
Let's try this:

Imports System.Data.OleDb

Public Class DataLayer

   Public Sub FillListView(ByVal view As ListView, ByVal table As String, ByVal columns As String, ByVal where As String, ParamArray fieldList() As String)

      ' Initialize the connection.
      Dim connection As New OleDbConnection("connection string goes here")

      ' Build the Select query statement.
      '   Example:  SELECT StDate FROM InventoryItem WHERE StDate = #6/20/2006#
      Dim commandText As String = String.Format("SELECT {0} FROM {1} WHERE {2}", columns, table, where)

      ' Create a command object to get the selected data from the specified table.
      Dim command As New OleDbCommand(commandText, connection)

      ' Open the connection
      connection.Open()

      ' Define a data reader
       Dim reader As OleDbDataReader

      Try
         reader = command.ExecuteReader()
      Catch ex As Exception
         MessageBox.Show(ex.ToString())
      End Try

      If Not reader Is Nothing Then
        ' Loop through all the records.
        While reader.Read
            ' Loop through all the fields passed in as method arguments.
            For Each fieldName In fieldList
               ' Add each item to the ListView.
               view.Items.Add(reader(fieldName).ToString())
            Next fieldName
        End While
      End If

   End Sub

End Class

Example usage:
  Dim layer As New DataLayer
  layer.FillListView(frmNonModal.ListView1, "InventoryItem", "StDate", "StDate = #" & DateSelected & "#")

Bob
0
 
Tmiles75Author Commented:
Bob

Not sure how to get this to work when I click on my MonthCalendar date that this will open the code? Also for (fieldName) in this line For Each fieldName In fieldList. It wants me to declare fielName and I don't know how to? I have tried multiple ways to include this (Imports System.Data.OleDb) but I keep getting (C:\Projector_VB\Projector\Projector.vb(737): 'OleDbCommand' is a type in 'OleDb' and cannot be used as an expression.)

Sorry I am a Pain but very new to all of this

Thanks,

Tim
0
 
Bob LearnedCommented:
Oops, another one which fell through the crack (I hate when that happen!!!)  Did you still need help?

Bob
0
 
Tmiles75Author Commented:
Bob,

Althought you never answered my last question. I still need help. I will post the code that I used to fill the listview with the Data from my database. I would like to give points but my question has not even been fully answered.
The help I need now is How do you get the date that I select on my monthcalendar To the listview.(I only want the date selected to show up in my listview.) If you know how any advice would be great.


    Private Sub Calendar_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim con As New OleDb.OleDbConnection
        Dim shtFieldCntr As Short
        Dim lvwColumn As ColumnHeader
        Dim myData As OleDb.OleDbDataReader


        'database connection
        con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Projector_Database\Projector.mdb"


        'open connection
        con.Open()

        'build the select query statement
        Dim Str1 As String = "SELECT UID,StDate,StTimeID,EDate,ETimeID,ProjectorID FROM InventoryItem"
        ' Dim Str1 As String = "SELECT DATEPART(StDate, EDate),UID,StTimeID,ETimeID,ProjectorID FROM InventoryItem"

        'Declare and select data from table
        Dim cmd1 As New System.Data.OleDb.OleDbCommand(Str1, con)


        'make available a data reader for the InventoryItem table.
        myData = cmd1.ExecuteReader()


        'Add columns
        ListView1.Columns.Add("User Name", 70, HorizontalAlignment.Left)
        ListView1.Columns.Add("Start Date", 140, HorizontalAlignment.Left)
        ListView1.Columns.Add("Start Time", 70, HorizontalAlignment.Left)
        ListView1.Columns.Add("End Date", 140, HorizontalAlignment.Left)
        ListView1.Columns.Add("End Time", 70, HorizontalAlignment.Left)
        ListView1.Columns.Add("Projector", 100, HorizontalAlignment.Left)

        lvwColumn = Nothing

        'Adding the ListItems to the ListView with the data
        'Using VB .Net and an ADO.Net SqlDataReader named MyData
        Dim itmListItem As ListViewItem



        Do While myData.Read
            itmListItem = New ListViewItem

            itmListItem.Text = myData(0)

            For shtFieldCntr = 1 To myData.FieldCount() - 1
                If myData.IsDBNull(shtFieldCntr) Then
                    itmListItem.SubItems.Add("")
                Else
                    itmListItem.SubItems.Add(myData.GetString(shtFieldCntr))
                End If
            Next shtFieldCntr

            ListView1.Items.Add(itmListItem)
        Loop


        'close connection  
        con.Close()
End Sub
0
 
Tmiles75Author Commented:
Bob,
 I was able to get it to work with this code below. I had to format the date to match my Table.

  Dim Fodate As String = Format(MonthCalendar1.SelectionStart, "dddd, MMMM d, yyyy")


        Dim Str1 As String = String.Format("SELECT UID,StDate,StTimeID,EDate,ETimeID,ProjectorID FROM InventoryItem Where StDate = '{0}' ", Fodate)

Thanks for all the help.
0
All Courses

From novice to tech pro — start learning today.