I have this program that uses OLEDB connection to import excel file to data table then use it in other functions.Now I have this question: What are the possible limitations of OLEDB when it reads the Excel file. The File is on xls format, and I want to know if there is such limitations(for example: It has limitation in reading value on a cell).
View 6 RepliesWe're converting a Classic ASP site to an ASP.NET site. One function was to upload a 'template' of data in CSV format for importing into the database. There were several different record types in there (the first field always indentifies the type of data).The task was to get the CSV into a DataTable so it could be validated (new project is to have MUCH better validation rules)
The code seemed pretty straightforward - watered down (taking out comments, Try/Catch, etc) it is as follows:
Dim da As New System.Data.OleDb.OleDbDataAdapter
Dim cn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDirectory & ";" & "Extended Properties=""Text;HDR=No;FMT=Delimited;""")
[code]....
The DataTable (dtData) is populated, but only starting with the second line of the CSV file DESPITE the fact that "HDR=No" is in the connection string.
How to read an Excel file without using OLEDB connection string?
View 2 RepliesHow can I read specific cell from Excel file using OLEDB Connection with VB.NET?
View 2 RepliesHow can we read an excel file without using Oledb?
View 3 RepliesFor my current Project I need to fetch the data from an excel file.Each second the data in the excel file is changing... What I want to do is, copy it from the excel and Validate it in every second.So I use an Oledb connection to get the data from Excel file... s working excellent for two hours... After 2 hours I am getting an error that"the connection for viewing your linked microsoft excel worksheet was lost......"
View 1 RepliesI'm trying to connect excel sheet with VB 2008 in my project, but it show a error message: A first chance exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll
[Code]...
How to write into excel sheet using oledb connection or how to export dataset into excel sheet using oledb connection.
Iam using console application in vb.net. If there are any alternate ways to write into the excel files from dataset.
A While back I developed a application that connected to Excel using DB connection. It worked fine since the application was designed for usage on one particular PC with Excel 2003. I especially liked the solution since the data retrieval was much faster compared to what I would get if I connected to Excel using Interop libraries.
However, recently when I tried to implement the same solution onto another app that would run on various PC that could use different versions of MS Office I ran into multiple problems. First of all, I can't use the same connection string on various versions. Also, the whole x64 vs x86 thing is adding to the problem.
I wonder if there is a way to check the version and use the apropriate connection string for every version?
I am trying to read some data from an Excel 8.0 worksheet using OLEDB thusly:(Warning - contains curly braces)
Code:
DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
using (DbConnection connection = factory.CreateConnection())
{
[code]....
The problem is that for some columns it sets the data type to be Double even though the spreadsheet column format is "Text" and teh column contains non-numeric data. Then when it gets to that cell it returns DBNull instead of the text value.
I am using asp.net oledb to export information to excel file. I encounter problems when the information to export becomes too big, in this case the code I have given below, the excel file generated becomes an empty spreadsheet.If I changed the loop to 1123 for insertion of the rows. The generated excel file is fine, 1125 rows, and 4 columns shown.A test program in windows form is also working fine regardless of how many rows.[code]I couldn't find a solution to my problem as well. What I did eventually was to run the process using another separate windows service. The code works perfectly fine running from a windows form or service program, but not asp.net, not sure why.
View 1 RepliesI am loading a considerable amount of data from SQL into Excel. For specific reasons, I need to use the Resize method with an array when loading data to Excel.I have found a condition where the size of a cell/row causes the Resize method to crash. I have not found any doco anywhere that shows a Resize limit. My array is OK. My code is OK. But, the data for one row is very large - one field that I am trying to load to a cell has 1097 characters. I have breakpointed the code to assure that this one is the culprit.[code]Does anyone know of any size limits when using Resize. The total number of bytes being written to the row is 1402.Anyone know limits on length for Resize or "overrides" to handle this?
View 1 RepliesMy program reads a 3rd party .txt file using OleDb and reads the file into a datatable. It's overall working fine except a user will occasionally have a problem reading a file. At this point.
Line that gets hung up
Try
Dim comm1 As New OleDbCommand("SELECT * FROM " & safeFileName & " Where " & Status & " = '" & StatusClosed & "'", con)
Dim dasold As New OleDbDataAdapter(comm1)
dasold.Fill(dtsold)
AddColsold()
[Code] .....
It can not retrieve a date for one of the fields, then gives error and does not get dates for each records after the one it gets hung up on but the date is in the file. If I open the file in excel and play with the column sizing, for instance just autoformat column width then it read the date and will work fine. I have looked at the file in notepad and cant see anything wrong in particular on the record it gets hung up on. I have no control over the .txt file since it comes form a 3rd party.
Im looking into learning OLEdb to run with Excel, Where do i start? has anyone got the full default sample code? and how do i need to set up the Excel test file. Sounds daft but im looking at the system namespace for it and its just looking bafling. What not to put on an Employee evaluation: This employee has hit rock bottom and shows signs of starting to dig.
View 8 RepliesIs there a way to handle quotes with a field in text tab delimited file example:"This program works "really" nicely and is helpful" my reading and understand thus far is that while the file is tab delimited fields can also begin and end with quotes. Therefore is a field contains a quote it gets interpreted as end of field.[code]
View 9 RepliesJust wondering if i might hit a snag in my program. Ive got 10+ users using the same files, via a Oledb to put info into an excel spreadsheet. If 2 or more people save there file at the same time will the program go into read only on one person. Normally one 3 will use the file at one time. I know Sql itself would be better to use. best way i can describe it, normally using excel if you manage to open the same file twice one opens as normal but the other opens as a Read only file, will the same thing happen if im using Oledb connections?
View 2 RepliesI have always used Excel.interop before but I thought oledb would be simpler because, for this application, I only have to read the data which it can't seem to do. It accesses the file alright because I can't open the file while I am at a breakpoint. It attempts to read the file because oExcelReader.fieldcount = 14 which is correct. oExcelReader.hasrows = true which is not correct. The line where I use getstring(1) errors out and says there is no data for this row/column.
[Code]...
I have some excel file, where I have to programatically delete all hidden columns.The problem if that, when I receive these files, the cells are mainly formulas, and as I delete some columns I get some "#REF#" problems.
I thought a bit about the problem an decided to create a function that loops throught all cells and replace the value by its own value, before deleting the columns, via OLEDB:
dt.Rows(row)(col) = dt.Rows(row)(col).ToString I don't know if this works. So I followed an update example I found in google, but it doesn't work:
Dim conexao_Excel As String = "Provider=Microsoft.Jet.OleDb.4.0;data source=" & fileName & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1;"""
cn = New OleDbConnection(conexao_Excel)
cn.Open()
da = New OleDbDataAdapter("SELECT * FROM [" & sheetname & "]", cn)
da.Fill(dt)
[Code]...
"Update requires a valid UpdateCommand when passed DataRow collection with modified rows." I image this is pretty basic, but I have already lost so much time on this issues...
Using OleDbDataReader, how does one determine if the Excel workbook is empty? BTW, OleDbDataReader.HasRows seems to always be True even for a workbook with no data.
View 1 RepliesI've done successful updates to Access databases with VB.net code using OleDb lines, with some sample code as follows:
Dim GRDatabase As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=|DataDirectory|OWVGuestRegister.mdb")
Dim GRCommand As OleDbCommand
GRDatabase.Open()
[Code] .....
How to update Excel and Text files the same way?
I am trying to connect DSN from my VB 2008 project. When i try using myoledb.connectionstring="DSN=myDNSname" I get the following error message An OLEDB provider was not specified in the connectionstring.
View 1 Repliesi want to ask what is the difference between oledb connection and odbc connection. I have a case, my office used ncomputing then i have to developed an application using sqlserver 2000 and vb.net 2005, i used oledb connection. When i try to implement it in ncomputing, why user must has admin access level and dbo level in sql server, it seems trouble when all users are admin. But there's a rumor that when using odbc connection, we dont need to give admin access level for users.es.
View 1 RepliesI want to read a tab delimited file so i set up a data reader with the following connection string: [Code] However when I do this it doesn't seem to delimiter at the tab characters (I cannot work out why it is breaking up the data where it is). the first few lines of the csv are in the attached "dbamstr.txt" - the select statement I am running is: "SELECT * FROM dbamstr.txt".
View 3 RepliesI'm using vs2010 and can't get this to work. The same code has worked in earlier versions. What has changed?
config file:
<appSettings>
<add key="ConnectionString" value="Data Source=MikeLaptop;Initial Catalog=AdventureWorksDW2008R2;Integrated Security=True"/>
</appSettings>
Code:
Dim strProvider As String = ConfigurationManager.AppSettings("ConnectionString")
strProvider is always = nothing
How to do this now?
I am trying to retrieve the data from EXCEL 2003 spreadsheet from row 8 onwards because from Row 1 to Row 7 the row is merged columns from A1 to K1, A2 to K2, A3 to K3, A4 to K4, A5 to K5, A6 to K6, A7 to K7.
View 28 RepliesI'm trying to read an Excel xls file into a dataset with OLE. I can do that, but it's loosing data. The sheet is formatted with merged cells on the first 11 rows and after that it's just a table. I need to read this with OLE, because the server on which this web-application is working does not have Excel installed.
If I remove the first 11 rows with excel, then save it and then let my program read it, all is fine, but with those rows, it's ok. The excel sheet has header names in row 12. After the read in to the dataset, some of those cells with the header names are empty. I assume this is caused by the formatting. Is there any other way to open and read an excel file on a server without Excel or expensive dll's? Is it possible to open the xls file with OLE, delete the first 11 rows before using da.fill(dataset,tablename)?
I am having problems reading the contents of an excel file into a combo box I am using the following code but the only thing that is populating the combo box is "System.__ComObject"
XLbook = GetObject(pthSup & "offices.xls")
XLsheet = XLbook.Sheets("Offices")
Dim i As Integer
[Code]....
I am relatively new Visual Basic and programming in general and I am trying to update a program I wrote for AutoCAD in VBA to the .NET frame work
just as I would read an SQL file in order with code such as:DCReader02 = DCCommand02.ExecuteReader()While DCReader02.Read() End While I am looking for the equivalent code to read each line of an Excel file until end of file. The Excel file was opened with
Microsoft.Office.Interop.Excel.Application / Workbook / Worksheet coding.
I want to get a value from 12 excel sheet. is there any way that i get the values without opening the excel sheet? I am using vb.net. if there is a way to read values without opening the excel file.
View 3 RepliesI am connecting to a data stream that pushes data continuously and when I tried to set up a network stream I just could not connect and kept getting the 'not found' error. I used the httpwebrequest with webrequest.keepalive = true and started to consume the data by reading into a buffer and then appending to a stringbuilder. Once the stringbuilder reaches max size I flush it to a text file. The problem is that I get disconnected from the data stream because I am not reading the data fast enough. I need to keep the connection open and read the data while ensuring data gets stored in text files. If I use method "CONNECT" it does not connect to the stream.
While (True)
webrequest = DirectCast(Net.WebRequest.Create(url), HttpWebRequest)
webrequest.Credentials = New System.Net.NetworkCredential(username, password)
webrequest.Method = "GET"
[code]....
should I increase the buffer size and if so what is the max? that would mean I can read more data at a time and keep up with incoming push. Disparate here.
ACtually also I noticed that it gets stuck on this line
numbytesread = responseStream.Read(bufferread, 0, BUFFER_SIZE)
and then eventually I get disconnected.