hi all,
i have some problem in my web application, am using vb.net
i have a text box and an add button, when the user click on the add button, whatever written in the textbox stores on my sql database
the problem is whenever there is a ( ' ) character in the text, it send me error
i know why this problem occure but i dont know what is the reqired code for it
this is the code:
sql.commandtext = "INSERT INTO myDB (Ques) VALUES ('" & txtQues.text & "')
what is the solution?
The answer is very simple in C# those are literals and in ANSI SQL they are Delimited Identifiers per ANSI SQL 92 and SQL Server is compliant so try the link below on how to enable it and handle it correctly. Hope this helps.
http://msdn2.microsoft.com/en-us/library/ms176027.aspx
|||If you think about it, you are putting together the string before it gets sent to SQL Server. So, if your value has a single quote it, your insert would look something like:
INSERT INTO myDB (Ques) VALUES ('myvalue's')
This is confusing. The string seems to end after the 'e', when you really want it to end after the 's'. As said, SQL Server is taking that literally. If you're going to build the insert statement this way (as opposed to using a stored procedure), you need to replace single quotes with two single quotes (not a double quote - literally two single quotes).
txtQues.Text.Replace("'", "''")
The string passed to the database will be:
INSERT INTO myDB (Ques) VALUES ('myvalue''s')
which the database will interpret correctly.
|||Parameterized queries.|||thanks alot... for your help
