CODEWITHSUNDEEP
c#asp.netexcel
Samiey Mehdi
Samiey Mehdi

Reputation: 9424

How to insert query to Excel by ODBC connection in ASP.NET?

I have an excel file like this:

enter image description here

I have tow TextBox(Fname,Lname) , tow Button(Search,Insert) and one GridView to show result. Search button work fine but Insert button not work.

Error in insert Button is:

ERROR [HY000] [Microsoft][ODBC Excel Driver] Operation must use an updateable query.

ASPX:

Fname:<asp:TextBox ID="txtFname" runat="server"></asp:TextBox><br />
Lname:<asp:TextBox ID="txtLname" runat="server"></asp:TextBox><br />
<asp:Button ID="cmdSearch" runat="server" onclick="cmdSearch_Click" Text="Search" />
<asp:Button ID="cmdInsert" runat="server" onclick="cmdInsert_Click" Text="Insert" /><br />
<asp:GridView ID="GridView1" runat="server"></asp:GridView>

Code behind:

string conStr = @"Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};"+
    @"DBQ=|DataDirectory|\q.xlsx;";
protected void cmdInsert_Click(object sender, EventArgs e)
{
    OdbcConnection con = new OdbcConnection(conStr);
    con.Open();
    string query = "insert into [Sheet1$] (Fname,Lname) values (?,?)";
    OdbcCommand cmd = new OdbcCommand(query, con);
    cmd.Parameters.AddWithValue("?",txtFname.Text);
    cmd.Parameters.AddWithValue("?", txtLname .Text);
    cmd.ExecuteNonQuery();// has error
    con.Close();
}
protected void cmdSearch_Click(object sender, EventArgs e)
{
    OdbcConnection con = new OdbcConnection(conStr);
    con.Open();
    string query = "";
    query = "select * from [Sheet1$] where Fname=? or Lname=?";
    OdbcCommand cmd = new OdbcCommand(query, con);
    cmd.Parameters.AddWithValue("?", txtFname.Text);
    cmd.Parameters.AddWithValue("?", txtLname.Text);
    GridView1.DataSource = cmd.ExecuteReader();
    GridView1.DataBind();
    con.Close();
}

Upvotes: 2

Views: 5765

Answers (2)

V-Lamp
V-Lamp

Reputation: 1678

Using something more structured like LinqToExcel (You will also find it in NuGet), which is really easy to use and uses the ODBC behind the scenes may solve the error or at least narrow down the possible mistakes.

Upvotes: 0

Automate This
Automate This

Reputation: 31364

Per http://support.microsoft.com/kb/316475

make sure you add "ReadOnly=0"

string conStr = @"Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};"+
@"DBQ=|DataDirectory|\q.xlsx;ReadOnly=0;";

Upvotes: 6

Related Questions