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


Top 5 Contributors of the Month
david stephan

Home >> Forum >> C# >> Post New QuestionBookmark and Share Subscribe to Forum

excel to table update prefix zero problem

Posted By: Catherine     Posted Date: June 20, 2011    Points:5   Category :C#
Hi i upload my excel data to database table. in my table i have account number. when account number upload i did not take prefix zero. when i update rows affected but no change in my table. here is my procedure. i set account field in table as numeric and in excel as number. when i change this i get scientific format in my table that is come as 343e+66 like that. what can i do


ALTER PROCEDURE [dbo].[exceltable]
@SheetName varchar(20),
@FilePath varchar(100),
@HDR varchar(3),
@TableName varchar(50)
AS
BEGIN
DECLARE @SQL NVARCHAR(1000)


delete from tbltemp



SET @SQL = 'INSERT INTO ' + @TableName + ' SELECT * FROM OPENDATASOURCE'

--UPDATE tbltemp SET BankAcc_No='00' + BankAcc_No WHERE LEN(BankAcc_No)=12

--UPDATE tbltemp SET BankAcc_No='0' + BankAcc_No WHERE LEN(BankAcc_No)=13




SET @SQL = @SQL + '('Microsoft.Jet.OLEDB.4.0','Data Source='

SET @SQL = @SQL + @FilePath + ';Extended Properties='Excel 8.0;HDR='

SET @SQL = @SQL + @HDR + ';IMEX=1'')...['

SET @SQL = @SQL + @SheetName + ']'
print @SQL

EXEC sp_executesql @SQL

END

Responses
Author: Dhiraj Ranka             
Posted Date: June 23, 2011     Points: 20   


Post Reply

You must Sign In To post reply
 
 
Find more Forum Questions on C#, ASP.Net, Vb.Net, SQL Server and more Here
Quick Links For Forum Categories:
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  OOPs  SilverlightIISJQuery
JavaScript/VBScriptBiztalkWPFPatten/PracticesWCFOthers
www.DotNetSpark.comUnAnsweredAll

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