getting error when inserting rows in table , after 1783 rows inserted i got error

Question

0.00/5 (No votes)

See more:

Hi, Every One...

while run my ASP.net application with C#.. I got one error like below..
------------------------------------------------------------------------------------

System.Data.SqlClient.SqlException was unhandled by user code
  Message="Incorrect syntax near 'S'."
  Source=".Net SqlClient Data Provider"
  ErrorCode=-2146232060
  Class=15
  LineNumber=1
  Number=102
  Procedure=""
  Server="PINNACLESERVER\\SQLEXPRESS"
  State=1
  StackTrace:
       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
       at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
       at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
       at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
       at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
       at Ad_Defaulters.Page_Init(Object sender, EventArgs e) in c:\Documents and Settings\pc\Desktop\123\Ad_Defaulters.aspx.cs:line 53
       at System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e)
       at System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e)
       at System.Web.UI.Control.OnInit(EventArgs e)
       at System.Web.UI.Page.OnInit(EventArgs e)
       at System.Web.UI.Control.InitRecursive(Control namingContainer)
       at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
  InnerException:

------------------------------------------------------------------------------------
How to resolve this error

In this me inserting a 4000 rows in a table.
after insert 1783 rows, the error coming like this

Incorrect syntax near 'S'.

Posted 20-Jun-12 20:00pm

Raajendran

Updated 20-Jun-12 21:08pm

Mohamed Mitwalli

v2

Add a Solution

4 solutions

Solution 3

This is problem with using " ' " in your inserting value.It might be happen that character is exist in your 1784th rows.If that character is exist in your first Rows , you can't insert first rows.

So, better approach is using Stored Procedure for Inserting your records in Table in stead of using direct query.

Go through http://msdn.microsoft.com/en-us/library/ms345415.aspx[^] for creating a stored procedure.

use link http://support.microsoft.com/kb/320916[^] to call Stored Procedure through your code.

Posted 20-Jun-12 20:31pm

ashriv

Solution 4

C#

sqlcon.Open();
for (int i = 0; i < dtMfcStand.Rows.Count; i++)
{
    string str;
    str = "insert into TableName values(";
    for (int j = 0; j < dtMfcStand.Columns.Count; j++)
    {
        string dtstr = dtMfcStand.Rows[i][j].ToString().Replace("'", "");
        str += "'" + dtstr + "',";

    }
    str += ")";
    string str1 = str.Replace(",)", ")");

    SqlCommand cmd = new SqlCommand(str1, sqlcon);
    int k = cmd.ExecuteNonQuery();
}
lblMessage.Text = "Suucessfully Inserted in Database.";
sqlcon.Close();

Posted 20-Jun-12 21:16pm

MAKReddy

Updated 20-Jun-12 21:21pm

v2

Comments

Sebastian T Xavier 21-Jun-12 3:31am

this is wrong....

.ToString().Replace("'", "")

please make sure before you hit POST

MAKReddy 22-Jun-12 7:33am

Hi Sebastian,
if i will not remove apostrophe(') between string
while inserting i am getting this error Incorrect syntax near 'S'.
that's way i done like this can u tell how to do.
here i have done data inserting from MS Access to sql server.

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)

Sebastian T Xavier · Accepted Answer · 2012-06-20T20:02:00

Solution 1

This is due to special characters. In your 1784th row there is a '. Either you use YOURSTRING.Replace("'","''") or you need to use parameterized query.

Parameterized-Queries[^]

Regards
Sebastian

Posted 20-Jun-12 20:02pm

Sebastian T Xavier

Updated 20-Jun-12 20:08pm

v2

Comments

Raajendran 21-Jun-12 2:07am

Thank u so much sir..
can u tell how to write parameterized query..

Raajendran 21-Jun-12 2:11am

i'm new to this sir... so i don't know...

Sergey Alexandrovich Kryukov 21-Jun-12 2:18am

I provided an answer, please see.
--SA

Sergey Alexandrovich Kryukov 21-Jun-12 2:12am

Answered, my 5. I think, not using parametrized query would be just silly.
--SA

Raajendran 21-Jun-12 2:13am

