-1

i am using OleDbCommand for importing CSV when i am using query its rounding the decimal to integer. For Example My Table Data is Below

 SalID  Commission 
    1        2.5
    1        2.5

My Access query is select salID, Comission from table GROUP BY SalID, Commission

Output should be

1 ..... 2.5 but instead its showing output

1.......2

its rounding 2.5 to 2. but when i use simple query without group . it works perfectly its show 2.5 thank you

Allex
  • 141
  • 1
  • 14
  • As is, a _group by_ cannot cause this. Thus, something else you don't tell about is going on. – Gustav Sep 21 '18 at 16:51
  • Yes Your Right . I tested my code many times. and found out. if first row value is in Decimal(2.5) . It works Correct if First Row Value is in Integer(5). it round up the other value in datagridview When Importing From CSV – Allex Sep 21 '18 at 17:24
  • Makes sense. But I don't know how to add and use an _import specification_ with OleDbCommand. Sorry. – Gustav Sep 21 '18 at 17:31

2 Answers2

0

Have you try to add a Cdbl() over the commission field (in group and select clause).

This would help the database engine to determine the type of this field.

Marco Guignard
  • 601
  • 3
  • 9
  • select salID, CDBL(Comission) from table GROUP BY SalID, CDBL(Commission) But its still not working still showing 2.0 insted of 2.5 – Allex Sep 21 '18 at 14:27
  • Okay, as you have said in your comments. It's all about the "guessing type column" engine of jet engine. There are several trick about that. One of the "safest" is forcing the interpretation of all column as string by adding "IMEX=1" in the extended properties of the connection string. But don't forget to cast or convert the column in your futher calculation... – Marco Guignard Sep 24 '18 at 07:40
0

I think if ADO.net is unsure of the type, it samples the first few pieces of data to determine the data type for a particular column. Try explicitly adding the columns to your DataTable before you fill it.

Dim columns As DataColumnCollection = DataSet1.Tables("Orders").Columns
columns.Add("Total", System.Type.GetType("System.Decimal"))
Mary
  • 12,341
  • 3
  • 17
  • 26