sql error when insert string value in database..

Question

0.00/5 (No votes)

See more:

when i try to insert

42"O/D X 36"O/D x 5'-00"lg.col.pipe Fabricated to drg.no. JPI-30368-B

this string through textBox, It's give an error

Syntax error in string in query expression ''42"O/D X 36"O/D x 5'-00"lg.col.pipe Fabricated to drg.no. JPI-30368-B'

My Code is -:

C#

using System;
using System.Windows.Forms;
using System.Data.OleDb;

namespace ForPrintPre
{
    public partial class Form2 : Form
    {
        OleDbConnection Conn;
        OleDbCommand Cmd;
        public Form2()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            Cmd.CommandText = "insert into Table1 values('" + textBox1.Text + "')";
            Cmd.Connection = Conn;
            Cmd.ExecuteNonQuery();
        }

        private void Form2_Load(object sender, EventArgs e)
        {
            Conn = new OleDbConnection(@"Provider=microsoft.jet.oledb.4.0; Data Source=d:\PiyashData.mdb;");
            Conn.Open();
            Cmd = new OleDbCommand();            
        }
    }
}

Thanks in Advance!!!

Posted 13-Feb-13 18:53pm

JayantaChatterjee

Add a Solution

3 solutions

Solution 1

Hi Jayanta,

Looks like a quotation marks and single quotation problem.
You need to make sure that in your INSERT string you don't have single quote (') since that messes up the SQL query.
This is one of the most common ways to attack a DB, it's SQL Injection

Here's an example on how use parametes:

C#

string sqlIns = "INSERT INTO table (name, information, other) VALUES (@name, @information, @other)";

db.Open();
try
{
SqlCommand cmdIns = new SqlCommand(sqlIns, db.Connection);
cmdIns.Parameters.Add("@name", info);
cmdIns.Parameters.Add("@information", info1);
cmdIns.Parameters.Add("@other", info2);
cmdIns.ExecuteNonQuery();
cmdIns.Dispose();
cmdIns = null;
}
catch(Exception ex)
{
throw new Exception(ex.ToString(), ex);
}
finally
{
db.Close();
}

Cheers,
Edo

Posted 13-Feb-13 19:15pm

Joezer BH

Updated 13-Feb-13 19:29pm

v2

Comments

JayantaChatterjee 14-Feb-13 1:24am

You are right, but the single quote will automatically appended to the string when i insert it..
If i used another string it does not appended any quote,,
i think the string contain too many single and double quote that's why the error occurred..
please tell me how to avoid this kind of error....

Joezer BH 14-Feb-13 1:30am

Sure,
Added code sample :)

JayantaChatterjee 14-Feb-13 1:33am

Thanks for helping me....

My problem is solved.....

Solution 2

You should use SqlParameter rather than constructing SQL like you have done. Read the documentation[^] and change your code to use parameters. This takes care of properly quoting and thus preventing SQL injection.

Posted 13-Feb-13 19:18pm

N a v a n e e t h

Comments

Joezer BH 14-Feb-13 1:32am

5+

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)

_Maxxx_ · Accepted Answer · 2013-02-13T19:23:00

Solution 3

edo is correct - the problem is in the use of quotes in the string you need to insert.

The easiest solution is to parameterise the query

C#

Cmd.CommandText = "insert into Table1 values(@myString)";
cmd.Parameters.Add("@myString", SqlDbType.String).Value = textBox1.Text;

Posted 13-Feb-13 19:23pm

_Maxxx_

Comments

JayantaChatterjee 14-Feb-13 1:31am

thankssss a Lottttttttttt..

My Code is -:

Cmd.CommandText = "insert into Table1 values(@myString)";
Cmd.Parameters.Add("@myString", OleDbType.VarChar).Value = textBox1.Text;
Cmd.Connection = Conn;
Cmd.ExecuteNonQuery();

Joezer BH 14-Feb-13 1:32am

5+