We help IT Professionals succeed at work.

subtract value from database field with update

Chris Jones
Chris Jones asked
on
322 Views
Last Modified: 2012-05-05
i have a field in my database that is an INT and i want to subtract 1 from it everytime someone registers how can i do this with a query update
Comment
Watch Question

Jaime OlivaresSoftware Architect
CERTIFIED EXPERT
Top Expert 2008

Commented:
You need a query string like this:

UPDATE YOURTABLE SET YOURFIELD=YOURFIELD-1 WHERE (SOME CONDITION HERE)

Do you need the vb code portion?
Chris JonesLead Application Web Developer

Author

Commented:
ok can you help me with this

why did this not subtract my value

Dim up As New SqlCommand("UPDATE CHRTR SET REMSEAT = REMSEAT-1 WHERE CourseID=@CourseID", conn)
up.Parameters.Add("@CourseID", Data.SqlDbType.Int).Value = ID
 conn.Open()
cmd.ExecuteNonQuery()

Commented:
Dim sSQL as String = "UPDATE CHRTR SET REMSEAT = REMSEAT-1 WHERE CourseID=@CourseID"
cmd.CommandText = sSQL
cmd.Parameters.Add("@CourseID", Data.SqlDbType.Int).Value = ID
 conn.Open()
cmd.ExecuteNonQuery()
Chris JonesLead Application Web Developer

Author

Commented:
hmmm, it still did not work

Commented:
Dim cmd as New SQLCommand
Dim sSQL as String = "UPDATE CHRTR SET REMSEAT = REMSEAT-1 WHERE CourseID=@CourseID"
cmd.Connection = conn
cmd.CommandText = sSQL
cmd.Parameters.Add("@CourseID", Data.SqlDbType.Int).Value = ID
 conn.Open()
cmd.ExecuteNonQuery()
Chris JonesLead Application Web Developer

Author

Commented:
ok here is my code maybe this will help
    Protected Sub RegisterCourse(ByVal sender As Object, ByVal e As RepeaterCommandEventArgs)
        If e.CommandName = "Cancel" Then
            Dim ID As Integer = CType(e.CommandArgument, Integer)
            'Register the course here
            Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings("TCPRODConnectionString3").ConnectionString)
                Dim cmd As New SqlCommand("UPDATE Registrations SET CancelledTime=getdate() WHERE CancelledDate IS NULL AND UserID=@UserID AND CourseID=@CourseID", conn)
                cmd.Parameters.Add("@UserID", Data.SqlDbType.Int).Value = Session("CWID")
                cmd.Parameters.Add("@CourseID", Data.SqlDbType.Int).Value = ID
                Dim sSQL As String = "UPDATE CHRTR SET REMSEAT = REMSEAT-1 WHERE CourseID=@CourseID"
                cmd.CommandText = sSQL
                cmd.Parameters.Add("@CourseID", Data.SqlDbType.Int).Value = ID
                conn.Open()
                cmd.ExecuteNonQuery()
                CourseTable.DataBind()
            End Using
        End If
    End Sub

Open in new window

Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Chris JonesLead Application Web Developer

Author

Commented:
ok i do not get an error but it only works when the user cancels a clas from my cancel link how can i make it were it will subtract the class on page load
   Protected Sub RegisterCourse(ByVal sender As Object, ByVal e As RepeaterCommandEventArgs)
        If e.CommandName = "Cancel" Then
            Dim ID As Integer = CType(e.CommandArgument, Integer)
            'Register the course here
            Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings("TCPRODConnectionString3").ConnectionString)
                Dim cmd As New SqlCommand("UPDATE Registrations SET CancelledTime=getdate() WHERE CancelledDate IS NULL AND UserID=@UserID AND CourseID=@CourseID", conn)
                cmd.Parameters.Add("@UserID", Data.SqlDbType.Int).Value = Session("CWID")
                cmd.Parameters.Add("@CourseID", Data.SqlDbType.Int).Value = ID
                conn.Open()
                cmd.ExecuteNonQuery()
                cmd.Parameters.Clear()
                Dim sSQL As String = "UPDATE CHRTR SET REMSEAT = REMSEAT-1 WHERE ID=@CourseID"
                cmd.CommandText = sSQL
                cmd.Parameters.Add("@CourseID", Data.SqlDbType.Int).Value = ID
                cmd.ExecuteNonQuery()
                conn.Close()
                CourseTable.DataBind()
            End Using
        End If
    End Sub

Open in new window

Chris JonesLead Application Web Developer

Author

Commented:
ok i fixed it thanks for your help
Chris JonesLead Application Web Developer

Author

Commented:
Great answer fast respons works with the user

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.