Quotation mark problem in  VB.NET + database

prabhatia
prabhatia used Ask the Experts™
on
I need to replace ( ' -> Quotation mark) with blank in my code in VB.NET as passing parameters in Database crashes the application if there is Quotation mark is present in the text box.
The values of First Name & Last Name are taken from edit box in a vb.net form
 
SQLQuery = "insert into Table(FirstName, LastName) " & "Values ('" txtFirstName.Text "','" txtLastName.Text "')"

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Use can use the Replace function to get rid of single quotes. Something like this


SQLQuery = "insert into Table(FirstName, LastName) Values (REPLACE('"&txtFirstName.Text&"','''',''),REPLACE('"&txtLastName.Text&"','''','') )"

Open in new window

Author

Commented:
Nice one but sorry to say didn't help
Use parameterized query, and then you don't need to worry about the quotation mark.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Commented:
What is the error message that you are getting?
P.

Commented:
Also, how about using a Replace function in VB instead of using it on sql server side.
http://msdn.microsoft.com/en-us/library/bt3szac5(VS.80).aspx
P.

Commented:

SQLQuery = "insert into Table(FirstName, LastName) " & "Values ('" & txtFirstName.Text.Replace("'","''") & "','" & txtLastName.Text.Replace("'","''") & "')"

Open in new window

Author

Commented:
Many thanks for this, but sorry to say this didn't help.
I know that parameterized query is one solution... but me being lasy do not want to change the code. I need something similar to .Replace or can I replace the quotation mark in the form validation?

Commented:
what is your input in textboxes that createp problem?

Author

Commented:
Thanks for all ur help... I hope I managed to stop the wrong input at the form itself... used the regular expression... ^[ \t\r\n\v\fa-zA-Z0-9_-]*$

Commented:
Hey, don't close it!
I can give you the correct answer here it is :

Just replace the quotation with additional quote in it:

for example this one:

txtFirstname.text.Replace("'","''")


SQLQuery = "insert into Table(FirstName, LastName) " & "Values ('" txtFirstName.Text.Replace("'","''") "','" txtLastName.Text..Replace("'","''") "')"

Open in new window

Commented:
I provide you the correct answer

So I object

Commented:
by using the code I gave you, you can still use the quotation in your query... So o need to remove it.

Author

Commented:
I wanted to remove the single quotation not... & the solution provided is not helpful & does not work

Commented:
@prabhatia

Your previous comment (24911008) I know that parameterized query is one solution... but me being lasy do not want to change the code. I need something similar to .Replace or can I replace the quotation mark in the form validation?

You mention you need something simalar that works like if you are using parameterized query. In parameterized query quotation mark is not replace or removed. So, I gave you the code that works and accept a string with quotation mark in it.

This is the reason why I object, when you request to delete this question.



cool will give u ur points... Thanks :)

Author

Commented:
what was that non english comment... was that an abuse???

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial