[RESOLVED] Insert problem

[popskie]

HI,

I have a problem during inserting my new record but if I change to select statement. I can display the datareader value. Do you have any idea?

        string contring;

        private void Form1_Load(object sender, EventArgs e)
        {
            contring = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Book1.xls;Extended Properties=""Excel 8.0;HDR=YES;IMEX=1""";		
        }

        private void button2_Click(object sender, EventArgs e)
        {            
            DbProviderFactory dpF = DbProviderFactories.GetFactory("System.Data.OleDb");
            {
                using (DbConnection dbCon= dpF.CreateConnection())
                {
                    dbCon.ConnectionString = contring;
                    DbCommand dbCom = dbCon.CreateCommand();                    

                    //dbCom.CommandText = "select * from [sheet1$]";
                    dbCom.CommandText = "insert into[sheet1$](first,second)values(\"unse\",\"unse\")";
                    dbCon.Open();
                    DbDataReader dreader = dbCom.ExecuteReader();
//                    while (dreader.Read())
   //                 {
      //                  MessageBox.Show(dreader["first"].ToString());
         //           }            


                    dbCon.Close();
                }

Thanks,
Popskie

[jmcilhinney]

Firstly, I would question why you're using a DataReader to insert data. While a DataReader can be used to insert data it is usually only done when the insert is followed by a query. As the name suggests, a DataReader is for reading data. If you're not reading any data then don't use one. To execute a non-query SQL statement you call the aptly named ExecuteNonQuery method rather than ExecuteReader.

As for your issue, text values in SQL statements must be enclosed in single quotes. If you want to insert the value "unse" then your code needs to look like this:

dbCom.CommandText = "insert into [sheet1$] (first, second) values ('\"unse\"', '\"unse\"')";

[popskie]

Just ingnore executereader cause it came from a select statement. JM i tried your suggestion but I encounter same error. Another question do I need to enclosed a backslash in every value cause in sql is not?

[jmcilhinney]

What value are you actually trying to insert? Does it include the double quotes or not? If so then you must either escape them as I have above. If the values don't include the double quotes then they shouldn't be there. As I said, SQL encloses text literals in single quotes:

dbCom.CommandText = "insert into [sheet1$] (first, second) values ('unse', 'unse')";

Also, do you have columns named "first" and "second"?

[popskie]

What value are you actually trying to insert? Does it include the double quotes or not? If so then you must either escape them as I have above. If the values don't include the double quotes then they shouldn't be there. As I said, SQL encloses text literals in single quotes:

dbCom.CommandText = "insert into [sheet1$] (first, second) values ('unse', 'unse')";

Also, do you have columns named "first" and "second"?

JM thanks for the reply again. I also try that one before it work if im using MS SQL but in excel it is not and yes I have columns first and second. I can display all the value of this columns using select But if I try to add another record, error raise. I have so many experience in ado.nt programming like access, ms sql, mysql, foxpro. Only in excel I encounter this error. I think there is a property I missing in the connection string.

[jmcilhinney]

Dunno. Never inserted into an Excel sheet using ADO.NET.

[tacoman667]

I didn't know you could use ADO.NET to manipulate Excel worksheets. What else can you use ADO.NET to manipulate?

[jmcilhinney]

I didn't know you could use ADO.NET to manipulate Excel worksheets. What else can you use ADO.NET to manipulate?

Anything you like. If a provider doesn't exist you can write your own. See www.connectionstrings.com for connection string formats for common providers.

[popskie]

Ok JM I try to research more. I mark this as solved.