single quote error

[abenitez77]

I am trying to update a SQL table with the string from excel using vba. strBrokerCategory (sixth line) has a value of "Cat 29 OTC / 2930 Women's Health". The single quote is causing issues for me. How can I fix this ? I tried a Replace(strBrokerCategory, "'", "''") but it does not help. Any help would be appreciated.

tSQL = tSQL & "Values('" & strLCLDeal & "','" & strVendorsDeal & "','" & strDateCreated & "','" & strDealDate & "','" & strDealType & "','" & strActivityType & "','" & _
strDeliveryMethod & "','" & strBillType & "','" & strCatNum & "','" & strPCSNum & "','" & strMCH1 & "','" & strMch1Desc & "','" & strWeek & "','" & _
strYear & "','" & strDealDur & "','" & strPreShipEffDate & "','" & strDealEffDate & "','" & _
strManVendorName & "','" & strManVendorNum & "','" & strManSAPVendNum & "','" & strManAddress & "','" & strManCity & "','" & strManProvince & "','" & strManPostal & _
"','" & strManPhone & "','" & strManRepName & "','" & strManCategory & "','" & strBrokerName & "','" & strBrokerStreet & "','" & strBrokerCity & "','" & _
strBrokerProvince & "','" & strBrokerPostal & "','" & strBrokerPhone & "','" & strBrokerRepName & "','" & Replace(strBrokerCategory, "'", "''") & "','" & strBanner & "','" & _
strBannerCode & "','" & strBanner2 & "','" & strBannerCode2 & "','" & strBanner3 & "','" & strBannerCode3 & "','" & strBanner4 & "','" & strBannerCode4 & _
"','" & strBanner5 & "','" & strBannerCode5 & "','" & strBanner6 & "','" & strBannerCode6 & "','" & strBanner7 & "','" & strBannerCode7 & "','" & _
strBanner8 & "','" & strBannerCode8 & "','" & strBanner9 & "','" & strBannerCode9 & "','" & strBanner10 & "','" & strBannerCode10 & _
"','" & xSheetName & "','" & xFilePath & "','" & xFileName & "')"

Set rs = Connection2.Execute(tSQL)

 

[dUBBINS]

First, I am far from a pro at this. It looks correct to me. So I googled it. I found that your variable is not allowed to have an apostrophe in it for this syntax. Something to look into on your end.

 

[abenitez77]

The variable is holding a value from a cell...

 

[Scott Huish]

Try replacing Chr(146) as well. But what is your replacement string? A double-quote?

 

[abenitez77]

found my issue ... I was using .Value when I should have used .Text to get the data. The replacement string is a single quote.