?
Solved

ASP .NET2 Updating SQL Record

Posted on 2006-04-19
16
Medium Priority
?
1,641 Views
Last Modified: 2008-01-09
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

         EOLUpdate.Update()

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  :(

0
Comment
Question by:mrnmnew
16 Comments
 
LVL 15

Expert Comment

by:GavinMannion
ID: 16485780
You are missing a bracket

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

Author Comment

by:mrnmnew
ID: 16485826
sorry that was me and my cut and pasting, the bracket is there in the real code.

Sorry about that
0
 
LVL 11

Expert Comment

by:Ramesh Srinivas
ID: 16485836
Remove all the brackets and try it!
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 7

Expert Comment

by:irps20001
ID: 16486259
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)


0
 

Author Comment

by:mrnmnew
ID: 16486959
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.

0
 
LVL 15

Expert Comment

by:GavinMannion
ID: 16487195
Are you trying to update a database or a DataTable in asp.net?

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

Author Comment

by:mrnmnew
ID: 16488433
EOLUpdate is a SQLDataSource object

The only index is one on the key field called line_number
0
 
LVL 15

Expert Comment

by:GavinMannion
ID: 16495641
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?
0
 

Author Comment

by:mrnmnew
ID: 16496051
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 :(

0
 
LVL 15

Expert Comment

by:GavinMannion
ID: 16496101
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



0
 

Author Comment

by:mrnmnew
ID: 16496552
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)
0
 
LVL 15

Expert Comment

by:GavinMannion
ID: 16496581
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.
0
 

Author Comment

by:mrnmnew
ID: 16497233
       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 :(

0
 
LVL 15

Accepted Solution

by:
GavinMannion earned 2000 total points
ID: 16497463
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">
                    <UpdateParameters>
                                <asp:ControlParameter ControlID="GridView1" Name="myVal" PropertyName="SelectedDataKey.Value" />
                    </UpdateParameters>
                </asp:SqlDataSource>
0
 

Author Comment

by:mrnmnew
ID: 16497614
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


0
 
LVL 15

Expert Comment

by:GavinMannion
ID: 16500491
Hey I am just glad my brain finally decided to wake up :)

Glad to help...
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In an ASP.NET application, I faced some technical problems. In this article, I list them out and show the solutions that I found.  I hope it will be useful. Problem: After closing a pop-up window, the parent page should be refreshed automaticall…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question