Imports System.Data
Imports System.Data.OleDb
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.ReportSource
Imports CrystalDecisions.Shared
Public Class frmReport
Inherits System.Windows.Forms.Form
#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 GroupBox1 As System.Windows.Forms.GroupBox
Friend WithEvents txtFromDate As System.Windows.Forms.TextBox
Friend WithEvents txtToDate As System.Windows.Forms.TextBox
Friend WithEvents Label1 As System.Windows.Forms.Label
Friend WithEvents Label2 As System.Windows.Forms.Label
Friend WithEvents btnRetrieve As System.Windows.Forms.Button
Friend WithEvents CrystalReportViewer1 As CrystalDecisions.Windows.Forms.CrystalReportViewer
Friend WithEvents OleDbDataAdapter1 As System.Data.OleDb.OleDbDataAdapter
Friend WithEvents OleDbSelectCommand1 As System.Data.OleDb.OleDbCommand
Friend WithEvents OleDbInsertCommand1 As System.Data.OleDb.OleDbCommand
Friend WithEvents chkFutureDates As System.Windows.Forms.CheckBox
Friend WithEvents chkDeceasedInc As System.Windows.Forms.CheckBox
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Me.GroupBox1 = New System.Windows.Forms.GroupBox
Me.Label2 = New System.Windows.Forms.Label
Me.Label1 = New System.Windows.Forms.Label
Me.txtToDate = New System.Windows.Forms.TextBox
Me.txtFromDate = New System.Windows.Forms.TextBox
Me.btnRetrieve = New System.Windows.Forms.Button
Me.CrystalReportViewer1 = New CrystalDecisions.Windows.Forms.CrystalReportViewer
Me.OleDbDataAdapter1 = New System.Data.OleDb.OleDbDataAdapter
Me.OleDbInsertCommand1 = New System.Data.OleDb.OleDbCommand
Me.OleDbSelectCommand1 = New System.Data.OleDb.OleDbCommand
Me.chkFutureDates = New System.Windows.Forms.CheckBox
Me.chkDeceasedInc = New System.Windows.Forms.CheckBox
Me.GroupBox1.SuspendLayout()
Me.SuspendLayout()
'
'GroupBox1
'
Me.GroupBox1.Controls.Add(Me.Label2)
Me.GroupBox1.Controls.Add(Me.Label1)
Me.GroupBox1.Controls.Add(Me.txtToDate)
Me.GroupBox1.Controls.Add(Me.txtFromDate)
Me.GroupBox1.Location = New System.Drawing.Point(8, 8)
Me.GroupBox1.Name = "GroupBox1"
Me.GroupBox1.Size = New System.Drawing.Size(392, 56)
Me.GroupBox1.TabIndex = 0
Me.GroupBox1.TabStop = False
Me.GroupBox1.Text = "Date Parameters"
'
'Label2
'
Me.Label2.Location = New System.Drawing.Point(208, 24)
Me.Label2.Name = "Label2"
Me.Label2.Size = New System.Drawing.Size(40, 12)
Me.Label2.TabIndex = 4
Me.Label2.Text = "To:"
'
'Label1
'
Me.Label1.Location = New System.Drawing.Point(40, 24)
Me.Label1.Name = "Label1"
Me.Label1.Size = New System.Drawing.Size(40, 12)
Me.Label1.TabIndex = 3
Me.Label1.Text = "From:"
'
'txtToDate
'
Me.txtToDate.Location = New System.Drawing.Point(248, 24)
Me.txtToDate.Name = "txtToDate"
Me.txtToDate.TabIndex = 2
Me.txtToDate.Text = ""
'
'txtFromDate
'
Me.txtFromDate.Location = New System.Drawing.Point(88, 24)
Me.txtFromDate.Name = "txtFromDate"
Me.txtFromDate.TabIndex = 1
Me.txtFromDate.Text = ""
'
'btnRetrieve
'
Me.btnRetrieve.Location = New System.Drawing.Point(608, 16)
Me.btnRetrieve.Name = "btnRetrieve"
Me.btnRetrieve.Size = New System.Drawing.Size(112, 32)
Me.btnRetrieve.TabIndex = 3
Me.btnRetrieve.Text = "Retrieve Records"
'
'CrystalReportViewer1
'
Me.CrystalReportViewer1.ActiveViewIndex = -1
Me.CrystalReportViewer1.Anchor = CType((((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Bottom) _
Or System.Windows.Forms.AnchorStyles.Left) _
Or System.Windows.Forms.AnchorStyles.Right), System.Windows.Forms.AnchorStyles)
Me.CrystalReportViewer1.Location = New System.Drawing.Point(16, 80)
Me.CrystalReportViewer1.Name = "CrystalReportViewer1"
Me.CrystalReportViewer1.ReportSource = Nothing
Me.CrystalReportViewer1.Size = New System.Drawing.Size(696, 360)
Me.CrystalReportViewer1.TabIndex = 0
'
'OleDbDataAdapter1
'
Me.OleDbDataAdapter1.InsertCommand = Me.OleDbInsertCommand1
Me.OleDbDataAdapter1.SelectCommand = Me.OleDbSelectCommand1
Me.OleDbDataAdapter1.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "NED", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("DOE", "DOE"), New System.Data.Common.DataColumnMapping("Event", "Event"), New System.Data.Common.DataColumnMapping("Name", "Name")})})
'
'OleDbInsertCommand1
'
Me.OleDbInsertCommand1.CommandText = "INSERT INTO NED(DOE, Event, Name) VALUES (?, ?, ?)"
Me.OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("DOE", System.Data.OleDb.OleDbType.DBDate, 0, "DOE"))
Me.OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Event", System.Data.OleDb.OleDbType.VarWChar, 255, "Event"))
Me.OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Name", System.Data.OleDb.OleDbType.VarWChar, 255, "Name"))
'
'OleDbSelectCommand1
'
Me.OleDbSelectCommand1.CommandText = "SELECT DOE, Event, Name FROM NED"
'
'chkFutureDates
'
Me.chkFutureDates.Checked = True
Me.chkFutureDates.CheckState = System.Windows.Forms.CheckState.Checked
Me.chkFutureDates.Location = New System.Drawing.Point(408, 16)
Me.chkFutureDates.Name = "chkFutureDates"
Me.chkFutureDates.Size = New System.Drawing.Size(192, 16)
Me.chkFutureDates.TabIndex = 1
Me.chkFutureDates.Text = "Exclude Names with Future Dates"
'
'chkDeceasedInc
'
Me.chkDeceasedInc.Location = New System.Drawing.Point(408, 40)
Me.chkDeceasedInc.Name = "chkDeceasedInc"
Me.chkDeceasedInc.Size = New System.Drawing.Size(200, 24)
Me.chkDeceasedInc.TabIndex = 2
Me.chkDeceasedInc.Text = "Include Deceased Clients"
'
'frmReport
'
Me.AcceptButton = Me.btnRetrieve
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
Me.ClientSize = New System.Drawing.Size(728, 454)
Me.Controls.Add(Me.chkDeceasedInc)
Me.Controls.Add(Me.chkFutureDates)
Me.Controls.Add(Me.CrystalReportViewer1)
Me.Controls.Add(Me.btnRetrieve)
Me.Controls.Add(Me.GroupBox1)
Me.Name = "frmReport"
Me.Text = "Reports"
Me.GroupBox1.ResumeLayout(False)
Me.ResumeLayout(False)
End Sub
#End Region
Dim sSQL As String
Dim strFrom As String
Dim strTo As String
Dim strNlkupSQL As String
Dim blnNlkup As Boolean
Private Sub btnRetrieve_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRetrieve.Click
Dim intCnt As Integer
Dim strMsg As String
Dim dsAllWill1 As New dsAllWill
'build SQL statement
sSQL = "SELECT RecID, Name, Event, DOE FROM AllWill"
Try
strFrom = Date.Parse(txtFromDate.Text).ToShortDateString.ToString
Catch ex As Exception
strFrom = ""
End Try
Try
strTo = Date.Parse(txtToDate.Text).ToShortDateString.ToString
Catch ex As Exception
strTo = ""
End Try
sSQL = sSQL & " WHERE "
If ((strFrom.Trim.Length + strTo.Trim.Length) > 0) Then
If (strFrom.Trim.Length > 0) Then
sSQL = sSQL & " DOE >= #" & strFrom.Trim & "# AND "
End If
If (strTo.Trim.Length > 0) Then
sSQL = sSQL & " DOE <= #" & strTo.Trim & "# AND "
If (chkFutureDates.Checked) Then
sSQL = sSQL & " NAME IN (SELECT NAME FROM NameLkup where not(blnReport)) AND "
End If
End If
End If
strNlkupSQL = " NAME IN (SELECT NAME FROM NameLkup where "
blnNlkup = False
If (strTo.Trim.Length > 0 And chkFutureDates.Checked) Then
strNlkupSQL = strNlkupSQL & " not(blnReport) AND "
blnNlkup = True
End If
If Not (chkDeceasedInc.Checked) Then
strNlkupSQL = strNlkupSQL & " not(blnRemoved) AND "
blnNlkup = True
End If
strNlkupSQL = strNlkupSQL + "true) AND "
If blnNlkup Then
sSQL = sSQL & strNlkupSQL
End If
sSQL = sSQL & " true "
sSQL = sSQL & " ORDER BY Name, DOE, Event"
If (chkFutureDates.Checked And strTo.Trim.Length > 0) Then
Dim tSQL As String
tSQL = "Update NameLkup Set blnReport=false"
OleCn = New OleDbConnection(gstrConnectionString)
OleCn.Open()
OleDbDataAdapter1.UpdateCommand = New OleDbCommand(tSQL)
OleDbDataAdapter1.UpdateCommand.Connection = OleCn
OleDbDataAdapter1.UpdateCommand.ExecuteNonQuery()
OleCn.Close()
tSQL = "Update NameLkup Set blnReport = true where name in " & _
"(select name from AllWill where DOE > #" & strTo.Trim & "#)"
OleCn = New OleDbConnection(gstrConnectionString)
OleCn.Open()
OleDbDataAdapter1.UpdateCommand = New OleDbCommand(tSQL)
OleDbDataAdapter1.UpdateCommand.Connection = OleCn
OleDbDataAdapter1.UpdateCommand.ExecuteNonQuery()
OleCn.Close()
End If
OleCn = New OleDbConnection(gstrConnectionString)
OleCn.Open()
Try
OleDbDataAdapter1.SelectCommand.CommandText = sSQL
OleDbDataAdapter1.SelectCommand.Connection = OleCn
OleDbDataAdapter1.Fill(dsAllWill1, "AllWill")
Catch ex As Exception
dsAllWill1.Clear()
End Try
OleCn.Close()
intCnt = dsAllWill1.Tables("AllWill").Rows.Count
If intCnt = 0 Then
strMsg = "Nothing qualified"
If ((strFrom.Trim.Length + strTo.Trim.Length) > 0) Then
strMsg = strMsg & " from " & strFrom.Trim & " to " & strTo.Trim & "."
End If
MessageBox.Show(strMsg, "Date Parameters", MessageBoxButtons.OK, MessageBoxIcon.Information)
Else
'Perform report
Dim rpt As New AllCustomers
rpt = New AllCustomers
rpt.SetDataSource(dsAllWill1)
Me.CrystalReportViewer1.ReportSource = rpt
End If
dsAllWill1.Clear()
End Sub
End Class
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:
157:
158:
159:
160:
161:
162:
163:
164:
165:
166:
167:
168:
169:
170:
171:
172:
173:
174:
175:
176:
177:
178:
179:
180:
181:
182:
183:
184:
185:
186:
187:
188:
189:
190:
191:
192:
193:
194:
195:
196:
197:
198:
199:
200:
201:
202:
203:
204:
205:
206:
207:
208:
209:
210:
211:
212:
213:
214:
215:
216:
217:
218:
219:
220:
221:
222:
223:
224:
225:
226:
227:
228:
229:
230:
231:
232:
233:
234:
235:
236:
237:
238:
239:
240:
241:
242:
243:
244:
245:
246:
247:
248:
249:
250:
251:
252:
253:
254:
255:
256:
257:
258:
259:
260:
261:
262:
263:
264:
265:
266:
267:
268:
269:
270:
271:
272:
273:
274:
275:
276:
277:
278:
279:
280:
281:
by: TheLearnedOnePosted on 2009-10-16 at 05:26:41ID: 25588821
Is the report pretty complicated, or can it be recreated in the new version? Crystal Reports has a lot of quirky edges, that are difficult to smooth out, and sometimes it is just better to start over again.