insert text area as it is

Question

0.00/5 (No votes)

See more:

i can't insert multiline textbox into database...i want to insert it as it is

C#

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ConnectionString);
           SqlCommand com = new SqlCommand();
           con.Open();
           com.Connection = con;
           string text = TextBox1.Text.ToString();

           string cmd1 = "insert into student_note ([note]) values("+text+") where student_id='" + Session["student_id"] + "' and course_id='" + Session["course_id"] + "'";
           SqlCommand cmd1x = new SqlCommand(cmd1, con);
           cmd1x.ExecuteNonQuery();
           table3.Visible = false;
           table1.Visible = true;

Posted 16-Apr-13 9:03am

amr mustafa

Add a Solution

Comments

Richard C Bishop 16-Apr-13 15:04pm

What is the problem? Do you have a question?

amr mustafa 16-Apr-13 15:05pm

i want to insert the text as it is...i can't insert to database the text whith characters and spaces

[no name] 16-Apr-13 15:08pm

make a try - catch to see exception and watch where is the problem...

or

try to insert directly without defining String!

"insert into student_note ([note]) values("+textBox1.Text+") where student_id='" + Session["student_id"] + "' and course_id='" + Session["course_id"] + "'";

amr mustafa 16-Apr-13 15:10pm

i made debugging...the problem basically in the spaces

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)

OriginalGriff · Accepted Answer · 2013-04-16T09:23:00

Solution 1

Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead. That will also solve your problem...

C#

string cmd1 = "INSERT INTO student_note ([note]) VALUES(@NOTE) WHERE student_id=@SID AND course_id=@CID";
SqlCommand cmd1x = new SqlCommand(cmd1, con);
cmd1x.Parameters.AddWithValue("@SID", Session["student_id"]);
cmd1x.Parameters.AddWithValue("@CID", Session["course_id"]);
cmd1x.Parameters.AddWithValue("@NOTE", TextBox1.Text);
cmd1x.ExecuteNonQuery();

[edit]
I must be part asleep!
You don't use a WHERE clause on an INSERT statement - it adds a new row, not filters existing ones. You could use a WHERE with an UPDATE, but if you want to add a new row then change your SQL above to:
INSERT INTO student_note ([note], student_id, course_id) VALUES(@NOTE, @SID, @CID)";
- OriginalGriff
[/edit]
[edit2]Forgot a comma :doh: - OriginalGriff[/edit2]

Posted 16-Apr-13 9:23am

OriginalGriff

Updated 16-Apr-13 9:29am

v4

Comments

amr mustafa 16-Apr-13 15:31pm

incorrect syntax near where...

amr mustafa 16-Apr-13 15:33pm

ok ok......thank you so much...i'll try it

amr mustafa 16-Apr-13 15:35pm

The parameterized query '(@SID nvarchar(4000),@CID nvarchar(4000),@NOTE nvarchar(36))INSE' expects the parameter '@SID', which was not supplied.

amr mustafa 16-Apr-13 15:36pm

which comma?

OriginalGriff 16-Apr-13 15:39pm

It's gone in the latest version.

amr mustafa 16-Apr-13 15:39pm

i just want to add the note..the course_id and the student_id..already existed in the table

OriginalGriff 16-Apr-13 15:42pm

No, they don't!
An INSERT statement creates a new row - blank, with default entries unless you supply them. You need to store the student_id and course_id against the note, or you will end up with a database full of notes you can't cross reference back to anyone or anything. Each row with a Note needs it's own student and course reference - it can't share "the one above", SQL does not work like that!

amr mustafa 16-Apr-13 15:47pm

thank you for valuable information :)

OriginalGriff 16-Apr-13 15:59pm

You're welcome!