how to fix this error in c# ?

Question

0.00/5 (No votes)

See more:

hi, i have a form on my program , it have name , year , director , quality , size ( all of this have text box ) when i write something to those text boxes and click on add i get this error :

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

Additional information: Unclosed quotation mark after the character string '954MB)'.

i know that i have write it like this : '954MB')'. but i dont know how to do it on C# i just can write it on sql server. this is my code of that form :

C#

private void BtnٍEdit_Click(object sender, EventArgs e)
       {
           String sqlcon = "Data Source=desktop-nee5996\\shadow;Initial Catalog=Filelib;Integrated Security=True";
           SqlConnection cn = new SqlConnection(sqlcon);

           try
           {
               cn.Open();
               if (TxtName.Text == "")
               {
                   MessageBox.Show("Please Insert Name.", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
               }
               else
               {
                   SqlStr = "insert into Filelib (Id_Files,";
                   SqlStr = SqlStr + " Name,";
                   SqlStr = SqlStr + " Year,";
                   SqlStr = SqlStr + " Director,";
                   SqlStr = SqlStr + " Quality,";
                   SqlStr = SqlStr + " Size) ";
                   SqlStr = SqlStr + " Values(";
                   SqlStr = SqlStr + "'" + TxtId_Files.Text + "',";
                   SqlStr = SqlStr + "'" + TxtName.Text + "',";
                   SqlStr = SqlStr + "'" + TxtYear.Text + "',";
                   SqlStr = SqlStr + "'" + TxtDirector.Text + "',";
                   SqlStr = SqlStr + "'" + TxtQuality.Text + "',";
                   SqlStr = SqlStr + "'" + TxtSize.Text + ")";
                   sqlcmd = new SqlCommand(SqlStr, cn);
                   sqlcmd.ExecuteNonQuery();
                   MessageBox.Show("Save Successefully.", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
                   clear();


               }

           }
           finally
           {
               cn.Close();
           }

       }

       public void clear()
       {


           TxtName.Text = "";
           TxtYear.Text = "";
           TxtDirector.Text = "";
           TxtQuality.Text = "";
           TxtSize.Text = "";
       }

how can i fix it ? by the way english is my second language - excuse me if i type words wrong.

Posted 4-Sep-15 19:43pm

brandon1999

Add a Solution

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)

Wendelius · Accepted Answer · 2015-09-04T19:53:00

Solution 1

The immediate solution is to add the missing last quotation mark to the statement:

C#

SqlStr = SqlStr + "'" + TxtSize.Text + "')";

However, I suggest not concatenating the data from controls directly to the SQL statement. This leaves you wide open to SQL injections. Instead, use SQLParameter[^].

So in overall your code should look something like

C#

SqlStr = @"
INSERT INTO Filelib (Id_Files, Name, Year, Director, Quality, Size)
VALUES (@Id_Files, @Name, @Year, @Director, @Quality, @Size)";

using(sqlcmd = new SqlCommand(SqlStr, cn)) {
   sqlcmd.Parameters.AddWithValue("@Id_Files", TxtId_Files.Text);
   sqlcmd.Parameters.AddWithValue("@Name", TxtName.Text);
   sqlcmd.Parameters.AddWithValue("@Year", TxtYear.Text);
   sqlcmd.Parameters.AddWithValue("@Director", TxtDirector.Text);
   sqlcmd.Parameters.AddWithValue("@Quality", TxtQuality.Text);
   sqlcmd.Parameters.AddWithValue("@Size", TxtSize.Text);
   sqlcmd.ExecuteNonQuery();
}

Also it would be advisable to wrap the command into a using block that defines the connection.

For more discussion, have a look at Properly executing database operations[^]

Posted 4-Sep-15 19:53pm

Wendelius

Updated 4-Sep-15 20:33pm

v3

Comments

brandon1999 5-Sep-15 2:20am

thank u so much but when i write :

SqlStr = @"
INSERT INTO Filelib (Id_Files, Name, Year, Director, Quality, Size)
VALUES (@Id_Files, @Name, @Year, @Director, @Quality, @Size))";

using(sqlcmd = new SqlCommand(SqlStr, cn)) {
sqlcmd.Parameters.AddWithValue("@Id_Files", TxtId_Files.Text);
sqlcmd.Parameters.AddWithValue("@Name", TxtName.Text);
sqlcmd.Parameters.AddWithValue("@Year", TxtYear.Text);
sqlcmd.Parameters.AddWithValue("@Director", TxtDirector.Text);
sqlcmd.Parameters.AddWithValue("@Quality", TxtQuality.Text);
sqlcmd.Parameters.AddWithValue("@Size", TxtSize.Text);
sqlcmd.ExecuteNonQuery();
}

it says Incorrect syntax near ')'. how to fix it ? thank u again

Wendelius 5-Sep-15 2:33am

Sorry, there was an extra parenthesis in the end of the SQL statement. The example has now been modified.

brandon1999 5-Sep-15 2:37am

it worked - thank u so much

Wendelius 5-Sep-15 2:38am

You're welcome.

Sarath kumar.N 5-Sep-15 2:43am

Pls accept the solution

Rob Philpott 5-Sep-15 2:43am

Looks good to me! Also interesting to see how other people do it. When I set up the parameters, I usually do something like this:

command.Parameters.Add("@name", SqlDbType.VarChar, 40).Value = "dddd";

.. and now I'm wondering whether the addition of the type information is worth doing.

Wendelius 5-Sep-15 3:36am

I'd say it depends on the situation if it's worth a while. Mostly AddWithValue is sufficient, but if you need to specify the direction or if you use the same prepared statement twice, you need to define length,precision and scale so in these situations the full blown syntax is required.