Click here to Skip to main content
15,867,686 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

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!
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 :)

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