App Reads A Spreadsheet Into A DataSet With ADO.NET
Jul 23, 2010
I'm writing an application in vb.net 2005. The app reads a spreadsheet into a DataSet with ADO.NET and uses a column of that table to populate a ListBox. When a ListBox Item is selected, the user will be presented with detailed information on the selected record.One part of this information isn't in the DataSet. I have to compare a column from the spreadsheet with several external data sources to determine the nature of the record in question. Here's where I have my problem.This comparison has to search through 9.5m rows in a SQL table at one stage. I've checked and there's no way to "shrink" the query down as I'm already only searching absolutely essential data.What happens is that the application never visibly does anything. The CPU usage shoots up to 100% regardless of what it was at beforehand and the system's performance becomes almost unbearably slow.Can anyone suggest a way I can improve this situation while this massive query is running? I was originally going to write the contents of the 9.5m rows in the database table to a text file which I'd then read from, but after 6.5m rows, I got an OutOfMemoryException.
View 2 Replies
ADVERTISEMENT
Jul 15, 2010
My application reads information from an Excel spreadsheet into a DataSet (ds1) which is then filtered into another DataSet (ds2) based on user-specified criteria. The information is basically telephone call logs that reflects when a phone call was made,what extension it was made one, how long the call was, what number was called, etc.When the user's "search" criteria is matched in ds1 above, the application adds the number called to a ListBox on the form. When the ListBox Item is selected, additional information (call duration and such) is displayed to the right of the ListBox. My problem is that a single phone number could have been called multiple times on an extension within the date range the user specifies and for each occurrence of that number in the ListBox, the "expanded" info only reflects the information for the 1st call to that number listed in the DataSet.
The only unique identifier I have is the DateTime field for the row (as this goes up to the second). I thought about putting it into the ListBox item, but I'd prefer that this date not be visible until the user selects the ListBox item to view the details for that call. [code]
View 7 Replies
Oct 30, 2007
I have a simple Windows Service that reads from an Excel spreadsheet and imports the records into a SQL Server 2000 database table. Before it loads it checks if the file has been successfully imported before (using filename) and whether any of the records in the file have been imported before (using primary key). If these checks are ok I load the records into a datatable and then update the datatable to the database.
However, I need to do this in a transaction as I want to ignore the whole file if there is an error. The file goes to a discarded folder and the user gets an email with why the import failed. (This is likely to be because the store the record is for is not yet added to the database and the referential intergrity of the db fails). If there is no transaction the records before the failure record are imported. When the file is imported again (the store has been added to store table) the file is then rejected because duplicate records are already in the db.
Heres my code:
[CODE]...
View 3 Replies
Jul 28, 2009
I have a classic ASP page which reads incoming XML, Queries the database based on the values in the XML and returns an XML page with the results from the query. How can i invoke a .NET webservice with some values which sends to the classic ASP page and returns the results as a Data Set?
View 2 Replies
Oct 19, 2009
In my latest project, I'm iterating through an Excel Spreadsheet and importing the information from it into a DataSet row for row. Based on certain conditions (namely, the text in a certain column on the spreadsheet). For certain conditions, the app is to write the row into a new spreadsheet and email that spreadsheet as an attachment to a pre-defined email address.
[Code]...
View 7 Replies
Jul 22, 2010
My application uses a badly arranged Excel spreadsheet as a data source. I say badly designed because there's no unique identifier to each row aside from the column where dates and times are found. My app uses the date and time on each row as an identifier to retrieve information from the other columns in the row. The idea is that the user will select a date/time from a ListBox at which time, the app will loop through the DataSet and find the date/time in the right column and display the rest of the info in that row. My issue is that the ListBox control isn't being populated and I can't see why...
Here's a sample row from the spreadsheet: (Each | character represents a cell border)
Team - FNB | O | 2010/02/18 08:59:24 | 5034 | Frederico Turnbridge | 27839963586 | SA - MOBILE - (ZA) | | 69 | O_NORMAL | | 00:01:06 |R 2.83
Here's my code:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim j As Integer = 0
If TextBox1.Text.Length = 4 Then
For i As Integer = 0 To CallData.Tables(0).Rows.Count - 1
[Code] .....
This basically works on a search where the user will specify a term to search for (be it a telephone number or (in this case) a telephone extension) and a date range. Now, I know for a fact that the spreadsheet (and thus, the DataSet) has information where the date column - column index 2 - falls within the date range I'm specifying and the column after that contains my search term, but I'm not getting any results.
View 2 Replies
Nov 30, 2007
I am looking for an example to export the data from a dataset to an existing excel speadsheet in vb.net 2005?
View 6 Replies
Jul 6, 2010
I am trying to create a spreadsheet based on a filtered Dataset. The problem is I cannot connect to XL. I get the following error:
Warning1Namespace or type specified in the Imports 'Microsoft.office.interop' doesn't contain any public member or cannot be found. Make sure the namespace or the type is defined and contains at least one public member. Make sure the imported element name doesn't use any aliases.
From these lines of
Imports Microsoft.office.interop
Imports Excel = Microsoft.office.interop.excel
Another issue is I only have XL 2000, and I won't be able to get 2003 anytime soon. 2003 is what the target computer is using. I would like to develop the app with 2000 and make the appropriate changes to 2003 when I get upgraded. I couldn't get any information on the 2000 PIA here: [URL]. After reading this: [URL] it almost seems like I don't need the 2000 PIA. Many of the techniques described in this document involve the use of IAs for the Office applications.
For Office 97 and Office 2000, you can autogenerate these IAs using TlbImp. Office XP includes pregenerated (and optimized) IAs that you should use instead of autogenerating IAs. An IA distributed by the owner of the original COM server is called a primary interop assembly (PIA). You should always use the specific version of the IA that matches the version of Office you're targeting. I just want to develop the app. Then I can download the 2003 PIA, right? I would use this: [URL].
View 5 Replies
Jan 28, 2009
I have a hardware which sends out packets containing 17 bytes to the comport in bursts of 3. the little test VB that I have written receives and displays the bytes in a line of 17 bytes long and works fine until it gets to around 170th packet, then it starts to mix up (looks like shifting the data left) the bytes shift mostly by one digit sometimes by two. then it will continue to give almost a packet correct and a packet shifted for about 50 packets. Then it will correct itself for a long while, but then the speed at which it receives drops. All the packets are sent 3 times, and before things go wrong (within the first 170 packets) the packet counter shows that it receives 3 packet each time but after the problem it goes to 2 packet per burst and then to one.
If i close my VB monitoring program say at packet 165 and immediately open it again, it wont go wrong for another 170 packets, this points the finger at VB. I have check my hardware, there is no problem with that.The program reads port - waits for 17 bytes - once 17 has been received it will go and buffer the packet in an array. In fact they are 2 arrays but printing fro either results in the same problem.I have tried whatever i could think of but to no avail, i am wondering if some kind of house keeping needs to be done to clear something... I am sorry the program is a bit messy but I have been making many changes to find out what the problem is - my code below:[code]
View 11 Replies
Sep 19, 2011
I am attempting to write the entire contents of an ADO.NET DataSet to an Excel 2007 spreadsheet and, while having partial success, I am unable to dump the contents of the DataSet into the .xlsx file. "Partial success" meaning that I can generate the spreadsheet(s) ok, but cannot get the DataAdapter.Update to work.
[Code]....
View 8 Replies
Nov 11, 2011
I have a VB.Net program that reads in a flat file, and then parses line by line, formatting the data into different spreadsheets in an excel workbook (each line can be any 10+ different record types so I parse and put in appropriate excel sheet).
For smaller sized flat files (under 10mb), the parser works great. However, I am trying this on a file that is over 120mb (400k+ lines). While running, I will get an OleDBException saying that the spreadsheet is full. Now I am pretty confident that Excel can handle a much larger data set than a flat file. So I assume this exception is not giving me the true story as to what is really occuring.
I open a connection, and then parse each line in the file, inserting each row into the excel file. I assumed it would be bad performance wise to open/close the connection between each insert. Could this be causing the issue? Any ideas what I need to do to handle such a large file? There are cases where the flat file can be over 500mb.
To actually do the insert into excel, I am just doing the following (I construct an sql query based on the type of row and values parsed):
Dim conn As New OleDbConnection()
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExportLocation.Text + "" + importFileName + "-PVF.xls;Extended Properties=""Excel 8.0;HDR=YES"""
[Code]....
View 1 Replies
Mar 29, 2012
i have to create something that uses OpenFileDialog, and reads textfiles that i created it.
i did this
Private Sub SK_btn_OpenFile_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SK_btn_OpenFile.Click
SK_OpenFileDialog1.ShowDialog()
End Sub
I know how to read the File name, but i want to post the texts that are in the TextFile in my textbox when i press Open/Display file.
Private Sub OpenFileDialog1_FileOk(ByVal sender As System.Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles SK_OpenFileDialog1.FileOk
View 6 Replies
Apr 5, 2009
how to make a program that reads the url of ie or Firefox.. really Want to make a program like this ..
View 7 Replies
Oct 3, 2011
I would like to query a string that only reads a last character.
sqlstmt = "select * from table where Area like'%A'"
Dim cmd4 As New SqlCommand(sqlstmt, con2)
da = New SqlDataAdapter(cmd4)
ds = New DataSet
[code].....
I have the above codes but i've got '-1' if nothing to read..
View 8 Replies
Jun 6, 2011
I used the same exact simple loop to read all the tags on a different xml doc in the same program, but when i put it into this function (litterally a copy and paste and changed filepaths/node names) it only reads the first node, and doesn't go the others.
Private Function ReadKeyXML()
Try
' Algorithm
[code].....
View 5 Replies
Jun 21, 2010
This is my 50th attempt to read the memory and probably my last attempt if I don't succeed now T_T I've been trying to write a class that reads from the memory, simply by creating wrappers around the Win32 APIs but I just can't get it to work.
This is how I currently am trying to read the memory from calc.exe:
Public Function ReadMemory(ByVal hProcess As IntPtr, ByVal lpBaseAddress As IntPtr, ByVal nSize As Integer, ByVal lpNumberOfBytesRead As Integer) As String
Dim tmpBuff As Single = 0
[Code]...
View 2 Replies
Apr 24, 2010
I've managed to add a media player that reads items from a combobox. Only problem is the next and previous buttons on the media players dont work. I know they work for a LISTEBOX but how to make it work for a COMBOBOX? what code do i need to make it play the next song in the list or the previous song in the list. The name of the combobox is combobox2.
View 3 Replies
Feb 17, 2010
I'm launching my application at the end of installation using custom installer class - Sub oncommited.I'm using vb 2005 setup project.The application main form load event need to read/write values from HKCUSoftwareMyApp registry.The problem is that when the application is launched at the end of installation ,it reads/writes from HKEY_Users.DefaultSoftwareMyApp instead of HKCU though I've written
My .Computer.Registry.CurrentUser.CreateSubKey(RegKey).GetValue("Name","")
When the application is launched through Program files shortcut,it reads/writes properly.I need to exit the application depending on registry values.
View 1 Replies
Jun 24, 2010
I've got an application that reads a XML file via a stream reader. The problem is I can't edit my XML file and save it when others are using the application that is using the XML file at the time. how can i get around this?
View 1 Replies
Apr 3, 2011
I need to create an application that reads numbers from a file. it then should display the total of the numbers, display the average of the numbers, display the highest number in the file and display the lowest number in the file. We are using visual basic 2008. I am new to this and stuck . How do I begin. I included the number set that we are supposed to read from.
Attached File(s) NumberSet.txt (990bytes)Number of downloads: 80
View 14 Replies
Mar 24, 2010
I have successfully gotten the filestream function to read a text file and return bytes. I want it to return letters, not bytes, using random access.
View 4 Replies
Sep 12, 2011
We have a reporting app thats needs to update it's charts as the data gets written to it's corresponding table. (the report is based off just one table). Currently we just keep the last read sessionid + rowid (unique combo) in memory and a polling timer just does a select where rowid > what we have in memory (to get the latest rows added). Timer runs every second or so and the fast sql reader does it's job well. So far so good. However I feel this is not optimal because sometimes there are pauses in the data writes due to the process by design. (user clicking the pause button on the system that writes data). Meanwhile our timer keeps hitting the db and does not get any new rows. No errors or anything. How is this situation normally handled. The app that writes the data is separate from the reporting app. The 2 apps run on different machines. Bottomline : How to get data into a c# app as and when it is written into a sql server table without polling unnecessarily.
View 4 Replies
Jul 7, 2009
The code below reads each line from a file and if the conditions become true it adds the contents of the file from the particular line and after into a string collection (i.e. the All contents). This is what at least what the code should do as the current one only adds the line that it encounters into the string collection.
View 5 Replies
Feb 24, 2010
I'm currently finishing up a project that reads / writes to an access database. The only issue I'm having is that it needs to have the file path to the database file. EX:
[Code]...
View 11 Replies
Jun 11, 2010
What I do is I have a variable on form1 and its an integer which has a value on load of 0 and named count.When I click the 'doSomething' button it is increased by 1 and when I use the function 'msgBox' I get to see 1.After that I start a thread which is going to look up count but when I check the value there is 0.
View 22 Replies
Jul 20, 2011
I am writing a windows service that will read records from one table and write them to another table. Problem is when i declare cmd = New SqlCommand(l_sSQL) the code stop executing but the service will still be running. I can insert into the other table using the service but i cant read from the other table.
Code Sample:
Private Sub dbcon()
Dim con As New SqlConnection
Dim cmd As New SqlCommand
Dim adapter As New SqlDataAdapter
[code]...
View 1 Replies
Dec 20, 2011
I am trying to write a basic .net program that reads from one file with streamreader, scans each line for specific errors in formating and if the errors exist correct them in an output to another file that I've just renamed as same file name.cache.
View 2 Replies
Feb 2, 2009
Is there a way to rotate a label such that it reads vertically? I've checked all of the label properties but there doesn't seem to be one that will do the trick.
View 1 Replies
Oct 17, 2011
I have a USB device which creates a Virtual Serial Port on Windows. I am using VB.Net to write and read from the port. My device responds with specific sized set of bytes, but I am finding that SerialPort.Read(byte-array,offset,number-bytes) does not return the full number-bytes but it also does not timeout or generate an exception. Repeated calls return additional fragments (up to 3 calls required). Python code using pySerial does not have the same problem.So, what am I doing wrong here? Am I expecting too much?
Some scenarios are:I write a command to the port and expect to get 4 bytes in response. I get 1 byte first and then 3 bytes on the subsequent call.I write a command and expect 21120 bytes in response. I get 1, 12671 and then 8448 bytes in 3 calls to read from the port.
Here are some excerpts from my code:
Private Sub SetupVirtualSerialPort()
Dim portName As String = "COM" + (m_DeviceContext * -1).ToString
Const baud As Int32 = 9600 '7680000
Const parity As Parity = parity.None
[code]....
View 2 Replies
Mar 15, 2009
I am writing an app that reads from the com port, filters the data, then writes to the text box, then immediately starts over. This all takes place in an endless " do while" loop within the "start" button code. I also have a end button too which closes the com port and ends the program. Some other buttons are present also to change the filter settings.Problem is when I click start, data is logged, but none of the other buttons work. Also some of the text boxes are "see through" and dont look like thy have updated with the rest of the form.
View 9 Replies
