Apostrophe problem and much more

[KTuohy]

I have really searched the forum yet haven't found a total answer to my problem. I am updating a SQL table and the information is coming from another SQL table. The data being updated with could be null or might have an apostrophe or it might not.

I've literally read a mutlitude of posts yet none have dealt with all three together and when I try to pull what individual posts have said to do, in my update, I bomb . . .BIG TIME!

I'm hoping one of you good souls will help me.

Admittedly this is very crude but I've tried so many other ways, this is my latest attempt:

strSQL = "UPDATE [CFA-RPI Tracker] SET" _
& "[Preliminary Failure Analysis]=iif('" & my_Pre_Failure_Analysis & "' Not Like '*'*','" & my_Pre_Failure_Analysis & "','" & StrQuoteReplace(my_Pre_Failure_Analysis)) & "'," _

The StrQuoteReplace is a Function that reads like this:

Function StrQuoteReplace(strValue)
StrQuoteReplace = Replace(strValue, "'", "''")
End Function

The StrQuoteReplace Function worked great. . . so long as there was an astrophe in the data. Once it hit a string without one and/or was null or ="", then it bombed again.

Thanks for looking into this for me.

 

[MarkK]

By SQL table do you mean a table in an SQL Server database as opposed to an Access(Jet) table?
But where the string delimiter might be ambiguous like this I would use parameters in the query or command object. Look into the DAO.Parameter or ADO.Parameter object depending on the data access model you're using.
Cheers,

 

[KTuohy]

Thanks, Lagbolt, the tables are SQL, the application is 2003. I searched for DAO Parameter but hate the 2003 Help Services.

Thanks, any way.

 

[MarkK]

Consider this code ...

Sub ADOTestParameterQuery()
   Dim cmd As New ADODB.Command
   
[COLOR="Green"]   'notice no delimiters around the ? marks[/COLOR]
   cmd.CommandText = "UPDATE tTestData SET Name = ? WHERE ID = ?"
   cmd.ActiveConnection = CurrentProject.AccessConnection
[COLOR="Green"]   'notice no datatype specified for parameters[/COLOR]
   cmd.Parameters(0) = "TestName"
   cmd.Parameters(1) = 44
   cmd.Execute
End Sub

Obviously for SQL Server you need to provide a different connection object.
Cheers,

 

[KTuohy]

I apologize, you are trying so hard to help me.

I've got all my connection to SQL through ADO etc.

My problem is how do account for any eventuality, nulls, apostrophe, or non-apostrohes.

Thanks

 

[CraigDolphin]

You could try something like:

Function StrQuoteReplace(strValue as Variant) as String
strValue = strValue & "" 'this converts any null to a zero length string
  'the Instr function returns the position of the first apostrophe in the string, 
  'or gives a zero if absent
If Instr(1,strValue,"'") > 0 then    'if larger than 0, there is an apostrophe
   StrQuoteReplace = Replace(strValue, "'", "''") 
Else
   StrQuoteReplace = strValue 'if no apostrophe present, return the original variable as a string
End if
End Function

This function should return a zero-length string instead of a null, and would remove any apostrophes that may be present. Or you could re-type the function to return a variant and leave the null as a null if you prefer. Warning: Air-code (so I haven't tested it) HTH

 

[KTuohy]

Craig, THIS WORKED!

Thank you, thank you, thank you!

Lagbolt, thank you for your assistance too.

I appreciate this site soooo much because y'all don't give up on me.

Kathleen

 

[MarkK]

I guess my example is not explicit enough. There is no need to remove characters if you do this (modified to handle nulls) ...

Sub SaveAnything(vAnyDataIncludingApostrophesANDQuotes as Variant)
   Dim cmd As New ADODB.Command
   
[COLOR="Green"]   'notice no delimiters around the ? marks[/COLOR]
   cmd.CommandText = "UPDATE tTestData SET Name = ? WHERE ID = ?"
   cmd.ActiveConnection = CurrentProject.AccessConnection
 [COLOR="Green"]  'notice no delimiters[/COLOR]
   cmd.Parameters(0) = Nz(vAnyDataIncludingApostrophesANDQuotes, "")
   cmd.Parameters(1) = 44
   cmd.Execute
End Sub

I have customers who need to save data like: 12' x 6" in which case removing characters is not acceptable.