asked on

SQL UPDATE PROBLEM

Hi
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
 
        Try
            rowsAffected = EclipesDBDataSource.Insert()
 
 
 
        Catch ex As Exception
 
            ' TODO: work out how to wirte to windows error log
            Server.Transfer("Error.aspx")
 
        Finally
            EclipesDBDataSource = Nothing
        End Try
End Sub

Open in new window

ASKER CERTIFIED SOLUTION

Roger Baklund

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

hendrik_johns

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ruzbuk

ASKER

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

Roger Baklund

Did you follow Hendrik's advice and change this:

rowsAffected = EclipesDBDataSource.Insert()

to

rowsAffected = EclipesDBDataSource.Update()

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

Ruzbuk

ASKER

Sorry for late replay i am new to asp.net 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".