How to insert string with hyphen ( ' ) into database

Question

0.00/5 (No votes)

See more:

Hello there,

I was trying to run a simple INSERT query in which i passed a string value with hyphen ( ' ). It throws error. How can i insert/Update a string value with hyphen.
Front-End : VB.net, C#
Back-End : MS-Access, Sql Server

Table :

SID SName
1 ABC
2 DEF

Problem : Trying to insert Name = xyz's

Code :

VB

Dim Da As OleDbDataAdapter = New OleDbDataAdapter("Insert Into Student(SName) Values('" & txtUserName.Text & "')", Conn)
        Dim Ds As New DataSet
        Da.Fill(Ds)

Posted 13-Jun-14 19:47pm

Raghubir_Sarkar

Updated 13-Jun-14 19:54pm

v3

Add a Solution

Comments

Awadhendra Tripathi 14-Jun-14 1:55am

What is Datatype for that column?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

phil.o · Accepted Answer · 2014-06-13T19:54:00

Solution 1

Use a parameterized query:

C#

string test = "O' what happened?";

using (SqlConnection cnx = new SqlConnection(connectionString)) {
   cnx.Open();
   using (SqlCommand cmd = new SqlCommand("INSERT INTO [TestTable] ([TestField]) VALUES (@value)", cnx)) {
      cmd.Parameters.AddWithValue("@value", test);
      int result = cmd.ExecuteNonQuery();
   }
}

Moreover, it will have the advantage of bulletproofing your code to SQL injection attacks.
Parameterized queries should be used everywhere user input is involved.
Good luck :)

Posted 13-Jun-14 19:54pm

phil.o

Comments

Raghubir_Sarkar 14-Jun-14 2:02am

Thanks phil it works, and also for your suggestion next time i'll use SqlCommand instead of dataadapter.

phil.o 14-Jun-14 4:02am

Why next time? As far as security is concerned, you should use parameterized queries whenever an unkown input source (user, file, db...) is involved. :)
For example, if someone enters ';DROP TABLE Student-- in txtUserName, with your initial code you just lost the Student table; with a parameterized query the string would be inserted in the SName column, without any harm done to the database schema.

Raghubir_Sarkar 14-Jun-14 5:56am

You are right phil but My current project is too lengthy so it is not possible to change all insert or update queries. In future projects and new forms in current project i'll use parameterized quiry. Thank you so much for your support.