ASP .NET2 Updating SQL Record

You are missing a bracket

  mysql = "UPDATE supplier_details SET(clicks = (clicks + 1)    )     WHERE (user_ID = '" + myval + "')"

sorry that was me and my cut and pasting, the bracket is there in the real code.

Sorry about that

Remove all the brackets and try it!

Seems to me, the error you are getting is in the line myval = GridView1.SelectedDataKey.Value
And if not, the error is from sql statement, as saleek said, you can remove the brackets soon after SET ( remove the bracket ) and remove the last bracket
If the error is in the line, myval = GridView1.SelectedDataKey.Value, can u just check by replacing the statement ( to verify the exact value in debug mode ) with Server.HtmlEncode(GridView1.SelectedDataKey.Value)

I have tried hard coding the sql as suggested but still get the error. when I single step the error occurs on the last line but that may not mean too much as that is the line that runs the query etc.


I would love to know what the error means, but can't find a description of it that makes any sense.

Are you trying to update a database or a DataTable in asp.net?

Make sure that [clicks] has no indexes on it.

EOLUpdate is a SQLDataSource object

The only index is one on the key field called line_number

Very strange problem.

Have you tried running the exact SQL that is generated in SQL Query Analyzer?
Does it work then?

I am also struggling to find anything on the internet... Have you used EOLUpdate somewhere else on this page to insert or update anything? Did it work?

What about changing your sql text into a stored procedure and just calling that through a SqlCommandObject?

I have tried the sql in query analyer and it is fine.

EOLupdate connects fine and can return data from a select query, it seems it's the update that is causing problems.

I could try using a stored proc I guess, but I'm not convinced that is the problem. The only thing I found on the internet was a posting on a forum saying that .NET2 was very broken when connecting to datasources and quoted the same error message , but there was no answer :(

Yeah I saw that posting as well.

I prefer Stored procedures for many reasons, the 2 biggest being security and speed.

I think it will work around the problem but I agree that is not the actual problem.

For the actual problem though since my brain has finally decided to join me :) (It's been a while)

What the SqlDataSource.Update() command does is execute the UpdateCommand setup INSIDE your SqlDataSource. The way you are trying to create it on the codebehind at the moment is incorrect.

The param's need to be specified seperately.

Take a look here
http://msdn2.microsoft.com/en-US/library/system.web.ui.webcontrols.sqldatasource.update(VS.80).aspx

If you want it to work in the codebehind try this

string mysql = "UPDATE supplier_details SET clicks = (clicks + 1) WHERE user_ID = @myVal"
EOLUpdate.UpdateCommand = mysql
EOLUpdate.UpdateParameters.Add("@myVal", GridView1.SelectedDataKey.Value)
EOLUpdate.Update()

That should work

I'm gald your brain has joined you : )

I've just tried your suggestion and believe it or not I get  the same error. :(

this is the full error:

System.Data.SqlClient.SqlException was unhandled by user code
  Class=15
  ErrorCode=-2146232060
  LineNumber=1
  Message="Line 1: Incorrect syntax near '('."
  Number=170
  Procedure=""
  Server="TC"
  Source=".Net SqlClient Data Provider"
  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.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
       at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
       at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
       at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
       at System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation)
       at System.Web.UI.WebControls.SqlDataSourceView.ExecuteUpdate(IDictionary keys, IDictionary values, IDictionary oldValues)
       at System.Web.UI.WebControls.SqlDataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues)
       at System.Web.UI.WebControls.SqlDataSource.Update()
       at _Default.GridView1_SelectedIndexChanged(Object sender, EventArgs e) in W:\EOLNET\Default.aspx.vb:line 47
       at System.Web.UI.WebControls.GridView.OnSelectedIndexChanged(EventArgs e)
       at System.Web.UI.WebControls.GridView.HandleSelect(Int32 rowIndex)
       at System.Web.UI.WebControls.GridView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup)
       at System.Web.UI.WebControls.GridView.RaisePostBackEvent(String eventArgument)
       at System.Web.UI.WebControls.GridView.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
       at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
       at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
       at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

Hmm wouldn't this be a different error?

This looks like a simple syntax error.

Let's check the SQL again.... try this

UPDATE supplier_details SET clicks = CONVERT(int, clicks) + 1 WHERE user_ID = @myVal

Could you post your new code as well if this fails.

       Dim myval, mysql As String
         mysql = "UPDATE supplier_details SET clicks = CONVERT(int, clicks) + 1 WHERE user_ID = @myVal"
        EOLUpdate.UpdateParameters.Add("@myVal", GridView1.SelectedDataKey.Value)
        EOLUpdate.UpdateCommand = mysql

We are getting somewhere it seems, I get a different error :)

Must declare the variable '@myVal'.

when the codes runs. Not sure how to do this as dim myVal as string doesn't work nor does dim myVal as parameter or dim @myVal  the @ is not allowed :(

Yeah it works !!!!

Final code is

        Dim mysql As String

        mysql = "UPDATE supplier_details SET clicks = CONVERT(int, clicks) + 1 WHERE user_ID = @myVal"
        EOLUpdate.UpdateParameters.Add("myVal", GridView1.SelectedDataKey.Value)
        EOLUpdate.UpdateCommand = mysql
        EOLUpdate.UpdateCommandType = SqlDataSourceCommandType.Text
        EOLUpdate.Update()

Thanks for all your help.

The reason for the code being in the code behind page was that I wanted this code to run when the user opened up a details page on a particular record and so recorded the number of times the detail page was viewed so I put the code in the sub for _selectedIndexChanged event for the grid view.

I will assign max points to your answer, thanks for all the hard work, I wish I had a brain like yours

Mark Newton

Hey I am just glad my brain finally decided to wake up :)

Glad to help...