Query1 = "SELECT P.Id,P.regno,P.StdName, P.ClassSection,A.Standard, A.Description, A.Fee FROM Tbl_Sc_StudentProfile P INNER JOIN Tbl_Sc_Feeallocation A ON P.Standard = A.Standard where not exists (select c.description,c.class from Tbl_Sc_FeeCollChild c Where c.class = P.classsection and c.stdid = P.Id and c.description=A.Description) group by A.Description,P.Id,P.regno,P.StdName,P.ClassSection, A.Standard , A.Fee order by Standard asc";
da = new SqlDataAdapter(Query1, con);
ds = new DataSet();
dt = new DataTable();
da.Fill(ds, "Tbl_Sc_StudentProfile");
dt = ds.Tables["Tbl_Sc_StudentProfile"];
foreach (DataRow row1 in dt.Rows)
{
con.Open();
Query2 = "INSERT INTO dummytbl VALUES('" + row1[0] + "','" + row1[1] + "','" + row1[2] + "','" + row1[3] + "','" + row1[4] + "','" + row1[5] + "','" + row1[6] + "')";
cmd = new SqlCommand(Query2, con);
cmd.ExecuteNonQuery();
con.Close();
}

this is my code sir...
in this only am getting error after 1783 rows inserted

Sebastian T Xavier 21-Jun-12 3:10am

We understand your problem. That's why we recommend to use parameterized query. (see the second answer also.).... Now If you don't understand how to use it ; just try the following....

Query2 = "INSERT INTO dummytbl VALUES('" +
row1[0].Tostring().Replace("'","''") + "','" + row1[1].Tostring().Replace("'","''") + "','" + row1[2].Tostring().Replace("'","''") + "','" + row1[3].Tostring().Replace("'","''") + "','" + row1[4].Tostring().Replace("'","''") + "','" + row1[5].Tostring().Replace("'","''") + "','" + row1[6].Tostring().Replace("'","''") + "')";

This could solve your issue. (But I would recommend Parametrized query as it is the BEST method)

Sergey Alexandrovich Kryukov 21-Jun-12 11:26am

Parametrized query is no just much better; not using them in this case is hardly acceptable.
--SA

Sebastian T Xavier 21-Jun-12 13:24pm

I agree with you.

Sergey Alexandrovich Kryukov · Accepted Answer · 2012-06-20T20:18:00

Solution 2

[Answering a follow-up question:]

Parametrized query is one of the very critical aspects of database programming, so you have to learn it. Please start here:
http://msdn.microsoft.com/en-us/library/yy6y35y8.aspx[^].

This is related not just to the problems like yours. One of the most critical problem solved with parametrized statements is related to security. Actually, if you compose your command based on user-supplied data, it can be anything, including some SQL code, which opens wide doors to a well-known exploit called SQL injection. This article explains the importance of parametrized statements:
http://en.wikipedia.org/wiki/SQL_injection#Parameterized_statements[^].

—SA

Posted 20-Jun-12 20:18pm

Sergey Alexandrovich Kryukov

Updated 20-Jun-12 20:31pm

v2

Comments

Raajendran 21-Jun-12 2:25am

Thank U So Much...

Raajendran 21-Jun-12 2:30am

Query1 = "SELECT P.Id,P.regno,P.StdName, P.ClassSection,A.Standard, A.Description, A.Fee FROM Tbl_Sc_StudentProfile P INNER JOIN Tbl_Sc_Feeallocation A ON P.Standard = A.Standard where not exists (select c.description,c.class from Tbl_Sc_FeeCollChild c Where c.class = P.classsection and c.stdid = P.Id and c.description=A.Description) group by A.Description,P.Id,P.regno,P.StdName,P.ClassSection, A.Standard , A.Fee order by Standard asc";
da = new SqlDataAdapter(Query1, con);
ds = new DataSet();
dt = new DataTable();
da.Fill(ds, "Tbl_Sc_StudentProfile");
dt = ds.Tables["Tbl_Sc_StudentProfile"];
foreach (DataRow row1 in dt.Rows)
{
con.Open();
Query2 = "INSERT INTO dummytbl VALUES('" + row1[0] + "','" + row1[1] + "','" + row1[2] + "','" + row1[3] + "','" + row1[4] + "','" + row1[5] + "','" + row1[6] + "')";
cmd = new SqlCommand(Query2, con);
cmd.ExecuteNonQuery();
con.Close();
}

in this while run,... inserting 1783 rows successfully in table dummytbl..
after that am the error below

sqlexception was unhandled by user code
Incorrect syntax near 'S'.

Sebastian T Xavier 22-Jun-12 0:36am

my 5+

Sergey Alexandrovich Kryukov 29-Jun-12 20:44pm

Thank you, Sebastian.
--SA