0

I'm using this code for insert from Excel to SQL. I want every insert in my query to have comma replaced by dot.

SqlCommand cmd2 = new SqlCommand("insert into Market (Barcode,Name,Code,TemporaryStock,VatcodeID,Unit,NetValue,DiscountValue,UnderlyingValue,VatValue,TotalValue) 
select * from OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=" + textBox1.Text + "', 'SELECT * FROM [datasheet1$H:R]')", con);

cmd2.ExecuteNonQuery();

Should I use global System.Globalization.CultureInfo?

DRKblade
  • 347
  • 2
  • 13
user6927546
  • 33
  • 1
  • 2
  • 7
  • Can you elaborate a bit? Which comma do you mean? Something in the resultset of the other query? – Crusha K. Rool Oct 16 '16 at 14:21
  • You can use `REPLACE()` function of SQL Server for each field ;). – shA.t Oct 16 '16 at 14:21
  • No, that would most likely be part of the `Microsoft.ACE.OLEDB.12.0` settings http://stackoverflow.com/questions/17027552/reading-excel-files-in-a-locale-independent-way – Slai Oct 16 '16 at 14:26

1 Answers1

-1

For replacing chars/strings in another string use the string.Replace method.

string str = "insert into Market(Barcode,Name,Code,TemporaryStock,VatcodeID,Unit,NetValue,DiscountValue,UnderlyingValue,VatValue,TotalValue) select * from OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=" + textBox1.Text + "', 'SELECT * FROM [datasheet1$H:R]')";

SqlCommand cmd2 = new SqlCommand(str.Replace(',', '.'), con);
cmd2.ExecuteNonQuery();

You can find detailed info about it here.

Marian Dolinský
  • 3,312
  • 3
  • 15
  • 29