Sql insert problem with double type numbers!

[Gulredy]

Trying to upload a database from an excel file.
All is going well until it gets to a value which is given in this format : 312,45
Every other values are integer values so the problem only comes out when it reaches a double value.

So it works like this:
I have my variables which are containing the numbers dim-ed as double.
Then the program with some loops and ifs it inserts the value to the sql database.
Every time I insert a whole line so every field have its own variables in excel in order to give them to sql.

The part of the code which does the sql insert:

    Dim v_irodanev, v_valutaneve As String    Dim v_eladas As Double
    Dim v_vetel As Double
    Dim i As Integer
    Dim k As Integer
    
    i = 2
    Do While ws.Range("A" & i).Value <> ""  
        If ws.Range("A" & i).Value <> "***" Then    
            If ws.Range("A" & i).Value = "#" Then    
                v_irodanev = ws.Range("B" & i).Value   
                   
                    Else
                   
                    v_valutaneve = ws.Range("A" & i).Value 'itt megkapjuk a valuta nevét
                    v_vetel = ws.Range("B" & i).Value
                    v_eladas = ws.Range("C" & i).Value
                    strSQL = "INSERT INTO arfolyam (irodanev,valutanev,eladas,vetel,datum)"
                    strSQL = strSQL & "VALUES (""" & v_irodanev & """,""" & v_valutaneve & """,""" & v_eladas & """,""" & v_vetel & """,'" & datumido & "')" 


                    Debug.Print strSQL  
                    cn.Execute strSQL, adExecuteNoRecords
                    
            End If
        End If
    i = i + 1


    Loop

So the problem occurs when it reaches a value in the excel file which is not integer but double (so its not in the format like the other numbers but in this format: 123,13 with comma in it) and it reaches the cn.Execute strSQL part of the code.

The error it gives is:
Run-time error '2147467259 (80004005)
Automation error


I've had an error like this before, but I've solved it with changing the apostrophes in the secont strSQL!

Also in the sql table I have those fields which contains the numbers declared as double.
So I don't know what could cause the problem.

Is there anyone who can help?
If something is not clear I'll explain it more, just ask!

 

[Gulredy]

If I rewrite the format of the number which should be uploaded to the sql database from 123,12 to 123.12
Then it writes:
Run-time error '13':
Type mismatch

 

[Norie]

You are putting quotes round all the values you are inserting.

The first 3 values are enclosed in double quotes and the 4th single quotes.

Only text data should have quotes around it.

Are all the values text?

 

[Gulredy]

You are putting quotes round all the values you are inserting.

The first 3 values are enclosed in double quotes and the 4th single quotes.

Only text data should have quotes around it.

Are all the values text?

No, not all the values are text! Those which are defined double, are containing numbers, and those which are defined string they contains string, and theres and at the last which called datumido contains date which is defined as string.

By the way, what you said helped, but I had to define those variables which contains numbers from double to Variable, because I always had a Type missmatch error.

 

[Gulredy]

Now I have another problem, at another part of the code.

I'll paste it but it nearly the same as the one in my first comment:

...
    Dim valuta_nev As String
    Dim jav_vetel As Variant
    Dim jav_eladas As Variant
...

 k = 2
    Do While ws.Range("A" & k).Value <> ""
        If ws.Range("A" & k).Value <> "***" Then
            If ws.Range("A" & k).Value = "#" Then
            
            Else
               If Replace(CStr(ws.Range("D" & k).Value), " ", "") <> "" Or Replace(CStr(ws.Range("E" & k).Value), " ", "") <> "" Then 
            
            v_valutaneve = ws.Range("A" & k).Value
            'If k = 17 Then
            'MsgBox ("W" & ws.Range("D" & k).Value & "W")
            'End If
            jav_vetel = ws.Range("D" & k).Value
            jav_eladas = ws.Range("E" & k).Value
            strSQL = "INSERT INTO javasolt (valuta_nev,javasolt_vetel,javasolt_eladas)"
            strSQL = strSQL & "VALUES (""" & v_valutaneve & """," & jav_vetel & "," & jav_eladas & ")"
            Debug.Print strSQL
            cn.Execute strSQL
                
                End If
            End If
        End If
     k = k + 1
    Loop

What it does (or it should) in short:

2012.08.03. 11:11
#	nameofoffice1
currencyname1	225
currencyname2	226	236		3,14
***
#	nameofoffice2
currencyname1	227	237		13
currencyname2	228	238	4,2
currencyname3	229
***

<tbody>
</tbody>

So heres an excel table from which it works:
What the code does, it export the 3rd and 4th columns to an sql table. As you can see there are cells which contains no value, some contain integer number and some contains double.
What it should do to export only those lines with the currencynames which contains a value either in the 3rd or the 4th column.

There's no problem when it reaches a row with no value in 3rd and 4th column, but when it reaches a row which contains value in only one (either in the 3rd or 4th column).

What happens at that time in debug:
The jav_vetel contains the value from the actual row (named "k") and "D" column.
The jav_eladas contains the value from the actual row(named "k") and "E" column.
But when theres no, value in one of those cells, it gives a space (" ") character to the value of the variable, which causes an error in process when it executes the sql command! (gives automation error)

In the sql table it has been set, that when no value is received it gives NULL (so nothing will be written in) but it cant handle the space when it expects a number!


How can I solve this? I've tried everything I had in my mind as a solution!

 

[Norie]

Again there seems to be a lot of quotes in the SQL.

Are you sure they are needed?

 

[Gulredy]

(""" & v_valutaneve & """," & jav_vetel & "," & jav_eladas & ")"

I think they are needed but correct me if I'm wrong:
the first 3 quotes (in pair its six) needed becuse v_valutaneve is a string, after the first comma the jav_vetel is a variant (but it should be double but it works only in this datatype) so theres only one pair of quote because if I don't put them there it only gives to the sql the name of the variable! For example jav_vetel will be put into sql (or at least it will try to put that, but it cant because it expects double in sql)
The same as jav_eladas as in jav_vetel its too contains a number.