SQL Server :: Ad Hoc Queries - Open An Excel File And Insert The Data
Aug 11, 2010
We moved to a new SQL server and I started getting an error on one of my sps only when I ran it from a website. If I manually ran the sp it worked fine. The error I got was this System.Data.SqlClient.SqlException: OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode. I can tell you that this sp does some things, then calls another sp which writes some sql to open an excel file and insert the data. Here is the pseudo
SET @sql = 'INSERT INTO dbo.TempParts (partKey, partKeyNumeric, partKeyAlpha, PartNumber, [Description], Comment, notAvailable ,BatchID)
View 6 Replies
Similar Messages:
Jan 19, 2011
i have a excel file in which "PuNumber,MachineNo,MachineName,StartDateTime,EndDateTime,WorkOrder,RescheduleInterval,Description ,ACT CODE,ACTIVITY DESCRIPTION" are column. WorkOrder is Primary key. we have multiple value of " ACT CODE, ACTIVITY DESCRIPTION" of one work order. i want to store this data into two table one is shcedule table and another in activity table. shcedule table has this column "PuNumber,MachineNo,MachineName,StartDateTime,EndDateTime,WorkOrder,RescheduleInterval,Description " work order is primary key. and Activity table has three column ACT CODE, ACTIVITY DESCRIPTION and WORKORDER " act code and workorder is primark key.
View 11 Replies
Dec 9, 2010
I have created a CSV file on the server and want to send it to the user using the following code:
Dim strPhysicalPath As String
strPhysicalPath = Server.MapPath( "CSV/" & PathVirtual)
Dim objFileInfo As System.IO.FileInfo = New System.IO.FileInfo(strPhysicalPath)
Response.Clear()
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader("Content-Disposition", "attachment; filename=" & objFileInfo.Name)
Response.AddHeader("Content-Length", objFileInfo.Length.ToString())
Response.WriteFile(strPhysicalPath)
Response.Flush()
Response.End()
This seems to work fine except when the downloaded file is viewed in Excell the html code behind the page from which it came is also appended to the file. Does anyone have any idea why it does this?
View 5 Replies
Apr 9, 2010
In my project I have to open excel application on click of a button without having ms-excel installed in server and the same case applies to word also. Please suggest me a solutions asap
View 2 Replies
Oct 21, 2010
I have a web page with a gridview control and a button for Exporting to Excel
I get following error when exporting to Excel.
"The file you are trying to open, 'someFileName[1].xls, is in a different format than specified by the file extension. Verify that file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?"
Clicking Yes opens the file. I would like to avoid showing the above error to the users.
Following code is used from the click event. I've left the commented code here to show what I've tried based on results from internet search. I have Microsoft Office Excel 2007 on local PC.
[Code]....
View 1 Replies
Mar 26, 2016
I am trying to import excel sheet, got an error like "
The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data"
I have given all necessary permissions but no luck..
View 1 Replies
Apr 23, 2014
I want to insert data in sql server from sheet within excelsheet ,there are three sheets in the file which i want to upload in three different tables,currently i am doing by selecting an individual sheet and the table to upload thus doing it three times for three sheets can i do it in one go,like passing all the sheets to a function or something like that or how can i do it in a better way and also i cannot directly upload sheet into table as i have to map one column of excel to different table of database to get its value like i need statecode but statename is provided in the sheet so i map statecode to statename in a staging table and then i upload the data...
View 1 Replies
Nov 17, 2010
I am a total newbie here. I have generated a web page using Visual Studio 2008. It connects to a database and lets me insert records. On the bottom of the form it automatically pus a link for "Insert" or "Cancel". I need them to be buttons. Is this possible? I don't even know where to begin.
View 4 Replies
Mar 17, 2010
I using this code for opening excel is asp.net but its not working
[Code].....
View 8 Replies
Jan 25, 2010
How is it possible to open an excel template (Which has a small text of hello) from the server onto the client machine?
View 2 Replies
Mar 18, 2011
We have a tab delimited file that we are making available for download. The file extension is txt, but the users want it to open automatically in Excel. The previous developer had used the following code:
[code]....
The ContentType is being set to Excel, but that doesn't seem to have any effect. I am guessing the txt file extension is causing it to be picked up by notepad or the like instead. I have tried just renaming it to a xls extension, which works but Excel complains that I am trying to trick it or it might be a corrupted file.
View 5 Replies
Mar 1, 2011
I have a requirement where i need to open excel file from system using link
i build link from code <a href="path fo file">
but this does not work from aspx page
if i put same thing in seperate html page nd run it works fine
View 6 Replies
Feb 17, 2011
i have a store procedure that load data in a table of SQL SERVER 2008 from an excel file.the issue is, that the excel file have a column of DATE type, and i want that the system interprets the format in it, no matter what comes in it.dd/MM/yyyy mm/dd/yyyyi have an argue about this with my parnerts, cause i think is imposible, if you don't have a format before the process runs.
View 3 Replies
Mar 20, 2013
I tried simple code like this using href element. But it tries to open the sheet.xls in the browser window and says it can't find the file. I want the excel file to be opened in excel (not browser).
<a href="file://domainname/vol1/destinationfiles/sheet.xls" >some excel file</a>
View 1 Replies
May 17, 2010
Excel cannot open the file "Report.xlsx" because the file format or extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file
View 2 Replies
May 19, 2010
this is me once again moving around data access. my problem now hide in the import process of an excel file that would be inserted into sql db.
the problem is the excel file may be updated or changed and i have to periodically import it and perform the process but I still cant check or compare between the two Data Source (excel, sql) as I violate the entity integrity (PK)
i found a kind of stupid approach that each time i want to import the file i will empty the table then re-insert the data, but at this step a new problem raise that other tables are !engaged! with it ... so i will violate the referential integrity (FK)
i looking for solution to compare between two sources for each tuple / row, ignore the existing one and insert the new
[Code]....
View 2 Replies
Apr 26, 2010
I have tried using GridView control to display the data from SqlDataSource control but there are just so many columns and one of the fields contains a lot of texts so using GridView control to display the data does not look look since it goes out of the master template. I have tried using GridView control to display my data and use the following code to export as Excel but in case, there are just so many columns.
[Code]....
What are some other better ways to export to Excel file from my web page?
View 4 Replies
Oct 6, 2010
I have an excel file created at this link:
<a href=""Exports/" & fileName & """></a>
how to open it in a button click, using asp.net / vb.net as codebehind.
View 3 Replies
Nov 10, 2010
I have a couple of queries that I need to run one to a linked server and one not like this
Dim InvestorLookup As String = "DECLARE @investor varchar(10), @linkedserver varchar(25), @sql varchar(1000) "
InvestorLookup += "SELECT @investor = '" & investor & "', @linkedserver = '" & db & "', "
InvestorLookup += "@sql = 'SELECT * FROM OPENQUERY(' +@linkedserver + ', ''SELECT * FROM db WHERE investor = ' + @investor + ' '')' EXEC(@sql)"
Dim queryInvestorLookup As SqlCommand = New SqlCommand(InvestorLookup , conn)
Dim BondNoDR As SqlDataReader = queryInvestorLookup.ExecuteReader()
Dim PasswordCheck As String = "DECLARE @investor varchar(10), @password varchar(20), @linkedserver varchar(25), @sql varchar(1000) "
PasswordCheck += "SELECT @investor = '" + investor + "', @password = '" + password + "', @server = '" + db2 + "', "
PasswordCheck += "@sql = 'SELECT * FROM @server WHERE investor = @investor AND password = ' + @password + ' '' EXEC(@sql)"
Dim queryPasswordCheck As SqlCommand = New SqlCommand(PasswordCheck, conn)
Dim PasswordDR As SqlDataReader = queryPasswordCheck.ExecuteReader()
As far as I can tell from debugging the queries both run as they should but I get the error There is already an open DataReader associated with this Command which must be closed first. Is it possible to run two queries in two different DataReaders. I need to later reference each DataReader and select values from each.
View 3 Replies
Jan 29, 2010
I would now like to check if my code is still open to SQL Injections after this work. I believe the code is now working as it should, but any blinding errors that you see i'd love to hear about too. My code is now looking like:
-code removed-
View 5 Replies
Mar 31, 2010
I have code to export the grid to excel . i have taken it from [URL] now i want to save excel file directly without asking user to save it or open it at client side. i want to save it on server folder. how to do that .
View 2 Replies
Dec 12, 2010
when i upload a file i need to insert the data into sql server data (file will be in comma separated with different extension .ord) in vb.net file will be stored on server in a folder
View 3 Replies
May 8, 2010
I want to load the data from an EXCEL 2007 file to SQL Server 2008 database. I am geting following error.
The Microsoft Office Access database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.
Following is the complete source code. Kindly any senior member guide me what is the problem in the following code.
[Code]....
View 1 Replies
Jan 1, 2010
How to port the data of a SQL Server table to a excel file?
View 8 Replies
Aug 20, 2012
i want to export an excel from my web application and want to save it on Web Server, but i am not able to save it on web server, i am able to do it on my local machine, but on web server it's not working.
View 1 Replies
