.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

can't insert a group of data into excel using OleDb connection

Posted By:      Posted Date: October 26, 2010    Points: 0   Category :WPF
OleDbConnection MyConnection;
                OleDbCommand myCommand = new OleDbCommand();
                string sql = null;
                MyConnection = new System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Project\Sample1.xls;Persist Security Info=False;Extended Properties='Excel 8.0;HDR=YES'");
                myCommand.Connection = MyConnection;
                sql = "Insert into [first$] (Name,Group) values('jag','f');";
                myCommand.CommandText = sql;
this the code. and the error is- syntax error in insert into statement as it is not able to insert.
if i use "insert into [first$] (Name) values('jag');" its working..but the subsequent update statem

View Complete Post

More Related Resource Links

Insert data to Excel using OLEDB



I am trying to insert data to Excel Document using OLEDB as follows:-

 sql = "Insert into [MyFirstSheet$] (" + Title1 + "," +Title2 + "," + Title3 + ") 
values('" + Value1 + "','" + Value2 + "','" + Value2 + "')";
myCommand.CommandText = sql;

 sql = "Insert into [MyFirstSheet$] (" + Title1 + "," +Title2 + "," + Title3 + ") 

values('" + Value1 + "','" + Value2 + "','" + Value2 + "')";

myCommand.CommandText = sql;


But, the Title1 ,Title2, Title3 are in the 3rd row of the Excel Document. So, the data is not inserted.

Insert Excel Data via File Upload into sql server database???

Hi all, I have requirement that User can Upload the Excel Sheet Data to sql server Database at once. How i do that Any Article or hint is apperciated.   Regards 

How to update single cell of excel sheet using oledb connection,

 Friends i got some codes from net  by using that i can update a excel sheet ,but there we have to specify some header fields name in command string like as fallows ,Update [Sheet1$] set ProductName="IBG" where ProductId=5but i want to update a single cell of excel sheet only,let say O12 cell i want to change somedata,how it is possible that i don't know.plz help me out... thanks in advance...  

SSIS Excel Connection Manager Data Type Conversion Issues with SS Agent Job

Hi All! I have an issue I've been trying to fix but can't seem to figure it out. I was hoping a kind person would point me in the right direction. :o) I have an SSIS package that uses an excel connection manager source, and I want to run this package through a job scheduled in the SQL server agent. The data types for the excel file fields are 2 (DT_WSTR) and 5 (DT_R8). When I run the package directly through the SSIS package (VS solution) all of the data fields are properly imported into the database table. But...when I run this package through the SQL server agent job, ONLY the string (DT_WSTR) fields in each row are being imported, all of the float fields are imported as NULL. I set the data types for these float fields as "float" in the SQL server import table (data type). Even though the excel source float fields are indicating a type of DT_R8 in the excel connection manager and I set the data types in the SQL server table to "float", I also used the data conversion component and set the type to "float" as a fail-safe. I guess I should add to that the data access mode in the excel connection manager is using a custom code to select only those columns that I needed and to trim rows that I didn't need. Here's my code that I have in the excel source editor: select f1, f2, f3, f5, f6, f7, f8 from [mdo$] where f2 <> 'Rep Name'

Import excel sheet data to datagrid without using oledb

In my application(C#) i want to import excel sheet to show its data in DatatGridView i dont want to use oledb Need sample code can any any Help

Insert Data to table from excel file


guys, i am new to sql development

my question is how do i import data from an excel table,

i was trying to do it via a query and just paste the values, but received a syntax error. thanks

data structure is:


Import Excel file with Data Connection into SSIS


I have got an Excel spreadsheet with a Data Connection that I want to import into SSIS. The problem is that the Data Connection does not seem to run at the time the file is imported, so it only calls "old" data.

How can I set SSIS up to run the Data Connection first, or is there something I need to change in my Excel spreadsheet setup?

When a person opens the Excel file normally, they first need to "Enable" the Data Connection in the Security Settings. Once that is done the data will update immediately. I wonder if that is something I somehow need to change to make it work in SSIS?

SSIS import Excel with Data Connection


Hi guys, I am trying to use SSIS for the first time. What I am hoping to do is set up a scheduled job that will get SSIS to read an Excel spreadsheet on a regular basis and import the data out of it into an SQL Server database. Now my Excel spreadsheet uses a Data Connection to a different source and I want the data to be refreshed before SSIS imports it. Is that possible?

The problem I am facing right now is that the Data Connection does not seem to run before SSIS import the data. It keeps importing the old data that was sitting in the Excel spread sheet at the time it was saved last.

Is there any way I can tell SSIS to run the data connection first? Or is there something I need to do in my Excel file to ensure the connection is run?

By the way: the Excel file is set up to run the Data Connection immediately when it is opened normally in Excel, but the general user always first has to go into the Security Settings and enable Data Connections.

Thanks for the help!

Insert data into Excel in an exact cell



If I do the following, I can update a cell in Excel from SQL Express 2008 with the data I want:

SET @strsql = 'UPDATE OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', ''Excel 8.0;Database=' + @strFileName + ''',''SELECT * FROM [MySheet]'') SET [' + @Column + ']=''' + @Variable + ''' WHERE RowID=''' + @RowID + ''''

