errors on delete command ASP.NET

Question

3.00/5 (1 vote)

See more:

hey guys, im trying different ways of deleting from a gridview

So i have used the autogenerate functions on the data source and the gridview,
now this is the error im getting:

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near 'int'.

an this is the stack trace:

VB

[SqlException (0x80131904): Incorrect syntax near 'int'.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +2073550
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +5064508
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() +234
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2275
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +215
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +987
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
   System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +178
   System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +137
   System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +394
   System.Web.UI.WebControls.SqlDataSourceView.ExecuteDelete(IDictionary keys, IDictionary oldValues) +576
   System.Web.UI.DataSourceView.Delete(IDictionary keys, IDictionary oldValues, DataSourceViewOperationCallback callback) +92
   System.Web.UI.WebControls.GridView.HandleDelete(GridViewRow row, Int32 rowIndex) +946
   System.Web.UI.WebControls.GridView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +1161
   System.Web.UI.WebControls.GridView.RaisePostBackEvent(String eventArgument) +210
   System.Web.UI.WebControls.GridView.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +13
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +176
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5563

OK. so where it says incorrect syntax near int i cannot see an error with the delete command this is the string i am using:
DeleteCommand="DELETE FROM Customer WHERE ([Customer ID] = @ID)">

i keep getting the error and its very frustrating, i have even tried removing the [] and the space near = and still no joy.

the other problem i have is as this was not working i thought i would hardcode some button and text box to do the same function but it is not deleting either.

this is the code i have used for delete button:

C#

protected void deleteButton1_Click(object sender, EventArgs e)
        {
            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["TravelShopConnectionString1"].ConnectionString);
            SqlCommand deleteUser = new SqlCommand("DELETE FROM Customer WHERE [Customer ID] = @ID", conn);
            deleteUser.CommandType = CommandType.Text;
            try
            {
                conn.Open();
                deleteUser.ExecuteNonQuery();
                Response.Redirect("AdminRegistered.aspx");
            }
            catch (Exception)
            {
                Response.Write("ERROR delete failed");
            }
        }

any help would be greatly appreciated

Regards Matt.

Posted 10-Apr-12 5:48am

BBCokeley

Add a Solution

2 solutions

Solution 1

huge problem

where you are passing value of @ID parameter in your code.

I think once you pass the value of @ID parameter issue will resolve easily...

Happy coding...

Posted 10-Apr-12 5:55am

Pranay Rana

Updated 10-Apr-12 5:56am

v2

Comments

BBCokeley 10-Apr-12 12:04pm

yup, that was it, string was missing "'" at the end
plus i had no delete parameter.

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)

Gopal Rakhal · Accepted Answer · 2012-04-10T05:55:00

Solution 2

try it, this may help You...

SqlConnection objcon = new SqlConnection(StrCon);
objcon.Open();
string StrDelete = "Delete from Company Where CompanyID='" + TextBox1.Text + "'";
SqlCommand cmd = new SqlCommand(StrDelete, objcon);
cmd.ExecuteNonQuery();
messageBox("Deleted Sucessfully");
objcon.Close();
objcon.Dispose();

Posted 10-Apr-12 5:55am

Gopal Rakhal

Comments

Pranay Rana 10-Apr-12 11:57am

this will cause sql injection attack so please pass the parameter in using SqlParameter

BBCokeley 10-Apr-12 12:04pm

LEGEND

had the string all wrong, thank you very much friend

I hate string lol.

still miffed with the auto generated delete tho, but that doesnt matter now.

Thanks again
Regards Matt.