How to Update Excel data using asp.net

Question

I have an Excel workbook which contains data in different sheets. I want to update excel workbook using asp.net.

In asp.net, I am using following query to update excel data:

OdbcConnection con = new OdbcConnection(ConfigurationManager.ConnectionStrings["SportTech"].ConnectionString);
OdbcCommand cmd;
con.Open();

string name = "sandeep";
string pos = "1";
string time = "11";
string point = "70";

string query = "UPDATE [Sheet1$] SET POSITION=\"" + pos + "\",Time=\"" + time + "\",Point=\"" + point + "\" WHERE NAME=" + name;

cmd = new OdbcCommand(query,con);
string x = cmd.ExecuteNonQuery().ToString();
MessageBox.Show(x.ToString());

but the query is showing error

ERROR [42000Ձ] [Microsoft][ODBC Excel Driver] Syntax error in UPDATE statement.

Thanks!

Answer 1

Put excel file into App_Data foldet in your site.

You can try this:

string name = "sandeep";
string pos = "1";
string time = "11";
string point = "70";
string conStr = @"Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" +
@"DBQ=|DataDirectory|\SportTech.xls;ReadOnly=0;";
OdbcConnection con = new OdbcConnection(conStr);
con.Open();
string query = "update [Sheet1$] set [POSITION]=?,[Time]=?,[Point]=? where [NAME]=?";
OdbcCommand cmd = new OdbcCommand(query, con);
cmd.Parameters.AddWithValue("?", pos);
cmd.Parameters.AddWithValue("?", time);
cmd.Parameters.AddWithValue("?", point);
cmd.Parameters.AddWithValue("?", name);
cmd.ExecuteNonQuery();
con.Close();