Hi Cetin, thanks for your very informed reply.

On thinking about it, this is a once off update requirement only, and it will probably be easier to do it in VFP.
Can I set up a link to a Sql Express table in VFP ?
(and populate records , including Primary Keys)

Regards,
Gerard





>>Hi .
>>I have a sql database set up and want to import data from an excel file.
>>In VFP, I would just do:
>>Use MyDbf
>>Appe from MyXls type XLS
>>
>>Anybosy know what is the corresponding codw in C#,Sqlexpress
>>i.e. I have a Sql express table and I want to import an XLS file.
>>
>>Any help appreciated.
>>Regards,
>>Gerard
>
>To do that you can either directly use SQL server T-SQL or C# code.
>T-SQL:
>

>insert myTable ( fieldlist )
>  select ... from openrowset(...)
>
>or:
>
>insert myTable ( fieldlist )
>  select ... from opendatasource(...)...sheetname$
>

With SQL 2005 openrowset and opendatasource are disabled by default. You should enable it first (configurator). You can search for samples here in old messages.
>
>With C# code:
>-Create an OLEDB connection to excel
>-Get a reader from excel
>-Create an SQL conection to target SQL table
>-Create a bulk copy class
>-Adjust column mappings
>-Write to server
>ie:
>

>using System;
>using System.Data;
>using System.Data.OleDb;
>using System.Data.SqlClient;
>
>class xlAppendFromSample
>{
>   static void Main()
>   {
>    string strSource = "Provider=Microsoft.Jet.OLEDB.4.0;"+
>"Data Source=c:\\temp\\sample.xls;Persist Security Info=False;Extended Properties=\"Excel 8.0\"";
>    string strTarget = "Data Source=.\\SQL2005;Initial Catalog=test;Integrated Security=True";
>
>    OleDbConnection conSource = new OleDbConnection(strSource);
>    OleDbCommand cmd = new OleDbCommand("select * from [sheet1$]", conSource);
>    conSource.Open();
>    OleDbDataReader rdr = cmd.ExecuteReader();
>    SqlConnection conTarget = new SqlConnection(strTarget);
>    SqlBulkCopy cpy = new SqlBulkCopy(conTarget);
>    conTarget.Open();
>    cpy.DestinationTableName = "dbo.Location";
>    cpy.ColumnMappings.Add(0, "[Name]");
>    cpy.ColumnMappings.Add(1, "[CostRate]");
>    cpy.ColumnMappings.Add(2, "[Availability]");
>    cpy.ColumnMappings.Add(3, "[ModifiedDate]");
>    cpy.WriteToServer(rdr);
>    conTarget.Close();
>    rdr.Close();
>    conSource.Close();
>    cpy.Close();
>  }
>}
>

>PS:Test.dbo.Location is a slightly modified copy of AdventureWorks.Production.Location.
>In excel sheet column order was same as those fields so 0 maps to name, 1 maps to CostRate etc.
>Cetin