Reading Excel files with Jet OLE DB Provider

TheMSsForum.com: The Microsoft Software Forum

The MSS Forum ‹ Ado
- Archive
  - Biz
  - MCSE
  - CRM
  - Drivers
  - Framework
  - ADO
  - ASP
  - Compact
  - Forms
  - Dotnet
  - C#
  - VB
  - FontpageGen
  - Excel
  - WorkSheet
  - Exchange
  - Setup
  - Fox
  - Fontpage
  - ASP
  - IIS
  - Entourage
  - Money
  - Messanger
  - PocketPC
  - Powerpoint
  - Project
  - Publisher
  - Excel
  - VB
  - Security
  - Portal
  - Services
  - SQLServerDev
  - SVCS
  - SQLServer
  - VB
  - VC
  - MFC
  - ExcelGen

- Previous
  - 1
    - Jet 4.0 OLE DB Connection String Reference Where can I find a reference for all the parameters that you can put in a connection string for the OLE DB Jet 4.0 provider? I've been looking all over and can't seem to find it. Thanks. - Kevin Tag: Reading Excel files with Jet OLE DB Provider Tag: 64361
  - 2
    - I have problems with DataSet I am using the DataSet and I have problems, when --> used a SqlDataReader dr ----> while(dr.Read()). With dataset ds ---> while(ds.?????). Thanks Tag: Reading Excel files with Jet OLE DB Provider Tag: 64359
  - 3
    - Cant search Date from mySQL. Hi, I search some record(s) according to specific date which is took from datetimepicker, but a dataset.table(tbname).count is zero. here is my code. I use ODBC.net and Option strict On dim seaSql as string seaSql = "Select * from Orders where JobDate = @tmpJdate " DBCom.CommandText = seaSql DBCom.CommandType = CommandType.Text DBCom.Parameters.Add("@tmpJDate", OdbcType.Date) DBCom.Parameters("@tmpJDate").Value = seaJobDate.Value.Year.ToString & "-" & _ seaJobDate.Value.Month.ToString & "-" & seaJobDate.Value.Day.ToString) 'because mySQL date Vale = YYYY-MM-DD DBAdapter.SelectCommand = DBCom DBAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey DBAdapter.Fill(seaJobSet, "Orders") IS the code got any mistake?? I try the date value is "'" & "2003-9-15" & "'" or "2003-9-15", but it also cant work. Thanks KWOK Tag: Reading Excel files with Jet OLE DB Provider Tag: 64358
  - 4
    - Access memo field problem Hi, I am trying to retrieve access memo field's data using ADO.NET, but every method described in the SDK fails to work (I tried retrieve the field using OleDbDataReader methods GetValue, GetValues and GetChars). The problem is in the size of the field. I can successfully retrieve strings which are less than 255 characters, but not more. The content of the field is always truncated. I am using .NET Framework version 1.1.4322.573, MDAC 2.8, Access 2000 Format Database (created in Access XP) and Visual Studio .NET 2003 EA. What am I doing wrong? Any answer would be really appreciated, Honza Ambroz. Tag: Reading Excel files with Jet OLE DB Provider Tag: 64355
  - 5
    - Combobox and databinding Hi, I've bound a dataset to the textbox. SerieTextBox.DataBindings.Clear(); SerieTextBox.DataBindings.Add("Text", CertainSerieDataSet.Tables[0], "Serie"); When I change the text in the textbox, the text in the dataset also changes, but the rowstate remains unchanged. How is this possible? Thanks for the answers, Filip Tag: Reading Excel files with Jet OLE DB Provider Tag: 64353
  - 6
    - beginner#vb.net using ado.net dear respected sir, i'm beginner in vb.net and ado.net enviornment... i would like to work and learn business(database applicatiosn area), please can any one help me solve this... i wanted to make very simple windows forms,then by programming want to create query, connection, datasets, dataadapter,databinding for using oledb but i am facing a lots problem in syntax i am stuck/dead imports system.data imports system.data.oledb private sub form1_load(......some val,ref..etc) dim cn as new oledb.oledbconnection("provider=microsoft.jet.oledb.4.0;date source=c:\northwind.mdb") cn.open dim da as new dataadapter("select * from customers",cn) dim ds as new datasets() da.fill(ds,"cutomers") textbox1.text = textbox1.databinding.add(ds,"companyname") end sub and i have some buttons add, edit, delete, update... so i can not success above...can any one help me to tell in very simple form and how to update actual datbase...in fact by using dataset it is update modify data...using datagrids...but failure in case of indvidual records on the forms...and plus only update at datasets time...not actual when i close form and open again..then it give me same no of old records... please can any one give some guidence appreciated sir urs obediently shahzad Tag: Reading Excel files with Jet OLE DB Provider Tag: 64346
  - 7
    - SqlDataReader C# Index (Item Property) I have a question about what the SqlDataReader index value returns exactly (in VB SqlDataReader.Item("Value")). According to the .Net SDK this property returns: Overloaded. Gets the value of a column in its native format. I translated this as this property returns the value as it's type, so an int type in my sql database would return as an int? If this is true then I'm guess that the following code *should* run with out error. Assume current is an sql feild of type int: if ((int) this.Reader["Current"] == 0) { table += "..."; } else { table += "..."; } Are my assumptions correct? Or am I about to run into some trouble. Thanks for clarification! Carlo Tag: Reading Excel files with Jet OLE DB Provider Tag: 64342
  - 8
    - Number of concurrent users Hi Is there a reliable way to place a 'number of concurrent users' limit in vb.net applications? Thanks Regards Tag: Reading Excel files with Jet OLE DB Provider Tag: 64340
  - 9
    - Feedback on an article I've been working with two of my co-workers on developing a .NET content site. Anyway, writing an article is a little more difficult than I thought. How do you make sure you have the important info without boring non-beginners. How do you write important stuff without turning off beginners? Well, I'm posting a sample link of one of my first articles... and would appreciate any feedback, be it techincal, grammatical or anything else. I'm really interested in writing, but it's not been my strength (I've had enough trouble remembering to comment my code and use non-cryptic variable names ;-) ) Anyway, we have a few other articles (about 20 more on the way) but we really want to make something useful (like www.eggheadcafe.com or c-sharpcorner.com ) Any and all feedback is appreciated. http://www.knowdotnet.com/testsite/ Thanks again, Bill Tag: Reading Excel files with Jet OLE DB Provider Tag: 64339
  - 10
    - Determine whether a query returns results or not (before running it) I'm writing a web-based "Query analyser" tool for our company intranet. It allows a user to type any SQL statement in a form, and execute it over the Web. The SQL can be a query that returns results (e.g: SELECT * FROM members) or it can be a T-SQL (e.g: UPDATE/DELETE/INSERT). What I want to know is, is there any way using either SQLCommand or DataSet or any of the other ADO.NET classes to determine what kind of query it is, BEFORE running it? Then, if it is a T-SQL, I can just use the ExecuteNonQuery method, and I can hide my datagrid that would contain the results. Or if it is a "SELECT" query, I can populate a DataSet and show the results in a grid. Do I have to manually parse out the SQL string to determine if it is SELECT query? Or is there a more elegant way ? Thanks! Tag: Reading Excel files with Jet OLE DB Provider Tag: 64330
  - 11
    - Sorting a DataSet with related tables I have a DataList control bound to a DataSet containing two tables. How do I sort the parent table without losing my relation? If I bind the DataList to a DataView, it's no longer relational data. I'm guessing this has something to do with the DataViewManager but there is little info in help on this. -- Paul Turley, MCSD.Net, MCAD, MCT MSF Practitioner, A+ paul@createsolutions.ne_ (fix address to send mail) Tag: Reading Excel files with Jet OLE DB Provider Tag: 64327
  - 12
    - Retrieving available OLEDB Providers and settings programmatically Hi there, (slightly off topic) is it possible to retrieve the installed OLEDB providers and the settings they require in a connection string programmatically, by means of the OLEDB API? I want to provide something similar to the DataLinks-dialog to end-users, but in a web browser, configuring data links for a web server. Your feedback is much appreciated, thank you. Philipp Tag: Reading Excel files with Jet OLE DB Provider Tag: 64324
  - 13
    - OleDb DataAdapter Fill puzzle Hi, Working with ADO.Net/OleDb and Access2000. Using dataAdapter1 to update Access table field and dataAdapter2 to display the value from the same Access data source. Problem is, although dataAdapter1 is updating the Access table (verified in Access), subsequent calls to the dataAdapter2 Fill method do not refresh from the Access source - the value coming out of the dataAdapter2 dataset is always the same value - the one initially read on application startup. According to the documentation the Fill method should refresh the dataset from the data source - but does it? Tag: Reading Excel files with Jet OLE DB Provider Tag: 64323
  - 14
    - Passing datetime as parameter throws Exception I have data field MyTime of type Date/Time in MS Access table Test. I want to add record into this table with value passed as parameter. I do the following in C#: OleDbCommand cmd = new OleDbCommand(); cmd.Connection = MyConnection; cmd.CommandText = "INSERT INTO Test (MyTime) VALUES(?);"; cmd.Parameters.Add("MyTime", DateTime.Now); cmd.ExecuteNonQuery(); Last statement throws an exception with message "Data type mismatch in criteria expression." If I pass only date part to parameter, for example: cmd.Parameters.Add("MyTime", DateTime.Today); it works fine. How can I create and use parameter that wold pass both date and time parts to date/time field? Tag: Reading Excel files with Jet OLE DB Provider Tag: 64321
  - 15
    - ADO.NET Performance Tuning Hello, I have used ADO.NET to retrieve records (about 700 rows) from SQL2000 server and found that the performance (speed) is not good as ADO object. (Same Select statement applied) I would like to askwhat setting, in the DB side or client sides,I should aware so that I can improve the performance? Thanks Douglas Tag: Reading Excel files with Jet OLE DB Provider Tag: 64309
  - 16
    - MSDE in install Hi How to include MSDE into install and during install create some tables so program and database server are ready to work? Thanks Konrad Tag: Reading Excel files with Jet OLE DB Provider Tag: 64308
  - 17
    - GetXML doesn't show Computed Columns Anyone know if this is the way this is supposed to be working or any potential workarounds? I have a DateTime column 'DateExpires' which I am returning as a computed column via my SELECT statement. When I call GetXML() on the DataSet, it isn't returning the computed column. However, when I do a quickwatch on the DS, I see that the column is in the dataset. Also, I have a bound webform control which is getting populated with the value from this column in the dataset. strCmd = "SELECT ID, CAST((DATEPART(m, DateExpires)) as varchar(2))+ '/' + CAST((DATEPART(d, DateExpires)) as varchar(2))+ '/' + CAST((DATEPART(yy, DateExpires)) as varchar(4)) as DateExpires FROM Articles" sqlDA.SelectCommand = new SqlCommand(strCmd, sqlCN); sqlDA.Fill(objDS); objDS.GetXML() returns: <DSArticles xmlns="http://tempuri.org/Schema_Articles.xsd" /> <NewDataSet> <Articles> <ID>e75baa38-f084-472e-a14f-2517f00f3d67</ID> </Articles> </NewDataSet> XSD as follows: <?xml version="1.0" encoding="utf-8"?> <xs:schema id="Schema_Articles" targetNamespace="http://tempuri.org/Schema_Articles.xsd" elementFormDefault="qualified" xmlns="http://tempuri.org/Schema_Articles.xsd" xmlns:mstns="http://tempuri.org/Schema_Articles.xsd" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"> <xs:element name="Document"> <xs:complexType> <xs:choice maxOccurs="unbounded"> <xs:element name="Articles"> <xs:complexType> <xs:sequence> <xs:element name="ID" msdata:DataType="System.Guid, mscorlib, Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" type="xs:string" /> <xs:element name="DateExpires" type="xs:string" minOccurs="0" /> </xs:sequence> </xs:complexType> </xs:element> </xs:choice> </xs:complexType> <xs:unique name="DocumentKey1" msdata:PrimaryKey="true"> <xs:selector xpath=".//mstns:Articles" /> <xs:field xpath="mstns:ID" /> </xs:unique> </xs:element> </xs:schema> Tag: Reading Excel files with Jet OLE DB Provider Tag: 64303
  - 18
    - ANN: September 2, "ADO.NET with Visual Basic .NET"" chat Every application uses data. Come and ask members of the Visual Studio and SQL teams about the best approaches for working with data in your applications. Date: September 2, 2003 Time: 1:00 - 2:00 P.M. Pacific time 4:00 - 5:00 P.M. Eastern time 20:00 - 21:00 GMT 21:00 - 22:00 BST (For a list of local time zones relative to GMT, please see http://msdn.microsoft.com/chats/timezones.asp.) Location: http://msdn.microsoft.com/chats (then click the name of the chat to enter the chat room) To see a list of upcoming chats or set a reminder for this chat, please see http://msdn.microsoft.com/chats. You can use the following link to add an event reminder to your calendar for this chat. http://msdn.microsoft.com/chats/outlook_reminders/VB_Sept2.vcs To see an archive of previous chat transcripts, please see http://msdn.microsoft.com/chats/recent.asp. Thanks! Jason Cooke VB.NET Team ======== This posting is provided "AS IS" with no warranties, and confers no rights. You assume all risk for your use. (c) 2003 Microsoft Corporation. All rights reserved. Tag: Reading Excel files with Jet OLE DB Provider Tag: 64301
  - 19
    - network packet size I recently switched my production db from one server to another. My app connects with this connection string ("data source=servername;initial catalog=dbname;integrated security=SSPI;packet size=8192") to the old server fine, but will not connect to the new server unless I decrease the packet size from 8192 to 4096. I ran sp_config on both machines and the network packet size is the same for each server. Any ideas or areas I could check? I guess it's not that big of a problem, but I would like to know why. Thanks Tag: Reading Excel files with Jet OLE DB Provider Tag: 64291
  - 20
    - DataRowChangeEventHandler Issue I have written a DataRowChangeEventHandler to act as a "security" filter based on the current users "role". The event handler needs to be able to reject/cancel Inserts to the ADO.NET DataTable (these inserts are the results of a Select against the dataSource using the Fill method on a SqlDataAdapter). I've tried throwing an exception inside the handler, which doesn't appear to do anything. I've also tried calling RejectChanges on the row. This seems to work, but also seems to leave an invalid row in the table in a "Detached" state, which causes things like AcceptChanges to blow up later on. I suspect I'm just going about this the wrong way, but haven't seen an example anywhere of how to prevent a Row from being added to a DataTable. My code is shown below -- does anyone know what the "right" way is to do this? Thanks in advance, Jeff private void FrameworkRowAddedSecurityMonitor(object sender, IFrameworkTableSecurityEvent e) { if (e.Action == DataRowAction.Add) { if (e.SecurityObject.DataType != null) { if ((e.SecurityObject.AccessMask & ContentManager.FW_ACL_PERMISSION_READ) == 0) { // Option 1 DataRow row = (DataRow) e.SecurityObject; row.RejectChanges(); // Option 2 throw (new ContentManagerSecurityException("Access Denied",e.SecurityObject,ContentManager.FW_ACL_PERMISSION_R EAD,e.SecurityObject.AccessMask)); } } } } Tag: Reading Excel files with Jet OLE DB Provider Tag: 64288
  - 21
    - Oracle CLOB parameter limit? Hey all, I'm getting the following error when calling an Oracle9i proc from a .net 1.1 app, passing a clob parameter that is larger than ~32,000 characters: ORA-01460: unimplemented or unreasonable conversion requested. Is there a parameter size restriction for oracle procs? How do I pass in a large clob to the database? I'm using the MS managed provider for oracle, and the oracle9i drivers.... TIA, Andrew Tag: Reading Excel files with Jet OLE DB Provider Tag: 64287
  - 22
    - Cannot execute DTS package from ASP.Net Hello: I've got another problem executing DTS package from ASP.Net. I use this code: Dim oPkg As DTS.Package oPkg = CreateObject("DTS.Package") oPkg.LoadFromSQLServer("WIN2000", "UID", "password", DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, , , , "Import_Sales_History") oPkg.Execute() oPkg.UnInitialize() oPkg = Nothing I used this code before, and it always worked. Now it doesn't. The problem is that I'm not receiving any error messages, so I cannot find out what's wrong. When I step through the code I don't see anything unusual. But in fact it doesn't do anything, no data has been imported. When I execute the package manually - everything is OK. What could be a problem? I would appreciate your help. Thank you, -- Peter Afonin Tag: Reading Excel files with Jet OLE DB Provider Tag: 64285
  - 23
    - Problem updating existing typed dataset from Generate Dataset designer Hi, I'm trying to update an existing Typed dataset in a VB Web project using the "Generate Dataset" designer from a SqlDataAdapter. When I try to add new tables or updated fields, the wizard closes like it does for a new typed dataset, but the selected dataset schema does not change. What I've been doing as a workaround is to just delete the existing ds and creating a new one with the same name. Is there a fix out there or do I just have something setup wrong? I'm using: Visual Studio 2002 VB - Web forms SqlDataAdapters - SQL Server 2000 .NET Framework 1.0 Thanks in advance, Eric Tag: Reading Excel files with Jet OLE DB Provider Tag: 64278
  - 24
    - Change Winform Query Question I had an experience this morning that I don't quite understand with VS 2003. I have a Winform with the underlying data coming from three different tables. This data for the Winform is obtained by use of a dataset. The form has tabs. Data in the Contracts table goes to the Contracts tab, data in the Students tables goes to the Students tab etc. For each of these tables I have a SQLDataAdapter in the component tray of the Winform. DaContracts, DaStudents, & DaArd. When I generate a Dataset for each of these 3 data adapters I point to a DataSet I call DSMain (DsMain1). That is, DSMain has schema involving all there of these tables and they are (in the schema) related together in the proper way and in the same way they are related in the underlying SQL 2000 tables. Sorry to be too wordy. Don't know how to explain the problem, however, without saying at least that much. It was necessary for me to add some fields to the main table (contracts). These fields were not selected in the original query that produced the contracts table in the DataSet Schema. I added the fields in the query of the DaContracts DataAdapter and re-generated the dataset for that adapter. I then tried to run the form and got back an error message of "Could not create a child list for fields DrContractsArd". This is the data relations name for the Contracts and Ard tables. I horsed around with it and finnaly got the form back up and running as before but for future reference, I would like to understand what happend. I think the moral of the story is that if you add fields or change things in the future with regard to one table in a dataset involving more than one table, it is not enough to re-generate the dataset for just that table, you must regenerate the dataset for all the tables. Is this right or are there other comments or items that others would like to bring to my attention? I am new to VS and may have made an incorrect assumption in tying these three tables together in one dataset. Maybe each table should have a dataset of it's own. I don't know. I did it this way in the first place for reasons I don't remember other than, it worked. Tag: Reading Excel files with Jet OLE DB Provider Tag: 64263
  - 25
    - Cannot delete last row in a datagrid Hi all. I have a datagrid bound to a dataset/datatable. Obviously I created a BindingManager and a CommandBuilder and all runs well when I ADD new rows or DELETE existent rows, saving all back to the database. Here is the code associated to a button, I use for "deleting" a row: ---------------------------------------------------- Dim dr As DataRow dr = ds.Tables("checkout").Rows(bm.Position) dr.Delete() MsgBox(ds.Tables("checkout").Rows(bm.Position).RowState) If ds.Tables("checkout").Rows(bm.Position).RowState = DataRowState.Deleted Then MsgBox("row deleted") da.Update(ds, "checkout") End If ------------------------------------------------------ As you can see I found the row first and the "delete" it (the RowState is only for debugging purpose :-) Well I CAN delete all rows in the datagrid (clicking on the row and then clicking on the Delete Button), obtaining a DataRowState.Deleted value (as expected). But the problem is when I try to delete the last row in the grid: the row is deleted from the grid, but no changes are made to the RowState (obtaining a Unchanged value). So no Update is possibile on that row. I can't understand why! Have you any idea? Many thanks. -- Francesco De Carlo Tag: Reading Excel files with Jet OLE DB Provider Tag: 64259
- Next
  - 1
    - Lhotka's VB.Net Business Objects Has anyone tried implementing Rockford Lhotka's CSLA framework from his book Expert One on One Visual Basic .Net Business Objects? The reason I pose this question here is 2-fold. 1. He basically does away with most of ADO.net, other than the DataReader to read a row in order to stuff the values into Properties of objects, which are then bound to UI controls. To update the database, the values from the Properties are used to generate a SQL update statement. It certainly seems an odd way of doing things - albeit in an OOP way. 2. Having spent 3 weeks trying to work through the book and make it work, I can't seem to be getting anywhere. His downloaded source code refuses to work - even after some of the more obvious problems are fixed (like references to a Strongname Key file stored on his own h:\ drive in some of the Assembly Info files). The sample Project Tracker keeps quiting with a System error. I am very intrigued with his framework, and I think Mr Lhotka is one of the best technical writers around. I find myself stuck in the mud and would like to know if anyone out there has been able to make this thing work. Thanks Tag: Reading Excel files with Jet OLE DB Provider Tag: 64256
  - 2
    - Class Reference Sorry for crossposting! Is there a complete reference of .NET Framework class libraries? And i mean COMPLETE reference with full explanation of what each class does and perhaps what it's good for. Reason for this is that recently i spent 2.5 days coding a class only to find out that Framework already supplied me with a similar class (the same functionality), only i never knew about it :-( So i would like to know when i must code my own functionality solutions and when there's something in .NET that i can use. Btw don't point me to MSDN cause i have latest library and i'm aware of the library online, but they only give a very little information about classes in Framework, and almost no examples. Thanx in advance Tag: Reading Excel files with Jet OLE DB Provider Tag: 64243
  - 3
    - Urgent Help Needed !! FTP Files from Webserver to Client PC Hello, I need to download files from the web server to the PCs without the "Save As" Dialog Box using ASP.NET . Pls advise if there are any components available for any help is appreciated. Thank You EJ Tag: Reading Excel files with Jet OLE DB Provider Tag: 64237
  - 4
    - DB optimisation vs OOP layer separation Hi, I'm facing a code-optimisation issue on an asp.net/vb.net/SQL Server 2000 project. A web page containing not much more than 3 DropDownLists is taking nigh on 6 seconds to load, because each ddl opens up a separate connection to the DB, pulls out its data, and closes its own connection before the next ddl repeats the process. The code to handle each DDL's connection to the DB is packaged in an object (presentation-layer code below); two of the hits go via a Person object, and one via a User object. Both these objects are separated into a business layer and a data layer. My optimisation goal is to have all these three DB hits done on just one DB connection. The only way I can think of doing this would be: * create a connection object in the presentation layer (more likely, an SqlCommand object which contains an open connection), * pass this open connection to Person.GetAllActiveNonIntroducersOrderBy("LastName", "ASC") * the presentation layer gets the open connection back from Person.GetAllActiveNonIntroducersOrderBy("LastName", "ASC"), laden with data * the pres layer takes out the data, fills up DDL1 with it, and passes the still-open-connection to Wec.User.GetAllOrderBy("LastName", "DESC") * the open connection comes back once more to the presentation layer, laden with the second batch of data * DDL2 is filled up with this data, and the process repeats for DDL3 * once the pres layer has got all the necessary data it kills the connection I dare say I could adapt the various Business and Data Layer routines to handle this. My main worry is about violating the OOP principle of layer separation - if I'm going to start creating SqlConnections / SqlCommands in my *presentation layer*, I might as well slit my OOP wrists right now.... Any input welcome! TIA, JON 'DDL1 ddlIntroducerNames.DataSource = Person.GetAllActiveNonIntroducersOrderBy("LastName", "ASC") ddlIntroducerNames.DataTextField = "WholeName" ddlIntroducerNames.DataValueField = "PersonID" ddlIntroducerNames.DataBind() 'DDL2 ddlWECReps.DataSource = Wec.User.GetAllOrderBy("LastName", "DESC") ddlWECReps.DataTextField = "WholeName" ddlWECReps.DataValueField = "UserID" ddlWECReps.DataBind() 'DDL3 ddlIntroducedBy.DataSource = Person.GetAllActivesOrderBy("LastName", "ASC") ddlIntroducedBy.DataTextField = "WholeName" ddlIntroducedBy.DataValueField = "PersonID" ddlIntroducedBy.DataBind() Tag: Reading Excel files with Jet OLE DB Provider Tag: 64232
  - 5
    - How do I insert into an Excel spreadsheet? I am using Excel as a small database to store settings of a comm program I am writing. While I can read from excel fine, I am stuck on how to write any changes back to the database. I have been trying to use an INSERT INTO but I get the following error: Operation must use an updateable query I am really new with using ADO.Net and this is my first application trying to use Excel as a datasource. Any guidance or suggestions on how to go about fixing my error would be appreciated. The code I am using is as follows: 'clear the dataset dsSettings.Tables("Settings").Clear() 'insert a new row into the datatable Dim dtSettings As DataTable = dsSettings.Tables("Settings") Dim drNewRow As DataRow = dtSettings.NewRow drNewRow.Item("Baudrate") = sBaudRate drNewRow.Item("Parity") = sParity drNewRow.Item("Handshake") = sHandShake drNewRow.Item("Databits") = sDataBits drNewRow.Item("Stopbits") = sStopBits 'add new row to Settings table dtSettings.Rows.Add(drNewRow) Dim sSQL As String sSQL = "INSERT INTO [Settings$] " sSQL = sSQL & "(Baudrate," sSQL = sSQL & "Parity," sSQL = sSQL & "Handshake," sSQL = sSQL & "Databits," sSQL = sSQL & "Stopbits) " sSQL = sSQL & "VALUES (" sSQL = sSQL & "@Baudrate," sSQL = sSQL & "@Parity," sSQL = sSQL & "@Handshake," sSQL = sSQL & "@Databits," sSQL = sSQL & "@Stopbits)" Dim mExcelConnection As OleDb.OleDbConnection = New OleDb.OleDbConnection mExcelConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sAppPath & "Settings.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""" Dim mExcelAdapter As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter mExcelAdapter.InsertCommand = New OleDb.OleDbCommand(sSQL, mExcelConnection) mExcelAdapter.InsertCommand.Parameters.Add("@Baudrate", System.Data.OleDb.OleDbType.VarChar, 10, "Baudrate") mExcelAdapter.InsertCommand.Parameters.Add("@Parity", System.Data.OleDb.OleDbType.VarChar, 10, "Parity") mExcelAdapter.InsertCommand.Parameters.Add("@Handshake", System.Data.OleDb.OleDbType.VarChar, 10, "Handshake") mExcelAdapter.InsertCommand.Parameters.Add("@Databits", System.Data.OleDb.OleDbType.VarChar, 10, "Databits") mExcelAdapter.InsertCommand.Parameters.Add("@Stopbits", System.Data.OleDb.OleDbType.VarChar, 10, "Stopbits") mExcelConnection.Open() mExcelAdapter.Update(dsSettings, "Settings") mExcelConnection.Close() Tag: Reading Excel files with Jet OLE DB Provider Tag: 64226
  - 6
    - MS Oracle ADO.NET provider bug 2? The following code returns an InvalidOperationException syntax error when stepping over the da.FillSchema statement : Syntax Error: Expecting ',' or 'FROM' after column alias or '*'. The same code works fine using either the MS OleDb data provider or Oracle's native provider. If I add parentheses around BUDGET*5, then the code executes as expected. string connStr = "Data Source=myserv;User ID=scott;Password=tiger;"; OracleConnection conn = new System.Data.OracleClient.OracleConnection(connStr); string sql = "SELECT BUDGET*5 FROM PROJ WHERE PNUM='P1'"; OracleCommand cmd = new OracleCommand(sql); cmd.Connection = conn; conn.Open(); try { DataSet ds = new DataSet(); OracleDataAdapter da = new OracleDataAdapter(sql, conn); da.SelectCommand = cmd; da.FillSchema(ds, SchemaType.Source); ds.EnforceConstraints = false; da.Fill(ds); } catch (Exception Ex) { MessageBox.Show(Ex.Message); } conn.Close(); Tag: Reading Excel files with Jet OLE DB Provider Tag: 64216
  - 7
    - OleDb .NET provider bug with Oracle? I appear to have hit a couple of bugs in the OleDb .NET data provider, working with Oracle 9.2.0 on a Windows 2000 client. I'm not sure whether this is the correct forum to report this? Whitespace after the DISTINCT clause appears to get ignored, causing an exception. With the code below, on stepping over the da.fill statement, I get this exception error: ORA-00904: "DISTINCTcol1": invalid identifier Using the Microsoft ADO.NET provider for Oracle (or Oracle's native provider), the SQL statement is executed correctly. I'm not sure if this has been reported before. Is there a fix available to correct this? Simon. string connStr = "Provider=OraOLEDB.Oracle.1;Persist Security Info=False;Data Source=myserv;User Id=scott;Password=tiger;"; OleDbConnection conn = new OleDbConnection(connStr); string sql = "SELECT SUM(DISTINCT col1) FROM mytable"; OleDbCommand cmd = new OleDbCommand(sql); cmd.Connection = conn; conn.Open(); try { DataSet ds = new DataSet(); OleDbDataAdapter da = new OleDbDataAdapter(sql, conn); da.SelectCommand = cmd; da.FillSchema(ds, SchemaType.Source); ds.EnforceConstraints = false; da.Fill(ds); } catch (Exception Ex) { MessageBox.Show(Ex.Message); } conn.Close(); Tag: Reading Excel files with Jet OLE DB Provider Tag: 64210
  - 8
    - file download - using response.writefile() Hi, I have ASP.NET page that would like to download a list (many) pdf files. I intend to down the file into the browser C:\ root drive without prompting the user to "SAVE AS" dialog box. Would appreciate some help on this (maybe a code snipnet). Thanks EJ Chew Tag: Reading Excel files with Jet OLE DB Provider Tag: 64205
  - 9
    - DataRepeater for windows forms Hi, I've sen that VB6 has a datarepeater and that ASP.NET has something like that. But is there something like that for windows forms, or has someone something like that? Thanks Filip Tag: Reading Excel files with Jet OLE DB Provider Tag: 64204
  - 10
    - ADO.NET in C# - statement problem, please help? I've been stuck with this stupid problem for the past two days.. I give up now, please help me? Here's the code : OleDbCommand updatedb = conn.CreateCommand(); updatedb.CommandText = "INSERT INTO maintable (name, number) VALUES ('John Smith', '01215489746')"; updatedb.ExecuteNonQuery(); When I release/debug my app in VS.NET I get a succeeded build. However when I want to run the app I get on the screen : " OleDbException : Syntax Error in INSERT INTO statement. " Obviously there's something wrong with the SQL command I'm writing, but what? I really don't get it and I've honestly tried everything. For the info, I'm trying to connect to an Access DB. Please share you knowledge with me.. thanks so much! By the way, if anyone's got a good website with an ADO.NET tutorial (for beginners:P) please let me know.. thanks jez Tag: Reading Excel files with Jet OLE DB Provider Tag: 64200
  - 11
    - datareaders and GetDirectory() Hi all, in my quest to implement my own data reader, i've been delving deeper into the inner workings of data readers and am now stuck on the GetDirectory() method. I know that this method is an internal method of the data reader class. It takes a string that is used to create a new DirectoryInfo object: internal void GetDirectory(String command) { _dir = new DirectoryInfo(command); _fsi = _dir.GetFileSystemInfos(); _RecordsAffected = _fsi.Length; _CurrentRow = -1; _ie = _fsi.GetEnumerator(); _isClosed = false; } I also know that it is used when ExecuteNonQuery() or ExecuteReader() are called. When either of these methods are called this is what happens (as i understand it): a DataReader is instantiated, its internal GetDirectory() method is called which returns either: - the number of directory entries returned (i.e. the number of rows affected) - or a DataReader over the set of entries (by getting an enumerator over the directory entries) The code above seems to suggest the CommandText of the Command being executed is passed to GetDirectory(). But the constructor for DirectoryInfo expects a string that represents the path of a directory. This is where i'm stuck...what should be passed to GetDirectory()? The code above and the sketchy info i possess on all of this was gleaned from : http://msdn.microsoft.com/msdnmag/issues/01/12/dataprov/ (Under the "Implementing DataReader" section. The code is part of Figure 8.) Tag: Reading Excel files with Jet OLE DB Provider Tag: 64196
  - 12
    - [Q] Reading BOOLEAN return value from Oracle function Hello, Can anyone tell me how to retrieve a boolean return value from an Oracle Function, using MS .Net Framework Data Provider for Oracle? In the documentation there's a "Mapping Data Types" table that shows which Oracle data types correspond to which .Net types (OracleType.xxx), but Boolean is omitted... My code is like this: ... other parameters... oracleCommand.Parameters.Add("O_retval",OracleType.XXXXX).Direction = ParameterDirection.ReturnValue; .... what do use in place of the XXXXX when the Oracle function returns a BOOLEAN? Any help would be greatly apreciated. Thanks in advance, Joao Martins -- João Pedro Martins jota@create.pt www.create.pt www.usabilidade.com Tag: Reading Excel files with Jet OLE DB Provider Tag: 64186
  - 13
    - Trusted connection doesn't work with SQL server and ASP.Net Hello: I need to execute a DTS package from an ASP.Net application. The SQL server registration properties are set to "Use Windows authentication". domain\ASPNET is among the SQL server users. However, when I use this string to load DTS package: oPkg.LoadFromSQLServer("WIN2000", , , DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection, , , , "Import_Sales_History") I get this error: "System.Runtime.InteropServices.COMException (0x80004005): Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. at DTS._Package.LoadFromSQLServer(String ServerName, String ServerUserName, String ServerPassword, DTSSQLServerStorageFlags Flags, String PackagePassword, String PackageGuid, String PackageVersionGuid, String PackageName, Object& pVarPersistStgOfHost) If I specify UID and password: oPkg.LoadFromSQLServer("WIN2000", "UID", "password", DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, , , , "Import_Sales_History") everything works OK. Why the trusted connection doesn't work in my case, despite that the SQL server properties are set to use it? I would appreciate your help. Thank you, -- Peter Afonin Tag: Reading Excel files with Jet OLE DB Provider Tag: 64184
  - 14
    - SQL Indexes I have a question regarding indexes. This may be a SQL newsgroup question. If it is I apologize. I actually posted it on both just in case. If I create an index on a table. Will this index be checked through when I execute a select statement from VB.NET or is this index only used through strored procs within sql. Tag: Reading Excel files with Jet OLE DB Provider Tag: 64182
  - 15
    - Saving Times in .Net I'm trying to save a time (08:01 for example) to a Data/Time field (formatted Short Time) in an Access table using VB.NET. The problem is that even when I write this time to a dataset, it winds up saving as #08/28/2003 08:01 AM# in the database, where the date is today's date. In plain ol' VB6, I am able to write 08:01 to the database with no date. Is there any way to write the time only in VB.NET? Tag: Reading Excel files with Jet OLE DB Provider Tag: 64178
  - 16
    - Sorting Datatable I have a datatable in which the adapter has not been updated (and I dont intend to) and I want to stop after adding the rows to the datatable and sort them by lets say the SLSM column DESC. Usually I would when calling data sorted in a SQL server table would use a select statement but how would I do this when all it is is a dataset with all the rows added? If I have to I will update the data adapter and add all the rows to the datatable but I would rather not do that if I do not have to. Thanks for your help...... Me.Dshistory1.HISTORY.Rows.Add(drnew)'row dshistory1.acceptchanges End If Loop 'now sort this dataset(datatable) as I get ready to do actions Tag: Reading Excel files with Jet OLE DB Provider Tag: 64169
  - 17
    - TSEQUAL doesn't work with ADO? Hi all, I use the TSEQUAL function to check the equality of timestamp columns to perform optimistic concurrency. I know TSEQUAL is undocumented, but it usually works well. Unfortunately, when I use sps that use TSEQUAL, they do not throw errors when the timestamp values are different. Instead, it simply writes over the existing row. I have verified that TSEQUAL works correctly within the sp by running tests in Query Analyzer. The only difference between my test scenario and live scenario is that in the live scenario the two updates take place on different connections. I made sure ,however, that the changes were commited before attempting to update from another connection. Does anyone have any experience with this? Cole Tag: Reading Excel files with Jet OLE DB Provider Tag: 64167
  - 18
    - Using ADO.NET to connect to a CSV File, Need Help Using an ODBCConnection and ODBCDataAdapter object, I am able to connect to a CSV file. However, my data tables are all comeing back with the first line of the CSV file used as a column header line. How do i disable this? TIA// Tag: Reading Excel files with Jet OLE DB Provider Tag: 64166
  - 19
    - Ambiguous ConstraintException I am doing a pretty basic Fill() call to a SqlDataAdapter filling a DataTable. The Fill() call throws a ConstraintException, but the message is extremely ambiguous. I get the ever-cryptic message, "Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints." Does anyone know where to begin debugging such a problem with over 300 columns? I wish it would provide more useful information such as which constraint and what value violated it. Tag: Reading Excel files with Jet OLE DB Provider Tag: 64160
  - 20
    - Why doesn't GetColumnError or GetColumnsInError return anything when the dataset HasErrors? If I do the following code I get nothing returned. Dim i As Integer For i = 0 To ds.Tables(0).Columns.Count - 1 If ds.Tables(0).Rows(0).HasErrors Then MsgBox(ds.Tables(0).Rows(0).GetColumnError(i)) End If Next I get a message box for every column and message boxes are always blank. Why? Tag: Reading Excel files with Jet OLE DB Provider Tag: 64158
  - 21
    - populate a treeview from a db I'm trying to populate a treeview from a SQL tables in C#. i've written the SQL procedures (i.e. database wise is sorted). However, I would like to see an example of how to retrieve the data from the tables (recrusive method). I finally worked out how to retrieve the data. it was just a matter of understanding the database class I am using. However, one problem I have at the moment is that I could display the parent nodes and the first node subnodes which is not even what I want to display (i need to modify the procedure). the following is the method I created to display the data from the table. any idea how I could display subnodes (i.e. code logic!) //------------------------------------------------ private void buildTreeView() { // the connection string and command string // "upDisplaySites" is the SQL Procedure name ..... string connstring = ConfigurationSettings.AppSettings["ConnectionString"]; DataTable dt = TMG.Data.DataAccess.GetResultTable("upDisplaySites", connstring); DataTable dt1 = TMG.Data.DataAccess.GetResultTable("upDisplayGroups", connstring); DataTable dt2 = TMG.Data.DataAccess.GetResultTable("upDisplayUsers", connstring); treeView1.Nodes.Clear(); try { TreeNode parentNode = new TreeNode(); foreach(DataRow dr in dt.Rows) { treeView1.Nodes.Add(dr["Name"].ToString()); //TreeNode tNode = new TreeNode(); parentNode = treeView1.Nodes[0]; } // this needs to be changed! foreach(DataRow dr1 in dt1.Rows) { TreeNode subNode = new TreeNode(); parentNode.Nodes.Add(dr1["Name"].ToString()); subNode = treeView1.Nodes[0]; } } catch (Exception ex){ MessageBox.Show("Err");} } } //-------------------------------------------------------------------------------------- the above displayed the Sites as follows: ============== -a -1 -2 -3 -b -c -d -f ============== The idea is to display the sites then the groups within the sites and then the users in each group. (i.e. 3 levels of nodes) Any idea of I could logically work this out. (note: the table are as follows: site, group, user and others involved. i.e. multiple table are used) Thank you in advance. Kam Tag: Reading Excel files with Jet OLE DB Provider Tag: 64157
  - 22
    - Does anybody have any idea what this message means? "The data value could not be converted for reasons other than sign mismatch or data overflow. For example, the data was corrupted in the data store but the row was still retrievable." I can't find this anywhere in MSDN, and the google search I did didn't return anything useable. I am useing and OleDb dataAdapter to update a table in a Sybase ASA database. I have another adapter that does a similar action on another table and it works fine. There are only two minor wrinkles in what I'm doing, and I don't think they are having any impact: 1) I am updateing based on the primary key for the table (call this Column A), however my select command retrieves based on an foreign key (Column B). I am not updating either of these columns. 2)I am using a stored procedure with a bit and a long varchar argument, amoung three others which are varchar and an int which is the primary key. I tried taking the bit and long varchar out and got the same error. The only other thing I can say is that the code is exactally what the "wizard" created, so I doubt it's a typo or anything. Thanks, James Tag: Reading Excel files with Jet OLE DB Provider Tag: 64134
  - 23
    - how to create/populate strongly typed dataset from data in a listview I am trying to populate a strongly typed dataset from data that is residing in a list view. Any hints or suggestions would be appreciated. Thanks. I guess i should mention this is in vb .net thanks. Tag: Reading Excel files with Jet OLE DB Provider Tag: 64131
  - 24
    - Datagrid Navigation event again Here is the scenario: I have a hierarchical data grid. I click on expansion sign on row (this row is not the currently selected row) and then navigate to show the detals of that row. Now is there a way to get the row on which I clicked to navigate to the child-rows? Tag: Reading Excel files with Jet OLE DB Provider Tag: 64127
  - 25
    - HELP: Recordcount always -1 (using adOpenKeyset) I cannot get my recordcount to show up properly (using ADO 2.5). It keeps showing -1! Here's a snippet: Dim dbMyDb As New ADODB.Connection Dim rsWebData As New ADODB.Recordset dbMyDb.Open gConnectionString rsWebData.Open "SELECT * FROM WebDataLogAc;", dbMyDb, adOpenKeyset, adLockOptimistic rsWebData.MoveFirst Debug.Print rsWebData.RecordCount This always returns -1 even though there are records in the table. HELP!!! Thanks! Tag: Reading Excel files with Jet OLE DB Provider Tag: 64125

