Imports System.Data
Imports System.Data.SqlClient
Public Class Form1
Inherits System.Windows.Forms.Form
create database connection
Dim myConnection As SqlConnection = New SqlConnection("server=(local); database=CallSchedule; trusted_connection=true;")
create the dataAdapter
Dim myDataAdapter As SqlDataAdapter = New SqlDataAdapter
Dim myDataSet As DataSet = New DataSet
Dim sqlspScheduleList As SqlCommand = New SqlCommand
Dim myDataReader As SqlDataReader
Public Sub GetDBItems()
With Me.sqlspScheduleList
.Connection = Me.myConnection
.CommandType = CommandType.StoredProcedure
.CommandText = "spScheduleList"
End With
Me.myDataAdapter.SelectCommand = Me.sqlspScheduleList
With Me.sqlspScheduleList.Parameters
.Add(New SqlParameter("@intTblInstitutionId", SqlDbType.Int))
.Add(New SqlParameter("@dtmStartDate", SqlDbType.DateTime))
.Add(New SqlParameter("@dtmStopDate", SqlDbType.DateTime))
.Add(New SqlParameter("@vcShiftName", SqlDbType.VarChar, 50))
.Add(New SqlParameter("@cInitials", SqlDbType.Char, 4))
.Add(New SqlParameter("@debug", SqlDbType.Int))
.Add(New SqlParameter("@ErrorNo", SqlDbType.Int, 4, System.Data.ParameterDirection.Output, False, CType(0, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
.Add(New SqlParameter("@ErrorMsg", SqlDbType.VarChar, 50, System.Data.ParameterDirection.Output, False, CType(0, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
End With
Dim StartDate As Date
Dim StopDate As Date
Dim ShiftName As String
Dim Initials As String
StartDate = "6/15/2003"
StopDate = "6/30/2003"
ShiftName = "non"
Initials = "mm"
With Me.sqlspScheduleList
.Parameters("@intTblInstitutionId").Value = 1
.Parameters("@dtmStartDate").Value = StartDate
.Parameters("@dtmStopDate").Value = StopDate
.Parameters("@vcShiftName").Value = ShiftName
.Parameters("@cInitials").Value = Initials
.Parameters("@debug").Value = 0
End With
Me.myDataSet = New DataSet
open connection
Me.myConnection.Open()
Try
Me.sqlspScheduleList.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex)
Throw (ex)
End Try
Me.myConnection.Close()
Fill the DataAdapter
Me.myDataAdapter.Fill(Me.myDataSet, "CalendarList")
populate the datagrid
Me.grdGrid1.DataSource = Me.myDataSet
Me.grdGrid1.DataMember = "CalendarList"
End Sub
Public Structure MyDates
Dim _dtmDates As Date
Dim _Shift As String
Dim _InstitutionName As String
Dim _Initials As String
Dim _ScheduleId As Integer
End Structure
Public MyCollection As Collection
Public tempDate As DateTime
#Region " Windows Form Designer generated code "
Public Sub New()
MyBase.New()
This call is required by the Windows Form Designer.
InitializeComponent()
Add any initialization after the InitializeComponent() call
End Sub
Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub
Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer
NOTE: The following procedure is required by the Windows Form Designer
It can be modified using the Windows Form Designer.
Do not modify it using the code editor.
Friend WithEvents Calendar1 As System.Windows.Forms.MonthCalendar
Friend WithEvents grdGrid1 As System.Windows.Forms.DataGrid
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Me.Calendar1 = New System.Windows.Forms.MonthCalendar
Me.grdGrid1 = New System.Windows.Forms.DataGrid
CType(Me.grdGrid1, System.ComponentModel.ISupportInitialize).BeginInit()
Me.SuspendLayout()
Calendar1
Me.Calendar1.Location = New System.Drawing.Point(64, 96)
Me.Calendar1.Name = "Calendar1"
Me.Calendar1.TabIndex = 0
grdGrid1
Me.grdGrid1.DataMember = ""
Me.grdGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText
Me.grdGrid1.Location = New System.Drawing.Point(72, 392)
Me.grdGrid1.Name = "grdGrid1"
Me.grdGrid1.Size = New System.Drawing.Size(728, 168)
Me.grdGrid1.TabIndex = 1
Form1
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
Me.ClientSize = New System.Drawing.Size(896, 598)
Me.Controls.Add(Me.grdGrid1)
Me.Controls.Add(Me.Calendar1)
Me.Name = "Form1"
Me.Text = "Form1"
CType(Me.grdGrid1, System.ComponentModel.ISupportInitialize).EndInit()
Me.ResumeLayout(False)
End Sub
#End Region
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
tempDate = Me.Calendar1.TodayDate
GetDBItems()
MyCollection = New Collection
Dim temp As MyDates
While (mydatareader.read())
temp = New MyDates
temp._dtmDates = CDate(myDataReader.Item("dtmDate"))
temp._Shift = myDataReader.Item("vcShiftName")
temp._InstitutionName = myDataReader.Item("vcInstitutionName")
temp._Initials = myDataReader.Item("cInitials")
temp._ScheduleId = CInt(myDataReader.Item("intTblScheduleId"))
MyCollection.Add(temp)
End While
For Each mycollection.Item
If DayTextHasChanged = True Then
Exit For
End If
MultipleItemDay = False
If dayhold = item._dtmDates Then
dayhold = item._dtmDates
Else
MultipleItemDay = True
End If
If MultipleItemDay = False Then
temp(New stringbuilder)
Else
temp.append("<br>")
End If
temp.append(item._shift.tostring())
DayTextHasChanged = True
Next
End Sub
End Class
