Search code examples
sqlsql-servervbasqldatatypes

Preserving data format Decimal(6,5) from vba to sql

I am moving data from Excel to SQL using VBA and Stored procedures. One of my fields Sensor_Measure is of data type Decimal(6,5) in SQL. The stored procedure is like this: 

Alter Procedure dbo.SP_Sensor  
(  
@Name varhchar(4),    
@Sensor_D varchar(10)  
)  
Declare  
@iSensor Decimal(6,5)  
@iSensor=@Sensor  
Insert into dbo.SensorTble Values(@Name,@iSensor )

My VBA code is like this: 

Dim Name, Sensor as String  
Dim oName, oSensor as ADODB.Parameter  

Name=ws.Cells(2,3)  
Sensor=ws.Cells(4,5)   
Set oSensor = cmd.CreateParameter("@Sensor_D", adVarChar, adParamInput, 30, Sensor)
        cmd.Parameters.Append oSensor  
cmd.Execute

In the excel workbook the data point for Sensor is

0.000121778

but when it goes to sql it looks like this

1.21778

What I should be looking at is

0.00012

. Where am i going wrong.

Solution

  • Excel was sending 1.223523515E-4 to SQL and SQL didnt recognize the E-4 part and was giving me 1.22. So I did this in Excel: 

    SENSOR_ = Format(ws.Cells(4, 5), "0.######")

    This fixed my problem