I have some ADO.NET code that uses the Jet OLE DB Provider to read Excel
files. The reading of the files works okay. However, I get strange
behavious when I have Excel running and run the program that reads the file
that Excel currently has loaded. Excel will pop-up messages such as "File
is now available for read-write." In other cases, if I use Excel to modify
the file and save it, then run my program, I get the old data, not the most
recent data.

I think maybe I have to tell the OLE DB provider to open the file in
read-only mode, but I don't know how to do that.

Thanks.
- Kevin

Top

Re: Reading Excel files with Jet OLE DB Provider by Paul

Paul
Tue Sep 02 10:04:00 CDT 2003

On Sun, 31 Aug 2003 10:58:38 -0600, "Kevin Dietz" <kevinzapit1@earthlink.net> wrote:

¤ I have some ADO.NET code that uses the Jet OLE DB Provider to read Excel
¤ files. The reading of the files works okay. However, I get strange
¤ behavious when I have Excel running and run the program that reads the file
¤ that Excel currently has loaded. Excel will pop-up messages such as "File
¤ is now available for read-write." In other cases, if I use Excel to modify
¤ the file and save it, then run my program, I get the old data, not the most
¤ recent data.
¤
¤ I think maybe I have to tell the OLE DB provider to open the file in
¤ read-only mode, but I don't know how to do that.
¤

Do you have the workbook open in shared mode? By default Excel opens workbooks for exclusive access.

You could also try adding Mode=Read to your connection string.

Paul ~~~ pclement@ameritech.net
Microsoft MVP (Visual Basic)

Top