StringBuilder sbExcelFileConnStr = new StringBuilder(); sbExcelFileConnStr.Append("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="); sbExcelFileConnStr.Append(sFile); sbExcelFileConnStr.Append(";Extended Properties=\"Excel 8.0;HDR=No;\""); OleDbConnection xlConn = new OleDbConnection(sbExcelFileConnStr...
I am using excel as a database. I am searching through it using a query. I am using LIKE from SQL. I would like to search ignoring the case. When I try following ...LOWER([COLUMN_NAME]) LIKE "%query%" It throws me the error as IErrorInfo.GetDescription failed with E_FAIL(0x80004005). When I use [LOWER(COLUMN_NAME)] LIKE "%query%" It g...
Hello, I have a problem of using the where clause for limiting dates I can't even get a simple statement like on "Feb 5 2010" to work, e.g., select * from LineItems where DueDate = 2/5/2010; I tried "2/5/2010" "2010/2/5" "2010-2-5" "2010-02-05" 2010-2-5 2010-02-05 ... but none worked. Does anyone have an idea what the proper format fo...
I'm using C++ and ADO to add data to a SQL Server 2005 database. When calling the Recordset Update method for a sql_variant column I'm getting the error DB_E_ERRORSOCCURRED and the error message Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. If the value I'm adding is NU...
I am using Oledb driver to load data from excel for displaying in tabcontrol with datargids I am using following loop to load data from every sheet foreach (string str in sheets) { string query = "SELECT * FROM [" + str + "]"; adapter.SelectCommand.CommandText = query; ...
I am using Microsoft.Jet.OLEDB.4.0 from .NET to read a CSV file. Here is a sample input data row: 102A Avenue,97 Street,99 Street,2 Lanes Closed,2007-04-13,2009-12-31 When I read the last two valuee they come out as DateTime rather than strings and that is neither what I want nor what I expect. It seems that the provider performs type...
I am having trouble using the Jet provider and OleDb to get the sheet names in an Excel95 workbook. I have a workbook with 3 sheets, Sheet1, Sheet2, Sheet3. I am using the following code to extract the sheet name: var connectionString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=...
Hello. I have to parse csv (txt extension) file. It is delimited with ',' and has all values in "". First problem. If file has name like this firstpart.secondpart.txt executing the reader throws exception saying that Microsoft Jet engine couldn't find table (or sth like that - I have polish WinXp). When I remove "secondpart" from file ...
I want to read a csv file into an access database , here is my code : Private Sub load_csv() Dim ConnectionString As String ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source=" & input_file & ";" & _ "Extended Properties=""Text;HDR=Yes""" Dim TextConnection As New System.Data.OleDb.OleDbConnect...
I'm trying to get the Foreign Keys for a table in my SQL Server 2005 database. I'm using the GetOleDbSchemaTable function right now: DataTable schemaTable = connection.GetOleDbSchemaTable( OleDbSchemaGuid.Foreign_Keys, new object[] { null, null, null, "TABLE" }); This pulls back the right foreign keys, the only problem...
I am trying to import data from DBF file to SQL table using the following command - select * from openrowset('MSDASQL', 'Driver={Microsoft dBase Driver (*.dbf)};DBQ=E:\data\;', 'select * from E:\data\a.dbf') But it is failing saying OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC dBase Dr...
I am using VFPOLEDB driver to read DBF files and I keep getting this error and I am not sure why and how to fix the problem: The provider could not determine the Decimal value. For example, the row was just created, the default for the Decimal column was not available, and the consumer had not yet set a new Decimal value. Here is the c...
Short question: I am finding I need to use MARS over ODBC but not over ADO/OLEDB, is that correct? Longer explanation: I just discovered my ODBC code (using "Driver={SQL Native Client}", MFC CDatabase code) needs to have MARS ("MARS_Connection=yes;") because, although I do not issue multiple SELECTs when opening a RecordSet, I do fetch...
I am trying to connect to excel table from Delphi 7 using TAdoConnection component. The problem is when I select Microsoft.Jet.OLEDB.4.0, Extended Properties="Excel 8.0;", I sometimes receive error, that external table is not in the expected format. When i select: Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;...
Hello, 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,...
I'm using Access via OleDb. I have a table with columns ID, GroupID, Time and Place. An application inserts new records into the table, unfortunately the Place isn't calculated correctly. I want to update each record in a group with its correct place according to its time ascending. So assume the following data: ID GroupId Time ...
I want to gather some data from some tables of an Access Database, I've found some solutions online, but I haven't found ways to fill a datatable, or dataset, and get each single field properly. Is it easier for me to get whole tables then get just the info that i want, or should I make a lot of searches in the access DB getting just wh...
I've recently made another question about connecting to MS-ACCESS database with .NET in C# or VB.NET. It worked just as intended with MDB, but with accdb it caused an exception in conn.Open(); which follows: Is there another way to do this? My original intention (like stated in the original question) is gathering some (actually, a ...
I have a database in .ACCDB format with some tables. I'm successfully loading it into an OleDbDataReader with the following code: string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;data source=C:\\marcelo.accdb"; OleDbConnection conn = new OleDbConnection(connectionString); string sql = "SELECT * FROM Clientes"; OleDbComma...
I'm getting a formatting problem if I use more than 10 UNION ALL statements in my VBA Code. If I use 10 or less everything works great. What I'm trying to do is combine 12 worksheets (Excel 2007). I have a numerical column called SC that turns into string and date if I have more than 10 UNION ALL. If I try to use ROUND with more than ...