Click here to Skip to main content
12,397,610 members (59,192 online)
Rate this:
 
Please Sign up or sign in to vote.
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:
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 30-Dec-12 6:12am
Updated 30-Dec-12 6:15am
v2
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Hi,

The problem is here:
SqlConnection oconn = new SqlConnection("Data Source=d:\website1\example.xls; Initial catalog=master;" + Server.MapPath("Example.xls") + ";Extended Properties=Excel 8.0");
and here:
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.
  Permalink  
Comments
Mika Wendelius 30-Dec-12 12:58pm
   
Good answer, 5.
ProgramFOX 30-Dec-12 13:01pm
   
Thank you!
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

To add to previous answer another possibility is to use @-quoted strings. In such case your connection string would look like:
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)[^]
  Permalink  
Comments
ProgramFOX 30-Dec-12 13:12pm
   
+5!
Mika Wendelius 30-Dec-12 13:56pm
   
Thanks :D
jibesh 30-Dec-12 17:04pm
   
my +5 and its adds better readability
Mika 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)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160721.1 | Last Updated 30 Dec 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100