473,320 Members | 1,707 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

XML binding advice

I generate an SQL statement, fill a dataset with the results, then run an write XML doc from the dataset, then bind the XML to the Crystal report. When someone changes a parameter and runs the procedure again, it recreates everything again. Sounds slow. Please see my procedure below and comment. I let the user build their reports requirements from a form, then send the values to this Function. This is my first Dot Net try, so Im sure it isnt pretty. Thanks All, Eric. Code below

Public Function ProcessExpense(ByVal StartDate As Date, ByVal EndDate As Date, ByVal XMLPath As String, ByVal GroupOnOffice As Boolean, ByVal Officeselected As String
Dim sqlExpense As Strin
Dim adoOleDbConnection As OleDbConnectio
Dim adoOleDbDataAdapter As OleDbDataAdapte
Dim dataSet As DataSe
Dim connectionString As String = "
Dim crDTEReportOffice As New DTEExpenseOffic
Dim crDTEReportName As New DTEExpenseNam
StartDate = Format(StartDate, "MM/dd/yyyy"
EndDate = Format(EndDate, "MM/dd/yyyy"

connectionString = "Provider=SQLOLEDB;
connectionString += "Server=dt0101;Database=dte2002;
connectionString += "User ID=sa;Password=

''Create and open a connection using the connection strin
adoOleDbConnection = New OleDbConnection(connectionString

'Expense(SQL
sqlExpense = "SELECT '" & StartDate & "' as StartDate,'" & EndDate & "' as EndDate,timekeepers.name AS Name, dtetime.timekeeper AS TimeKeeper#, (CASE timekeepers.office
sqlExpense = sqlExpense & "WHEN '01' THEN 'Omaha'
sqlExpense = sqlExpense & "WHEN '02' THEN 'Denver'
sqlExpense = sqlExpense & "WHEN '03' THEN 'Washington DC'
sqlExpense = sqlExpense & "WHEN '04' THEN 'Atlanta'
sqlExpense = sqlExpense & "WHEN '05' THEN 'Scottsdale'
sqlExpense = sqlExpense & "WHEN '06' THEN 'Pasadena'
sqlExpense = sqlExpense & "WHEN '09' THEN 'Des Moines'
sqlExpense = sqlExpense & "WHEN '10' THEN 'Arkansas'
sqlExpense = sqlExpense & "WHEN '11' THEN 'Kansas City'
sqlExpense = sqlExpense & "WHEN '12' THEN 'Wichita'
sqlExpense = sqlExpense & "WHEN '14' THEN 'Richmond'
sqlExpense = sqlExpense & "WHEN '18' THEN 'Chicago'
sqlExpense = sqlExpense & "WHEN '17' THEN 'Irvine'
sqlExpense = sqlExpense & "WHEN '19' THEN 'Lincoln'
sqlExpense = sqlExpense & "WHEN '21' THEN 'Oklahoma City'
sqlExpense = sqlExpense & "WHEN '97' THEN 'National'
sqlExpense = sqlExpense & "ELSE timekeepers.office end)AS Office,
sqlExpense = sqlExpense & "dtetime.isexpense, CONVERT(decimal(10, 2), dtetime.hours) AS Amount, dtetime.client AS Client, dtetime.matter AS Matter,
sqlExpense = sqlExpense & "convert(varchar(50),dtetime.workdate,101) AS WorkDate
sqlExpense = sqlExpense & "FROM dtetime INNER JOIN
sqlExpense = sqlExpense & "timekeepers ON dtetime.timekeeper = timekeepers.timekeeper
sqlExpense = sqlExpense & "WHERE (dtetime.isexpense = 'Y') and timekeepers.timekeeper <> '22222' and timekeepers.timekeeper <> '22223' and timekeepers.timekeeper <> '22224'and timekeepers.timekeeper <> '22225'and timekeepers.timekeeper <> '22226'and timekeepers.timekeeper <> '22227'and timekeepers.timekeeper <> '22231'
sqlExpense = sqlExpense & "and timekeepers.timekeeper <> '22230'
sqlExpense = sqlExpense & "and timekeepers.timekeeper <> '22228'
sqlExpense = sqlExpense & "and timekeepers.timekeeper <> '22229'
If Officeselected = "timekeepers.office" The
sqlExpense = sqlExpense & "and timekeepers.office = timekeepers.office
Els
sqlExpense = sqlExpense & "and timekeepers.office = '" & Officeselected & "'
End I
sqlExpense = sqlExpense & " and dtetime.released = 'N'
sqlExpense = sqlExpense & "and workdate between '" & StartDate & "' and '" & EndDate & "' ORDER BY DATEPART(yyyy, workdate), DATEPART(mm, workdate), DATEPART(dd, workdate)

''Retrieve the data using the SQL statement and existing connection
adoOleDbDataAdapter = New OleDbDataAdapter(sqlExpense, adoOleDbConnection)
''Create a instance of a Dataset
dataSet = New DataSet

''Fill the dataset with the data retrieved. The name of the table
''in the dataset must be the same as the table name in the report.

Try
adoOleDbDataAdapter.Fill(dataSet, "DTEExpense")

Catch objectDataFill As Exception
'Throw objectDBConnect

MsgBox(objectDataFill.Message)

End Try

''Create an instance of the strongly-typed report object

dataSet.WriteXml(XMLPath, XmlWriteMode.WriteSchema)
''Pass the populated dataset to the report
If GroupOnOffice = True Then
crDTEReportOffice.SetDataSource(dataSet)
ProcessExpense = crDTEReportOffice

Else
crDTEReportName.SetDataSource(dataSet)
ProcessExpense = crDTEReportName
End If

''Set the viewer to the report object to be previewed.
adoOleDbConnection.Close()
adoOleDbConnection = Nothing
Nov 12 '05 #1
0 1056

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

26
by: lbolognini | last post by:
Hi all, I have a very long list of parameters coming from a web form to my method foo(self, **kwargs) I would like to avoid manually binding the variables to the values coming through the...
0
by: PCC | last post by:
I am writing an ASP.NET web application and I control all the tiers. I need to return all results from the Web Service mid-tier as XML so other people can use our business objects in the future if...
0
by: relaxedrob | last post by:
Hi All, I have a portType such as this: <portType name="CMLeJobSoapGetEmpBrand"> <operation name="EJobGetEmpBrand"> <input message="tns:EJobEmpBrdReq" name="EJobEmpBrdReq"/> <output...
3
by: nvx | last post by:
Hello everyone... Just for the record, I'm a newbie to this data binding thing... In my C# application I need to bind a cell of an Access database table to a multiline TextBox. How should I do...
20
by: andreas | last post by:
Hi, I am working with the streams objects (filestream,streamwriter,streamreader) May I do or is it good programming to make a sub like : public sub Closestreams(ByRef ob as object) if not (ob...
10
by: Scott | last post by:
Ok, I've lost half a day to pounding my head against this one and I'm sending out a distress call... This is so simple that it's emberassing. However, VC# 2005 Express has made things so...
0
by: SMcLellan | last post by:
Say, is anyone else using WCF under Vista and seeing delays on the order of 7-8 seconds when a WCF client, using the net.TCP binding, establishes a connection with a non-local service? I have a...
2
by: Randy | last post by:
Hi, I'm trying to figure out some binding navigator code. If I have the primary field value as selected by the user in a combo box, how can I instruct the binding navigator to move to that...
14
by: Siv | last post by:
hi, I am converting an application that writes to an Excel spreadsheet and the code trips the "option Strict" that I would like on because the parser says "option Strict On disallows late...
1
by: chdl | last post by:
Hello there - I'm currently researching Java-to-XML binding techniques, and have come across JAXB, Castor and XMLBeans. But not having implemented any of these before, I'm little skeptical to...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.