Jan,
How has the table been created?
When you do the "create table", you can specify the datatypes.
Eg,
string strConnect=
"Provider=Micro soft.Jet.OLEDB. 4.0;" +
"Data Source=" + Filename + ";" +
"Extended Properties=\"Ex cel 8.0;HDR=yes;\"" ;
System.Data.Ole Db.OleDbConnect ion conn = new
System.Data.Ole Db.OleDbConnect ion(strConnect) ;
string strSql = "CREATE TABLE SampleTable ( ix NUMBER, CustName
char(255), Stamp datetime )";
System.Data.Ole Db.OleDbCommand cmd= new
System.Data.Ole Db.OleDbCommand (strSql, conn);
conn.Open();
cmd.ExecuteNonQ uery();
conn.Close();
then you can insert as you like, eg:
string strSql = "insert into [SampleTable] ([ix],[CustName],[Stamp])
values(@p1,@p2, @p3)";
System.Data.Ole Db.OleDbCommand cmd= new
System.Data.Ole Db.OleDbCommand (strSql, conn);
System.Random r= new System.Random() ;
cmd.Parameters. Add("@p1", System.Data.Ole Db.OleDbType.Nu meric).Value =
r.Next(42);
cmd.Parameters. Add("@p2", System.Data.Ole Db.OleDbType.Va rChar).Value =
"Some text";
cmd.Parameters. Add("@p3", System.Data.Ole Db.OleDbType.Da te).Value =
System.DateTime .Now;
conn.Open();
cmd.ExecuteNonQ uery();
conn.Close();
If the document already exists and you do not get a chance to explicitly
create the table (and specify datatypes at that time), then I don't know
what you need to do. At one point the driver was set up to inspect the
first 8 rows of the data and use a heuristic to set the datatypes for the
columns, but I don't know if that is still true.
-Dino
"Jan Agermose" <ja*@topix.dk > wrote in message
news:%2******** ********@TK2MSF TNGP09.phx.gbl. ..
Im writing information into an existing excel document using a connection
string like:
strConn = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" + Filename +
";Extended Properties=\"Ex cel 8.0;HDR=yes;\"" ;
and one of to methods for inserting data. First I simply tried building
insert statements as strings
"insert into [Ark1$] ([MyText], [MyInt]) values ('Some text', 42)"
And I also tried
com = OleDbCommand(in sert into [Ark1$] ([MyText], [MyInt]) values (?,?));
com.Parameters. Add("@MyText", OleDbType.VarCh ar).Value = "test";
com.Parameters. Add("@MyText", OleDbType.Int). Value = 42;
Now both methods do insert the values into the excel, but there is one
very big problem. When you open the excel document, the int values are inserted
as text! Excel markes the column with an warrning and this allowes you to
convert the "text" into prober integer values. The real problem is, that
if you fail to make this conversion, and enter/alter data in the "int-column"
you will be unable to load the excel data using an OleDbCommand("s elect *
from [Ark1$]");
Does anyone know a solution - or a nother newgroup to post to, if this is
not the place?
Jan Agermose
