Using TransactionScope - Windows Service That Reads From An Excel Spreadsheet And Imports Records Into A SQL Server 2000 Database Table
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.
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
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]
Is it possible to deliver an Excel Spreadsheet via an Web Service. I believe not as ASP.NET web services seem only to deal in Serializable elements which the excel binary format isn't (I think). If the Excel is saved in XML format will this work?
I have a number of reports that will be emailed, but the request has been put into allow these reports to be pulled via Web Services.
I am working on moving applications from Windows 2000 to new server Windows 2003 R2(64 Bit). I noticed that there are some VB programs and config files for those programs have been placed in Windows 2000 Registry. Some other applications are using them from different servers.Why do we use registry here? How can i move these to registry in windows 2003? Can I just move these or do I have to write VB programs to place them in 2003 registry?
I have a windows service application that is meant to interact with SQL server database (INSERT, UPDATE, ETC). The windows service application is also multi-threaded.I created an "App_Data " folder to keep my database and used app.config file for connection information, etc.
After installing and starting the service, nothing happens, the database doesnt get updated, etc.
Has anyone ever written a windows service application that interacts with a database? Kindly advice me on how to overcome this problem..
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.
and this is the parameter that I am calling.For some reason this method does not work. I get an error saying that it expects a parameter @DELAY_CAUSE_1 which is not supplied. I check my procedure code and everything is matching up. Am I missing something here? Sorry that I haven't provided more into but the short question is how do I add the value of a combobox that has been selected? I just want to enter the number of the value selected into the table. But the combobox list will display the string.
The CLR has been unable to transition from COM context 0x1b3008 to COM context 0x1b3178 for 60 seconds. The thread that owns the destination context/apartment is most likely either doing a non pumping wait or processing a very long running operation without pumping Windows messages. This situation generally has a negative performance impact and may even lead to the application becoming non responsive or memory usage accumulating continually over time.To avoid this problem, all single threaded apartment (STA) threads should use pumping wait primitives (such as CoWaitForMultipleHandles) and routinely pump messages during lonunning operations.
Sub ExportToExcel07() Dim cnx As Data.SqlClient.SqlConnection = SqlCnx.SourceDB
I am just beginner in ASP.NET programming, I am trying to prepare a small billing project by using ASP.NET and VB.NET. I would like to create a SQL Server 2000 table by passing table name and number of rows in textbox control.How I can achieve this?
I am trying to export table data to an excel spreadsheet.It would be beneficial to have filtering pull-downs in the spreadsheet.I would be will to purchase any software tools to make this task easier. Otherwise are there some tutorials for accomplishing this?
How to create a pivot table on an Excel spreadsheet via code. The reason I took so long is because I added a reference to the Excel Interop 12.0, rather than the Excel Interop 11.0. I assume that 12.0 is for Excel 2007, while 11.0 if for 2003 (or whatever I have). If that assumption is correct, this will cause me a bit of trouble, as all the people using the program are in the process of moving from 2003 to 2007, and I should have moved myself, but didn't for some reason unknown to me (I thought the upgrade was pushed out to my computer when I was at work, but I don't seem to have it anymore). However, my concern is that the objects needed to create a pivot table in the 12.0 library are significantly different from the objects needed to create a pivot table in the 11.0 library.
I don't believe I can late bind to solve this, because there will need to be different steps taken depending on the version, since the objects have different interfaces. It appears to pertain only to pivot tables (for what I'm doing), as I was able to create the workbook, the worksheets, and export all my data with some old code that I had, and all of that worked with both libraries, the difference is just with the pivot tables because the interfaces have changed, such that the methods needed for one library don't even exist in the other one (and the code crashes, but that's probably because I have the PIAs for 2003 installed, and not 2007).
I am beginner in Visual Basic.net Currently I have been given a project to design a database for college in which record of all the students and college staff will be stored i have the interface in vb.net but now when i want to make a connection to sql server 2000 database i am sufering from many problems.
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.
I want to develop a small application that reads in scanned barcodes into a database table - Would a vb.net form based application that connects to the database be best approach? What I'm think is each time the user scans in the barcode - a message appears on the form saying the barcode that has been scanned.
1.Would I need to program anything within the program to check whether the barcode scanner is connected (or is this not necessary since it works like a keyboard device?)
2. How could I automate the scanning in without requiring any user intervention like a button which saves it to the database - so basically as soon as a barcode is scanned in - it is saved to the database and the next barcode can be scanned in etc.
When reading date fields, if the year is 1900 it subtracts 1 day from the value (1/1/1900 becomes 12/31/1899). Anyone else have this issue? If so, were you able to fix without coding for it?
I have a flow document table with text in it. The text some timess just on one line , often on many lines, that means it could include a return and a tab spacing (all in the same cell).I want to put the data from the table to the clipboard in such a way that i
I am using Visual Studio 2005. I am using a data reader to read an Excel spreadsheet and want to update a SQL Express table. The datatypes in this table mimic a software application I will eventually be passing data to and are listed in the comments in my code. No matter what I do or change, this will not run. I even populated my spreadsheet with zeros and this will not run. I have found many suggestions on the web and tried changing things but nothing seems to work. I also don't know specifically which variable is causing the error or the proper way to make sure there are no blanks in this spreadsheet.The error says "conversion from string "" to 'Double' is not valid Here is my code:
Public Sub btnUpload_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpload.Click 'Connect to the Excel spreadsheet Dim xConnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & txtFileName.Text & ";" & _
using a data reader to read an Excel spreadsheet and want to update a SQL Express table
I am having a problem with using the SQL Server 2008 FileStream function with an Excel 2007 spreadsheet. I can store an Excel 2003 format spreadsheet and bring it back with no problem. When I bring the Excel 2007 format spreadsheet back from the DB I get a message that there are errors in opening the Spreadsheet and the Excel App asks if I want them repaired before it is displayed. I answer yes and the Spreadsheet is renedered perfectly. Could this be a FileStream problem or an Excel 2007 problem?
I'm having problem with our germany customers whose operating system uses dot as thousand separator and comma as decimal point.I have an application which reads some text from an Access database, parse it into data and display on a datagridview,the program also needs to write the data into another file as input to another application. right now, it seems when my application parses doubles (say 0.2500),germany os will always return everything except the decimal dot(so 0.2500 becomes 2500),and that just messed up the whole process...so my question is, how can we detect the locale of the windows and parse the doubles correctly?
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.
I am new to VB.NET. How to import data from excel spread sheet into database (SQL Server Management Studio Express 2005) by clicking a button in windows form application using VB codes (visual studio 2010).
I'm trying to write data from an excel spreadsheet to an Access database. When i run my program a message appears saying that the correct number of records have been saved, however when i check my access database no records have been written to the database table.
The following is a copy of my code (sorry i've just edited it to get rid of the white space that was added when i included it here for some reason.The code for writing to database:
Public Class Form1 Dim connection As OleDbConnection Dim Command As OleDbCommand
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"""
I have a .net Windows Service developed in VB.net. I have a settings file in the root directory called Connections.XML and I am setting the basedirectory [code]...
When I schedule the service this is working absolutely fine in my Windows XP machine. But when I installed the same service in our development server (Windows 2003 64 bit Server) for some reason it is not able to locate this file.
I'm encountering a hang when the program tries to access the fruit database. I've already enabled network access MSDTC on both my development computer and the SQL Server server.
