ASP .NET2 Updating SQL Record

I want to update a field in a record when the users selects that record. This is to record the number of 'clicks' a record gets. It sounds like it should be simple enough, but I am using ASP .NET2 and I am struggling. My code is:

      Dim myval, mysql As String

        myval = GridView1.SelectedDataKey.Value

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


but this returns an error 'In order to evaluate an indexed property, the property must be qualified and the arguments must be explicitly supplied by the user'

I have trawled the help pages and tried several versions of the code, but can not get things to work  :(

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

You are missing a bracket

  mysql = "UPDATE supplier_details SET(clicks = (clicks + 1)    )     WHERE (user_ID = '" + myval + "')"
mrnmnewAuthor Commented:
sorry that was me and my cut and pasting, the bracket is there in the real code.

Sorry about that
Ramesh SrinivasTechnical ConsultantCommented:
Remove all the brackets and try it!
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

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)

mrnmnewAuthor Commented:
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

Make sure that [clicks] has no indexes on it.
mrnmnewAuthor Commented:
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?
mrnmnewAuthor Commented:
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

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)

That should work

mrnmnewAuthor Commented:
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
  Message="Line 1: Incorrect syntax near '('."
  Source=".Net SqlClient Data Provider"
       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.
mrnmnewAuthor Commented:
       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 :(

Okay so you are getting this error on the EOLUpdate.Update() line I presume?

Try adding

EOLUpdate.UpdateCommandType = SqlDataSourceCommandType.Text before the update.
and also change the param add to
EOLUpdate.UpdateParameters.Add("myVal", GridView1.SelectedDataKey.Value)

From everywhere I have seen they generally use the SqlDataSource object in Design time and not in codebehind.
Is there a reason you don't do this?

Basically it would look like this.
<asp:SqlDataSource ID="EOLUpdate" runat="server" UpdateCommand="UPDATE supplier_details SET clicks = CONVERT(int, clicks) + 1 WHERE user_ID = @myVal">
                                <asp:ControlParameter ControlID="GridView1" Name="myVal" PropertyName="SelectedDataKey.Value" />

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mrnmnewAuthor Commented:
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

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...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.