Solved

Insert Double value via SQL to database

Posted on 2012-03-28
10
1,793 Views
Last Modified: 2012-03-29
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.
0
Comment
Question by:baabaa_nl
  • 3
  • 3
  • 2
  • +2
10 Comments
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 37779475
What is the locale/culture of the computer?
0
 

Author Comment

by:baabaa_nl
ID: 37779485
dutch and english
0
 
LVL 6

Expert Comment

by:Patrick Tallarico
ID: 37779554
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...
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:baabaa_nl
ID: 37779563
it was set to currency from the beginning
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 37779565
Is the separator , or . on the system locale?
0
 

Author Comment

by:baabaa_nl
ID: 37779572
at first it was ,

but then i changed it to engish keyboard, but even after that it didnt work
0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 500 total points
ID: 37779579
Try enclosing the value in single quotes

sql = insert into tblBank (cashInCome) values ('dblCashIn');
0
 
LVL 6

Expert Comment

by:Patrick Tallarico
ID: 37779580
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
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 37780008
Have you considered creating a parameterized query? It should take care of converting the value for Access on your behalf.

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 Using

Open in new window


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).
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 37780742
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
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
FrontEnd tools to create web database application 7 54
Write to a printer using vb.net 9 21
SQL Insert parts by customer 12 31
Help with DoEvents 8 25
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question