Text containing single quotation marks 1

[elbiel]

I wrote a function SQLEncode which replaces ' with '' for text containing single quotation marks. The text is then sent as a parameter to a sqlserver stored procedure. I have a problem that stores the '' as part of the text and everytime the record gets updated it adds more ''. In the end there could be zillions of ' sa part of the text.

I have used the function with ASP where a dynamic sql statement was created by code, and it works. It must have something to do with how the stored procedure interprets the parameter value sent.
THE CODE:
Function SQLEncode(ByVal strText As String) As String
SQLEncode = strText
If strText = "" Or IsDBNull(strText) Then Exit Function
SQLEncode = Replace(strText, "'", "''")
End Function

ADING THE PARAMETER:
.Parameters.Add("@Name", SQLEncode(txtName.Text))

THE STORED PROC:
UPDATE [Campaign] SET [Name] = @Name WHERE [ID] = @ID

Any assistance will be appreciated.

 

[ca8msm]

If you are using parameterised queries, you do not need to worry about replacing single quotes with double quotes; the parameters will protect you against sql injection and also handle the inclusion of the single quote.

Mark,

[URL unfurl="true"]http://lessthandot.com[/url] - Experts, Information, Ideas & Knowledge
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Website Design
[URL unfurl="true"]http://aspnetlibrary.com[/url] - An online resource for professional ASP.NET developers

 

[elbiel]

Thanks ca8msm! It works 100% now that I remove the SQLEncode from text fields sent as parameters!