Click here to Skip to main content
15,883,749 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
C#
using System;
using System.Collections;
using System.Configuration;
using System.Data;
//using System.Data.OleDb; //This namespace is mainly used for dealing with 
//Excel sheet data
using System.Data.SqlClient;
//using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
//using System.Xml.Linq;
using System.IO;



public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void insertdata_Click(object sender, EventArgs e)
    {
        
        SqlConnection oconn = new SqlConnection("Data Source=d:\website1\example.xls; Initial catalog=master;" + Server.MapPath("Example.xls") + ";Extended Properties=Excel 8.0");
            try
        {

            SqlCommand ocmd = new SqlCommand("select * from Example", oconn);
            oconn.Open();  //Here [Sheet1$] is the name of the sheet 
           
            SqlDataReader odr = ocmd.ExecuteReader();
            string fname = "";
            string lname = "";
            string city = "";
            
            while (odr.Read())
            {
                fname = valid(odr, 0);//Here we are calling the valid method
                lname = valid(odr, 1);
                city = valid(odr, 3);
                insertdataintosql(fname, lname, city);
            }
            oconn.Close();
        }
        catch (DataException ee)
        {
            lblmsg.Text = ee.Message;
            lblmsg.ForeColor = System.Drawing.Color.Red;
        }
        finally
        {
            lblmsg.Text = "Data Inserted Sucessfully";
            lblmsg.ForeColor = System.Drawing.Color.Green;
        }

    }
    public void insertdataintosql(string fname, string lname,string city)
    {
       
       SqlConnection oconn = new SqlConnection("Data Source=d:\website1\example.xls; Initial catalog=master;" + Server.MapPath("Example.xls") + ";Extended Properties=Excel 8.0");
        
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = oconn;
        cmd.CommandText = "insert into Example(fname,lname,city)values(@fname,@lname,@city)";
        cmd.Parameters.Add("@fname", SqlDbType.NVarChar).Value = fname;
        cmd.Parameters.Add("@lname", SqlDbType.NVarChar).Value = lname;
        cmd.Parameters.Add("@city", SqlDbType.NVarChar).Value = city;
        cmd.CommandType = CommandType.Text;
        oconn.Open();
        cmd.ExecuteNonQuery();
        oconn.Close();
    }
    protected string valid(SqlDataReader myreader, int stval)//if any columns are 
    {
        object val = myreader[stval];
        if (val != DBNull.Value)
            return val.ToString();
        else
            return Convert.ToString(0);
    }
}

When I run this code I got the error: Unrecognized escape sequence

The error is show in the following line:
C#
SqlConnection oconn = new SqlConnection("Data Source=d:\website1\example.xls; Initial catalog=master;" + Server.MapPath("Example.xls") + ";Extended Properties=Excel 8.0");

Please help me and solve my error.
Posted
Updated 30-Dec-12 6:15am
v2

To add to previous answer another possibility is to use @-quoted strings. In such case your connection string would look like:
C#
SqlConnection oconn = new SqlConnection(@"Data Source=d:\website1\example.xls; Initial catalog=master;" + Server.MapPath("Example.xls") + @";Extended Properties=Excel 8.0");

The @ character in the beginning changes the behaviour of escapes. For more information, see string (C# Reference)[^]
 
Share this answer
 
Comments
Thomas Daniels 30-Dec-12 13:12pm    
+5!
Wendelius 30-Dec-12 13:56pm    
Thanks :D
Jibesh 30-Dec-12 17:04pm    
my +5 and its adds better readability
Wendelius 4-Jan-13 15:11pm    
Thanks :)
Hi,

The problem is here:
C#
SqlConnection oconn = new SqlConnection("Data Source=d:\website1\example.xls; Initial catalog=master;" + Server.MapPath("Example.xls") + ";Extended Properties=Excel 8.0");

and here:
C#
SqlConnection oconn = new SqlConnection("Data Source=d:\website1\example.xls; Initial catalog=master;" + Server.MapPath("Example.xls") + ";Extended Properties=Excel 8.0");

A backslash in a C# string must be written as a double backslash: \\
Read more about escape sequences here:
http://msdn.microsoft.com/en-us/library/h21280bw.aspx[^]
A \\ is the escape sequence for a backslash. Change in your strings all backslashes into \\

Hope this helps.
 
Share this answer
 
Comments
Wendelius 30-Dec-12 12:58pm    
Good answer, 5.
Thomas Daniels 30-Dec-12 13:01pm    
Thank you!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900