[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 146
  • Last Modified:

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

0
Ruzbuk
Asked:
Ruzbuk
  • 2
  • 2
2 Solutions
 
Roger BaklundCommented:
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

0
 
hendrik_johnsCommented:
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") & "' "

to

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") & "' "


and

rowsAffected = EclipesDBDataSource.Insert()

to

rowsAffected = EclipesDBDataSource.Update()

Cheers,

Hendrik
0
 
RuzbukAuthor Commented:
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
0
 
Roger BaklundCommented:
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.
0
 
RuzbukAuthor Commented:
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".


0

Featured Post

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.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now