Posted on 2009-04-19
Last Modified: 2012-05-06
Have troble with the below SQL UPDATE when i run it i get the error at the sqlException was caught Incorrect syntax near '('.
i have been looking at it for the last 20mins and can not find the problem. i would be thankfull for any help.

Useing Visual Studo 2008 with SQL express 2005
Protected Sub IncomeTaxBUT_Click(ByVal sender As Object, ByVal e As EventArgs) Handles IncomeTaxBUT.Click

        Dim EclipesDBDataSource As New SqlDataSource()

        EclipesDBDataSource.ConnectionString = ConfigurationManager.ConnectionStrings("EclipseDBConnectionString1").ToString()

        EclipesDBDataSource.UpdateCommandType = SqlDataSourceCommandType.Text

        EclipesDBDataSource.InsertCommand = "UPDATE Buget SET (UserID, IncomePersonalAllowance, StartingRate, BasicRate, HigherRate, StartingRateThreshold, BasicRateThreshold, HigherRateThreshold) VALUES (@UserID, @IncomePersonalAllowance, @StartingRate, @BasicRate, @HigherRate, @StartingRateThreshold, @BasicRateThreshold, @HigherRateThreshold) WHERE UserID='" & Session("User_ID_S") & "' "

        EclipesDBDataSource.InsertParameters.Add("UserID", Session("User_ID_S"))

        EclipesDBDataSource.InsertParameters.Add("IncomePersonalAllowance", PersonalAllowanceTB.Text)

        EclipesDBDataSource.InsertParameters.Add("StartingRate", StartingRateTB.Text)

        EclipesDBDataSource.InsertParameters.Add("BasicRate", BasicRateTB.Text)

        EclipesDBDataSource.InsertParameters.Add("HigherRate", HigherRateTB.Text)

        EclipesDBDataSource.InsertParameters.Add("StartingRateThreshold", StartingRateThresholdTB.Text)

        EclipesDBDataSource.InsertParameters.Add("BasicRateThreshold", BasicRateThresholdTB.Text)

        EclipesDBDataSource.InsertParameters.Add("HigherRateThreshold", HigherRateThresholdTB.Text)

        Dim rowsAffected As Integer = 0


            rowsAffected = EclipesDBDataSource.Insert()

        Catch ex As Exception

            ' TODO: work out how to wirte to windows error log



            EclipesDBDataSource = Nothing

        End Try

End Sub

Open in new window

Question by:Ruzbuk
    LVL 39

    Accepted Solution

    That syntax is not valid for updates, only for inserts. Try this:
    EclipesDBDataSource.InsertCommand = "UPDATE Buget SET UserID=@UserID, IncomePersonalAllowance=@IncomePersonalAllowance,
      StartingRate=@StartingRate, BasicRate=@BasicRate, HigherRate=@HigherRate,
      StartingRateThreshold=@StartingRateThreshold, BasicRateThreshold=@BasicRateThreshold,
      HigherRateThreshold=@HigherRateThreshold WHERE UserID='" & Session("User_ID_S") & "' "

    Open in new window

    LVL 1

    Assisted Solution

    Hi Ruzbuk,

    It looks like that you are using code for inserting data not updating data :-)

    Try to change the following lines

    Change line:
    EclipesDBDataSource.InsertCommand = "UPDATE Buget SET (UserID, IncomePersonalAllowance, StartingRate, BasicRate, HigherRate, StartingRateThreshold, BasicRateThreshold, HigherRateThreshold) VALUES (@UserID, @IncomePersonalAllowance, @StartingRate, @BasicRate, @HigherRate, @StartingRateThreshold, @BasicRateThreshold, @HigherRateThreshold) WHERE UserID='" & Session("User_ID_S") & "' "


    EclipesDBDataSource.UpdateCommand = UPDATE Buget SET UserID = @UserID, IncomePersonalAllowance = @IncomePersonalAllowance, StartingRate = @StartingRate, BasicRate = @BasicRate, HigherRate = @HigherRate, StartingRateThreshold = @StartingRateThreshold, BasicRateThreshold = @BasicRateThreshold, HigherRateThreshold = @HigherRateThreshold WHERE UserID='" & Session("User_ID_S") & "' "


    rowsAffected = EclipesDBDataSource.Insert()


    rowsAffected = EclipesDBDataSource.Update()



    Author Comment

    Hi cxr

    Your syntax got me through with out an error but i am now having the troble that the datebase is not being updated. i know that my .txt fields have values as the code is run and that the connecton to my datebase is good.
    Is there somthing else wronge with this bit of code or is this a unrelated to the above code
    LVL 39

    Expert Comment

    by:Roger Baklund
    Did you follow Hendrik's advice and change this:

    rowsAffected = EclipesDBDataSource.Insert()


    rowsAffected = EclipesDBDataSource.Update()

    I'm not a ASP coder, I don't have any other suggestions.

    Author Comment

    Sorry for late replay i am new to and SQL my self but my other UPDATE SQL i used
    rowsAffected = EclipesDBDataSource.Insert() and they work fine.

    When i do change the code as  hendrik_johns says to i get a error saying.
    Must declare the scalar variable "@UserID".


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    User art_snob ( encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now