Is there a way to insert a range of data into Excel starting at a specific cell, rather than just updating one specific cell?



Is There Other Methods to Create and INSERT data into A SQL Database besides ADODB.Connection


OK - I have everything working on my Production computer to find out that when I upload my page to my Shared Hosting Server, to find out my application will not work because I do not have high enough permissions to for aspcompat to be true.

This works on my development server which of course I have full access to:


        'Creating Connection Object and opening the database
        con = Server.CreateObject("ADODB.Connection")

        'Done. Close the connection
        con = Nothing
        Response.Write("Your Information was Successfully Added.")


Is there another method to execute my  sql string (which contains my INSERT INTO statement, and is in my .vb code file ) with just a Medium Access Level?  Other wise I will have to purchase a Dedicated server, which currently I can not really afford!


How to insert Binary Image data from XML To Excel using XSLT


Hi All,

I had created a XSLT which converts the XML file generated from the system to the excel format. The XSLT has the styles required for this transformation.

Now i am told to insert image in one of the excel columns using XSLT. The image is in binary code format in the XML file.

Tried to google but could not found the syntaxes for this. Let me know if anyone has done the image embedding in excel using XSLT.


Can we import data from Green plum to SQL Server 2005 using OLEDB connection manager in SSIS



actually we are planning to move data from GreenPLum data base to SqlServer 2005 data base using SSIS as ETL tool. What type of connection manager can we use. Is to good to use OLEDB connection manager or ODBC connection manager.

Please give me a reply as soon as possible.




to insert a row of data in excel of specific font color through C#


i m using Oledb connection  to connect with excel.i wanted to insert a row of data in excel of specific color say - red..

how can i do programmatically in c#?

SQL Data export to Excel problem with Microsoft.JET.OLEDB.4.0



I got a problem with datatransfer via Microsoft.JET.OLEDB.4.0.

As long as I use the following statement it's working but only 255 Characters will be transferred to Excel.

INSERT INTO OPENROWSET('Microsoft.JET.OLEDB.4.0','Excel 8.0;HDR=YES;Database=C:\xxxxxx.XLS','SELECT F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12 FROM [Tabelle1$]')SELECT TOP 65000 [Störungsnummer], CONVERT(varchar(50),[Annahmedatum],120) AS [Annahmedatum], CAST([Teilnehmernummer A] AS VARCHAR(255)) AS [Teilnehmernummer A], CAST([Annahmeperson] AS VARCHAR(255)) AS [Annahmeperson], [Annahmeplatz], CAST([Störungscode] AS VARCHAR(255)) AS [Störungscode], [Störungscode Nummer], CAST([Störungsmeldungstext] AS VARCHAR(255)) AS [Störungsmeldungstext], CAST([Wartungsvertrag] AS VARCHAR(255)) AS [Wartungsvertrag], CAST([SOSegment] AS VARCHAR(255)) AS [SOSegment], CAST([Kundensegment] AS VARCHAR(255)) AS [Kundensegment], CAST([TDV] AS VARCHAR(255)) AS [TDV] FROM [yyyyyyyyyyyy].

As soon as I try to extend the varchar over the limit of 255 the following error occurs:

String or binary data would be truncated. Export ERROR: XLS - Export in work.. (Microsoft.JET.OLEDB) The statement has been terminated.

OLEDB Connection for Excel not a valid file path


Hi there, I'm using a FileUpload control on a webform to allow users to select an excel file. I then want to read the excel file and dump the data into a dataset, but I can't get it to work. This is my code:

Dim fileNameString As String = Me.BillUpload.FileName
Dim filePathString As String = Me.BillUpload.PostedFile.FileName
Dim fileLength As

Excel services external data source connection


can't connect to an excel services data source on the sharepoint 2010

Error : ... can't connect to external datra source .....

Convert MS Excel to XML and then insert all data records into MS SQL



I am developing a web page for users to input data records. I am seeking the fastest way that users can upload their MS Excel files and the system can help data insertion into one data table. I know XML can insert data records into SQL database easily. Could any one give me some ideas how to perform this issue? Thanks a lot.


ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend