Problem updating Oracle from Excel VBA

[GBall]

Hi,
I'm updating a table from a spreadsheet and it all works fine except that one field is playing up.
The destination column is varchar2(500) and I'm picking up the contents of a column which holds freeform notes.
For some reason after running the procedure, the notes field(Oracle) just contains question marks to the lenght of the notes data from the spreadsheet.
I've tried single, double (2,3, and 4) around the field but it just doesn't work.

Dim strComments As String
strComments = worksheet1.Cells(intCurrentrow, 35)
Also tried .text and .value here

!notes = strComments


thanks for your help



Regards,
Graham

 

[Nelviticus]

Try inserting 'Msgbox strComments' before you assign it to the database field. That way you'll know whether the problem lies in getting the data from the spreadsheet or inserting it into the database.

Have you checked the properties of the field that the data's going into? I don't know Oracle but it may be set to only hold certain types of data.

If all else fails you can write a function to 'clean' strComments, i.e. trim it of leading/trailing spaces then go through it a character at a time making sure that they're all valid ASCII characters.

One other thought - do the comments contain apostrophes or quotation marks? These can sometimes cause problems when transferred to databases. I recall having to write a function to turn 'smart' quotes into 'straight' quotes before I could insert text data properly with an SQL Server database I was working on.

Nelviticus

 

[GBall]

I've debugged it and strcomments does contain the correct value and the oracle field is set up correctly.
I've also tried
!notes = "hello" - with the same result.
What I've noticed is that the number of question marks is half the number of characters in the string.
Is this a conversion problem somewhere ?

Thanks.

Regards,
Graham

 

[Nelviticus]

Could you try it the other way round? Manually put some text into the notes field then pull that into a string. It may need some special characters.

Nelviticus

 

[PHV]

Seems a Unicode problem.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[GBall]

Finally found out what it was - thanks to Dov Trietsch's article on OO4O that I found on the internet (here, if anyone's interested

http://www.vbxml.com/conference/wrox/1999_dc/text/dov.asp).

What it needed was
!notes.value = strComments

thanks for your help.

Regards,
Graham