using a dictionary object to hold INSERT data. problem

[wadey]

I'm using a dictionary object to hold data that needs to be inserted into a sql server 2000 db. problem is i'm getting the error "The microsoft jet database engine could not find the object". The name of the table is spelt correctly. I've got a feeling that the problem lies somewhere in the marshalling that VB does, because I'm sure I read somewhere that you cannot send variants across in your sql text. Dictionarys hold everything as variants.
Has anyone come across this before and know how to get round it without changing to arrays. Also you cannot use the CONVERT or CAST in sql as the problem lies at the front end of the marshalling not at the DB. :-

strSQL = "INSERT INTO The_Data VALUES(" _
& "'Test'," _
& "'" & dicUploadData.Item(ITEM1) & "'," _
& "'" & dicUploadData.Item(ITEM2) & "'," _
& "'" & dicUploadData.Item(ITEM3) & "'," _
& "'" & dicUploadData.Item(ITEM4) & "'," _
& "'" & dicUploadData.Item(ITEM5) & "'," _
& "'" & dicUploadData.Item(ITEM6) & "'," _
& dicUploadData.Item(ITEM7) & "," _
& "0," _
& "0," _
& "'" & dicUploadData.Item(ITEM8) & "'," _
& dicUploadData.Item(ITEM9) & "," _
& "'" & dicUploadData.Item(ITEM10) & "'," _
& "'" & dicUploadData.Item(ITEM11) & "'," _
& "''," _
& "'" & dicUploadData.Item(ITEM12) & "');"


Excel_Connection.Execute (strSQL)


the ITEMx's are constants.

Regards

 

[wadey]

Forget this. It was the connection object which was wrong. I was using the same connection which was pointing to the excel spreadsheet from which i got the original data from to upload. DOH!!