FAQ Database Discussion Community


External table is not in the expected format.

c#,asp.net,excel,oledb
I am creating a small application to import excel data into my database, when i click the button it crashes with the error External table is not in the expected format. I tried googling and changing the codes here and there but the problem still occurs. I tried saving the...

how to create master detail combobox in C#?

c#,asp.net,excel,combobox,oledb
I Have a excel sheet test.xlsx, two sheets within Sheet name : Product1 has following data : ID ParentID Title 1 0 P1_1 2 0 P1_2 Sheet name : Product2 has following data : ID ParentID Title 1 1 P2_1 2 1 P2_2 3 1 P2_3 4 1 P2_4 5...

Inserting special chars (such as ' or +) into an Access database

c#,sql,ms-access,oledb
I have not much experience with SQL, Access nor C# but I find no solution to a problem that should look quite simple for someone who has more expertise. Basically, the user fill some textboxes in a Winform and he might insert some "special" characters (at least, special for DB...

Merged DataRows are not Updated in the database but Added DataRows are. Why?

c#,ms-access,datatable,oledb
I'm currently trying to read a big bulk of excel files which are stored in a datatable called total and then i need to save this to a access database, it works as it is now but i'm afraid it will be very inefficient with the amount of excel files...

Set Access 2010 record locking to optimistic locking through OleDb (C#)

c#,ms-access-2010,oledb
I am communicating with Access 2010 through OleDb in C#. How can I set the locking type to use optimistic locking from C#? In case anyone wonders, I am using this connection string to connect to my Access database: // Set the data source string. _sqlCon = new OleDbConnection( String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data...

Python removes quotes

python,sql,tkinter,oledb
I'm trying to make a simple gui with tkinter utilizing windows built-in search functionality. This is the code I'm having issues with. #gets search keywords from user input and splits into list keyword_list = self.entry.get() keyword_list = keyword_list.split() #creates empty string keyword_string = "" #fills empty string with list, appends...

Switching from ODBC to OLEDB and getting “Object Required”

vba,vb6,odbc,oledb
I have been tasked with modifying some VBA code to switch from using an ODBC connection to using an OLEDB connection so we can just code a username and password into a subroutine. I'm certainly not experienced with this but I think I'm managed to get the proper connection string...

Adding data to DBF file adds column _NullFlags

c#,winforms,oledb,dbf
Im currently working on a program that gets information from a sql database and creates a .DBF that has the information in there. I got it to the point where it makes the file and adds the columns/rows. But every time I run it, it adds a column named _NullFlags...

insert date with time in access database c#

c#,asp.net,oledb
In the access I'm only getting the date 5/12/2015 but no the time, I need something like this 5/12/2015 4:56 PM saved in the access database DateTime dtclickdate = DateTime.Now; OleDbParameter clickdate = new OleDbParameter("@clickdate ", OleDbType.DBDate); clickdate.Value = dtclickdate; cmd.Parameters.Add(clickdate); ...

No value given for required parameters, with value

c#,ms-access,oledb
I'm working in a c#/ASP.NET on a web app. As part of the app, I want to connect to an ms-access database, and get values from it into a dataset. For some reason, I get the error in the title when filling the dataset with a DataAdaptor- despite this, when...

OleDb Exception: Command contains unrecognized phrase/keyword

c#,sql,oledb,dbf,visual-foxpro
Certain users of an application are receiving exceptions when filling a dataset from a DBF file using the VisualFoxPro 9.0 driver. I can't seem to figure out what's going on because the error always happens for some users, never happens for other users and sometimes (based on the location of...

How to update an entire selected same column?

c#,oledb
I want to update an entire selected same column with another value... heres a code i have tried, apparently its not working (the error is no changes in DB) OleDbConnection con = new OleDbConnection(); con.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Rock.accdb"; OleDbCommand cmd = new OleDbCommand(); cmd.Connection = con; con.Open(); cmd.CommandText = "UPDATE...

Syntax Error In Insert Statement SQL C#

c#,oledb
I'm having trouble with the df.ExecuteNonQuery(); claiming that the insert statement has SQL errors. I'm not sure why the other parts of the program is working with the Insert statement, but the student one just refuses to work. Database: http://puu.sh/hoTCv/c1ccb77551.png OleDbCommand df = new OleDbCommand("INSERT into Students(ID,Password,FirstName,LastName,Street,City,State,Zip,EMail,GPA)" + "VALUES (?,?,?,?,?,?,?,?,?,?)",...

IBM i (AS/400) command works locally but not remotely

oledb,.net-2.0,ibm-midrange
I have a Windows service written in VB.NET 2.0 which connects to an IBM AS/400 server. Queries work fine, but when I try to do something like deleting a spool file, I get errors. For example: CPYSPLF FILE(PO630A) TOFILE(MPLCDATPAR/PO630APF) JOB(083064/ARUSER/POASYNCMON) SPLNBR(80) MBROPT(*REPLACE) Running this command with ExecuteNonQuery yields: CPF3342 -...

Why am i getting a “Syntax error in UPDATE statement.” exception?

c#,sql,data,ms-access-2007,oledb
public void EditUser(string id, string userid, string email, string password) { try { _dbConn.Open(); OleDbCommand updateCmd = new OleDbCommand("UPDATE LOGINS SET Username=?, Password=?, Email=? WHERE ID=?", _dbConn); OleDbParameter param1 = new OleDbParameter("Username", userid); OleDbParameter param2 = new OleDbParameter("Password", password); OleDbParameter param3 = new OleDbParameter("Email", email); OleDbParameter param4 = new...

Data in Excel with OLEDB connection from access don't update when use LIKE operator

excel,ms-access,connection,ms-office,oledb
When I use LIKE operator in a Query in Access and I create a OLEDB connection in Excel to import this data to Excel, the data don't update even after change the query with another filter. Is like if I had not changed the query with the new filter. I...

This query runs perfectly in Access 2010 but when I try to execute it in my code I get Syntax error in INSERT INTO statement

sql,vb.net,ms-access,oledb,insert-into
I am a newby programmer and I have had a look at other posts similar to this and they say that the problem is usually due to reserved words being used, however I am sure that I am not using any. The sql runs fine in access and creates the...

How to check for domain Constraints using oleDb?

c#,oledb
I have to find every columns that doesn't have a domain constraints in a database(MS Access) and for each one calculate min and max values to the current data and then to add from my program the corresponding constraint. For example, a column "Foo" has the min value 0 and...

VB.NET Releasing Excel file after reading so it can be overwritten

vb.net,excel,datagridview,garbage-collection,oledb
I am having the same issue that many before me have had, and I have found several threads on the issue but my application of their fixes have not yielded any change. I am reading an excel file and populating a DataGridView with it. Very simply the user can modify...

I have a visual C# project and I'm trying to insert data in a MS Access Database when I press a button [closed]

c#,ms-access-2010,oledb
private void btnSave_Click(object sender, EventArgs e) { string str = "insert into Checklist_Master values('"+comboBox2.Text+"','"+txtaurno.Text+"','"+comboBox1.Text+"','"+txtdeduction.Text+"','"+txtceckpoint.Text+"','"+mdlconnection.user_name+"')"; int dd = mdlconnection.excuteQuery(str); //MessageBox.Show(str); if (dd > 0) { MessageBox.Show("Data Saved Successfully..!!!"); reset(); } } And connectionstring is : public static void getconn() { con = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data...

How to force data type return when querying excel from Powershell?

excel,powershell,oledb
I'm using Powershell to query an excel spreadsheet using an OLEDB connection. I have one column with multiple values in it. I have noticed that when 1 our of the 4 cells contains letters, the query will return a NULL and the data type will be a System.DBNull. See below:...

SQL update query throwing a syntax error [closed]

c#,sql,ms-access,oledb
Problem I am trying to create a database management system in the form of a form application. I am using a Microsoft Access database. I have one particular SQL update query which is throwing an error and I cannot work out what is the cause of the error and would...

Filling DataGridView row by row only populates last row

vb.net,excel,datagridview,oledb
I'm trying to fill a datagridview with one column of integers using a loop. I'm pretty sure I've got the loop and excel reference correct, as I've used this on other projects, but it won't display correctly. Here's the code: Dim da As OleDbDataAdapter Dim ds As DataSet xlWorkbook2 =...

DataTable.Rows returns second row in c#

c#,datatable,oledb
Im reading excel sheet using oledbdatareader and convert to Datatable. So first few columns row in excel sheet tells information about the details of excel. Ex: Month ---- First Row Year --- Second Row Payment ---THird row Balance rows in that excel sheet have salary details of all employees. I...

Connecting to 32 bit Oracle client with 64 bit Excel

excel,oracle,vba,oledb,ado
I'm trying to connect my Excel to Oracle with this connection string in VBA: dim cn As New ADODB.Connection cn.Open "Provider=OraOLEDB.Oracle;Data Source=source;User Id=userid;Password=pwd;" I'm getting this error: Run-time error '3706': Provider cannot be found. It may not be properly installed. Relevant references: Microsoft ActiveX Data Objects 6.1 Library OraOLEDB 1.0...

Is it possible selecting “optional” column in single query?

sql,ms-access,oledb
I have three tables: // table item +-------+-------+----------+ | ID | Cat | SubCat | +-------+-------+----------+ | A001 | 001 | 001 | +-------+-------+----------+ | A002 | 001 | 000 | +-------+-------+----------+ // table category +-------+-------------+ | ID | CatName | +-------+-------------+ | 001 | MyCategory | +-------+-------------+ // table...

How to write OleDbCommand to get only distinct rows?

c#,excel,oledb
I just want to have rows that has distinct ManufacturerPartNumber from my excel sheet. So that, I gave my command text like this: cmdExcel.CommandText = "SELECT Name, DISTINT [ManufacturerPartNumber],Price From [" + SheetName + "] WHERE [ManufacturerPartNumber] IS NOT NULL"; But this's not working, and if I changed DISTINCT [ManufacturerPartNumber]...

Getting value of Date Time Picker and using BETWEEN for filtering date in Ms Access database

vb.net,ms-access,oledb
I have a From and To DateTimePicker. I converted them into a String and use it to filter a MsAccess database of a specific range but there is a problem with the filtering. For example, I would like to filter from January 1, 2015 to January 31, 2015. The results...

The SLXOLEDB.1 provider is not registered on the local machine

.net,wcf,iis,oledb,oledbconnection
I'm building a REST web service in WCF. When I run the WCF project from Visual Studio, everything works fine. Calling the 'Post' method on a specific endpoint works just fine. But when I try to run the same web service from the IIS Manager, calling the same method on...

Parametric 'UPDATE' string running with no errors but not doing anything

c#,sql,ms-access,oledb
I've recently changed an Update SQL string from dynamic SQL string to parametric SQL string. Here's what I had before: OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + this.DBstring); OleDbCommand comm = new OleDbCommand(); comm.CommandText = "UPDATE myTable SET MY_FIELD='" + myString + "' WHERE F_SERIAL = '"+mySerial+"'"; comm.CommandType = CommandType.Text;...

Loop to insert data in OleDb breaks when I try to execute more than 1 query

c#,.net,ms-access,oledb
I'm working on a small offline C# application with an Access 2002 database (.mdb) and OleDb. I have 2 tables where I need to insert data at the same time, one holding a foreign key of the other. So, to simplify let's say one table has 2 attributes: "idTable1" (auto-increment...

oledb exception syntax error in insert into statement (0x80040E14) in C#

c#,oledb
I'm having and exception in my insert into statement.But the data is inserting to the table correctly. can someone please show me the error in this code. private void btnAddNewSale_Click(object sender, EventArgs e) { string StrQuery; connection.Open(); OleDbCommand command = new OleDbCommand(); command.Connection = connection; for (int i = 0;...

Do I need to use try-catch for every command?

c#,oledb
I have oledb connection. I use try-catch because commands sometimes getting error. Like this: OleDbConnection Connection; Connection = new OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=" + Server.MapPath("~/db.mdb")); OleDbCommand Command1, Command2, Command3; Command1 = new OleDbCommand("SELECT a FROM Table1 WHERE ID = 1", Connection); Command2 = new OleDbCommand("SELECT a FROM Table1 WHERE ID = 2",...

Getting the second record where the sql query is satisfied

c#,sql,ms-access,oledb
I am trying to fill my list box with the task names from the database where the priority is equal to an item in my List<object>. The code below populates the list box, but the error is that in my database I have two records with the priority of 1...

How to get index names through SQL or OLEDB from .NET?

c#,sql,ms-access,oledb,database-metadata
How can I get index names for an Access table using OLEDB or SQL ? (I searched a lot on the internet in the last two days and did not find anything related to this issue.)...

Javascript: jQuery help getting
text from an append to new function for autocomplete (using OLEDB as souce)

javascript,jquery,css,oledb
I'm still learning JS and jQuery, so don't hate on my coding too much! :P Anyways, here's what I am trying to do: I have a database that has all cities and postal codes for a country. Some postal codes have multiple cities. I am first making an auto-complete for...

Cant find code to insert into AS access database using c#

c#,asp.net,gridview,oledb
I am very new to this. I have a MS access database (.mdb having only two columns) and i have linked it using a gridview. It displays whatever is there in the database on the webpage. I also have an post button for a text box (like the one in...

fill command cannot be used

c#,dataset,oledb,fill
This Is my code: using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.SessionState; //db con using System.Data; using System.Data.OleDb; using System.Data.SqlClient; using System.Configuration; namespace SOMaintenance.App_Code { public class readdata { string sConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; public void selectdata(DataSet dataset1, string sqlcommand) { string strConString =...

How can I identify which record has the MAX for a particular field?

sql,vb.net,oledb
The title is pretty much self explanatory. I want to find out a way to identify which record the MAX of a field is in.

Syntax error in INSERT statement into MS Access

c#,ms-access,oledb,oledbcommand
I couldn't find the syntax error in the following INSERT statement. public partial class doRegister : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { string str = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\database"; using (OleDbConnection con = new OleDbConnection(str)) using (OleDbCommand cmd = con.CreateCommand()) { cmd.CommandText = "INSERT INTO users (staffID,accessLevelIdD,username,password,email) VALUES (@staffID,...

Searching using like

c#,oledb
When im searching my datagrid, it should be always the first word to type. If i type the next word or consecutive letter it contains, it doesnt show. Example: Im going to search Welcome Back. If i type "Elcome" or "back", it doesnt show. Here is my code... OleDbCommand cmd...

How to use Oracle connection in SSIS?

oracle,ssis,oledb,oracle12c
I must develop a SSIS Package with Oracle connection. I'm using Windows 8.1 64bit with Visual Studio 2010 and Oracle version 12c(remotely). I would use "Microsoft OLE DB Provider for Oracle" like picture: Where I insert my connection parameters: After "TEST CONNECTION" command I get this error: [UPDATE] After installation...

Insert Binary String into CLOB column in Oracle using OLEDB

c#,.net,oracle,oledb,clob
I have this binary string (see below) to be inserted into CLOB column in Oracle using OLEDB. binary string :...

How to get the data from Excel into C#?

c#,excel,oledb
I have a C# program that takes some data from Excel and saves it to an SQL database. When I open the Excel file and run the program it is working successfully but if I close the Excel file and try to run the program again there is an error...

Trying to create foreign keys and insert their values in a .mdb database using C# and OleDb

c#,ms-access,oledb
I have a database with 3 tables. One of them holds a relation of the other two. E.g.: Table1 = idTable1 (PK_Table1), attribute1, attribute2, attribute3 Table2 = idTable2 (PK_Table2), attribute1 Table3 = idTable3 (PK_Table3), attribute1 (FK relating to idTable1), attribute2 (FK relating to idTable2) All primary keys are auto-incrementing fields...

Dapper, MS Access, Integers and “no value given for one or more required parameters”

c#,ms-access,visual-studio-2012,oledb,dapper
I keep getting the error "no value given for one or more required parameters" on the third attempt to insert in the code below. The first two attempts work fine. I'm running in .NET 4.0, against MS Access in VS 2012. And this problem appears to be consistent whether I'm...

C#, Oracle Database, SQL Update statement will not work (OLEDB)

c#,database,oracle,oledb
So I have used this exact same syntax with a million other updates, I have no idea what is going on and i am starting to get very frustrated. I set a numeric primary key, and an alphanumeric field that stores truck FINS, which is basically just a random mix...

OLEDB Import of CSV to VB.NET datatable reading '-' as 0

vb.net,visual-studio-2010,csv,oledb
Greetings helpful delightful people, I have a problem with reading CSV files and converting to datatables in VB.Net. If the CSV file contains a column full of '-' then on import into the datatable they appear as '0' and the entire column is formatted in a numeric format. The code...

How to check a Cell contains formula or not in Excel through oledb reader or excel library, excel datareader or NPOI etc (Except Interop)?

c#,asp.net,excel,winforms,oledb
How to check a Cell contains formula or not in Excel through oledb reader ? System.Data.OleDb.OleDbConnection conn2 = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source = " + strFileName + "; Extended Properties = \"Excel 8.0;HDR=NO;IMEX=1\";"); conn2.Open(); string strQuery2 = "SELECT * FROM [" + Table + "]"; System.Data.OleDb.OleDbDataAdapter adapter2 = new System.Data.OleDb.OleDbDataAdapter(strQuery2,...

Can't insert record into MS Access through C# oledb

c#,ms-access,oledb
I have a table in my MS Access database called "Genres". It has the following columns: ID -> AutoNumber Index -> Number GenreText -> Text Here is the C# code that I use: public static void AddGenre(string text, int index) { string query = "INSERT INTO Genres(Index, GenreText) VALUES(@index, @text)";...

Select statement using OleDbCommand throws InvalidOperationException

c#,sql,visual-studio,oledb
I need to find a record in C# but I get an InvalidOperationException: public static Auto findAuto(int kfznr) { Auto retAuto = new Auto(); try { myOleDbConnection.Open(); string query = "SELECT * FROM Auto WHERE Auto.KFZNR = " + kfznr; OleDbCommand select = new OleDbCommand(); select.Connection = myOleDbConnection; select.CommandText =...

Make connection string ADO.NET

c#,database,connection-string,oledb,sql-server-express
How can you make a connection string for an OleDbConnection? I have tried a lot of combinations and variants, but nothing works. I use a MS-SQL platform with C# to make the connection for SQL EXPRESS. Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Data\Northwind.mdb;User Id=admin;Password=; Data Source=(local);Provider=SQLOLEDB;Initial Catalog=FastCycle;Integrated Security=true Persist Security Info=False;Data Source=Northwind;Integrated Security=SSPI;Trusted_Connection=TRUE;providerName=System.Data.OleDb Provider=SQLOLEDB;Data...

Query to get the count of records from MS Access database

c#,sql,winforms,oledb
I am using below code to get the count of records present in database, but somehow it is not working. throwing me an error: Input string was not in a correct format. conn.Open(); OleDbCommand cmd1 = new OleDbCommand(); cmd1.CommandText = "select count(*) from Orders_Description where (ItemCode=" + Convert.ToInt32(row.Cells[1].Value) + ")...

How can I select multi-column from table where date is last day?

c#,xcode,ms-access,oledb,oledbcommand
I'm trying to select a field from my table where date is less than today. connect.Open(); command.Connection = connect; today = DateTime.Today.ToString("dd/MM/yyyy"); string query = "select attendance as [Attend], Emp_UserId as ID, 'Date_ofday' as [Today Date] , Emp_UserName as Name ,Delay_Hours as [Delay Hours] from Attendance where Date_ofday > '"+...

How to use VFPOLEDB to get DBF information

vb.net,oledb,foxpro,visual-foxpro
I can use GetSchemaTable and GetXMLSchema to get information about field types, sizes etc. from a Foxpro DBF opened with VFPOLEDB but can not get any information pertaining to what indexes are in the tables/CDX. I dont want to use the indexes, just the criteria on which the index is...

Syntax error in UPDATE statement OleDbException

c#,sql,oledb,oledbexception,updateexception
I'm writing an application which stores user information. Currently the user is supposed to update their Name, Height, Weight and Birthday. string height = TB_ClientHeight.Text; string weight = TB_ClientWeight.Text; string name = TB_ClientName.Text; string bday = dateTimePicker1.Value.ToString("dd-MM-yyyy"); int heightint = Convert.ToInt32(height); int weightint = Convert.ToInt32(weight); It's updated by calling the...