Insert Double value via SQL to database
Hi guys,
I am using vb.net and access 2010 database.
I am using an insert statement to send a double value from a grid-view cell to an access database.
The cell contains a double value.
The problem is i am able to send the values with a decimal next to every number which has two zeros following it:
Example:
10.00 , 25.00 , 100.00 (i.e. when i input a number with the decimal point and the following numbers with two consecutive zeros, there is no problem in sending the values to the database using the sql.)
but when i put in any number other than the two zeros after the decimal point such as 10.12, 10.52, 52.41, then i get the following error from the try catch block.
number of query values and destination fields are not the same
this is the way i am using the sql:
dim dblCashIn as double
sql = insert into tblBank (cashInCome) values (dblCashIn);
and i use the executenonquery command to insert the data into the database.
Thanks in advance.
I am using vb.net and access 2010 database.
I am using an insert statement to send a double value from a grid-view cell to an access database.
The cell contains a double value.
The problem is i am able to send the values with a decimal next to every number which has two zeros following it:
Example:
10.00 , 25.00 , 100.00 (i.e. when i input a number with the decimal point and the following numbers with two consecutive zeros, there is no problem in sending the values to the database using the sql.)
but when i put in any number other than the two zeros after the decimal point such as 10.12, 10.52, 52.41, then i get the following error from the try catch block.
number of query values and destination fields are not the same
this is the way i am using the sql:
dim dblCashIn as double
sql = insert into tblBank (cashInCome) values (dblCashIn);
and i use the executenonquery command to insert the data into the database.
Thanks in advance.
Nasir Razzaq
What is the locale/culture of the computer?
ASKER
dutch and english
And the column type of the MS Access table is set to double also??? For instance it wouldn't work if it was set to a number of type Integer...
ASKER
it was set to currency from the beginning
Is the separator , or . on the system locale?
ASKER
at first it was ,
but then i changed it to engish keyboard, but even after that it didnt work
but then i changed it to engish keyboard, but even after that it didnt work
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
As long as the column is set to the proper amount of digits to the right of the decimal point, you should be able to insert the values, can you set the variable as currency from the beginning? If so, it should work for you then.
If not, then perhaps you need to convert the double values to currency first...
The following link has some info on those functions.
http://office.microsoft.com/en-us/access-help/type-conversion-functions-HA001229018.aspx
If not, then perhaps you need to convert the double values to currency first...
The following link has some info on those functions.
http://office.microsoft.com/en-us/access-help/type-conversion-functions-HA001229018.aspx
Have you considered creating a parameterized query? It should take care of converting the value for Access on your behalf.
For example:
For OleDB, you use a question mark as a placeholder for each parameter you want to add. When you add parameters to the Command object, add them in the order they would naturally appear within the query (left-to-right).
For example:
Using con As New OleDbConnection("[your con string]")
Using cmd As new OleDBCommand("insert into tblBank (cashInCome) values (?)", con)
cmd.Parameters.AddWithValue("@someArbitraryName", dblCashIn)
con.Open()
cmd.ExecuteNonQuery()
End Using
End UsingFor OleDB, you use a question mark as a placeholder for each parameter you want to add. When you add parameters to the Command object, add them in the order they would naturally appear within the query (left-to-right).
The accepted answer cannot be the solution.
It will fail trying to insert the string "dblCashIn" into a the numeric field cashInCome.
You must insert a string expression in the SQL statement, and Str is exactly for this because it always converts the decimal char to a dot:
sql = "insert into tblBank (cashInCome) values (" & Str(dblCashIn) & ");"
/gustav
It will fail trying to insert the string "dblCashIn" into a the numeric field cashInCome.
You must insert a string expression in the SQL statement, and Str is exactly for this because it always converts the decimal char to a dot:
sql = "insert into tblBank (cashInCome) values (" & Str(dblCashIn) & ");"
/gustav