Solved

Type Mismatch Error

Posted on 2010-09-09
42
615 Views
Last Modified: 2012-05-10

Experts..

I got half solution from
http://www.experts-exchange.com/Programming/Languages/Q_26452147.html

i want to transfer data from csv to access


I am getting an error "Type Mismatch" and highlighted on

            objRecordSet(columns(i)) = props(i)
        Next

Please check attached code and assist me to solve this problem.

Thanks in advance


Const adOpenStatic = 3
Const adLockOptimistic = 3
Const ForReading = 1

Const MDB_FILE = "D:\Crystal\Auto_Trans\Trans.mdb"
Const TABLE_NAME = "B_Detail"
Const TABLE_COLUMNS = "BNo,Mode,BDate,City,To,"
Const CSV_SEARCH_PATH = "D:\Crystal\A_Trans"

Dim fso As New FileSystemObject
Dim objConnection As New adodb.Connection
Dim objRecordSet As New adodb.Recordset
Dim drive, objWMIService, path, line, objFile, oFile, columns, props, i, colFiles

Set objRecordSet = CreateObject("ADODB.Recordset")

objRecordSet.Open "SELECT * FROM " & TABLE_NAME, _
    con, adOpenStatic, adLockOptimistic

Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\cimv2")

drive = Split(CSV_SEARCH_PATH, "\")(0)
path = "\\" & Replace(Split(CSV_SEARCH_PATH, drive + "\")(1), "\", "\\") & "\\"

Set colFiles = objWMIService.ExecQuery("Select * from CIM_DataFile where Drive='" + drive + "' and path = '" + path + "' and extension = 'csv'")

For Each oFile In colFiles
    
    Set objFile = fso.OpenTextFile(oFile.Name, ForReading)
    
    For Each line In Split(objFile.ReadAll, vbNewLine)
        objRecordSet.AddNew
        columns = Split(TABLE_COLUMNS, ",")
        props = Split(line, ",")
        
        For i = 0 To UBound(columns)
            objRecordSet(columns(i)) = props(i)
        Next
        objRecordSet.Update
    
    Next

Next

objRecordSet.Close
con.Close

Open in new window

0
Comment
Question by:crystalsoft
  • 20
  • 20
  • +1
42 Comments
 
LVL 13

Expert Comment

by:Surone1
ID: 33639208
try
objRecordSet(i).value = props(i)
0
 
LVL 14

Expert Comment

by:VBClassicGuy
ID: 33639262
Try adding:
Dim columns() As String
to the section where your other Dim statements are.
0
 
LVL 1

Author Comment

by:crystalsoft
ID: 33644459
Surone1:
Run Time Error "Type Mismatch"


VBClassicGuy:
Run Time Error "Type Mismatch"
0
 
LVL 13

Expert Comment

by:Surone1
ID: 33646435
are all fields in the table of type text or memo?
can you add a breakpoint and a watch on both props(i) and
objRecordSet(i).value?
maybe even objRecordSet(i).name
0
 
LVL 1

Author Comment

by:crystalsoft
ID: 33646553

yeh in the table some field is number,
 and some field type is date time,
 and some field is text

on breakpoint first field i am getting is number
0
 
LVL 13

Expert Comment

by:Surone1
ID: 33646678
so that is the problem i guess. if you change all of the field types in the table to text the code will probably run fine.
0
 
LVL 1

Author Comment

by:crystalsoft
ID: 33646945

But I cant sir

Database is connecting with other application also
0
 
LVL 13

Expert Comment

by:Surone1
ID: 33647165
o.k. then whe have to figure out what type of data we should write when, and send the right type of data, can we access objRecordSet(i).Type ? if so we can do something like


select case objRecordSet(i).Type
case "text" 'replace by correct number
objRecordSet(i).value = props(i)
case "date" 'replace by correct number
objRecordSet(i).value = cdate(props(i))
case "number" 'replace by correct number
objRecordSet(i).value = cdbl(props(i))
case else
msgbox "type " &   objRecordSet(i).Type & " not handled yet" & vbcrlf & "Data: " & props(i)
end select
0
 
LVL 1

Author Comment

by:crystalsoft
ID: 33656076
Surone1:  I tried Both of methods

Attempt- 1

in the database after changing all fields type to "Text" and tried this code
but its giving me run time error

Run Time Error 2147217887(80040e21)
"The field is too small to accept the amount data you attempted to add. Try inserting or pasting less data."

and on debug its through me on following line

            objRecordSet(columns(i)) = props(i)

one more point is on props on the following line i am getting data like this
            objRecordSet(columns(i)) = props(i)
props(i) = "231456    TPY    10-08-2010    MUMBAI    BANGALORE.....



Attempt- 2
if i am using your suggested following code

        Select Case objRecordSet(i).Type
       
      Case "Number" 'replace by correct number
              objRecordSet(i).Value = CDbl(props(i))
        Case "text" 'replace by correct number
            objRecordSet(i).Value = props(i)
        Case "date" 'replace by correct number
              objRecordSet(i).Value = CDate(props(i))
             Case "number" 'replace by correct number
              objRecordSet(i).Value = CDbl(props(i))
        Case Else
              MsgBox "type " & objRecordSet(i).Type & " not handled yet" & vbCrLf & "Data: " & props(i)
        End Select
        '''''''''''''''''''''''

      its dislayed Run time error '13"
            Type Mismatch

      on debug its through me on following line
            Case "Number" 'replace by correct number
0
 
LVL 13

Expert Comment

by:Surone1
ID: 33658416
okay the first case failed because the text is longer than 20 characters.

the second because we try to compare a string with a number.
so lets first fint out the correct types with the following code:

   ' Select Case objRecordSet(i).Type
       
     ' Case "Number" 'replace by correct number
         '     objRecordSet(i).Value = CDbl(props(i))
     '   Case "text" 'replace by correct number
      '      objRecordSet(i).Value = props(i)
     '   Case "date" 'replace by correct number
      '        objRecordSet(i).Value = CDate(props(i))
    '         Case "number" 'replace by correct number
    '          objRecordSet(i).Value = CDbl(props(i))
    '    Case Else
              MsgBox "type " & objRecordSet(i).Type & " not handled yet" & vbCrLf & "Data: " & props(i)
     '   End Select

it will show you the number of the datatype in the field, and the data that should be inserted...
you would need to make the text field(s) larger since there is data longer than 20 characters.
0
 
LVL 1

Author Comment

by:crystalsoft
ID: 33661241
Sir

herewith file attached, Please check

I am getting MSGBOX

" type 200 not handled yet
data: 284749      TOPAY     10-08-2010        KOLKATA     AHMEDNAGAR"

data-type-mismatch.doc
0
 
LVL 13

Expert Comment

by:Surone1
ID: 33661784
this looks like text or memo..
make sure the field length is set
and then we handle type 200 as text :

Select Case objRecordSet(i).Type
       
     ' Case "Number" 'replace by correct number
         '     objRecordSet(i).Value = CDbl(props(i))

      Case 200 '"text"
          objRecordSet(i).Value = props(i)


     '   Case "date" 'replace by correct number
      '        objRecordSet(i).Value = CDate(props(i))
    '         Case "number" 'replace by correct number
    '          objRecordSet(i).Value = CDbl(props(i))
       Case Else
              MsgBox "type " & objRecordSet(i).Type & " not handled yet" & vbCrLf & "Data: " & props(i)
       End Select
0
 
LVL 1

Author Comment

by:crystalsoft
ID: 33661897

Yes i think its taking whole value which is in   props(i)
         
its just an idea , if we can split whole line which is in props(i) till space,

tried second suggestion but same mismatch error

and i set data type is text and length is 50 for first filed
 


0
 
LVL 13

Expert Comment

by:Surone1
ID: 33662591
http://www.w3schools.com/ado/ado_datatypes.asp

the field size is still too small (since above data is 62 chars) , but that should not give a type mismatch error.

to cut the data up in smaller portions you could probably use the split() function.





0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 33662955
I think you are too lazy here:

Dim drive, objWMIService, path, line, objFile, oFile, columns, props, i, colFiles

You may need to be more specific assigning type to the variables.

/gustav
0
 
LVL 1

Author Comment

by:crystalsoft
ID: 33663045

sir
whatever data is in props(i) , I don't want it to insert in single filed in access database,
70 fields in DB table, and i want first column in first filed then second column is in another field,

My point is if i will increase DB field size 50 to 200, I don't want complete row in single filed, because in CSV, row displaying single row with different fields (Data)



and here split function ,but why i am not getting only first filed instead of whole row on props,


    For Each line In Split(objFile.ReadAll, vbNewLine)
        objRecordSet.AddNew
        columns = Split(TABLE_COLUMNS, ",")
        props = Split(line, ",")
       
        For i = 0 To UBound(columns)



0
 
LVL 13

Expert Comment

by:Surone1
ID: 33663075
   props = Split(line, " ")
0
 
LVL 1

Author Comment

by:crystalsoft
ID: 33663223


i think problem is
'        props = Split(line, ",")

Now we can continue



0
 
LVL 13

Expert Comment

by:Surone1
ID: 33663495
here is the code to get the values in a nice list


test11 = Split("284749      TOPAY     10-08-2010        KOLKATA     AHMEDNAGAR", " ")
For i = 0 To UBound(test11)
If Not IsNull(test11(i)) And test11(i) <> "" Then
If i = UBound(test11) Then
valuelist = valuelist & "'" & test11(i) & "'"
Else
valuelist = valuelist & "'" & test11(i) & "',"
End If
End If
Next i
MsgBox valuelist
0
 
LVL 13

Expert Comment

by:Surone1
ID: 33663628
this way we can also try a different approach.

test11 = Split(TABLE_COLUMNS, " ")
For i = 0 To UBound(test11)
If Not IsNull(test11(i)) And test11(i) <> "" Then
If i = UBound(test11) Then
valuelist = valuelist & "'" & test11(i) & "'"
Else
valuelist = valuelist & "'" & test11(i) & "',"
End If
End If
Next i
objConnection.execute "insert into  B_Detail (BNo,Mode,BDate,City,To) values (" & valuelist & ")"
0
 
LVL 13

Expert Comment

by:Surone1
ID: 33663652
sorry :

test11 = Split(line, " ")
For i = 0 To UBound(test11)
If Not IsNull(test11(i)) And test11(i) <> "" Then
If i = UBound(test11) Then
valuelist = valuelist & "'" & test11(i) & "'"
Else
valuelist = valuelist & "'" & test11(i) & "',"
End If
End If
Next i
objConnection.execute "insert into  B_Detail (BNo,Mode,BDate,City,To) values (" & valuelist & ")"
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 1

Author Comment

by:crystalsoft
ID: 33666362

getting error

run time error
Syntex error in insert into statement

on debug its through me on this line
        con.Execute "insert into B_Dtl(BNo,mode,BDate,City,To) values (" & valuelist & ")"
0
 
LVL 13

Expert Comment

by:Surone1
ID: 33666764
objConnection.execute "insert into  B_Detail (BNo,Mode,BDate,City,To) values (" & valuelist & ")"
notice the space after the table name, which is different in your line?
0
 
LVL 1

Author Comment

by:crystalsoft
ID: 33666832

same error
Syntax error in insert into statement
0
 
LVL 1

Author Comment

by:crystalsoft
ID: 33667078

i tried same line as you suggested but no luck ,
Syntax error in insert into statement
0
 
LVL 1

Author Comment

by:crystalsoft
ID: 33667082

i tried same line as you suggested but no luck ,
Syntax error in insert into statement
0
 
LVL 13

Expert Comment

by:Surone1
ID: 33667096
i'm just about done for today, but i have requested for other experts to come look at this question..
a fresh eye may solve a lot.
0
 
LVL 1

Author Comment

by:crystalsoft
ID: 33667181
Thankx surone1
we almost solved this problem , except this last error
may be tomorrow ...

Thanks again
0
 
LVL 13

Expert Comment

by:Surone1
ID: 33667693
maybe some sample data may help..
0
 
LVL 13

Expert Comment

by:Surone1
ID: 33667711
the relevant database part and a few lines of the file to be read would help..
make sure you change any customer data..
0
 
LVL 1

Author Comment

by:crystalsoft
ID: 33668898

Good Morning Sir

Herewith MDB and CSV files attached

Trans.mdb
284749.csv
284750.csv
0
 
LVL 13

Expert Comment

by:Surone1
ID: 33670039
so close..
:-)
0
 
LVL 13

Accepted Solution

by:
Surone1 earned 500 total points
ID: 33670085
For i = 0 To UBound(test11)
If Not IsNull(test11(i)) And test11(i) <> "" Then
If valuelist = "" Then
valuelist = valuelist & "'" & test11(i) & "'"
Else
valuelist = valuelist & ",'" & test11(i) & "'"
End If
End If
Next i
If valuelist <> "" Then
objConnection.Execute "insert into  Bilty_Detail (BiltyNo,Mode,BDate,from_City,To) values (" & valuelist & ")"
End If
0
 
LVL 13

Expert Comment

by:Surone1
ID: 33670108
the valuelist actually had one "," too many..
and the table and some field names were different.
0
 
LVL 1

Author Comment

by:crystalsoft
ID: 33670120

We almost done sir,
except Synatax Error  


and i am trying your code

test11 = Split(line, " ")
For i = 0 To UBound(test11)
If Not IsNull(test11(i)) And test11(i) <> "" Then
If i = UBound(test11) Then
valuelist = valuelist & "'" & test11(i) & "'"
Else
valuelist = valuelist & "'" & test11(i) & "',"
End If
End If
Next i
objConnection.execute "insert into  B_Detail (BNo,Mode,BDate,City,To) values (" & valuelist & ")"
0
 
LVL 13

Expert Comment

by:Surone1
ID: 33670270
o.k.
try this :

open your mdb in msaccess
add a new module (in access!)

paste the following code:

Sub tryupdate()
On Error GoTo hell
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const ForReading = 1

Const MDB_FILE = "D:\Crystal\Auto_Trans\Trans.mdb"
Const TABLE_NAME = "B_Detail"
Const TABLE_COLUMNS = "BNo,Mode,BDate,City,To,"
Const CSV_SEARCH_PATH = "D:\Crystal\A_Trans"


Dim fso As New FileSystemObject

Dim drive, objWMIService, path, line, objFile, oFile, columns, props, i, colFiles



Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\cimv2")

drive = Split(CSV_SEARCH_PATH, "\")(0)
path = "\\" & Replace(Split(CSV_SEARCH_PATH, drive + "\")(1), "\", "\\") & "\\"

Set colFiles = objWMIService.ExecQuery("Select * from CIM_DataFile where Drive='" + drive + "' and path = '" + path + "' and extension = 'csv'")

For Each oFile In colFiles
   
    Set objFile = fso.OpenTextFile(oFile.Name, ForReading)
   
    For Each line In Split(objFile.ReadAll, vbNewLine)
 
        columns = Split(TABLE_COLUMNS, ",")
        props = Split(line, ",")
       

   
    test11 = Split(line, " ")
    valuelist = ""
For i = 0 To UBound(test11)
If Not IsNull(test11(i)) And test11(i) <> "" Then
If valuelist = "" Then
valuelist = valuelist & "'" & test11(i) & "'"
Else
valuelist = valuelist & ",'" & test11(i) & "'"
End If
End If
Next i
If valuelist <> "" Then
sqlstr = "insert into  Bilty_Detail (BiltyNo,Mode,BDate,from_City,To) values (" & valuelist & ")"
CurrentDb.Execute sqlstr
End If
   
   
   
    Next

Next
Exit Sub
hell:
emsg = Err.Number & vbCrLf & Err.Description & vbCrLf & sqlstr
Debug.Print emsg
MsgBox emsg

End Sub


it's almost the same as what you have, but it adds some error handling, allowing us to see the data when/where it goes wrong.
0
 
LVL 1

Author Comment

by:crystalsoft
ID: 33670380

wow....done

Let me write comment in my code ..
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''Function csv to access'''''''''
'''''''By Experts-Exchange Guru SURONE1:
'''''''''Thanks SURONE1:
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
0
 
LVL 1

Author Comment

by:crystalsoft
ID: 33670403

sorry
before i read your solution i posted my comment
0
 
LVL 1

Author Comment

by:crystalsoft
ID: 33670424

sorry
before i read your solution i posted my comment
0
 
LVL 1

Author Comment

by:crystalsoft
ID: 33670703
SURONE 1:
next part is for automatically sending csv  to mapped network drive
please assist me because you know very well what file format ,

How to send csv file to the network drive

http://www.experts-exchange.com/Programming/Misc/Q_26471226.html
0
 
LVL 1

Author Closing Comment

by:crystalsoft
ID: 33671467
Thanks SURONE1
0
 
LVL 13

Expert Comment

by:Surone1
ID: 33686273
this may help solve the problem where the number of fields does not match i'm not sure if it's a good solution since the number of spaces between the fieds is not always the same...
props = Split(line, "  ")
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

This is about my first experience with programming Arduino.
If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
The goal of the video will be to teach the user the difference and consequence of passing data by value vs passing data by reference in C++. An example of passing data by value as well as an example of passing data by reference will be be given. Bot…
The viewer will be introduced to the member functions push_back and pop_back of the vector class. The video will teach the difference between the two as well as how to use each one along with its functionality.

762 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now