Hello,
I am trying to insert some values into my database. This database contains a table which has 2 fields in it with data-type 'double'.
Now I am trying to insert my numeric value (with 3 decimals) into this double-field with the following:
Dim rs As Object
Dim strSQL As String
Dim ConnectionString As String
Dim artikelcode As String
Dim ldbLinkp_verp As Double
Dim ldbLverkp_verp As Double
'
'
Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
For n = 2 To Excel.Sheets("Importbestand").UsedRange.Rows.Count
If IsEmpty(Excel.Sheets("Importbestand").Range("D" & n).Value) = False Then
'
datum = Format(Now(), "yyyy-mm-dd")
' Inkoopprijs als datatype 'double'
ldbLinkp_verp = CDbl(thisSheet.Range("F" & n).Value)
' Artikelcode als string
artikelcode = thisSheet.Range("D" & n).Value
' String met connectiegegevens
ConnectionString = "Driver=Pervasive ODBC Client Interface;ServerName=server1; dbq=@db1"
' Query inkoopprijs
strSQL = "INSERT INTO arprsw (artcode,crdnr,datum,user_id,verkp_verp,inkp_verp) VALUES ('" & artikelcode & "',' " & Excel.Sheets("Importbestand").Range("A" & n).Value & "','" & datum & "','JOOP',-1," & ldbLinkp_verp & ")"
' Query inkoopprijsdatum
strSQL2 = "UPDATE artbst SET u_datuminkoop = '" & datum & "' WHERE RTRIM(artcode) = '" & Trim(artikelcode) & "'"
With conn
.Open ConnectionString 'Open connection.
Set rs = .Execute(strSQL)
End With
conn.Close
As you can see I am converting the numeric value from the cell to a double value and then insert it in my database.
I get the following query-string;
INSERT INTO arprsw (artcode,crdnr,datum,user_id,verkp_verp,inkp_verp) VALUES ('1012.0290',' 570','2008-08-13','JOOP',-1,3,85)
As you can see the variabel 'ldbLinkp_verp' contains the value '3,85' which makes the database see this as 2 values.
By this I get the following error which I just can't seem to resolve;
"Insert value does not match column list"
Can anybody help me with this issue?
Bookmarks