error in connection string

Question

Hi,
when i run this progarm, after entering values and while clicking save button, i am getting the following error
 
An OLE DB Provider was not specified in the ConnectionString. An example would be, Provider=SQLOLEDB;
Can someone please help me to resolve it...the code is given below..
 
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.OleDb;
 
namespace UItoExcelDuplicate
{
public partial class _Default : System.Web.UI.Page
{
private DataTable _dt;
public DataTable dt
{
get
{
return _dt;
}
set
{
_dt = value;
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
DataTable dt = new DataTable();
dt.Columns.Add("CodeID", typeof(string));
dt.Columns.Add("Content", typeof(string));
dt.Columns.Add("Mapping Code", typeof(string));
Session["dt"] = dt;
}
_dt = (DataTable) Session ["dt"];
}
private void BindGrid()
{
GridView1.DataSource = dt;
GridView1.DataBind();
}
 
protected void btnSave_Click(object sender, EventArgs e)
{
string con = "Microsoft.ACE.OLEDB.12.0;Data Source=C:/Users/323493/Desktop/test.xlsx;Extended Properties='Excel 12.0 Xml;HDR=YES'";
OleDbConnection obj = new OleDbConnection(con);
obj.Open();
OleDbCommand cmd = new OleDbCommand("Insert into [Sheet1$]values('" + txtCodeID.Text + "','" + txtContent.Text + "','" + txtMappingCode.Text + "')", obj);
cmd.ExecuteNonQuery();
//MessageBox.Show("Inserted");
obj.Close();
}
 
protected void btnSubmit_Click(object sender, EventArgs e)
{
DataTable dt = (DataTable)Session["dt"];
DataRow dr = dt.NewRow();
dr["CodeID"] = txtCodeID.Text;
dr["Content"] = txtContent.Text;
dr["Mapping Code"] = txtMappingCode.Text;
 
dt.Rows.Add(dr);
GridView1.DataSource = dt;
GridView1.DataBind();
BindGrid();
txtCodeID.Text = txtContent.Text = txtMappingCode.Text = string.Empty;
}
 
}

}
 

Please help me..
 
thanks in advance..

---

Thanks, Anusha

Answer 1

your connection string should start with "Provider=

That will fix your problem.

If you have connection string problems this site is a great source of help:

www.connectionstrings.com





---

Digital Forensic Software Developer
CCS LABS Digital Forensic Software
Mark as Answer or Vote up if useful thank you!

Answer 2

hey. ya. thanku.. :)

but now, i am getting an error like "Number of query values and destination fields are not the same c#"... can you give me a solution pls..

---

Thanks, Anusha

Answer 3

hey. ya. thanku.. :)

but now, i am getting an error like "Number of query values and destination fields are not the same c#"... can you give me a solution pls..

---

Is that because you're insert clause is wrong. You pass one value to insert(Sheet1$) and try to insert 3 values. if put something like, will work

OleDbCommand cmd = new OleDbCommand("Insert into [Sheet1$]values('" + txtCodeID.Text + "')", obj);

Otherwise, if you really need to insert those 3 values, instead using commas, to separete then, you can concatenate each of those values.

---

Web Developer

Answer 4

check the below syntex, you may need to separate each values with braces() and then comma

string con = "Microsoft.ACE.OLEDB.12.0;Data Source=C:/Users/323493/Desktop/test.xlsx;Extended Properties='Excel 12.0 Xml;HDR=YES'";
OleDbConnection obj = new OleDbConnection(con);
obj.Open();
OleDbCommand cmd = new OleDbCommand("Insert into [Sheet1$] values ('" + txtCodeID.Text + "'),('" + txtContent.Text + "'),('" + txtMappingCode.Text + "')", obj);
cmd.ExecuteNonQuery();
//MessageBox.Show("Inserted");
obj.Close();

regards

joon

Answer 5

check the below syntex, you may need to separate each values with braces() and then comma

string con = "Microsoft.ACE.OLEDB.12.0;Data Source=C:/Users/323493/Desktop/test.xlsx;Extended Properties='Excel 12.0 Xml;HDR=YES'";
OleDbConnection obj = new OleDbConnection(con);
obj.Open();
OleDbCommand cmd = new OleDbCommand("Insert into [Sheet1$] values ('" + txtCodeID.Text + "'),('" + txtContent.Text + "'),('" + txtMappingCode.Text + "')", obj);
cmd.ExecuteNonQuery();
//MessageBox.Show("Inserted");
obj.Close();

Just to add to Joon84 posts, dont forget to start your connection string with Provider=

---

Web Developer

Answer 6

Hii,

Thanks for the reply. :)

Everything is working fine..
 
But I am getting a run time error as
"Missing semicolon (;) at end of SQL statement." in the line cmd.ExecuteNonQuery();

Can you please help me to identify what it is.. Since I am a beginner, Ineed some help..
 
Thanks a lot.

---

Thanks, Anusha